Hi Everyone.
I hope you can help me to resolve my issues:
- My aim is to gather accurate spend/volume data from businesses.
- Usually, a purchase order (PO) comes first, before invoice.
- I want to be able to get one purchase order per invoiceNo based on the following condition.(a). If the source is “PO” or “INV” and have the same InvoiceNO, eg, 930015980, and the PO has value (“PO Line Amount”), then I want to only retain PO where source = PO (b). However, if PO “PO Line Amount”) has no value, I want to retain the INV where source = INV (PO Line Invoice Amount) (c). If both have value, I want to retain only Purchase order.
- I don’t want to have both INV and PO amount for the same invoiceNo and Vendor_Number.
Please my Test code below: Thanks for your help.
IF OBJECT_ID('tempdb..#Purchase_Invoice_Results') IS NOT NULL DROP TABLE #Purchase_Invoice_Results
GO
CREATE Table #Purchase_Invoice_Results(
[Vendor_Number] varchar NULL,
[VendorName] varchar NULL,
[InvoiceNo] varchar NULL,
[legalEntity] varchar NULL,
[Source] varchar NULL,
[Currency] varchar NULL,
[PO Line Amount] varcharNULL,
[Po Line Invoice Amount] varcharNULL,
[PO Line Receipt Amount] varcharNULL,
[LEPurchaseOrderLineInvoiceAmount] varcharNULL,
[PO Line Price] varcharNULL,
[InvoiceEntryFiscalYear] varcharNULL,
[InvoiceEntryDate] varcharNULL,
[invoicePostedFiscalYear] varcharNULL,
[invoicePostedDate] varcharNULL,
[PurchaseOrderFiscalYear] varchar NULL,
[PurchaseOrderDate] varchar NULL
) ON [PRIMARY]
GO
INSERT INTO #Purchase_Invoice_Results([Vendor_Number],[VendorName]
,[InvoiceNo],[legalEntity],[Source],[Currency],[PO Line Amount],[Po Line Invoice Amount],[PO Line Receipt Amount]
,[LEPurchaseOrderLineInvoiceAmount]
,[PO Line Price],[InvoiceEntryFiscalYear],[InvoiceEntryDate] ,[invoicePostedFiscalYear] ,[invoicePostedDate]
,[PurchaseOrderFiscalYear],[PurchaseOrderDate]
)
VALUES
('EUX-100530','Western Foods Plc.','930015991','RLM01','INV','HKD', '0', '0', '0', '22776.24', '0', '2025','15/09/2024','2025','27/10/2024', null,null),
('EUX-100530','Western Foods Plc.','930015980','RLM01','INV','HKD', '0','0','0','21639.66','0','2025','08/09/2024','2025', '08/11/2024', null,null),
('EUX-100530','Western Foods Plc.','930015992','RLM01','INV','HKD', '0','0','0','22179.86','0','2025','15/09/2024','2025','27/10/2024',NULL,NULL),
('EUX-100530','Western Foods Plc.','930015980','RLM01','PO', 'HKD', '231012.05','210855.48','231012.05','21194.27','121.632','2025','08/09/2024','2025','08/11/2024','2024','12/06/2024'),
('EUX-100530','Western Foods Plc.','930015991','RLM01','PO','HKD','250504.79','228912.16','250504.79','23216.24','136.824','2025','15/09/2024','2025','27/10/2024','2024','24/06/2024'),
('EUX-100530','Western Foods Plc.','930015992','RLM01','PO','HKD','234667.09','222890.63','234667.09','22605.56','90.548571','2025','15/09/2024','2025','27/10/2024' ,'2024','28/06/2024'),
('EUX-100530','Western Foods Plc.','930015993','RLM01','PO','HKD','216701.58','216701.58','216701.58','21781.91','66.575','2025', '15/09/2024','2025','27/10/2024','2024','12/06/2024'),
('EUX-100530','Western Foods Plc.','930015993','RLM01','INV', 'HKD', '0','0' ,'0','21566.89','0','2025','15/09/2024','2025', '27/10/2024',NULL,NULL);
Select * from #Purchase_Invoice_Results