Insert Data into temp Table

Hello Ramco Ramco,

To insert the specified data into a temporary table, you can utilize the following SQL statement. This query combines results from the OJDT and OPCH tables using a UNION ALL operation, ensuring that the data is filtered according to the provided conditions. Here’s how you can structure your SQL command:

INSERT INTO TempTable ("Vendor Code", "Vendor Name", "BPLName", "Document No", "TaxDate", "Document Type", "Debit Amount", "Credit Amount")
SELECT
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."BPLName",
    T2."BaseRef" AS "Document No",
    T2."TaxDate" AS "TaxDate",
    CASE
        WHEN T3."TransType" = '204' THEN 'A/P Down Payment Invoice'
        WHEN T3."TransType" = '46' THEN 'Outgoing Payment'
        WHEN T3."TransType" = '18' THEN 'A/P Invoice'
        ELSE ' '
    END AS "Document Type",
    T3."Debit" AS "Debit Amount",
    T3."Credit" AS "Credit Amount"
FROM
    "OJDT" T2
INNER JOIN
    "JDT1" T3 ON T2."TransId" = T3."TransId"
WHERE
    T3."ShortName" = :BpCode

UNION ALL

SELECT
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name",
    T2."BPLName" AS "BPLName",
    T2."DocNum" AS "Document No",
    T2."DocDate" AS "TaxDate",
    'A/P Invoice' AS "Document Type",
    0 AS "Debit Amount",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Credit Amount"
FROM
    "OPCH" T2
WHERE
    T2."DocDate" BETWEEN TO_DATE(:FromDate, 'YYYY-MM-DD') AND TO_DATE(:ToDate, 'YYYY-MM-DD')
    AND T2."CardCode" = :BpCode
    AND T2."CANCELED" NOT IN ('Y', 'C');

This command effectively gathers the required data and inserts it into the TempTable, ensuring that all conditions are met for accurate data representation.

Hello Ramco,

Please use below query

SELECT * 
INTO #tmp
FROM (
SELECT
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."BPLName",
    T2."BaseRef" AS "Document No",
    T2."TaxDate" As "TaxDate",
    CASE
        WHEN T3."TransType" = '204' THEN 'A/P Down Payment Invoice'
        WHEN T3."TransType" = '46' THEN 'Outgoing Payment'
        WHEN T3."TransType" = '18' THEN 'A/P Invoice'
        ELSE ' '
    END AS "Document Type",
    T3."Debit" AS "Debit Amount",
    T3."Credit" AS "Credit Amount"
FROM
    "OJDT" T2
INNER JOIN
    "JDT1" T3 ON T2."TransId" = T3."TransId"
WHERE
    T3."ShortName" = :BpCode

UNION ALL

SELECT
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name",
    T2."BPLName" AS "BPLName",
    T2."DocNum" AS "Document No",
    T2."DocDate" AS "TaxDate",
    'A/P Invoice' AS "Document Type",
    0 AS "Debit Amount",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Credit Amount"
FROM
    "OPCH" T2
WHERE
    T2."DocDate" BETWEEN TO_DATE(:FromDate, 'YYYY-MM-DD') AND TO_DATE(:ToDate, 'YYYY-MM-DD')
    AND T2."CardCode" = :BpCode
    AND T2."CANCELED" NOT IN ('Y', 'C');
) AS A

SELECT * FROM #tmp;

DROP TABLE #tmp;

Thanks