preview pending documents layout

[#1]

hello everyone

am facing a weird situation here , am pretty sure that most of you can help me through it,

i made a layout for Delivery Note and it work fine , the problem is it work only if the document is being added to the system , but b4 add it , the layout show no data , same when the document is pending for approval , it will show no data , so u must approve this document and then add it in order to make the layout work, anyway after a long journey i had this SP , which suppose to fix that , but something is wrong with it , as once u open the layout from sap , it will ask for insert the dockey and object id , if possible to help me with it , or even better if some one can help me to change this SP to be a regular query i'll appreciate it , and just to make sure , a little details about the parameters settings for it  , as am confuse if i should make a parameter for object id or no .

--------------------------------------------

USE [MENANewLive]

GO

/****** Object:  StoredProcedure [dbo].[Form_PO11]    Script Date: 9/2/2014 11:55:48 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

create PROCEDURE [dbo].[MENA_FULLDN]

@Dockey INT,

@ObjectId INT

AS

BEGIN

IF @ObjectId = 112

BEGIN

SELECT     odrf.DocEntry, odrf.CntctCode,drf1.SubCatNum, odrf.Address2, drf1.LineNum, odrf.DocType, odrf.CANCELED, odrf.Handwrtten, odrf.DocStatus, odrf.Transfered, odrf.DocDate, odrf.DocDueDate,

                      odrf.CardCode, odrf.Printed, odrf.CardName, odrf.Address, odrf.NumAtCard, odrf.VatPercent, odrf.VatSum, odrf.VatSumFC, odrf.DiscPrcnt, odrf.DiscSumFC,

                      odrf.DiscSum, odrf.PaidToDate, odrf.DocTotalFC, odrf.DocTotal, odrf.DocRate, odrf.Comments, odrf.VatSumSy, odrf.DocTotalSy,odrf.NumAtCard,  odrf.CreateDate, odrf.DocNum, drf1.U_LCCP,

                      odrf.TaxDate, drf1.ItemCode, drf1.Dscription, drf1.Price, drf1.DiscPrcnt AS Expr1, drf1.Rate, drf1.Quantity, drf1.StockSum, drf1.unitMsr, drf1.BaseDocNum,drf1.LineTotal, nnm1.seriesname

FROM         odrf INNER JOIN

                      drf1 ON odrf.DocEntry = drf1.DocEntry

inner join nnm1 on odrf.series = nnm1.series

WHERE ODRF.ObjType = 20 and ODRF.DocNum = @Dockey

END

IF @ObjectId = 20

BEGIN

SELECT     odln.DocEntry, odln.CntctCode,dln1.SubCatNum, odln.Address2, dln1.LineNum, odln.DocType, odln.CANCELED, odln.Handwrtten, odln.DocStatus, odln.Transfered, odln.DocDate, odln.DocDueDate,

                      odln.CardCode, odln.Printed, odln.CardName, odln.Address, odln.NumAtCard, odln.VatPercent, odln.VatSum, odln.VatSumFC, odln.DiscPrcnt, odln.DiscSumFC,

                      odln.DiscSum, odln.PaidToDate, odln.DocTotalFC, odln.DocTotal, odln.DocRate, odln.Comments, odln.VatSumSy, odln.DocTotalSy,ODLN.NumAtCard,  odln.CreateDate, ODLN.DocNum, dln1.U_LCCP,

                      odln.TaxDate, dln1.ItemCode, dln1.Dscription, dln1.Price, dln1.DiscPrcnt AS Expr1, dln1.Rate, dln1.Quantity, dln1.StockSum, dln1.unitMsr, dln1.BaseDocNum,dln1.LineTotal, nnm1.seriesname

FROM         odln INNER JOIN

                      dln1 ON odln.DocEntry = dln1.DocEntry

inner join nnm1 on odln.series = nnm1.series

WHERE odln.DocEntry = @DocKey

end

end

[former_member292966]

Hi Mohammad,

Your stored procedure is designed to be dynamic and handle different objects and documents.  So the parameters are necessary.

Are you wondering where to get the ObjectID and DocKey from?  That's hard to tell because we don't know your database or data.

It looks like you can only use 1 of 2 ObjectIDs: 112 and 20.  Then you will need to find a value for dockey which is tied to a document number.  How you do that is outside our skills here.

Depending on they ObjectID, the stored procedure is getting the document information from different tables, odrf and odln.  Run a query against these tables to find a document number that matches the object id.

This is more of an issue of knowing your database and data than fixing a query for the report.

Good luck,

Brian

[Former Member]

Hello Brian ,

thx for ur reply ,

I tried to use the parameters as u said ,  but didn't work out well .

i know about dockey wihch is =docentry , and for object = objtype ,

there is something am missing here, am using this layout on SBO 9.9 ,

-----------------------

if i'll ask the qustion in another way :

what should i do to make a layout to be preview-able no matter what is the status of the document , (open,closed,unapproved,pending,etc) , if i can just get the concept i'll start creating another PS , Query , or modifying the existence one .

thx again for ur time

[former_member292966]

Hi Mohammad,

If all you want to do is get a general idea of what the it's going to look like then take 1 of the queries from the Stored Procedure and run it without the dockey.

Create a view or SQL Command like:

SELECT     odrf.DocEntry, odrf.CntctCode,drf1.SubCatNum, odrf.Address2, drf1.LineNum, odrf.DocType, odrf.CANCELED, odrf.Handwrtten, odrf.DocStatus, odrf.Transfered, odrf.DocDate, odrf.DocDueDate,

                      odrf.CardCode, odrf.Printed, odrf.CardName, odrf.Address, odrf.NumAtCard, odrf.VatPercent, odrf.VatSum, odrf.VatSumFC, odrf.DiscPrcnt, odrf.DiscSumFC,

                      odrf.DiscSum, odrf.PaidToDate, odrf.DocTotalFC, odrf.DocTotal, odrf.DocRate, odrf.Comments, odrf.VatSumSy, odrf.DocTotalSy,odrf.NumAtCard,  odrf.CreateDate, odrf.DocNum, drf1.U_LCCP,

                      odrf.TaxDate, drf1.ItemCode, drf1.Dscription, drf1.Price, drf1.DiscPrcnt AS Expr1, drf1.Rate, drf1.Quantity, drf1.StockSum, drf1.unitMsr, drf1.BaseDocNum,drf1.LineTotal, nnm1.seriesname

FROM         odrf INNER JOIN

                      drf1 ON odrf.DocEntry = drf1.DocEntry

inner join nnm1 on odrf.series = nnm1.series

WHERE ODRF.ObjType = 20

Create your report against this and now you can see what you have to work with.  You'll probably get multiple documents but that's easy enough to work around.

This is simple data analysis.

Good luck,

Brian