select query

[Former Member]

hi

i do have the following requirement. Can anyone help me in writing the select query for this. pls help me.thanks in advance

1.1 Functional Specification

In June of year 1, new budget prices must be determined for the following year (year 2). The budget prices for year 2 are calculated out of the average contract prices in the first 5 months of year 1 (January – May). The average contract price will then (outside the system) be corrected with an index. To calculate new prices for Campina Netherlands, a report is needed to show all materials with current budget price, volumes and contract prices during the first five months of year 1

The purchasing assistant creates an excel file per Material type, based on the Budget pricelist out of SAP.

Purchasers check the file on:

 being correct,

 materials marked for deletion in SAP

 changes to be made in data

The new prices, calculated based ion the output of the SAP Budget pricelist, are sent to controlling in june year 1.

On the 1st of January year 2 the new budget prices are inserted in SAP as the new standard price / budget price.

1.2 Report Fields

Report/Form

Field ID SAP Table SAP

Field Name SAP Technical

Field Name Rules/Notes

Inkoop Organisatie EKKO Purchasing Organization EKORG In selection screen and on header level of output.

Materiaal type In selection screen and on header level of output.

Materiaal nummer EKPO Material Number MATNR

Materiaal Omschrijving MAKT Material description MAKTX

UNSPSC Code CAWN UNSPSC-Code (ZUNSPSC) ATWRT

Standaard prijs MBEW Standard price STPRS

Per MBEW Price unit PEINH

Eenheid MARA Base Unit of Measure MEINS

Volume

maand 1 S012 Sum of PO amount in month 1 Menge Purchased amount of materials within month 1, measured in base unit of measure

Volume

maand 2 S012 Sum of PO amount in month 2 Menge Purchased amount of materials within month 2, measured in base unit of measure

Volume

maand 3 S012 Sum of PO amount in month 3 Menge Purchased amount of materials within month 3, measured in base unit of measure

Volume

maand 4 S012 Sum of PO amount in month 4 Menge Purchased amount of materials within month 4, measured in base unit of measure

Volume

maand 5 S012 Sum of PO amount in month 5 Menge Purchased amount of materials within month 5, measured in base unit of measure

Contract prijs maand 1 EKPO Net price NETPR the price as is on date criteria 1

In EURO

Per EKPO Price unit PEINH

Eenheid EKPO Order price unit BPRME

Contract prijs maand 2 EKPO Net price NETPR the price as is on date criteria 2

In EURO

Per EKPO Price unit PEINH

Eenheid EKPO Order price unit BPRME

Contract prijs maand 3 EKPO Net price NETPR the price as is on date criteria 3

In EURO

Per EKPO Price unit PEINH

Eenheid EKPO Order price unit BPRME

Contract prijs maand 4 EKPO Net price NETPR the price as is on date criteria 4

In EURO

Per EKPO Price unit PEINH

Eenheid EKPO Order price unit BPRME

Contract prijs maand 5 EKPO Net price NETPR the price as is on date criteria 5

In EURO

<b>1.3 Error Handling</b>

A status report should be created after generating the output if one of the following errors occurs:

- Materials occurring more than once in one report.

The status report should mention the material numbers that occur more than once.

- The unit of measure of the 5 contract prices within one material are not equal (by example, in January an material costs € 100,- per KILO and in February the price is € 50,- per LITER)

The status report should mention the materials and contract prices that cause the Error.

- Materials that are not marked for deletion but do not have a contract price. These materials should be in the list with price ), the status report should mention the material numbers that do not have a contract price.

[Former Member]

Hello

Your Previous query "ALV Container" is solved. In case my reply was of any help, kindly Reward point. This way, u'll attract more replies.

Thanks & Regards

[Former Member]

hi rosalin

thanks for ur reply.

it really helped me.

thanks a lot