I have a report that require 1 to many relationship in 4 tables: BKPF, BSIS, T012K, T012.
Now I would like to join table BVOR.
Techical spec is as below:
1. Get (BUKRS, BELNR, GJAHR, HKONT) from BSIS with conditions BUKRS = parameter-BUKRS, HKONT between '750000' to '759999', GJAHR = select_option-GJAHR and BELNR = select_option-BELNR. 2. Get (BUKRS, BELNR, GJAHR, BVORG, HKONT) from BKPF with conditions BUKRS = BSIS-BUKRS, GJAHR = BSIS-GJAHR, BELNR = BSIS-BELNR AND BKTXT = ('&W&' OR '&w&'). 3. Get (BANKN, HBKID) from T012K with conditions BUKRS = BKPF-BUKRS, HKONT = BKPF-HKONT. 4. Get (BANKS) from T012 with conditions BUKRS = BKPF-BUKRS, HBKID = T012K~HBKID. 5. Get (BUKRS, BELNR, GJAHR, BVORG) from BVOR with conditions BUKRS <> BKPF-BUKRS, BELNR <> BELNR, BVORG = BKPF-BVORG. SELECT BSIS~BUKRS BSIS~BELNR BSIS~GJAHR BKPF~HKONT BKPF~BVORG T012K~BANKN T012K~HBKID INTO CORRESPONDING FIELDS OF TABLE ITAB FROM BSIS INNER JOIN BKPF ON BSIS~BUKRS = BKPF~BUKRS AND BSIS~BELNR = BKPF~BELNR AND BSIS~GJAHR = BKPF~GJAHR INNER JOIN T012K ON T012K~BUKRS = BKPF~BUKRS AND T012K~HKONT = BKPF~HKONT INNER JOIN T012 ON T012~BUKRS = BKPF~BUKRS AND T012~HBKID = T012K~HBKID WHERE BSIS~BUKRS = P_BUKRS "parameter AND BSIS~BELNR IN S_BELNR "select option AND BSIS~GJAHR IN S_GJAHR "select option AND BSIS~HKONT BETWEEN '0000750000' AND '0000759999' AND BKPF~BKTXT EQ '&W&' OR BKPF~HKONT EQ '&w&'.
Code above join tables BSIS, BKPF, T012K and T012. Can I join BVOR table too? But in BVOR, I need to store the BUKRS, BELNR and GJAHR. If I add 3 more fields to my ITAB, I have to name the field like BUKR1, BELN1 and GJAH1 so that data from BVOR won't overwrite the data get from BSIS. Is it possible?
- If it is not possible, how should I write the code (1 to many). Should I loop ITAB and select data from BVOR and store the data to another internal table? - How if I would like to join both internal table?
Please kindly give me suggestion as I wish this program can get better performance than having inner loop. Thanks a lot!