[7주차] SAP ERP : 항공편조회 with join

[6주차 SAP ERP : 항공편조회 ] 에서 추가 구현

SAP ERP : 항공편조회 with join 결과화면

1. 데이터 선언 추가

TABLES: sbook,    scarr, scustom.

TYPE-POOLS: slis, vrm.                                 "ALV Declarations

*Data Declaration
*----------------
TYPES: BEGIN OF t_join,
         carrid    TYPE sbook-carrid,
         carrname  TYPE scarr-carrname,
         connid    TYPE sbook-connid,
         fldate    TYPE sbook-fldate,
         bookid    TYPE sbook-bookid,
         customid  TYPE sbook-customid,
         name      TYPE scustom-name,
         telephone TYPE scustom-telephone,
         custtype  TYPE scustom-custtype,
         cancelled TYPE sbook-cancelled,
       END OF t_join.

2. 필드카탈로그 추가

FORM build_fieldcatalog.

  fieldcatalog-fieldname   = 'CARRID'.
  fieldcatalog-seltext_m   = 'Airline Code'.
  fieldcatalog-col_pos     = 0.
  fieldcatalog-outputlen   = 10.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CARRNAME'.
  fieldcatalog-seltext_m   = 'Airline name'.
  fieldcatalog-col_pos     = 1.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CONNID'.
  fieldcatalog-seltext_m   = 'Flight Connection Number'.
  fieldcatalog-col_pos     = 2.
  fieldcatalog-lzero       = 'X'.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'BOOKID'.
  fieldcatalog-seltext_m   = 'Booking number'.
  fieldcatalog-col_pos     = 3.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CUSTOMID'.
  fieldcatalog-seltext_m   = 'Customer Number'.
  fieldcatalog-col_pos     = 4.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'NAME'.
  fieldcatalog-seltext_m   = 'Customer name'.
  fieldcatalog-col_pos     = 5.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'TELEPHONE'.
  fieldcatalog-seltext_m   = 'Telephone number of flight customer'.
  fieldcatalog-col_pos     = 6.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CUSTTYPE'.
  fieldcatalog-seltext_m   = 'Customer type'.
  fieldcatalog-col_pos     = 7.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.


  fieldcatalog-fieldname   = 'CANCELLED'.
  fieldcatalog-seltext_m   = 'Cancelation flag'.
  fieldcatalog-col_pos     = 8.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

ENDFORM. 

3. 데이터 조회 join구문 추가

FORM data_retrieval.

  DATA: lv_cancelled TYPE sbook-cancelled.
  " r1: 모든 예약 조회 (취소 여부 상관없음)
  " r2: 유효한 예약 조회 (취소되지 않은 것만)
  " r3: 취소된 예약 조회 (취소된 것만)

  IF r1 = 'X'.
    lv_cancelled = ''.
  ELSEIF r2 = 'X'.
    lv_cancelled = space.  " 취소되지 않은 예약
  ELSEIF r3 = 'X'.
    lv_cancelled = 'X'.    " 취소된 예약
  ENDIF.


  IF r1 = 'X'.
    " 모든 예약 조회 (취소 여부 필터 X)
    SELECT b~carrid c~carrname b~connid b~fldate b~bookid b~customid
      cu~name cu~telephone cu~custtype
      b~cancelled
    FROM (
      sbook AS b
      INNER JOIN scarr AS c
      ON b~carrid = c~carrid

      INNER JOIN scustom AS cu
      ON b~customid = cu~id
    )
    INTO TABLE it_book
    WHERE b~carrid = p_carrid
      AND b~connid = p_connid
      AND b~fldate = p_fldate
      AND b~customid IN s_custid.
      
  ELSE.
    " r2 또는 r3 선택 시, CANCELLED 필터 적용
   SELECT b~carrid c~carrname b~connid b~fldate b~bookid b~customid
       cu~name cu~telephone cu~custtype
       b~cancelled
   FROM (
     sbook AS b
     INNER JOIN scarr AS c
     ON b~carrid = c~carrid

     INNER JOIN scustom AS cu
     ON b~customid = cu~id
   )
   INTO TABLE it_book
   WHERE b~carrid = p_carrid
     AND b~connid = p_connid
     AND b~fldate = p_fldate
     AND b~customid IN s_custid
     AND b~cancelled = lv_cancelled.
  ENDIF.
ENDFORM.                    " DATA_RETRIEVAL