Kindly give me code for below query. We are working as an intermediate between customer and supplier.
In our system, we have below data for each customer and each supplier..
Customer ID Customer Name Customer Currency Customer Country
Where Customer ID is unique.
Supplier ID Supplier Name Supplier Currency Supplier Country
Supplier ID is unique.
Based on prices available from different suppliers on respective brands, we are sending quotation to our customers. These data are saved in below table. Here Our Part NO is unique but can be associated with more than one brand. We can say one part number is having three or more different quality. Example shown below. But there will be only one Our Part NO for each Customer Part NO
Our Part NO Brand Quoted Price Customer Part NO
ABCXYZ A 10 1234 567
ABCXYZ B 20 1234 567
ABCXYZ C 30 1234 567
Customer ID Our Part NO Customer Part NO Brand Quoted Price
Also, for each supplier, we have purchase cost table.
Supplier Price Table:
Supplier ID Our Part NO Brand Supplier Part NO Purchase Cost
Based on our quotations, customers are sending orders as below...
Customer Order Table:
Customer ID Customer Sales Order NO Customer Part NO Brand Qty
Finally we are sending orders to our supplier based on customers' orders. We have below data stored for our Purchases/Sales.
Purchase Order Table:
Purchase Order NO Supplier ID Supplier Part NO Our Part NO Brand Qty Customer ID Customer Sales Order NO
I need code to get table with following columns.
|Purchase Order NO|
|Supplier Part NO|
|Our Part NO|
|Customer Part NO|
|Customer Sales Order NO|
Where Purchase Total = Qty * Purchase Cost and Invoice Total = Qty * Quoted Price
Also, if possible, I want unique line number in Final Table.
Thanks in advance.