SQLize Online / PHPize Online  /  SQLtest Online

TYPES :BEGIN OF IT, BELNR TYPE RBKP-BELNR, BUKRS TYPE RBKP-BUKRS, GJAHR TYPE RBKP-GJAHR, LIFNR TYPE RBKP-LIFNR, BUDAT TYPE RBKP-BUDAT, blart TYPE RBKP-BLART, Xrech TYPE RBKP-Xrech, OBJKE TYPE BKPF-AWKEY, FI_NO TYPE BKPF-BELNR, Doc_Date TYPE BKPF-BLDAT, NAME1 TYPE T001W-NAME1, EBELN TYPE RSEG-EBELN, VEN_NAME TYPE LFA1-NAME1, STCD3 TYPE LFA1-STCD3, XBLNR TYPE RBKP-XBLNR, WERKS TYPE RSEG-WERKS, HWBAS TYPE bset-HWBAS, KBETR TYPE bset-KBETR, JIS TYPE bset-HWSTE, JIC TYPE bset-HWSTE, JII TYPE bset-HWSTE, JIU TYPE bset-HWSTE, DMBTR TYPE bseg-DMBTR, MENGE TYPE RSEG-MENGE, DMBTR_TAX TYPE BSEG-DMBTR, NOT TYPE STRING, BEZEI TYPE T005U-BEZEI, ORT01 TYPE lfa1-ORT01, SGTXT TYPE bseg-SGTXT, HSN_SAC TYPE bseg-HSN_SAC, END OF IT. DATA : IT001 TYPE TABLE OF IT. TYPES : BEGIN OF TAB, VBELN TYPE VBRK-VBELN, BUKRS TYPE VBRK-BUKRS, FKSTO TYPE VBRK-FKSTO, FKART TYPE VBRK-FKART, BLART TYPE VBRK-BLART, GJAHR TYPE VBRK-GJAHR, KUNAG TYPE VBRK-KUNAG, XBLNR TYPE VBRK-XBLNR, BELNR TYPE BSEG-BELNR, BUZEI TYPE BSEG-BUZEI, BLDAT TYPE BKPF-BLDAT, BUDAT TYPE BKPF-BUDAT, AUBEL TYPE VBRP-AUBEL, WERKS TYPE VBRP-WERKS, NAME1 TYPE T001W-NAME1, KUNNR TYPE KNA1-KUNNR, STCD3 TYPE KNA1-STCD3, NAME1_KNA1 TYPE KNA1-NAME1, KBETR TYPE bset-KBETR, JOS TYPE bset-HWSTE, JOC TYPE bset-HWSTE, JOI TYPE bset-HWSTE, JOU TYPE bset-HWSTE, MENGE TYPE bseg-MENGE, DMBTR TYPE bseg-DMBTR, HWBAS TYPE bseT-HWBAS, KBETR_KAB TYPE bseT-KBETR, DMBTR_TAX TYPE BSEG-DMBTR, NOT_NA TYPE STRING, BEZEI TYPE T005U-BEZEI, ORT01 TYPE lfa1-ORT01, SGTXT TYPE bseg-SGTXT, HSN_SAC TYPE bseg-HSN_SAC, END OF TAB. DATA : TAB001 TYPE TABLE OF TAB. ranges : r_blnr for bseg-belnr. ranges : r_blnr_fi for bseg-belnr. ranges : r_bu for bseg-bukrs. ranges : r_gj for bseg-gjahr. DATA : IT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV, WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV, WA_LAYOUT TYPE SLIS_LAYOUT_ALV. SELECTION-SCREEN BEGIN OF BLOCK b2 ."WITH FRAME TITLE TEXT-002. SELECT-OPTIONS: s_bukrs FOR bseg-bukrs . SELECT-OPTIONS: p_Lifnr FOR rbkp-Lifnr MODIF ID G1 . SELECT-OPTIONS: P_VBELN FOR VBRK-VBELN MODIF ID G2. SELECT-OPTIONS: p_budat FOR rbkp-budat . SELECT-OPTIONS: p_GJAHR FOR rbkp-GJAHR . SELECTION-SCREEN END OF BLOCK b2 . SELECTION-SCREEN BEGIN OF BLOCK b1." WITH FRAME TITLE text. PARAMETERS :rad1 RADIOBUTTON GROUP rad USER-COMMAND frad1 DEFAULT 'X', rad2 RADIOBUTTON GROUP rad . SELECTION-SCREEN END OF BLOCK b1. AT SELECTION-SCREEN OUTPUT. IF rad1 = 'X'. LOOP AT SCREEN. IF screen-group1 = 'G1' . screen-active = '1'. ELSEIF screen-group1 = 'G2'. screen-active = '0'. ENDIF. MODIFY SCREEN. ENDLOOP. ELSEIF rad2 = 'X'. LOOP AT SCREEN. IF screen-group1 = 'G1' . screen-active = '0'. ELSEIF screen-group1 = 'G2'. screen-active = '1'. ENDIF. MODIFY SCREEN. ENDLOOP. ENDIF. clear sy-ucomm. START-OF-SELECTION. IF rad1 = 'X'. PERFORM get_data. PERFORM DISPLAY_DATA. ELSE . PERFORM get_data_0001. PERFORM DISPLAY_DATA_0001. ENDIF. *&---------------------------------------------------------------------* *& Form get_data *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM get_data . SELECT A~belnr, A~GJAHR, A~BUKRS, A~LIFNR, A~BUDAT, A~blart, A~Xrech, A~XBLNR, b~WERKS, C~NAME1, B~EBELN, D~NAME1 AS VEN_NAME, D~STCD3, f~kbetr FROM rbkp AS A INNER JOIN RSEG AS B ON ( A~BELNR = B~BELNR AND A~GJAHR = B~GJAHR AND A~BUKRS = B~BUKRS ) LEFT JOIN T001W AS C ON ( C~WERKS = B~WERKS ) LEFT JOIN LFA1 AS D ON ( D~lifnr = a~lifnr ) LEFT JOIN bset AS f ON ( f~BELNR = a~BELNR AND f~GJAHR = a~GJAHR AND f~BUKRS = a~BUKRS ) into CORRESPONDING FIELDS OF TABLE @it001 BYPASSING BUFFER WHERE a~bukrs in @s_bukrs and A~Lifnr in @p_lifnr and A~GJAHR in @p_gjahr and A~budat in @p_budat AND A~blart = 'RE' and A~Xrech = ' ' . SELECT A~BELNR AS FI_NO, A~BUKRS, A~GJAHR, A~LIFNR, A~DMBTR, B~BUDAT, B~blart, C~HWBAS, C~kbetr, b~XBLNR, B~BLDAT AS Doc_Date, D~WERKS,E~NAME1, f~NAME1 AS VEN_NAME, f~STCD3 FROM BSEG AS A INNER JOIN BKPF AS B ON ( A~BELNR = B~BELNR AND A~GJAHR = B~GJAHR AND A~BUKRS = B~BUKRS ) LEFT OUTER JOIN BSET AS C ON ( C~BELNR = A~BELNR AND C~BUKRS = A~BUKRS AND C~GJAHR = A~GJAHR AND C~BUZEI = A~BUZEI ) LEFT JOIN RSEG AS D ON ( D~BELNR = A~BELNR AND D~GJAHR = A~GJAHR AND D~BUKRS = A~BUKRS ) LEFT JOIN T001W AS E ON ( E~WERKS = D~WERKS ) LEFT JOIN LFA1 AS f ON ( f~lifnr = a~lifnr ) APPENDING CORRESPONDING FIELDS OF TABLE @it001 BYPASSING BUFFER WHERE A~BUKRS IN @S_BUKRS AND A~GJAHR IN @P_GJAHR AND A~LIFNR in @p_LIFNR AND B~budat in @p_budat AND A~KOART = 'K' AND B~BLART = 'KG' AND B~XREVERSAL = '' . loop at it001 ASSIGNING FIELD-SYMBOL(<F001>) . CONCATENATE <F001>-belnr <F001>-gjahr INTO <F001>-OBJKE. CONDENSE <F001>-OBJKE NO-GAPS. ENDLOOP. IF it001 IS NOT INITIAL. SELECT a~belnr, a~GJAHR,a~BUKRS, a~AWKEY ,a~BUDAT, a~BLDAT, b~DMBTR, b~KOART FROM BKPF as a INNER JOIN bseg as b on ( A~BELNR = B~BELNR AND A~GJAHR = B~GJAHR AND A~BUKRS = B~BUKRS ) into TABLE @DATA(it002) FOR ALL ENTRIES IN @it001 WHERE a~bukrs = @IT001-bukrs and a~GJAHR = @IT001-gjahr and a~budat = @IT001-budat AND a~AWKEY = @IT001-objke and b~KOART = 'K'. ELSE. MESSAGE 'DATA NOT FOUND' TYPE 'I'. ENDIF. loop at it001 ASSIGNING FIELD-SYMBOL(<WA001>) . READ TABLE IT002 INTO DATA(WA002) WITH KEY AWKEY = <WA001>-objke . IF SY-SUBRC = 0. <WA001>-FI_NO = WA002-belnr. <WA001>-Doc_Date = WA002-bldat. <WA001>-BUDAT = WA002-bUdat. <wa001>-DMBTR = WA002-DMBTR . ENDIF . <wa001>-KBETR = <wa001>-KBETR / 10. CLEAR : WA002. ENDLOOP. loop at it001 into DATA(hj). r_blnr-sign = 'I'. r_blnr-option = 'EQ'. r_blnr-low = hj-belnr. append r_blnr. endloop. loop at it001 into hj. r_blnr_fi-sign = 'I'. r_blnr_fi-option = 'EQ'. r_blnr_fi-low = hj-fi_no. append r_blnr_fi. endloop. loop at it001 into hj. r_bu-sign = 'I'. r_bu-option = 'EQ'. r_bu-low = hj-bukrs. append r_bu. endloop. loop at it001 into hj. r_gj-sign = 'I'. r_gj-option = 'EQ'. r_gj-low = hj-gjahr. append r_gj. endloop. ****-----------------------------BLAT = RE ---------------* SELECT A~BELNR,A~BUKRS, A~GJAHR, SUM( A~DMBTR ) AS DMBTR FROM BSEG AS A INTO TABLE @data(it_DMBTR) WHERE A~BELNR in @r_blnr AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~KOART = 'S' AND A~BUZID <> 'T' GROUP BY A~belnr,A~BUKRS, A~GJAHR. SELECT A~BELNR,A~BUKRS, A~GJAHR, SUM( A~DMBTR ) AS DMBTR FROM BSEG AS A APPENDING TABLE @it_DMBTR WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~KOART = 'S' AND A~BUZID <> 'T' GROUP BY A~belnr,A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIC) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIC' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIS) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIS' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JII) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JII' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIU) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIU' GROUP BY A~belnr, A~BUKRS, A~GJAHR. ****-----------------------------BLAT = KG ----- ---------------* SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIC_1) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIC' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIS_1) WHERE A~BELNR in @r_blnr AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIS' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JII_1) WHERE A~BELNR in @r_blnr AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JII' GROUP BY A~belnr, A~BUKRS, A~GJAHR. SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~HWSTE ) AS HWSTE FROM BSet AS A INTO TABLE @data(it_HWSTE_JIU_1) WHERE A~BELNR in @r_blnr AND A~bukrs in @r_bu AND A~GJAHR in @r_gj AND A~Ktosl = 'JIU' GROUP BY A~belnr, A~BUKRS, A~GJAHR. ****-----------------------------END OF BLAR ---------------* ****----------------------------- RE ---------------* SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~MENGE ) AS MENGE FROM RSEG AS A INTO TABLE @data(MENGE_RE) WHERE A~BELNR in @r_blnr AND A~bukrs in @r_bu AND A~GJAHR in @r_gj GROUP BY A~belnr, A~BUKRS, A~GJAHR. ****----------------------------- KG----------- ---------------* SELECT A~BELNR, A~BUKRS, A~GJAHR, SUM( A~MENGE ) AS MENGE FROM BSEG AS A INTO TABLE @data(MENGE_KG) WHERE A~BELNR in @r_blnr_FI AND A~bukrs in @r_bu AND A~GJAHR in @r_gj GROUP BY A~belnr, A~BUKRS, A~GJAHR. ****----------------------------- END OF ---------------* SELECT B~belnr, B~GJAHR,B~BUKRS, b~DMBTR, b~KOART,B~SHKZG,B~LIFNR,b~BUPLA,b~SGTXT,b~HSN_SAC,C~REGIO,D~BEZEI, e~ORT01 FROM bseg as b INNER JOIN LFA1 AS C on ( C~LIFNR = B~LIFNR ) LEFT JOIN T005U AS D ON ( D~BLAND = C~REGIO ) LEFT JOIN lfa1 AS E ON ( e~WERKS = b~BUPLA ) into TABLE @DATA(it003) FOR ALL ENTRIES IN @it001 WHERE B~bukrs = @IT001-bukrs and B~GJAHR = @IT001-gjahr AND B~BELNR = @IT001-FI_NO and b~KOART = 'K' AND D~SPRAS = 'E' AND d~LAND1 = 'IN'. LOOP AT it001 ASSIGNING FIELD-SYMBOL(<fk>). READ TABLE it_dmbtr INTO data(hk) WITH KEY belnr = <fk>-fi_no bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <fk>-dmbtr_tax = hk-dmbtr. ENDIF . IF <FK>-blart = 'RE'. READ TABLE it_HWSTE_JIS_1 INTO data(WA_HWSTE_JIS_1) WITH KEY belnr = <fk>-belnr bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIS = WA_HWSTE_JIS_1-hwste . ENDIF . READ TABLE it_HWSTE_JIC_1 INTO data(WA_HWSTE_JIC_1) WITH KEY belnr = <fk>-belnr bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIC = WA_HWSTE_JIC_1-hwste . ENDIF . READ TABLE it_HWSTE_JII_1 INTO data(WA_HWSTE_JII_1) WITH KEY belnr = <fk>-belnr bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JII = WA_HWSTE_JII_1-hwste . ENDIF . READ TABLE it_HWSTE_JIU_1 INTO data(WA_HWSTE_JIU_1) WITH KEY belnr = <fk>-belnr bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIU = WA_HWSTE_JIU_1-hwste . ENDIF . ELSE. READ TABLE it_HWSTE_JIS INTO data(WA_HWSTE_JIS) WITH KEY belnr = <fk>-FI_NO bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIS = WA_HWSTE_JIS-hwste . ENDIF . READ TABLE it_HWSTE_JIC INTO data(WA_HWSTE_JIC) WITH KEY belnr = <fk>-fi_no bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIC = WA_HWSTE_JIC-hwste . ENDIF . READ TABLE it_HWSTE_JII INTO data(WA_HWSTE_JII) WITH KEY belnr = <fk>-fi_no bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JII = WA_HWSTE_JII-hwste . ENDIF . READ TABLE it_HWSTE_JIU INTO data(WA_HWSTE_JIU) WITH KEY belnr = <fk>-fi_no bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-JIU = WA_HWSTE_JIU-hwste . ENDIF . ENDIF. IF <FK>-blart = 'RE'. READ TABLE MENGE_RE INTO data(WA_MENGE_RE) WITH KEY belnr = <fk>-belnr bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-menge = WA_MENGE_RE-menge . ENDIF . ELSE. READ TABLE MENGE_KG INTO data(WA_MENGE_KG) WITH KEY belnr = <fk>-FI_NO bukrs = <fk>-bukrs gjahr = <fk>-gjahr. if sy-subrc = 0 . <FK>-menge = WA_MENGE_KG-menge . ENDIF . ENDIF. READ TABLE IT003 INTO DATA(WA003) WITH KEY BELNR = <fk>-FI_NO bukrs = <fk>-bukrs gjahr = <fk>-GJAHR. IF SY-SUBRC = 0. IF WA003-shkzg = 'S'. DATA(NOT) = 'Debit Note'. ELSE. NOT = 'Credit Note'. ENDIF. <fk>-NOT = NOT . <fk>-BEZEI = wa003-BEZEI. <fk>-ORT01 = wa003-ORT01. <fk>-SGTXT = wa003-SGTXT. <fk>-HSN_SAC = wa003-HSN_SAC. ENDIF. CLEAR : WA_HWSTE_JIS ,WA_HWSTE_JIC, WA_HWSTE_JIU ,WA_HWSTE_JII, WA_HWSTE_JIU_1 , WA_HWSTE_JIS_1 ,WA_HWSTE_JIC_1 ,WA_HWSTE_JII_1,WA_MENGE_RE,WA003. ENDLOOP. SORT it001 BY belnr. ENDFORM.