Make your own free website on Tripod.com

Table of Contents:



REPORT ZIJALLIANCE LINE-SIZE 135
      LINE-COUNT 65 NO STANDARD PAGE HEADING.
*@----------------------------------------------------------------------
*@ Program id    : ZALLIANCE
*@ Program Desc  : This is a report that gives a summary of each sales
*@ order.  All the billing documents are added together for each sales
*@ order and the credits and depits are separated.
*@ Sorted by Job type, job date, and order #.
*@
*@ Transaction Code - ZVALLIANCE
*@ Input files   : ID - none
*@ Output files  : ID - none
*@
*@ Tables Updated: Table - none
*@
*@
*@ Author      : IJADALOW
*@ Date        : MAY 29, 2001
*@ Requested by:
*@----------------------------------------------------------------------
*@ Algorithm:
*@
*@----------------------------------------------------------------------
** Change History:
** Correction  Mod by  Date      Description
** LABK931392  IJADALOW  December 04, 2001.
**             Replacing tables TVV1 and TVV1T, with tables T2502 and
**             T25A2 respectively.  T2502 contains all job types from
**             the past as well as current ones.
**             Requestd by Ophelia Tik.
**             Look for "IJ1" for the changes made.
*@----------------------------------------------------------------------
*@----------------------------------------------------------------------
**                     Table Declarations                             **
TABLES: VBAK,
        VBRK,
        VBPA,
        bkpf,
*       tvv1t.                    "ij1
        t25a2.                    "ij1

TABLES: VBFA.

INCLUDE .
INCLUDE ZSMASTER.

TYPEs:
  BEGIN OF t_bills,
    kvgr1  LIKE vbak-kvgr1,    "job type
    aubel  like vbrp-aubel,    "sales order #
    xblnr  like bkpf-xblnr,    "Billing doc #
    Fkart  like vbrk-fkart,    "Billing Type
    netwr  like vbrk-netwr,    "Net Value
    vdatu  LIKE vbak-vdatu,    "job date=requested delivery date
    budat  like bkpf-budat,    "billing date
    auart  LIKE vbak-auart,    "doc. type
    bukrs  LIKE bkpf-bukrs,    "Company code/sales organization
    kunnr  LIKE vbak-kunnr,    "customer = sold-to-party
    vgbel  like vbak-vgbel,    "Reference Document
    credit       like vbrk-netwr,          "Credit Memo
    baschr(4)    TYPE c,                   "base in charge
    noncform(12) TYPE c,                   "non conformance
    location     TYPE mac_t_so_location,   "adrc-name1 / sadr-name1,
    formation    TYPE mac_t_so_formation,  "adrc-city2
*   description  like tvv1t-bezei,         "Job Description       "ij1
    description  like t25a2-bezek,         "Job Description       "ij1

  END OF t_bills.

TYPES: BEGIN OF t_download,
        location     TYPE mac_t_so_location,   "adrc-name1 / sadr-name1,
        vdatu  LIKE vbak-vdatu,        "job date=requested delivery date
        aubel  like vbrp-aubel,        "sales order #
*       description  like tvv1t-bezei, "Job Description    "IJ1
        description  like t25a2-bezek, "Job Description    "IJ1
        baschr(4)    TYPE c,           "base in charge
        total        like vbrk-netwr,
        noncform(12) TYPE c,           "non conformance
        credit(20)   TYPE c,           "Credit Memo
       END OF t_download.

  DATA:  wa_bill type t_bills,
         bills like standard table of wa_bill,
         itab  like standard table of wa_bill,
         wa_download TYPE t_download,
         it_download LIKE STANDARD TABLE OF wa_download.


  DATA: BEGIN OF i_auart OCCURS 1,
          sign(1)  TYPE c,
          option(2) TYPE c,
          low(18)   TYPE c,
          high(18)  TYPE c,
        END OF i_auart.

  DATA: sub_total like wa_bill-netwr,
        late_msg(35) type c,
        first_msg(80),
        second_msg(80),
        cust_name    like kna1-name1.          "customer name.

DATA: BEGIN OF it_title OCCURS 10,
        title(15)   TYPE c,
      END OF it_title.

DATA: w_excel_default_path(20) TYPE c,
      w_excel_default_file(20) TYPE c,
      w_excel_filename  LIKE ibipparms-path.

SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_compny FOR bkpf-bukrs
                NO INTERVALS NO-EXTENSION DEFAULT '1000'.
SELECT-OPTIONS: s_kunnr FOR vbak-kunnr NO INTERVALS NO-EXTENSION.
SELECT-OPTIONS: s_pstDat FOR bkpf-budat.
SELECT-OPTIONS: s_delDat FOR vbak-vdatu.
SELECTION-SCREEN END OF BLOCK 1.

INITIALIZATION.
*  s_delDat-LOW = '20010401'.
*  s_delDat-HIGH = '20010430'.
*  s_pstDat-LOW = '20010401'.
*  s_pstDat-HIGH = '20010412'.
*  APPEND s_delDat.

END-OF-SELECTION.
  SET PF-STATUS 'STATUS1'.
  PERFORM load_data.
  PERFORM display_data.

TOP-OF-PAGE.
  CONCATENATE 'Alliance Report for_'  cust_name
         INTO   first_msg.
  CONCATENATE 'Billing Date:_'
              s_pstDat-low(4) '/ '
              s_pstDat-low+4(2) '/ '
              s_pstDat-low+6(2)
              ' To_'
              s_pstDat-high(4) '/ '
              s_pstDat-high+4(2) '/ '
              s_pstDat-high+6(2)
         INTO second_msg.

*  mac_default_report_header mac_c_null.

  MAC_GENERIC_REPORT_HEADER first_msg second_msg MAC_C_TRUE MAC_C_TRUE.

  IF NOT late_msg is initial.
    WRITE: late_msg TO MAC_ZSMDISPL_G_LINE1(SY-LINSZ) CENTERED.
    WRITE: MAC_ZSMDISPL_G_LINE1.
  ENDIF.

  PERFORM print_col_headings.
*/---------------------------------------------------------\*

AT USER-COMMAND.

 CASE sy-ucomm.
   WHEN 'DOWNLOAD'.
      CLEAR w_excel_filename.
      CALL FUNCTION 'WS_FILENAME_GET'
           EXPORTING
                def_filename     = w_excel_default_file
                def_path         = w_excel_default_path
                mask             = ',*.* ,*.*.'
                mode             = 'S'
                title            = 'Select Output Excel File'
           IMPORTING
                filename         = w_excel_filename
           EXCEPTIONS
                inv_winsys       = 1
                no_batch         = 2
                selection_cancel = 3
                selection_error  = 4
                OTHERS           = 5.

      IF NOT w_excel_filename IS INITIAL.
        PERFORM proc_download.
      ENDIF.
    ENDCASE.

*&---------------------------------------------------------------------*
*&      Form  LOAD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM LOAD_DATA.

  SELECT bkpf~xblnr bkpf~budat vbrp~aubel
       into corresponding fields of table bills
       from bkpf join vbrp on bkpf~xblnr = vbrp~vbeln
       WHERE bkpf~bukrs IN s_compny   "Company code
        AND  bkpf~budat IN s_pstDat   "posting date
        AND  bkpf~blart = 'RV'
        AND  bkpf~bstat = ''.

  LOOP AT bills into wa_bill.

   IF wa_bill-aubel(4) = '0051'.  "This is a reference document
      SELECT SINGLE vgbel         "so we get the actual one: '0011*'
        INTO wa_bill-aubel
        FROM vbak
        WHERE vbeln = wa_bill-aubel.
   ENDIF.

   SELECT vdatu kvgr1 kunnr
        FROM vbak
        INTO CORRESPONDING FIELDS OF wa_bill
        WHERE vbeln = wa_bill-aubel
        AND   kunnr IN s_kunnr.
****    Get Job description
*** IJ1 Replace Starts
*        SELECT SINGLE bezei FROM tVV1t
        SELECT SINGLE bezek FROM t25a2
          INTO  wa_bill-description
          WHERE kvgr1 = wa_bill-kvgr1.

*       wa_bill-description = wa_bill-description+3.
*** IJ1 Repalce Ends

        append wa_bill to itab.

   ENDSELECT.

  ENDLOOP.

  SELECT SINGLE name1 from kna1
    INTO cust_name
    WHERE kunnr in s_kunnr.

ENDFORM.                    " LOAD_DATA

*/-------------------------------------------------------------------*\


FORM print_col_headings.

  FORMAT COLOR COL_HEADING.
  NEW-LINE.
  WRITE 01 line_top_left_corner AS LINE.
  ULINE 02(135).
  WRITE: 135 line_top_right_corner AS LINE NO-GAP.

  WRITE: /    SY-VLINE NO-GAP,
          002 'Location'(002),
          032 SY-VLINE NO-GAP,
          033 'Job Date'(003),
          043 SY-VLINE NO-GAP,
          044 'SalesOrder'(004),
          054 SY-VLINE NO-GAP,
          055 'Job Type'(005),
          075 SY-VLINE NO-GAP,
          076 'Base'(006),
          080 SY-VLINE NO-GAP,
          081 'Total'(007),
          101 SY-VLINE NO-GAP,
          102 'Non_Conf #'(008),
          114 SY-VLINE NO-GAP,
          115 'Credit'(009),
          135 SY-VLINE NO-GAP.

  uline.
  FORMAT RESET.
ENDFORM.
*/-------------------------------------------------------------------*\
*&---------------------------------------------------------------------*
*&      Form  display_data
*&---------------------------------------------------------------------*
*     - Display all the data that fall in the user's input criteria
*     -
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM  display_data.
  DATA: sub_total   like wa_bill-netwr      value 0,
        grand_total like wa_bill-netwr      value 0,
        job_type    like wa_bill-description.

  FORMAT COLOR COL_NORMAL.
  CLEAR late_msg.

  SORT itab by aubel.    "sort by SO #
  DELETE ADJACENT DUPLICATES FROM itab COMPARING aubel.

  SORT itab BY description vdatu aubel.


*/-- We will separate the Jobs pertaining to Prior months that
*/-- are outside the range and We will print them Below on the
*/-- same report.

  LOOP at itab INTO wa_bill.

    PERFORM GET_details.

    IF wa_bill-vdatu >= s_delDat-low and
       wa_bill-vdatu <= s_delDat-high.
         sub_total = sub_total + wa_bill-netwr.
         PERFORM detail_line.
         PERFORM to_download.
    ENDIF.

    job_type = wa_bill-description.

    AT END OF kvgr1.
     IF not sub_total is initial.
       FORMAT COLOR COL_TOTAL.
       ULINE.
       WRITE: /001 SY-VLINE NO-GAP,
              054 SY-VLINE NO-GAP,
              055 job_type,
              075 SY-VLINE NO-GAP,
              080 SY-VLINE NO-GAP,
              081 sub_total,
              101 SY-VLINE NO-GAP,
              135 SY-VLINE NO-GAP.
       ADD sub_total TO grand_total.
       CLEAR sub_total.
       ULINE.
       FORMAT COLOR COL_NORMAL.
     ENdIF.
    ENDat.
  ENDLOOP.
*/--- Grand Total
  FORMAT COLOR COL_TOTAL.
  WRITE: /001 SY-VLINE NO-GAP,
          003 'Grand Total'(010),
          080 SY-VLINE NO-GAP,
          081 grand_total,
          101 SY-VLINE NO-GAP,
          135 SY-VLINE NO-GAP.
   CLEAR grand_total.
   ULINE.
   FORMAT COLOR COL_NORMAL.

*/--- The Exception jobs are listed at the bottom of the list.
  NEW-PAGE.
  late_msg = 'JOBS PERTAINING TO PRIOR MONTHS'.
  clear sub_total.

  LOOP at itab INTO wa_bill.

    IF wa_bill-vdatu < s_delDat-low.
      sub_total = sub_total + wa_bill-netwr.
      PERFORM detail_line.
    ENDIF.
    job_type = wa_bill-description.

    AT END OF kvgr1.
     IF not sub_total is initial.
       FORMAT COLOR COL_TOTAL.
       ULINE.
       WRITE: /001 SY-VLINE NO-GAP,
              054 SY-VLINE NO-GAP,
              055 job_type,
              075 SY-VLINE NO-GAP,
              080 SY-VLINE NO-GAP,
              081 sub_total,
              101 SY-VLINE NO-GAP,
              135 SY-VLINE NO-GAP.
       CLEAR sub_total.
       ULINE.
       FORMAT COLOR COL_NORMAL.
     ENdIF.
    ENDat.
  ENDLOOP.

  FORMAT RESET.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  GET_details
*&---------------------------------------------------------------------*
*     - Calculate the Total net value and credits for each sales order
*     -
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_Details.

    CLEAR: wa_bill-netwr, wa_bill-credit.

    MAC_GET_BILL_DETAILS_FROM_SO wa_bill-aubel
                                 MAC_ZSMABAP4_I_SO_BILL_DETAILS.

    LOOP AT MAC_ZSMABAP4_I_SO_BILL_DETAILS WHERE KEY = 'TL'.
      IF MAC_ZSMABAP4_I_SO_BILL_DETAILS-type CA 'OS'.
          ADD MAC_ZSMABAP4_I_SO_BILL_DETAILS-NETWR TO wa_bill-credit.
      ELSEIF MAC_ZSMABAP4_I_SO_BILL_DETAILS-type CA 'PMN'.
          ADD MAC_ZSMABAP4_I_SO_BILL_DETAILS-NETWR TO wa_bill-netwr.
      ENDIF.
    ENDLOOP.

    mac_get_so_header_text wa_bill-aubel
       mac_c_so_text_base_in_charge wa_bill-baschr.

    mac_get_so_header_text wa_bill-aubel
      mac_c_text_so_nonconformance wa_bill-noncform.

    mac_get_so_well_loc_for wa_bill-aubel
      wa_bill-location wa_bill-formation.

    MODIFY itab from wa_bill.

ENDFORM.                    " GET_TOTALS

*&---------------------------------------------------------------------*
*&      Form  DETAIL_LINE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DETAIL_LINE.
       write: /001 SY-VLINE NO-GAP,
               002 wa_bill-location,
               032 SY-VLINE NO-GAP,
               033 wa_bill-vdatu,
               043 SY-VLINE NO-GAP,
               044 wa_bill-aubel,
               054 SY-VLINE NO-GAP,
               055 wa_bill-description,
               075 SY-VLINE NO-GAP,
               076 wa_bill-baschr,
               080 SY-VLINE NO-GAP,
               081 wa_bill-netwr,
               101 SY-VLINE NO-GAP,
               102 wa_bill-noncform,
               114 SY-VLINE NO-GAP,
               115 wa_bill-credit,
               135 SY-VLINE NO-GAP.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  TO_DOWNLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
FORM TO_DOWNLOAD.

  wa_download-location = wa_bill-location.
  wa_download-vdatu    = wa_bill-vdatu.
  wa_download-aubel    = wa_bill-aubel.
  wa_download-description = wa_bill-description.
  wa_download-baschr   = wa_bill-baschr.
  wa_download-total    = wa_bill-netwr.
  wa_download-noncform = wa_bill-noncform.
  IF wa_bill-credit IS INITIAL.
     wa_download-credit = ' '.
  ELSEIF wa_bill-credit < 0.
     wa_download-credit = wa_bill-credit * -1.
  ELSE.
     wa_download-credit = wa_bill-credit.
  ENDIF.

  APPEND wa_download TO it_download .

ENDFORM.                    " TO_DOWNLOAD
*&---------------------------------------------------------------------*
*&      Form  proc_DOWNLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
FORM proc_download.

  REFRESH it_title.

  DEFINE lmac_append_title.
    it_title-title = &1.
    append it_title.
  END-OF-DEFINITION.

    lmac_append_title: text-002, text-003, text-004, text-005,
    text-006, text-007, text-008, text-009.

  mac_ws_download_titled_itab w_excel_filename it_download it_title.
  IF sy-subrc = 0.
    MESSAGE i001(zz) WITH 'Report has been downloaded to '
                           w_excel_filename.
  ELSE.
    MESSAGE i001(zz) WITH 'Unable to download to ' w_excel_filename.
  ENDIF.

ENDFORM.                    " PROC_DOWNLOAD