Skip to Content

Crystal Report Sales Order DRAFT document

I'm trying to create a layout from crystal report, it's for the sales order draft document. When I run the layout I get the client information as well as the sales order total, what I don't see is the information from the items (I suppose is getting the info from OITM instead of DRF1). Here is the code:

ALTER PROCEDURE CTS_SALESORDER_DRAFT

(

IN DocEntry BIGINT

)

LANGUAGE SQLSCRIPT

AS

BEGIN

SELECT

A0."NumAtCard",

A0."DocNum" "Sales Order No.",

A0."DocDate" "Sales Order Date",

A0."DocDueDate" "Due Date",

A0."CardCode" "Customer Code",

A0."CardName" "Customer Name",

A0."DocTotal" "Total Order Value",

A0."DocEntry",

A0."DiscSum",

A0."TotalExpns",

A0."Comments",

A0."VatSum",

A0."DiscPrcnt",

A1."ItemCode" "Item No.",

A1."Dscription" "Description",

A1."PQTReqDate",

A1."Quantity" "Quantity",

A1."Price" "Unit Price",

A1."DiscPrcnt" "Discount %",

A1."LineTotal" "Total",

A1."SubCatNum" "Item No.",

A1."unitMsr",

A1."unitMsr2",

A1."InvQty",

A2."TrnspName" "Ship Via",

A3."SlpName" "Sales Employee",

A4."UomName" "UOM",

A5."CompnyAddr",

A5."CompnyName",

A5."Phone1" "Phone",

A5."Fax" "Fax",

A5."E_Mail" "E-Mail",

A6."PymntGroup",

A7."IntrntAdrs",

A11."ZipCode",

A11."Country",

A11."LicTradNum",

A11."Phone1",

A12."Address",

A9."SWW",

(select sum ("Quantity") from RDR1 where "DocEntry"=A0."DocEntry") as TOTALQTY, (select sum ("LineTotal") from RDR3 where "ExpnsCode"=1 AND "DocEntry"=A0."DocEntry") as FLETE,

(select sum ("LineTotal") from RDR3 where "ExpnsCode"=2 AND "DocEntry"=A0."DocEntry") as SEGURO

FROM ODRF A0

LEFT JOIN OCRD A11 ON A0."CardCode"=A11."CardCode"

LEFT JOIN OCPR A12 ON A0."CardCode"=A12."CardCode"

LEFT JOIN RDR1 A1 ON A0."DocEntry"=A1."DocEntry"

LEFT JOIN OSHP A2 ON A0."TrnspCode"=A2."TrnspCode"

LEFT JOIN OSLP A3 ON A0."SlpCode"=A3."SlpCode"

LEFT JOIN OUOM A4 ON A1."UomCode"=A4."UomCode"

LEFT JOIN OCTG A6 ON A0."GroupNum"=A6."GroupNum"

LEFT JOIN DRF1 A9 ON A9."SWW"=A1."ItemCode",

OADM A5

LEFT JOIN ADM1 A7 ON A7."Code"=A5."Code",

OADP A8

WHERE A0."DocEntry"=:DocEntry;

END;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 09 at 02:52 PM

    Hello, I see some problems in your query, since you are using ODRF for document head and RDR1 for details, which is contradicting. If you would like to report on draft orders, than you need to replace RDR1 with DRF1 to get the sales order draft lines.

    Instead of

    LEFT JOIN RDR1 A1 ON A0."DocEntry"=A1."DocEntry"

    I suggest:

    LEFT JOIN DRF1 A1 ON A0."DocEntry"=A1."DocEntry"

    Also

    LEFT JOIN DRF1 A9 ON A9."SWW"=A1."ItemCode" is maybe needs to be reviewed, since you are joining a item code with a property (SWW)

    I suggest

    LEFT JOIN OITM A9 ON A9."ItemCode"=A1."ItemCode"

    Best Regards,

    János

    Add comment
    10|10000 characters needed characters exceeded