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;