select quirey

[Former Member]

Hi experts

plz help me in writing select quiries for the following given specifications

All the tables and fields are given for ur information

plz help

1.Batch # (Lot No.) AFPO- CHARG CHAR 10 The Process Order(AFPO-AUFNR) passed by the transaction will be used to select the batch number(CHARG).

2. Part No. (NDC # and unit amount) AFPO- MATNR CHAR 18 The Process Order(AFPO-AUFNR) passed by the transaction will be used to select the material number(MATNR).

Include dashes

3. Page No. AFPO-AUFNR CHAR 12 The process order(AFPO-AUFNR) is being passed by the transaction.

Extract last digit from the order number.

4. Bottle Amount AFPO- MATNR CHAR 18

Last two digits of the material number(AFPO-MATNR) should be converted to a single digit and concatenated with a “C”.

Ex. 0378-0357-01

01 = 1C.

Bottle Amount = 1C.

5. Quantity AFKO- GAMNG CHAR 17 Select the total amount(AFKO-GAMNG) where the procress order(AFKO-AUFNR) equals the procress order selected from AFPO-AUFNR.

6. Strength AUSP-ATWRT CHAR 10 Select the Characteristic Value (AUSP-ATWRT), where the Internal characteristic(AUSP-ATINN) equals ‘CASE_LABEL_DESC_LINE5’, the Class type (AUSP-KLART) equals ‘001’, and the Object(AUSP-OBJEK) = the material number’s Finished product material number(AFPO-MATNR).

Adjust AFPO-MATNR, remove the last 3 digits from the material number, then change the -01 at the end to -00.

Ex.

0378-0457-01-C1

0378-0457-01

0378-0457-00.

Regards

divya

points sure

[Former Member]

plz someone help

[Former Member]

Hi Divya ,

its not the right approach or right way ? .U can do it on ur own i know ? just follow the way u want the output.

first get all the data from AFPO.

select * from afpo where matnr in S_matnr.

and for that order list get details from AFKO

and finall get the data from AUSP.

regards

prabhu

[Former Member]

Try the below logic.

SELECT A_{AUFNR, B}CHARG, B_{MATNR, A}GMANG

INTO ITAB

FROM AFKO AS A INNER JOIN AFPO AS B

ON A_{AUFNR = B}AUFNR

WHERE A~AUFNR = P_AUFNR.

LOOP AT ITAB.

ITFIN-BATCH = ITAB-CHARG(10).

ITFIN-PARTNO = ITAB-MATNR.

ITFIN-PAGENO = ITAB-AUFNR. (NOT SURE OF YOUR SPECS)

CONCATENATE ITAB-MATNR+17(1) 'C' INTO ITFIN-BOTAMT.

ITFIN-QTY = ITAB-GAMNG.

SELECT ATWRT INTO ITFIN-STRENGTH FROM AUSP

WHERE AUSP-ATINN = ‘CASE_LABEL_DESC_LINE5’

KLART = ‘001’ OBJEK = ITAB-MATNR.

APPEND ITFIN.

ENDLOOP.

Regards

Anurag

Message was edited by: Anurag Bankley