topic Inner Join Issue in Application Development and Automation Discussions

Inner Join Issue

Former Member — Sun, 08 Jul 2007 08:27:45 GMT

Hi Gurus,

iam using an inner joiN ON vbak,vabp,vbkd.

the selection screen is

select-options: s_erdat for vbak-erdat, "SO CREATION DATE

s_vbeln for g_vbeln, " SALES ORDER

s_bstkd for vbkd-bstkd_e. " CLIENT PURCHASE ORDER NO

THE sample records:

SO soline PO poline

86333 1 - -

87000 1 49000 1

my inner join:

select vbak_{vbeln vbap}posnr vbap_{route vbkd}bstkd_e vbkd~posex_e

into table t_vbap

from vbak

inner join vbap

on vbak_{vbeln = vbap}vbeln

inner join vbkd

on vbap_{vbeln = vbkd}vbeln

and vbap_{posnr = vbkd}posnr

where vbak~erdat in s_erdat

and vbap~vbeln in s_vbeln

and vbkd~bstkd_e in s_bstkd.

problem:

selection screen:

DATE: - TO -

SO : 86333 TO -

PO : - TO -

INPUT IS ONLY sales order no.

and in V BKD NO po - blank .

In VBKD table if the PO is blank am not getting any record.

mean the first one should be retrived but not happening.

at least it should show me like this below.

86333 1 - -

pls clera me asap.

Re: Inner Join Issue

Former Member — Sun, 08 Jul 2007 09:24:26 GMT

Hi Sridhar,

If the PO is blank and still you want to retrieve the data from VBKD then you shuld use OUTER JOIN instead of INNER JOIN.

Reward points if useful.

Regards,

Atish

Re: Inner Join Issue

Former Member — Sun, 08 Jul 2007 09:41:00 GMT

Hi ,

tahnx for that.

Even, i changed accordingly but its giving the below error.

NO-FILEDS FROM TEH RIGHT-HAND SIDE OF TABLE OF left -outer join may appear in WHERE condition : " vbkd~bstkd_e.

it seems in where conditon its not allowing this.

Re: Inner Join Issue

Former Member — Sun, 08 Jul 2007 09:48:35 GMT

Hi Sridhar,

i am not in front of system, but you must have written query wrongly. Just do F1 on OUTER JOIN, you can get the sample query.

Change your query accoringly.

Regards,

Atish

Re: Inner Join Issue

Former Member — Sun, 08 Jul 2007 09:52:36 GMT

thank you very much.

i placed if condition then with outer join.

its working.

Re: Inner Join Issue

Former Member — Wed, 08 Aug 2007 17:28:28 GMT

.2 Inbound Interface

Follow steps 1 to 5 inclusive as detailed above in outbound interface.

Step 6

Write a custom function module to handle custom inbound processing. This custom function module must

• Check for the correct message type

• Read the IDoc data segment

• Perform data conversion and validate the data as appropriate

• Post the data to the database

• Handle any error situations

• Set the correct return values for the status record

Note that the Function Module must not make a commit to the database. This is because the status record is not written until control returns to the ALE layer. So if you commit work in the Function Module and an error occurs in returning to the ALE Layer, the status record must not be updated with a successful outcome.

The commit work is executed in the ALE Layer after the status records are updated, via the standard SAP function module IDOC_INBOUND_PROCESS. This attributes of this function module are set up in (Transaction BD51), click on ‘New Entries’ and fill in data; the main input parameters being ‘Input Type’ and ‘Dialog Allowed’.

Take care as some standard SAP transactions contain a Commit Work as part of their processing. Therefore using a BDC to process inbound data to SAP may not be acceptable. You need to check that the SAP transaction is ALE enabled.

Step 7

(Transaction WE57)

Assign the custom function module to the IDoc type and the message type.

Set function module to type ‘F’ and direction ‘2’ for inbound.

Step 8

(Transaction WE42)

Create a new process code and assign it to the function module. The process code determines how the incoming IDoc is to be processed in SAP.

Step 9

(Transaction BD67)

Assign the function module to the process code created above. Got to ‘New Entries’ and enter the process code and the function module name.

Step 10

(Transaction WE20 and Transaction BD64)

Create a partner profile for your message and ensure that in transaction WE20 the process code is the one that points to your function module. (See step 6 of creating Outbound Idocs).

Step 11

Ensure that error handling functionality is present.

Note:

If an interface is expecting a file every day, and a custom IDoc may only be output from SAP periodically, it would be necessary to create some kind of output from SAP on a daily basis to pass to Mercator to allow it to map to an empty file. As we are dealing with custom IDoc types we can design the IDoc so that the data segments are not mandatory. Therefore the SAP program, on finding no data to populate the custom IDoc, would only need to create the control record. The empty IDoc containing the control record would be passed to Mercator. Mercator will map this to an empty file format.

Useful Transaction Codes:

WEDI : IDoc Menu

WE02 : IDoc List (can enter IDoc number to select on)

WE05 : IDoc List

WE19 : Transaction WE19 can be used to test the IDoc

WE30 : IDoc Type Development: Initial Screen

WE31: Development Segments: Initial Screen

WE60 : Documentation for IDoc Types. This allows you to view the IDoc you have created.

WE81 : Display View ‘EDI: Logical Message Types’: Overview

WE82: Display View ‘Message Types and Assignment to IDoc Types’: Overview

rewards if useful,