Skip to Content
0
May 09, 2018 at 02:09 PM

Crystal Report Sales Order DRAFT document

192 Views

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;