Skip to Content
0

Delivery-Return-AR Invoice-Credit Note

Nov 23, 2016 at 01:31 PM

119

avatar image

Dear Experts

I need a Query which shows the below details

in which i need to get Linked Documents llike Delivery >> Return >> AR invoice >>AR Credit Note >>

Also Open AR credit Note,

it should include both Service and Item Type doucment

Manual 'Cancalled', 'Closed', Document is not required to include

Also if i filter with Customer Name and Posting date

i need to get the link worked

Pls help me to sort it out

Customer Code Delivery No Delivery Posting Date Delivery Doc Total Return Doc No Return Date Return Doc Total AR Invoice No AR Invoice Posting Date AR Invoice Doc Total AR Credit Note No AR Credit Note Posting Date AR Credit Note Doc Total







Regards

10 |10000 characters needed characters left characters exceeded

Hi experts

Any updates

Regards

0

Hi experts

Pls any updates

Regards

0

Hi Experts

I had a Query which shows the link between PO >> GRPO >> AP Invoice

I need this query to convert it to SO >> Delivery >> Return >> AR Invoice >> AR Credit Note

Return & Ar credit note is what i need to add Additionally

also i think due to line level is included it repeating , which i need to remove

I try to Modify, but not able to sucess

Pls help to sort it out

Regards

select   PrchHd.DocNum [PO Document Number]
        ,PrchHd.DocDate [PO Document Date]
        ,PrchHd.DocTotal[Po Document Total]


   ,GRPHd.DocNum [GRPO Document Number]  
   ,GRPHd.DocDate [GRPO Document Date]
   ,GRPHd.DocTotal[GRPO Document Total]


   ,APINV.DocNum  [A/P Document Number]
   ,APINV.DocDate [A/P Posting Date]
   ,,APINV.DocTotal [A/P Doc Total]
   
   ,APINV.CardCode [Customer/Vendor ]
   ,APINV.CardName [Customer/Vendor Name]
      
   
From OPCH APINV Inner join PCH1 InvLin ON APINV.DocEntry =InvLin.DocEntry 
    Inner Join PDN1 GRPLin ON InvLin.BaseEntry=GRPLin.DocEntry and  InvLin.BaseLine=GRPLin.LineNum and InvLin.BaseType=20
    inner join OPDN GRPHd ON GRPLin.DocEntry=GRPHd.DocEntry 
    inner join POR1 PrchLin ON GRPLin.BaseEntry=PrchLin.DocEntry and GRPLin.BaseLine=PrchLin.LineNum and GRPLin.BaseType=22
    inner Join OPOR PrchHd ON PrchLin.Docentry=PrchHd.DocEntry 
       
     
    
    order by PrchHd.DocNum

0

Hi Experts

any one can convert this query to sales

also i need to include Return and AR Credit Note

and need to avoid Line level, due to line level is join, same document is Repeating according to Line level

Regards

select   PrchHd.DocNum [PO Document Number]
        ,PrchHd.DocDate [PO Document Date]
        ,PrchHd.Doctotal [PO Total]
		, case when PrchHd.DocType  = 'I' THEN 'Material Based'
               when PrchHd.DocType  = 'S' THEN 'Service Based'       		
		       else 'Invalid Parameters' end AS [PO Type]


   ,GRPHd.DocNum [GRPO Document Number]  
   ,GRPHd.DocDate [GRPO Document Date]
   ,GRPHd.DocTotal[GRPO Total]
   , case when GRPHd.DocType  = 'I' THEN 'Material Based'
          when GRPHd.DocType  = 'S' THEN 'Service Based'       		
		  else 'Invalid Parameters' end AS [GRPO Type]


   ,APINV.DocNum  [A/P Document Number]
   ,APINV.DocDate [A/P Posting Date]
   ,APINV.DocTOTAL [AP Total]
   , case when APINV.DocType  = 'I' THEN 'Material Based'
          when APINV.DocType  = 'S' THEN 'Service Based'       		
		  else 'Invalid Parameters' end AS [A/P Type]


   ,APINV.CardCode [Customer/Vendor ]
   ,APINV.CardName [Customer/Vendor Name]
   
   
From OPCH APINV Inner join PCH1 InvLin ON APINV.DocEntry =InvLin.DocEntry 
    Inner Join PDN1 GRPLin ON InvLin.BaseEntry=GRPLin.DocEntry and  InvLin.BaseLine=GRPLin.LineNum and InvLin.BaseType=20
    inner join OPDN GRPHd ON GRPLin.DocEntry=GRPHd.DocEntry 
    inner join POR1 PrchLin ON GRPLin.BaseEntry=PrchLin.DocEntry and GRPLin.BaseLine=PrchLin.LineNum and GRPLin.BaseType=22
    inner Join OPOR PrchHd ON PrchLin.Docentry=PrchHd.DocEntry 
	
WHERE PrchHd.DocDate BETWEEN '2016-01-01 00:00:00.000' AND '2016-12-31 00:00:00.000'


	order by  PrchHd.DocNum asc

0

Hi experts

any solution ????

Regards

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

P Vinod Kumar Nov 29, 2016 at 08:59 AM
1

Dear Mr. Shahan

Pls try this

Select distinct OCRD.CardName [Customer Name] ,OCRD.CardCode [Customer Code] ,ODLN.DocNum [Delivery Num],ODLN.DocDate [Delivery Date],ODLN.DocTotal [Delivery Total],OINV.DocNum [Invoice Num],OINV.DocDate [Invoice Date],OINV.DocTotal [Invoice Total] ,ORIN.DocNum [CM Num],ORIN.DocDate [CM Date],ORIN.DocTotal [CM Total] From OCRD inner join ODLN on OCRD .CardCode = ODLN .CardCode left outer join DLN1 on ODLN .DocEntry = DLN1 .DocEntry Left outer join INV1 on DLN1 .DocEntry = INV1 .BaseEntry and DLN1 .LineNum = INV1 .BaseLine and DLN1 .ObjType = INV1 .BaseType Left outer join OINV on INV1 .DocEntry = OINV .DocEntry Left Join ORIN on ORIN.DocEntry = INV1.TrgetEntry and ORIN.ObjType = INV1.TargetType Where ODLN.DocDate between '2016-01-01 00:00:00.000' and '2016-11-30 00:00:00.000' and OCRD.CardName not in ('Cash Customer (Dammam)','Cash Customer (Jeddah)','Cash Customer (Riyadh)') order by ODLN.DocNum asc

I had removed the Sales Order portion from the Query, also not include the Return Document

Regards

Share
10 |10000 characters needed characters left characters exceeded