B1ECM

5.2.2.1.1 HANA Syntax

Aktivität – Ablage

SELECT (
    CASE 
        WHEN T2."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T2."CardType" = 'C' THEN 'Kunden' 
        WHEN T2."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", UPPER(LEFT(T2."CardName", 1)) AS "A", T2."CardCode" AS "CARDCODE", 
    T2."CardName" AS "CARDNAME", T1."ClgCode" AS "CLGCODE", (
    CASE 
        WHEN T1."Action" = 'C' THEN 'Telefonat' 
        WHEN T1."Action" = 'M' THEN 'Besprechung' 
        WHEN T1."Action" = 'T' THEN 'Aufgabe' 
        WHEN T1."Action" = 'E' THEN 'Notiz' 
        WHEN T1."Action" = 'P' THEN 'Kampagne' 
        WHEN T1."Action" = 'N' THEN 'Sonstige' 
    END) AS "ACTION", 
    (SELECT IFNULL("Name", '') 
    FROM OCLT 
    WHERE "Code" = T1."CntctCode") AS "ACTIONTYPE", 
    (SELECT IFNULL("Name", '') 
    FROM OCLS 
    WHERE "Code" = T1."CntctSbjct") AS "SUBJECT", IFNULL(T1."Details", '') AS "DETAILS", 'Aktivitaeten' AS "DOCTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OCLG T1 
    INNER JOIN OCRD T2 ON T1."CardCode" = T2."CardCode" 
WHERE T1."ClgCode" = [%Key];

Aktivität – Suche

SELECT T0."ClgCode" AS "CLGCODE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OCLG T0 
WHERE T0."ClgCode" = [%Key];

Anlagenstamm – Ablage

SELECT UPPER(LEFT(T0."ItemCode", 2)) AS "I2", T0."ItemCode" AS "ITEMCODE", T0."ItemName" AS "ITEMNAME", T1."ItmsGrpNam" AS "ITEMGRP", (
    CASE 
        WHEN T0."ItemType" = 'F' THEN 'Anlagenstammdaten' 
        ELSE 'Artikelstammdaten' 
    END) AS "ITEMDOCTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OITM T0 
    LEFT OUTER JOIN OITB T1 ON T0."ItmsGrpCod" = T1."ItmsGrpCod" 
WHERE T0."ItemCode" = '[%Key]';

Anlagenstamm – Suche

SELECT "ItemCode" AS "ITEMCODE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OITM 
WHERE "ItemCode" = '[%Key]';

ATM Partnerauftrag – Ablage

SELECT UPPER(LEFT(T0."U_Cardname", 1)) AS "A", 'Partnerauftrag' AS "DOCTYPE", 'Partnerauftrag' AS "SDOCTYPE", 
    '' AS "PDOCTYPE", (
    CASE 
        WHEN T1."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T1."CardType" = 'C' THEN 'Kunden' 
        WHEN T1."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocNum" AS "DOCNUM", T0."U_Cardname" AS "CARDNAME", T0."U_Cardcode" AS "CARDCODE", 
    '' AS "NUMATCARD", T0."CreateDate" AS "DOCDATE", RIGHT(100 + MONTH(T0."CreateDate"), 2) AS "MONAT", 
    YEAR(T0."CreateDate") AS "JAHR", T0."U_Project" AS "PRJCODE", T3."PrjName" AS "PRJNAME", 
    IFNULL(T1."ZipCode", '') AS "ZIPCODE", IFNULL(T1."City", '') AS "CITY", IFNULL(T2."Name", '') AS "COUNTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM "@IAG_RAT0" T0 
    LEFT OUTER JOIN OCRD T1 ON T0."U_Cardcode" = T1."CardCode" 
    LEFT OUTER JOIN OCRY T2 ON T1."Country" = T2."Code" 
    LEFT OUTER JOIN OPRJ T3 ON T0."U_Project" = T3."PrjCode" 
WHERE T0."DocEntry" = '[%Key]';

ATM Partnerauftrag – Suche

SELECT (
    CASE 
        WHEN T0."Object" = 'IAG_RAT' THEN 'Partnerauftrag' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."U_Cardcode" AS "CARDCODE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM "@IAG_RAT0" T0 
WHERE T0."DocEntry" = '[%Key]';

ATM Warenausgang- Ablage

SELECT UPPER(LEFT(T0."U_Cardname", 1)) AS "A", 'ATM-Warenausgang' AS "DOCTYPE", 'ATM-Warenausgang' AS "SDOCTYPE", 
    '' AS "PDOCTYPE", (
    CASE 
        WHEN T1."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T1."CardType" = 'C' THEN 'Kunden' 
        WHEN T1."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocNum" AS "DOCNUM", T0."U_Cardname" AS "CARDNAME", T0."U_Cardcode" AS "CARDCODE", 
    '' AS "NUMATCARD", T0."U_Spddat" AS "DOCDATE", RIGHT(100 + MONTH(T0."U_Spddat"), 2) AS "MONAT", 
    YEAR(T0."U_Spddat") AS "JAHR", '' AS "PRJCODE", '' AS "PRJNAME", IFNULL(T1."ZipCode", '') AS "ZIPCODE", 
    IFNULL(T1."City", '') AS "CITY", IFNULL(T2."Name", '') AS "COUNTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM "@IAG_WAS0" T0 
    LEFT OUTER JOIN OCRD T1 ON T0."U_Cardcode" = T1."CardCode" 
    LEFT OUTER JOIN OCRY T2 ON T1."Country" = T2."Code" 
WHERE T0."DocEntry" = [%Key];

ATM Warenausgang – Suche

SELECT (
    CASE 
        WHEN T0."Object" = 'IAG_WAS' THEN 'ATM-Warenausgang' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."U_Cardcode" AS "CARDCODE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM "@IAG_WAS0" T0 
WHERE T0."DocEntry" = [%Key];

Ausgangszahlung – Ablage

SELECT UPPER(LEFT(T0."CardName", 1)) AS "A", 'Ausgangszahlung' AS "DOCTYPE", 'Ausgangszahlung' AS "PDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Mitarbeiter' 
        WHEN T2."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T2."CardType" = 'C' THEN 'Kunden' 
        WHEN T2."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocNum" AS "DOCNUM", T0."CardName" AS "CARDNAME", T0."CardCode" AS "CARDCODE", 
    T0."DocDate" AS "DOCDATE", RIGHT(100 + MONTH(T0."DocDate"), 2) AS "MONTH", YEAR(T0."DocDate") AS "YEAR", 
    IFNULL(T2."ZipCode", '') AS "ZIPCODE", IFNULL(T2."City", '') AS "CITY", IFNULL(T3."Name", '') AS "COUNTRY", 
    '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OVPM T0 
    LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode" 
    LEFT OUTER JOIN OCRY T3 ON T2."Country" = T3."Code" 
WHERE T0."DocEntry" = '[%Key]';

Ausgangszahlung – Suche

SELECT 'Ausgangszahlung' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."CardCode" AS "CARDCODE", 
    T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OVPM T0 
WHERE T0."DocEntry" = [%Key];

Chargendetails – Ablage

SELECT UPPER(LEFT(T1."ItemCode", 2)) AS "I2", T1."ItemCode" AS "ITEMCODE", T1."ItemName" AS "ITEMNAME", 
    T2."ItmsGrpNam" AS "ITEMGRP", (
    CASE 
        WHEN T1."ItemType" = 'F' THEN 'Anlagenstammdaten' 
        ELSE 'Artikelstammdaten' 
    END) AS "ITEMDOCTYPE", T0."DistNumber" AS "BATCH", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OBTN T0 
    INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode" 
    LEFT OUTER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod" 
WHERE T0."DistNumber" = '[%Key]' AND T0."ItemCode" = '[%ItemCode]';

Chargendetails – Suche

SELECT "DistNumber" AS "BATCH", "ItemCode" AS "ITEMCODE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OBTN 
WHERE "DistNumber" = '[%Key]' AND "ItemCode" = '[%ItemCode]';

Rahmenvetrag – Ablage

SELECT (
    CASE 
        WHEN T0."BpType" = 'C' THEN 'Kunden' 
        WHEN T0."BpType" = 'S' THEN 'Lieferanten' 
    END) AS "CARDTYPE", UPPER(LEFT(T0."BpName", 1)) AS "A", (
    CASE 
        WHEN T0."ObjType" = '1250000025' AND T0."BpType" = 'C' THEN 'Rahmenvertrag - Verkauf' 
        WHEN T0."ObjType" = '1250000025' AND T0."BpType" = 'S' THEN 'Rahmenvertrag - Einkauf' 
    END) AS "BADOCTYPE", T0."Number" AS "DOCNUM", T0."BpName" AS "CARDNAME", T0."BpCode" AS "CARDCODE", 
    T0."StartDate" AS "STARTDATE", T0."EndDate" AS "ENDDATE", T1."ZipCode" AS "ZIPCODE", T1."City" AS "CITY", 
    T2."Name" AS "COUNTRY", RIGHT(100 + MONTH(T0."SignDate"), 2) AS "MONTH", YEAR(T0."SignDate") AS "YEAR", 
    T0."Descript" AS "DESCRIPTION", 'Rahmenvertrag' AS "DOCTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE", T0."AbsID" AS "ABSID" 
FROM OOAT T0 
    LEFT OUTER JOIN OCRD T1 ON T0."BpCode" = T1."CardCode" 
    LEFT OUTER JOIN OCRY T2 ON T1."Country" = T2."Code" 
WHERE T0."AbsID" = [%Key];

Rahmenvertrag – Suche

SELECT (
    CASE 
        WHEN T0."ObjType" = '1250000025' AND T0."BpType" = 'C' THEN 'Rahmenvertrag - Verkauf' 
        WHEN T0."ObjType" = '1250000025' AND T0."BpType" = 'S' THEN 'Rahmenvertrag - Einkauf' 
    END) AS "BADOCTYPE", T0."Number" AS "DOCNUM", T0."AbsID" AS "ABSID", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OOAT T0 
WHERE T0."AbsID" = [%Key];

Geschäftspartner – Ablage

SELECT UPPER(LEFT(T0."CardName", 1)) AS "A", T0."CardName" AS "CARDNAME", T0."CardCode" AS "CARDCODE", (
    CASE 
        WHEN T0."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T0."CardType" = 'C' THEN 'Kunden' 
        WHEN T0."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", IFNULL(T0."ZipCode", '') AS "ZIPCODE", IFNULL(T0."City", '') AS "CITY", 
    IFNULL(T1."Name", '') AS "COUNTRY", 'Geschäftspartner-Stammdaten' AS "CARDDOCTYPE", T0."ProjectCod" AS "PRJCODE", 
    T2."PrjName" AS "PRJNAME", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OCRD T0 
    LEFT OUTER JOIN OCRY T1 ON T0."Country" = T1."Code" 
    LEFT OUTER JOIN OPRJ T2 ON T0."ProjectCod" = T2."PrjCode" 
WHERE T0."CardCode" = '[%Key]';

Geschäftspartner – Suche

SELECT T0."CardCode" AS "CARDCODE", (
    CASE 
        WHEN T0."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T0."CardType" = 'C' THEN 'Kunden' 
        WHEN T0."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OCRD T0 
WHERE T0."CardCode" = '[%Key]';

Schecks zur Zahlung – Ablage

SELECT 
	 UPPER(LEFT(T0."VendorName", 1)) AS "A",
	'Schecks zur Zahlung' AS "DOCTYPE", 
	'Schecks zur Zahlung' AS "PDOCTYPE", 
	'Lieferanten' AS "CARDTYPE",
	T0."PmntNum" AS "DOCNUM", 
	T0."VendorName" AS "CARDNAME", 
	T0."VendorCode" AS "CARDCODE",
	T0."PmntDate" AS "DOCDATE",
	RIGHT(100 + MONTH(T0."PmntDate"), 2) AS "MONTH", YEAR(T0."PmntDate") AS "YEAR", 
    IFNULL(T2."ZipCode", '') AS "ZIPCODE",
	IFNULL(T2."City", '') AS "CITY", 
	IFNULL(T3."Name", '') AS "COUNTRY",
    '[%Source]' AS "SOURCE",
	T0."CheckKey" AS "DOCENTRY",
	CURRENT_SCHEMA AS "SBO_DB_NAME",
	(SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME",
	T0."ObjType" AS "SBO_OBJTYPE"  
FROM 
	OCHO T0
LEFT JOIN 
	OCRD T2 ON T2."CardCode"=T0."VendorCode"
LEFT JOIN
	OCRY T3 ON T2."Country"=T3."Code"	
WHERE T0.CheckKey= '[%Key]'

Schecks zur Zahlung – Suche

SELECT 
	'Schecks zur Zahlung' AS "DOCTYPE", 
	'Schecks zur Zahlung' AS "PDOCTYPE", 
	T0."PmntNum" AS "DOCNUM", 
	T0."VendorName" AS "CARDNAME", 
	T0."VendorCode" AS "CARDCODE",
	T0."PmntDate" AS "DOCDATE",
	T0."CheckKey" AS "DOCENTRY",
	CURRENT_SCHEMA AS "SBO_DB_NAME",
	(SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME",
	T0."ObjType" AS "SBO_OBJTYPE"  
FROM 
	OCHO T0
LEFT JOIN 
	OCRD T2 ON T2."CardCode"=T0."VendorCode"	
WHERE T0.CheckKey= '[%Key]'

Einreichung/Deposit – Ablage

SELECT 
	 UPPER(LEFT(T1."AcctName", 1)) AS "A",
	'Einreichung' AS "DOCTYPE", 
	'Einreichung' AS "SDOCTYPE", 
	(CASE
		WHEN T0."DeposType" = 'K' THEN 'Scheck'
		WHEN T0."DeposType" = 'V' THEN 'Kreditkarte'
		WHEN T0."DeposType" = 'C' THEN 'Bar'
	END	) AS "CARDTYPE",
	T1."AcctName" AS "CARDNAME",
	T0."DeposNum" AS "DOCNUM", 
    '[%Source]' AS "SOURCE",
	T0."DeposId" AS "DOCENTRY",
	CURRENT_SCHEMA AS "SBO_DB_NAME",
	(SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME",
	T0."ObjType" AS "SBO_OBJTYPE" 
FROM 
	ODPS T0
	LEFT JOIN OACT T1 ON T0."BanckAcct" = T1."AcctCode"
WHERE T0."DeposId"= '[%Key]'

Einreichung/Deposit – Suche

SELECT 'Einreichung' AS "DOCTYPE", T1."AcctName" AS "CARDNAME", T0."DeposNum" AS "DOCNUM", T0."DeposId" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM ODPS T0 
    LEFT OUTER JOIN OACT T1 ON T0."BanckAcct" = T1."AcctCode" 
WHERE T0."DeposId" = '[%Key]';

Mahnungen – Ablage

SELECT T0."CardCode" AS "CARDCODE", T2."CardName" AS "CARDNAME", UPPER(LEFT(T2."CardName", 1)) AS "A", 
    'Mahnung' AS "DOCTYPE", 
    CASE 
        WHEN MAX(T0."EDunLevel") <= 1 THEN 'Zahlungserinnerung' 
        WHEN MAX(T0."EDunLevel") = 2 THEN '2. Mahnung' 
        WHEN MAX(T0."EDunLevel") = 3 THEN '3. Mahnung' 
        WHEN MAX(T0."EDunLevel") >= 4 THEN 'Letzte Mahnung' 
    END AS "DOCNUM", MAX(T2."DunnDate") AS "DOCDATE", T0."DocNum" AS "INVDOCNUMS", RIGHT(100 +
     MONTH(MAX(T2."DunnDate")), 2) AS "MONTH", YEAR(MAX(T2."DunnDate")) AS "YEAR", 
    IFNULL(T2."ZipCode", '') AS "ZIPCODE", IFNULL(T2."City", '') AS "CITY", IFNULL(T3."Name", '') AS "COUNTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM "DWZ2" T0 
    INNER JOIN "ODWZ" T1 ON T0."WizardId" = T1."WizardId" 
    INNER JOIN "OCRD" T2 ON T0."CardCode" = T2."CardCode" 
    LEFT OUTER JOIN "OCRY" T3 ON T2."Country" = T3."Code" 
WHERE T1."WizardId" = [%dunnId] AND T0."CardCode" = '[%cardCode]' 
GROUP BY T0."DocNum", T0."CardCode", T2."CardName", T2."ZipCode", T2."City", T3."Name" 
ORDER BY T0."CardCode";

Mitarbeiter – Ablage

SELECT 'Personal' AS "DOCTYPE", UPPER(LEFT(T0."lastName", 1)) AS "A", 
    T0."lastName" || ', ' || T0."firstName" || ' - ' || CAST(T0."empID" AS varchar(11)) AS "EMPNAME", 
    T0."empID" AS "EMPID", T0."lastName" AS "LASTNAME", T0."firstName" AS "FIRSTNAME", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OHEM T0 
WHERE T0."empID" = [%Key];

Mitarbeiter – Suche

SELECT T0."empID" AS "EMPID", 'Personal' AS "DOCTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OHEM T0 
WHERE T0."empID" = [%Key];

Lagerverwaltung – Wareneingang – Suche

SELECT 'Wareneingang' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."CardCode" AS "CARDCODE", T0."DocEntry" AS "DOCENTRY", 
    T0."ObjType" AS "SBO_OBJTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OPDN T0 
WHERE T0."DocEntry" = [%Key];

Eingangszahlung – Ablage

SELECT UPPER(LEFT(T0."CardName", 1)) AS "A", 'Eingangszahlung' AS "DOCTYPE", 'Eingangszahlung' AS "sDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Mitarbeiter' 
        WHEN T2."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T2."CardType" = 'C' THEN 'Kunden' 
        WHEN T2."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocNum" AS "DOCNUM", T0."CardName" AS "CARDNAME", T0."CardCode" AS "CARDCODE", 
    T0."DocDate" AS "DOCDATE", RIGHT(100 + MONTH(T0."DocDate"), 2) AS "MONTH", YEAR(T0."DocDate") AS "YEAR", 
    IFNULL(T2."ZipCode", '') AS "ZIPCODE", IFNULL(T2."City", '') AS "CITY", IFNULL(T3."Name", '') AS "COUNTRY", 
    '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM ORCT T0 
    LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode" 
    LEFT OUTER JOIN OCRY T3 ON T2."Country" = T3."Code" 
WHERE T0."DocEntry" = '[%Key]';

Eingangszahlung – Suche

SELECT 'Eingangszahlung' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."CardCode" AS "CARDCODE", 
    T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM ORCT T0 
WHERE T0."DocEntry" = [%Key];

Bestandszählung – Ablage

SELECT 
    CASE 
        WHEN T0."ObjType" = 1470000065 THEN 'Bestandszaehlung' 
        WHEN T0."ObjType" = 10000071 THEN 'Bestandsbuchung' 
    END AS "DOCTYPE", T0."DocNum" AS "DOCNUM", RIGHT(100 + MONTH(T0."CountDate"), 2) AS "MONTH", 
    YEAR(T0."CountDate") AS "YEAR", '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", 
    T0."ObjType" AS "SBO_OBJTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM [%Table] T0 
WHERE T0."DocEntry" = '[%Key]';

Bestandzählung – Suche

SELECT 
    CASE 
        WHEN T0."ObjType" = 1470000065 THEN 'Bestandszaehlung' 
        WHEN T0."ObjType" = 10000071 THEN 'Bestandsbuchung' 
    END AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."DocEntry" AS "DOCENTRY", T0."ObjType" AS "SBO_OBJTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM [%Table] T0 
WHERE T0."DocEntry" = [%Key];

Anfangsbestand – Ablage

SELECT 
    CASE 
        WHEN T0."ObjType" = 310000001 THEN 'Anfangsbestand' 
    END AS "DOCTYPE", T0."DocNum" AS "DOCNUM", RIGHT(100 + MONTH(T0."DocDate"), 2) AS "MONTH", 
    YEAR(T0."DocDate") AS "YEAR", '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", 
    T0."ObjType" AS "SBO_OBJTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM [%Table] T0 
WHERE T0."DocEntry" = '[%Key]';

Anfangsbestand – Suche

SELECT 
    CASE 
        WHEN T0."ObjType" = 310000001 THEN 'Anfangsbestand' 
    END AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."DocEntry" AS "DOCENTRY", T0."ObjType" AS "SBO_OBJTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM [%Table] T0 
WHERE T0."DocEntry" = [%Key];

Journalbuchung – Ablage

SELECT DISTINCT T0."Number" AS "NUMBER", T3."AcctCode" AS "ACCTCODE", T3."AcctName" AS "ACCTNAME", (
    CASE T4."CardType" 
        WHEN 'S' THEN 'Lieferant' 
        WHEN 'C' THEN 'Kunde' 
        WHEN 'L' THEN 'Interessent' 
    END) AS "CARDTYPE", T4."CardCode" AS "CARDCODE", T4."CardName" AS "CARDNAME", T0."RefDate" AS "DOCDATE", 
    RIGHT(100 + MONTH(T0."RefDate"), 2) AS "MONTH", YEAR(T0."RefDate") AS "YEAR", 
    IFNULL(T0."Project", '') AS "PRJCODE", IFNULL(T1."PrjName", '') AS "PRJNAME", T0."TaxDate" AS "REFDATE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OJDT T0 
    LEFT OUTER JOIN JDT1 T2 ON T0."TransId" = T2."TransId" 
    LEFT OUTER JOIN OACT T3 ON T2."ShortName" = T3."AcctCode" 
    LEFT OUTER JOIN OCRD T4 ON T2."ShortName" = T4."CardCode" 
    LEFT OUTER JOIN OPRJ T1 ON T1."PrjCode" = T0."Project" 
WHERE T0."TransId" = [%Key];

Journalbuchung – Suche

SELECT T0."Number" AS "NUMBER", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OJDT T0 
WHERE T0."TransId" = [%Key];

Wareneinstandspreise – Ablage

SELECT UPPER(LEFT(T0."SuppName", 1)) AS "A", 'Wareneinstandspreise' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", 
    T0."SuppName" AS "CARDNAME", T0."CardCode" AS "CARDCODE", T0."DocDate" AS "DOCDATE", RIGHT(100 +
     MONTH(T0."DocDate"), 2) AS "MONTH", YEAR(T0."DocDate") AS "YEAR", '[%Source]' AS "SOURCE", 
    T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OIPF T0 
WHERE T0."DocEntry" = [%Key];

Wareneinstandspreise – Suche

SELECT (
    CASE 
        WHEN T0."ObjType" = '69' THEN 'Wareneinstandspreise' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OIPF T0 
WHERE T0."DocEntry" = [%Key];

Marketing-Belege – Ablage

SELECT (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(LEFT(T0."ReqName", 1)) 
        ELSE UPPER(LEFT(T0."CardName", 1)) 
    END) AS "A", (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
    END) AS "DOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        ELSE '' 
    END) AS "SDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
        ELSE '' 
    END) AS "PDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Mitarbeiter' 
        WHEN T2."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T2."CardType" = 'C' THEN 'Kunden' 
        WHEN T2."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocNum" AS "DOCNUM", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN T0."ReqName" 
        ELSE T0."CardName" 
    END) AS "CARDNAME", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(T0."Requester") 
        ELSE T0."CardCode" 
    END) AS "CARDCODE", IFNULL("NumAtCard", '') AS "NUMATCARD", T0."DocDate" AS "DOCDATE", RIGHT(100 +
     MONTH(T0."DocDate"), 2) AS "MONTH", YEAR(T0."DocDate") AS "YEAR", IFNULL(T0."Project", '') AS "PRJCODE", 
    IFNULL(T1."PrjName", '') AS "PRJNAME", IFNULL(T2."ZipCode", '') AS "ZIPCODE", IFNULL(T2."City", '') AS "CITY", 
    IFNULL(T3."Name", '') AS "COUNTRY", '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM [%Table] T0 
    LEFT OUTER JOIN OPRJ T1 ON T1."PrjCode" = T0."Project" 
    LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode" 
    LEFT OUTER JOIN OCRY T3 ON T2."Country" = T3."Code" 
WHERE T0."DocEntry" = [%Key];

Marketing Belege-Suche

SELECT (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(T0."Requester") 
        ELSE T0."CardCode" 
    END) AS "CARDCODE", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM [%Table] T0 
WHERE T0."DocEntry" = [%Key];

Marketing Belege – Ablage (geparkte Belege)

SELECT (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(LEFT(T0."ReqName", 1)) 
        ELSE UPPER(LEFT(T0."CardName", 1)) 
    END) AS "A", (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
    END) AS "DOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        ELSE '' 
    END) AS "SDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
        ELSE '' 
    END) AS "PDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Mitarbeiter' 
        WHEN T2."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T2."CardType" = 'C' THEN 'Kunden' 
        WHEN T2."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."DocEntry" AS "DOCNUM", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN T0."ReqName" 
        ELSE T0."CardName" 
    END) AS "CARDNAME", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(T0."Requester") 
        ELSE T0."CardCode" 
    END) AS "CARDCODE", IFNULL("NumAtCard", '') AS "NUMATCARD", T0."DocDate" AS "DOCDATE", RIGHT(100 +
     MONTH(T0."DocDate"), 2) AS "MONTH", YEAR(T0."DocDate") AS "YEAR", IFNULL(T0."Project", '') AS "PRJCODE", 
    IFNULL(T1."PrjName", '') AS "PRJNAME", IFNULL(T2."ZipCode", '') AS "ZIPCODE", IFNULL(T2."City", '') AS "CITY", 
    IFNULL(T3."Name", '') AS "COUNTRY", '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", n'DRAFT_' || T0."ObjType" AS "SBO_OBJTYPE" 
FROM ODRF T0 
    LEFT OUTER JOIN OPRJ T1 ON T1."PrjCode" = T0."Project" 
    LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode" 
    LEFT OUTER JOIN OCRY T3 ON T2."Country" = T3."Code" 
WHERE T0."DocEntry" = [%Key];

Marketing Belege – Suche (geparkte Belege)

SELECT (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
    END) AS "DOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '23' THEN 'Angebot' 
        WHEN T0."ObjType" = '17' THEN 'Kundenauftrag' 
        WHEN T0."ObjType" = '15' THEN 'Lieferung' 
        WHEN T0."ObjType" = '16' THEN 'Retoure' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'N' THEN 'Verkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '203' AND T0."CreateTran" = 'Y' THEN 'Verkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '13' AND T0."IsICT" = 'Y' THEN 'Ausgangsrechnung und Zahlung' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'N' THEN 'Ausgangsrechnung' 
        WHEN T0."ObjType" = '14' THEN 'Ausgangsgutschrift' 
        WHEN T0."ObjType" = '13' AND T0."isIns" = 'Y' THEN 'Ausgangsvorausrechnung' 
        WHEN T0."ObjType" = '234000041' THEN 'Retourenanfrage' 
        ELSE '' 
    END) AS "SDOCTYPE", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN 'Bestellanforderung' 
        WHEN T0."ObjType" = '540000006' THEN 'Lieferantenanfrage' 
        WHEN T0."ObjType" = '22' THEN 'Bestellung' 
        WHEN T0."ObjType" = '20' THEN 'Wareneingang' 
        WHEN T0."ObjType" = '21' THEN 'Retoure' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'N' THEN 'Einkaufsanzahlungsanforderung' 
        WHEN T0."ObjType" = '204' AND T0."CreateTran" = 'Y' THEN 'Einkaufsanzahlungsrechnung' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'N' THEN 'Eingangsrechnung' 
        WHEN T0."ObjType" = '19' THEN 'Eingangsgutschrift' 
        WHEN T0."ObjType" = '18' AND T0."isIns" = 'Y' THEN 'Eingangsvorausrechnung' 
        WHEN T0."ObjType" = '234000042' THEN 'Warenretourenanfrage' 
        ELSE '' 
    END) AS "PDOCTYPE", T0."DocEntry" AS "DOCNUM", (
    CASE 
        WHEN T0."ObjType" = '1470000113' THEN UPPER(T0."Requester") 
        ELSE T0."CardCode" 
    END) AS "CARDCODE", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", n'DRAFT_' || T0."ObjType" AS "SBO_OBJTYPE" 
FROM ODRF T0 
WHERE T0."DocEntry" = [%Key];

Opportunity – Ablage

SELECT UPPER(LEFT(T1."CardName", 1)) AS "A", (
    CASE 
        WHEN T1."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T1."CardType" = 'C' THEN 'Kunden' 
        WHEN T1."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T1."CardName" AS "CARDNAME", T1."CardCode" AS "CARDCODE", 'Opportunity' AS "CARDDOCTYPE", 
    T0."OpprId" AS "OPPRID", '[%Source]' AS "SOURCE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OOPR T0 
    LEFT OUTER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" 
WHERE T0."OpprId" = [%Key];

Opportunity – Suche

SELECT "OpprId" AS "OPPRID", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OOPR 
WHERE "OpprId" = '[%Key]';

Kommissionierliste – Ablage

SELECT RIGHT(100 + MONTH(T0."PickDate"), 2) AS "MONTH", YEAR(T0."PickDate") AS "YEAR", '[%Source]' AS "SOURCE", 
    'Kommissionierliste' AS "SBODOCTYPE", 'Kommissionierliste' AS "DOCTYPE", T0."AbsEntry" AS "DOCNUM", 
    T0."AbsEntry" AS "DOCENTRY", T0."PickDate" AS "PICKDATE", T0."OwnerCode" AS "OWNERCODE", 
    IFNULL(T0."OwnerName", T1."U_NAME") AS "OWNERNAME", T0."Name" AS "NAME", 
    CASE 
        WHEN T0."Status" = 'R' THEN 'Freigegeben' 
        WHEN T0."Status" = 'Y' THEN 'Kommissioniert' 
        WHEN T0."Status" = 'P' THEN 'Teilweise kommissioniert' 
        WHEN T0."Status" = 'd' THEN 'Teilweise geliefert' 
        WHEN T0."Status" = 'C' THEN 'Geschlossen' 
    END AS "STATUS", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", CURRENT_SCHEMA AS "SBO_DB_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OPKL T0 
	LEFT JOIN
		OUSR T1 ON T0."OwnerCode" = T1."USERID"
WHERE T0."AbsEntry" = [%Key];

Kommissionierliste – Suche

SELECT 
	'Kommissionierliste' AS "SBODOCTYPE",
	'Kommissionierliste' AS "DOCTYPE",
	T0."AbsEntry" AS "DOCNUM",
	T0."AbsEntry" AS "DOCENTRY",
	T0."PickDate" AS "PICKDATE",
	T0."OwnerCode" AS "OWNERCODE",
	T0."OwnerName" AS "OWNERNAME",
	T0."Name" AS "NAME",
	CASE 
		WHEN T0."Status" = 'R' THEN 'Freigegeben'
		WHEN T0."Status" = 'Y' THEN 'Kommissioniert'
		WHEN T0."Status" = 'P' THEN 'Teilweise kommissioniert'
		WHEN T0."Status" = 'd' THEN 'Teilweise geliefert'
		WHEN T0."Status" = 'C' THEN 'Geschlossen'
	END as "STATUS",
	CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME",
	T0."ObjType" AS "SBO_OBJTYPE"
FROM 
	OPKL T0	
WHERE T0."AbsEntry"= [%Key]

Produktionsauftrag – Ablage

SELECT 'Produktionsauftrag' AS "SBODOCTYPE", 'Produktionsauftrag' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", RIGHT(100 +
     MONTH(T0."PostDate"), 2) AS "MONTH", YEAR(T0."PostDate") AS "YEAR", '[%Source]' AS "SOURCE", 
    T0."DocEntry" AS "DOCENTRY", T0."CardCode" AS "CARDCODE", T1."CardName" AS "CARDNAME", 
    T2."DocNum" AS "ORIGINNUM", T2."DocEntry" AS "ORIGINENTRY", 
    CASE 
        WHEN T0."Status" = 'P' THEN 'Eingeplant' 
        WHEN T0."Status" = 'R' THEN 'Freigegeben' 
        WHEN T0."Status" = 'L' THEN 'Geschlossen' 
        WHEN T0."Status" = 'C' THEN 'Abgebrochen' 
    END AS "STATUS", T0."ItemCode" AS "ITEMCODE", T3."ItemName" AS "ITEMNAME", UPPER(LEFT(T0."ItemCode", 2)) AS "I2", 
    T0."PlannedQty" AS "PLANNEDQTY", T0."Warehouse" AS "WAREHOUSE", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", CURRENT_SCHEMA AS "SBO_DB_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM OWOR T0 
    LEFT OUTER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" 
    LEFT OUTER JOIN ORDR T2 ON T0."OriginAbs" = T2."DocEntry" 
    LEFT OUTER JOIN OITM T3 ON T0."ItemCode" = T3."ItemCode" 
WHERE T0."DocEntry" = [%Key];

Produktionsauftrag – Suche

SELECT 
	'Produktionsauftrag' AS "SBODOCTYPE",
	'Produktionsauftrag' AS "DOCTYPE",
	T0."DocNum" AS "DOCNUM",
	T0."DocEntry" AS "DOCENTRY",
	T0."CardCode" AS "CARDCODE",
	T1."CardName" AS "CARDNAME",
	T2."DocNum" AS "ORIGINNUM",
	T2."DocEntry" AS "ORIGINENTRY",
	CASE 
		WHEN T0."Status" = 'P' THEN 'Eingeplant'
		WHEN T0."Status" = 'R' THEN 'Freigegeben'
		WHEN T0."Status" = 'L' THEN 'Geschlossen'
		WHEN T0."Status" = 'C' THEN 'Abgebrochen'
	END as "STATUS",
	T0."ItemCode" AS "ITEMCODE",
	T3."ItemName" AS "ITEMNAME",
	T0."PlannedQty" AS "PLANNEDQTY",
	T0."Warehouse" AS "WAREHOUSE",
	CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME",
	T0."ObjType" AS "SBO_OBJTYPE"
FROM 
	OWOR T0	
	LEFT JOIN
		OCRD T1 ON T0."CardCode" = T1."CardCode"
	LEFT JOIN
		ORDR T2 ON T0."OriginAbs" = T2."DocEntry"
	LEFT JOIN
		OITM T3 ON T0."ItemCode" = T3."ItemCode"
WHERE T0.DocEntry= [%Key]

Projekt – Ablage

SELECT 
	'Project' AS "SBODOCTYPE",
	'Project' AS "DOCTYPE",
	T0."DocNum" AS "DOCNUM",
	RIGHT(100 + MONTH(T0."START"), 2) AS "MONTH", 
    YEAR(T0."START") AS "YEAR",
	T0."CARDCODE" AS "CARDCODE",
	T0."CARDNAME" AS "CARDNAME",
	T0."NAME" AS "NAME",
	CASE 
		WHEN T0.STATUS = 'S' THEN 'Gestartet'
		WHEN T0.STATUS = 'P' THEN 'Anhalten'
		WHEN T0.STATUS = 'T' THEN 'Gestoppt'
		WHEN T0.STATUS = 'F' THEN 'Fertig'
		WHEN T0.STATUS = 'N' THEN 'Abgebrochen/Storniert'
	END AS "STATUS",
	T0."FIPROJECT" AS "FIPROJECT",
	'"%Source"' AS "SOURCE",
	T0."AbsEntry" AS "DOCENTRY",
    '234000021' AS "SBO_OBJTYPE", 
	CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" FROM OADM) AS "COMPANY_NAME" 
FROM 
	OPMG T0
WHERE T0."AbsEntry" = '[%Key]'

Projekt – Suche

SELECT 'Project' AS "SBODOCTYPE", 'Project' AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0.CARDCODE AS "CARDCODE", 
    T0.CARDNAME AS "CARDNAME", T0.NAME AS "NAME", 
    CASE 
        WHEN T0.STATUS = 'S' THEN 'Gestartet' 
        WHEN T0.STATUS = 'P' THEN 'Anhalten' 
        WHEN T0.STATUS = 'T' THEN 'Gestoppt' 
        WHEN T0.STATUS = 'F' THEN 'Fertig' 
        WHEN T0.STATUS = 'N' THEN 'Abgebrochen/Storniert' 
    END AS "STATUS", T0.FIPROJECT AS "FIPROJECT", T0."AbsEntry" AS "DOCENTRY", '234000021' AS "SBO_OBJTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM OPMG T0 
WHERE T0."AbsEntry" = '[%Key]';

Bestellanforderung – Ablage

SELECT "DocNum" AS "DOCNUM", 'Bestellanforderung' AS "DOCTYPE", "DocEntry" AS "DOCENTRY", "ObjType" AS "SBO_OBJTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OPRQ 
WHERE "DocEntry" = [%Key];

Bestellanforderung – Suche

SELECT "DocNum" AS "DOCNUM", 'Bestellanforderung' AS "DOCTYPE", "DocEntry" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", "ObjType" AS "SBO_OBJTYPE" 
FROM OPRQ 
WHERE "DocEntry" = [%Key];

Bestellanforderung – Ablage (geparkter Beleg)

SELECT "DocNum" AS "DOCNUM", 'Bestellanforderung' AS "DOCTYPE", '[%Source]' AS "SOURCE", "DocEntry" AS "DOCENTRY", 
    "ObjType" AS "SBO_OBJTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM ODRF 
WHERE "DocEntry" = [%Key];

Bestellanforderung – Suche (geparkter Beleg)

SELECT "DocNum" AS "DOCNUM", 'Bestellanforderung' AS "DOCTYPE", "DocEntry" AS "DOCENTRY", "ObjType" AS "SBO_OBJTYPE", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME" 
FROM ODRF 
WHERE "DocEntry" = [%Key];

Serviceabruf – Ablage

SELECT T0."DocNum" AS "DOCNUM", T0."callId" AS "CALLID", 'Serviceauftrag' AS "DOCTYPE", T0."customer" AS "CARDCODE", 
    T0."custmrName" AS "CARDNAME", IFNULL(T1."ZipCode", '') AS "ZIPCODE", IFNULL(T1."City", '') AS "CITY", 
    IFNULL(T2."Name", '') AS "COUNTRY", IFNULL(T0."itemCode", '') AS "ITEMCODE", 
    IFNULL(T0."itemName", '') AS "ITEMNAME", (
    CASE 
        WHEN (T0."manufSN" IS NULL OR T0."manufSN" = '') AND (T0."internalSN" IS NULL OR
         T0."internalSN" = '') THEN '' 
        WHEN (T0."manufSN" IS NULL OR T0."manufSN" = '') THEN T0."internalSN" 
        WHEN (T0."internalSN" IS NULL OR T0."internalSN" = '') THEN T0."manufSN" 
        WHEN (T0."internalSN" IS NOT NULL OR T0."internalSN" = '') AND (T0."manufSN" IS NOT NULL OR
         T0."manufSN" = '') THEN T0."manufSN" || ' || ' || T0."internalSN" 
    END) AS "SERIAL", IFNULL(T0."contractID", '') AS "CONTRACTID", UPPER(LEFT(T1."CardName", 1)) AS "A", (
    CASE 
        WHEN T1."CardType" = 'S' THEN 'Lieferanten' 
        WHEN T1."CardType" = 'C' THEN 'Kunden' 
        WHEN T1."CardType" = 'L' THEN 'Interessenten' 
    END) AS "CARDTYPE", T0."createDate" AS "DOCDATE", RIGHT(100 + MONTH(T0."createDate"), 2) AS "MONTH", 
    YEAR(T0."createDate") AS "YEAR", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OSCL T0 
    LEFT OUTER JOIN OCRD T1 ON T1."CardCode" = T0."customer" 
    LEFT OUTER JOIN OCRY T2 ON T1."Country" = T2."Code" 
WHERE T0."callID" = [%Key];

Serviceabruf – Suche

SELECT T0."DocNum" AS "DOCNUM", 'Serviceauftrag' AS "DOCTYPE", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."callID" AS "CALLID" 
FROM OSCL T0 
WHERE T0."callID" = [%Key];

Bestandstransaktionen – Ablage

SELECT (
    CASE 
        WHEN T0."ObjType" = '59' THEN 
        CASE 
            WHEN T2."BaseType" = '202' THEN 'Eingang aus Produktion' 
            ELSE 'Wareneingang' 
        END 
        WHEN T0."ObjType" = '60' THEN 
        CASE 
            WHEN T3."BaseType" = '202' THEN 'Ausgabe fuer Produktion' 
            ELSE 'Warenausgang' 
        END 
        WHEN T0."ObjType" = '1250000001' THEN 'Anfrage zur Bestandsumlagerung' 
        WHEN T0."ObjType" = '67' THEN 'Bestandsumlagerung' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", RIGHT(100 + MONTH(T0."DocDate"), 2) AS "MONTH", 
    YEAR(T0."DocDate") AS "YEAR", '[%Source]' AS "SOURCE", T0."DocEntry" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM [%Table] T0 
    LEFT OUTER JOIN OPLN T1 ON T0."GroupNum" = T1."ListNum" 
    LEFT OUTER JOIN IGN1 T2 ON T2."DocEntry" = T0."DocEntry" 
    LEFT OUTER JOIN IGE1 T3 ON T3."DocEntry" = T0."DocEntry" 
WHERE T0."DocEntry" = [%Key];

Bestandstransaktionen – Suche

SELECT (
    CASE 
        WHEN T0."ObjType" = '59' THEN 
        CASE 
            WHEN T1."BaseType" = '202' THEN 'Eingang aus Produktion' 
            ELSE 'Wareneingang' 
        END 
        WHEN T0."ObjType" = '60' THEN 
        CASE 
            WHEN T2."BaseType" = '202' THEN 'Ausgabe f�r Produktion' 
            ELSE 'Warenausgang' 
        END 
        WHEN T0."ObjType" = '1250000001' THEN 'Anfrage zur Bestandsumlagerung' 
        WHEN T0."ObjType" = '67' THEN 'Bestandsumlagerung' 
    END) AS "DOCTYPE", T0."DocNum" AS "DOCNUM", T0."DocEntry" AS "DOCENTRY", CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", T0."ObjType" AS "SBO_OBJTYPE" 
FROM [%Table] T0 
    LEFT OUTER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry" 
    LEFT OUTER JOIN IGE1 T2 ON T0."DocEntry" = T2."DocEntry" 
WHERE T0."DocEntry" = [%Key];

Arbeitszeitblatt – Ablage

SELECT 'Arbeitszeitblatt' AS "DOCTYPE", UPPER(LEFT(T0."lastName", 1)) AS "A", 
    T0."lastName" || ', ' || T0."firstName" || ' - ' || CAST(T0."UserID" AS varchar(11)) AS "EMPNAME", 
    T0."UserID" AS "EMPID", T0."lastName" AS "LASTNAME", T0."firstName" AS "FIRSTNAME", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OTSH T0 
WHERE T0."AbsEntry" = [%Key];

Arbeitszeitblatt – Suche

SELECT 'Arbeitszeitblatt' AS "DOCTYPE", T0."UserID" AS "EMPID", T0."AbsEntry" AS "DOCENTRY", 
    CURRENT_SCHEMA AS "SBO_DB_NAME", 
    (SELECT "CompnyName" 
    FROM OADM) AS "COMPANY_NAME", '[%Source]' AS "SOURCE" 
FROM OTSH T0 
WHERE T0."AbsEntry" = [%Key];

Reaktion

War das hilfreich?

Ja Nein
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Hinterlasse einen Kommentar.

Kommentar senden