items repeating when Multi Fright charge is SELECTED

[Former Member]

Hi experts

am having a Store Procedure for Purchase order print out via Crystal Report

am having a issue in the print out now,

when i use multi Fright charges and multi items in a PO the same is repeating as per the number of Fright is selected

for eg: if i use one item in PO and if i have 2 different type of Fright like Door to Door charges and Document Charges

the item will appear with same qty and same rate 2 times,

pls help me to solve this issue by editing

see the query and screen shot attached

Regards

GO
/****** Object:  StoredProcedure [dbo].[spPurchaseOrder]    Script Date: 01/25/2017 12:05:51 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE
[dbo].[spPurchaseOrder] ( @DocNUM int)  
AS  
 
 Begin  
  


select OPOR.DocEntry,OPOR.CardCode,OPOR.CardName,OPOR.DocNum,OPOR.DocDate,OPOR.NumAtCard AS 'Vendor Ref', OPOR.DocDueDate,
isnull(POR12.StreetS,'')+' '+ isnull(POR12.StreetNoS,'')+' '+isnull(POR12.BlockS,'')+' '+ isnull(POR12.CityS,'')+' '+ isnull(POR12.ZipCodeS,'')+' '+
isnull(POR12.CountryS,'')AS 'Ship To Address ',isnull(OPOR.PAYTOCODE,'')+' '+isnull(POR12.StreetB,'')+' '+ isnull(POR12.StreetNoB,'')
+' '+isnull(POR12.BlockB,'')+' '+ isnull(POR12.CityB,'')+' '+ isnull(POR12.ZipCodeB,'')+' '+
isnull(POR12.CountryB,'')AS 'Bill / Pay To Address ',POR1.ItemCode,POR1.Dscription,POR1.Quantity,
POR1.Price, (POR1.Quantity*POR1.Price) AS 'Line Total',OPOR.DocCur,OPOR.DocTotal as 'Docuemnt Total Local Currency',OPOR.DocTotalFC as 'Docuemnt Total Forign Currency'
,OITM.BuyUnitMsr AS 'UOM',OPOR.DiscSumFC AS 'Discount% Forgine Currency',
OPOR.DiscSum AS 'Discount% Local Currency',OPOR.TotalExpns,
POR3.TotalFrgn 'Freight',OPOR.Comments as 'Document Remarks',
OSHP.TrnspName as 'Shipping Type',OCTG.PymntGroup as 'PaymentTerms',
isnull(OHEM.firstName,'')+' ' +isnull(OHEM.middleName,'')+ ' '+isnull(OHEM.lastName,'')AS 'Requester',
OSLP.slpName as 'Sales Employee Name',OCRD.CntctPrsn as '@Vendor Contact Person',OPOR.Header as 'Remarks (H)',OPOR.Footer AS 'Remarks (F)',OPRJ.PrjCode as 'ProjCode',
OPRJ.PrjName AS 'ProjeName',isnull(OWHS.WhsName,'')+' '+isnull(OWHS.Street,'')+' '+isnull(OWHS.Street,'')+' '+isnull(OWHS.Block,'')+' '+isnull(OWHS.City,'')+' '+
isnull(OWHS.County,'') AS 'Warehouse Address' ,OPOR.Address,OPOR.Address2,OPOR.DiscPrcnt as 'Discount %',OPOR.U_LocalDel as 'Local Delivery Address',
OPOR.U_DeliverAt as 'International Deliver',OCRD.Phone1 as 'Telephone1',OCRD.Phone1 as 'Telephone2',OCRD.Fax,OCRD.E_Mail,OCRD.CntctPrsn as 'Contact Person Name',
NNM1.SeriesName as 'PO Series',OITM.U_LongDesc as 'Long Description',OPOR.DocType as 'DocType',POR1.U_srv_qty as 'Qty for Ser',POR3.ExpnsCode


        from OPOR


Left Join por12 on OPOR.DocEntry = por12.DocEntry
Left Join POR1 on  OPOR. DocEntry = POR1.DocEntry
Left Join OITM ON POR1.ItemCode = OITM.ItemCode
Left Join POR3 on OPOR.DocEntry=POR3.DocEntry
Left join OSHP ON OPOR.TrnspCode = OSHP.TrnspCode
Left Join OCTG ON OPOR.GroupNum = OCTG.GroupNum
LEFT JOIN OHEM ON OPOR.OWNERCODE= OHEM.EMPID
Left Join OSLP ON OPOR.SlpCode=oslp.SlpCode
Left Join OCRD ON OPOR.CARDCODE=OCRD.CARDCODE
Left join OPRJ on POR1.Project = OPRJ.PrjCode
Left Join OWHS on POR1.WhsCode = OWHS.WhsCode
Left Join NNM1 on OPOR.Series = NNM1.Series


    Where OPOR.DocEntry=@DocNUM
 
 End  
 RETURN

[Former Member]

Hi,

You need to remove the join of POR3 and get the freight expense diectly from OPOR.TotalExpns.

Thanks

Engr. Taseeb Saeed

[Former Member]

Hi taseeb

thnks for the replay

this issue is been solved, i used it by a Sub report, it seems more Effectively

Regards

[kothandaraman_nagarajan]

Try this,

GO
/******Object:  StoredProcedure [dbo].[spPurchaseOrder]    Script Date:01/25/201712:05:51 PM ******/SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE[dbo].[spPurchaseOrder](@DocNUM int)ASBeginselect OPOR.DocEntry,OPOR.CardCode,OPOR.CardName,OPOR.DocNum,OPOR.DocDate,OPOR.NumAtCard AS'Vendor Ref', OPOR.DocDueDate,
isnull(POR12.StreetS,'')+' '+ isnull(POR12.StreetNoS,'')+' '+isnull(POR12.BlockS,'')+' '+ isnull(POR12.CityS,'')+' '+ isnull(POR12.ZipCodeS,'')+' '+
isnull(POR12.CountryS,'')AS'Ship To Address ',isnull(OPOR.PAYTOCODE,'')+' '+isnull(POR12.StreetB,'')+' '+ isnull(POR12.StreetNoB,'')+' '+isnull(POR12.BlockB,'')+' '+ isnull(POR12.CityB,'')+' '+ isnull(POR12.ZipCodeB,'')+' '+
isnull(POR12.CountryB,'')AS'Bill / Pay To Address ',POR1.ItemCode,POR1.Dscription,POR1.Quantity,
POR1.Price,(POR1.Quantity*POR1.Price)AS'Line Total',OPOR.DocCur,OPOR.DocTotal as'Docuemnt Total Local Currency',OPOR.DocTotalFC as'Docuemnt Total Forign Currency',OITM.BuyUnitMsr AS'UOM',OPOR.DiscSumFC AS'Discount% Forgine Currency',
OPOR.DiscSum AS'Discount% Local Currency',OPOR.TotalExpns,
POR3.TotalFrgn 'Freight',OPOR.Commentsas'Document Remarks',
OSHP.TrnspName as'Shipping Type',OCTG.PymntGroup as'PaymentTerms',
isnull(OHEM.firstName,'')+' '+isnull(OHEM.middleName,'')+' '+isnull(OHEM.lastName,'')AS'Requester',
OSLP.slpName as'Sales Employee Name',OCRD.CntctPrsn as'@Vendor Contact Person',OPOR.Headeras'Remarks (H)',OPOR.Footer AS'Remarks (F)',OPRJ.PrjCode as'ProjCode',
OPRJ.PrjName AS'ProjeName',isnull(OWHS.WhsName,'')+' '+isnull(OWHS.Street,'')+' '+isnull(OWHS.Street,'')+' '+isnull(OWHS.Block,'')+' '+isnull(OWHS.City,'')+' '+
isnull(OWHS.County,'')AS'Warehouse Address',OPOR.Address,OPOR.Address2,OPOR.DiscPrcnt as'Discount %',OPOR.U_LocalDel as'Local Delivery Address',
OPOR.U_DeliverAt as'International Deliver',OCRD.Phone1 as'Telephone1',OCRD.Phone1 as'Telephone2',OCRD.Fax,OCRD.E_Mail,OCRD.CntctPrsn as'Contact Person Name',
NNM1.SeriesName as'PO Series',OITM.U_LongDesc as'Long Description',OPOR.DocType as'DocType',POR1.U_srv_qty as'Qty for Ser',POR3.ExpnsCode
        from OPOR
Inner Join por12 on OPOR.DocEntry = por12.DocEntry
Inner Join POR1 on  OPOR. DocEntry = POR1.DocEntry
Inner Join OITM ON POR1.ItemCode = OITM.ItemCode
inner Join POR3 on OPOR.DocEntry=POR3.DocEntry
Left join OSHP ON OPOR.TrnspCode = OSHP.TrnspCode
Left Join OCTG ON OPOR.GroupNum = OCTG.GroupNum
LEFT JOIN OHEM ON OPOR.OWNERCODE= OHEM.EMPID
Left Join OSLP ON OPOR.SlpCode=oslp.SlpCode
Left Join OCRD ON OPOR.CARDCODE=OCRD.CARDCODE
Left join OPRJ on POR1.Project = OPRJ.PrjCode
Left Join OWHS on POR1.WhsCode = OWHS.WhsCode
Left Join NNM1 on OPOR.Series = NNM1.Series
    Where OPOR.DocEntry=@DocNUM
 
 End

RETURN

[Former Member]

Hi rajan

i try the query u provided

but still it returning 6 line, but i had found a solution for this

i made a Sub Layout only for Fright. and i give a common and sum the Row, and also removed fright from the main SPT

now its returning the same value and the line is only 3 as per the SAP Interface

Regards

[kothandaraman_nagarajan]

Hi,

Change join like below for OPOR and POR3 table,

INNER POR3 on OPOR.DocEntry=POR3.DocEntry

[Former Member]

Hi Mr Rajan

Thanks for the Support

Can u pls help me by editing the Query

Regards