FM to read the spool list recipient maintianed in SM36

[Former Member]

Hi All,

My requirement is to send the output as Excel to all Email id/distribution list maintained in Spool list recipient inSM36 when scheduling a batch job.

When the batch is executed , in run time I need to read what ID's they have given in Spool recipient.

Any FM or tables?

[NTeunckens · Accepted solution]

Check Table "TBTCO" for your Job-characteristics for field "RECOBJTYPE" with value 'RECIPIENT' and check the "RECOBJKEY" for the Distribution List ... Then you can use FM "SO_DLI_LIST_READ" to get the E-mail ID's if necessary (or Fetch directly from tables "SOID" / "SODM" / "SOUD").

[Former Member · Accepted solution]

Hi Nic,

Thank you .I found the distribution list name from SOID table only passing the RECOBJKEY.

I need one more clarrification.

If user gives Internal user ,External address, Org Unit in SM36 spool recipient how to find it.?

If user give internal user the - The RECOBJKEY values is like USR41000000000XXX 410000000XXXX

If user give Distribution list - The RECOBJKEY value isDLI410000000000XX 410000000XXXX

Can u tel me the tables for internal user ,External address, Org Unit.

Thanks

James

[NTeunckens]

OK, then you can make use of FM "SO_USER_ADDRESS_READ_API1" and fill the TABLES Param "USER_ADDRESS-USERID" with that first part of the 'RECOBJKEY' ("&AD 0000039412") ... Execute the Function, and that should work ...

[Former Member]

Hi Nic,

Thank you its working good.

Can you tel me all the FM for reading all cases of spool recipient list in organizational unit

1. SM36 ->spool recipient ->Organizational Unit -> (Workcenter , Job, Person, User, Position)

2. SM36 ->spool recipient ->Addressess and internal users

Thanking you,

james

[stephenl1]

Worked a treat for me as well. Thanks.

[NTeunckens]

Hello James

Maybe you could try this way when you use the DistributionList-result :

• Get the "OBJNAM" (Distr.List-Name) from Table "SOOD" => Use 'RECOBJKEY'(3) as OBJTYP / 'RECOBJKEY'+3(2) as OBJYR and 'RECOBJKEY'+5 as OBJNO ...
• Use the resulting "OBJNAM" as ImportParam for FMP "SO_DLI_READ_API1" and check the results in Table "DLI_ENTRIES" which wil hold the Name and E-mail ...

Or maybe you already have the Distr.List, so you can skip everything before the FM?

Hope this helps ...

[Former Member]

hi Nic,

my requirement is ,

In SM36 ->spool recipient -> Choose external address

now schedule the job.

In Tbtco table the entry is like for RECOBJKEY = &AD 0000039412INT 41000000023378

Now to identiify what user from the given the recobjkey value

[Former Member]

Hi Nic,

Thank you .I found the distribution list name from SOID table only passing the RECOBJKEY.

I need one more clarrification.

If user gives Internal user ,External address, Org Unit in SM36 spool recipient how to find it.?

If user give internal user the - The RECOBJKEY values is like USR41000000000XXX 410000000XXXX

If user give Distribution list - The RECOBJKEY value isDLI410000000000XX 410000000XXXX

Can u tel me the tables for internal user ,External address, Org Unit.

Thanks

James

[Prema55331]

"-----------------------------Distribution list mail id---------------------------------------.
types: begin of ty_job,
        jobname    type btcjob,
        jobcount  type btcjobcnt,
        stepcount  type btcstepcnt,
        progname  type btcprog,
        sdldate   type btcsdldate,
        sdltime   type btcsdltime,
        status    type btcpstatus,
        recobjkey type swo_typeid,
end of ty_job.

DATA:   it_job type table of ty_job with header line,
        wa_job type ty_job.

types: begin of ty_dlist,
        objnam type so_obj_nam,
        dlitp  type so_dli_tp,
        dliyr  type so_own_yr,
        dlino  type so_own_no,
        recobjkey type swo_typeid,
end of ty_dlist.

DATA: it_dlist type table of ty_dlist WITH HEADER LINE,
      wa_dlist type ty_dlist.

DATA: job_key type string,
      receiver like soodd-objnam.

DATA: lv_part1 TYPE string,
      lv_part2 TYPE string.

data: it_tbtco type table of tbtco,
      wa_tbtco type tbtco.
"--------------------------XLSX FILE FORMAT---------------------------------------------------------------------.

DATA: it_member TYPE STANDARD TABLE OF sodm1,
      it_selc   TYPE STANDARD TABLE OF selc,
      it_soopl  TYPE STANDARD TABLE OF soop1.


DATA : gr_table     TYPE REF TO cl_salv_table,
       gr_columns   TYPE REF TO cl_salv_columns_table,
       l_column     TYPE REF TO cl_salv_column,
       lv_xml_type  TYPE salv_bs_constant,
       lt_mailtxt        TYPE STANDARD TABLE OF soli,
       l_mailtxt         TYPE  soli,
       gt_len       TYPE i,
       gt_srctab    TYPE solix_tab,
       lv_subject   TYPE so_obj_des,
       lv_filename  TYPE string,
       lt_att_head  TYPE soli_tab,
       lv_mail_id   TYPE adr6-smtp_addr,
       lv_xml       TYPE xstring.

DATA: lo_send_request TYPE REF TO cl_bcs.
DATA: lo_document TYPE REF TO cl_document_bcs .
DATA: lo_recipient TYPE REF TO if_recipient_bcs.

" select statement for Distribution list mail id.
select  jobname
        jobcount
        stepcount
        progname
        sdldate
        sdltime
        status from tbtcp into table it_job
        where progname = sy-repid and sdldate = sy-datum and status = 'R'.

select objnam
       dlitp
       dliyr
       dlino from soid into table it_dlist.

loop at it_dlist.
  CONCATENATE it_dlist-dlitp it_dlist-dliyr it_dlist-dlino into job_key.
  it_dlist-recobjkey  =  job_key.
  modify it_dlist.
endloop.


sort it_job by sdldate sdltime DESCENDING.

read table it_job into wa_job index 1.
select single recobjkey into wa_job-recobjkey from tbtco where jobcount = wa_job-jobcount and status = 'R'.

split wa_job-recobjkey at space into lv_part1 lv_part2.

select * from tbtco into table it_tbtco where jobcount = wa_job-jobcount and status = 'R'.

if it_tbtco is initial.
  MESSAGE TEXT-101 type 'E'.
endif.


loop at it_tbtco into wa_tbtco.
wa_tbtco-reclogsys = ''.
wa_tbtco-recobjtype = ''.
wa_tbtco-recobjkey = ''.
wa_tbtco-recdescrib = ''.
UPDATE tbtco from wa_tbtco.
endloop.

read table it_dlist into wa_dlist with key recobjkey = lv_part1.
receiver = wa_dlist-objnam.

CALL FUNCTION 'SO_DLI_READ'
 EXPORTING
   DISTRIBUTIONLIST                  = receiver
*   DLI_ID                           = ' '
   OWNER                             = ' '
   SYSTEM_DLI                        = 'X'
* IMPORTING
*   OBJECT_FL_DISPLAY                =
*   OBJECT_HD_DISPLAY                =
*   OBJECT_ID                        =
*   OBJECT_SD_DISPLAY                =
  TABLES
    member                           = it_member
    objpara                          = it_selc
    objparb                          = it_soopl.

IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.

sort it_member by address.

if abs_view = 'X'.

TRY.
  CALL METHOD cl_salv_table=>factory(
    IMPORTING
     r_salv_table   = gr_table
    CHANGING
      t_table        = it_final[] ).

ENDTRY.

  gr_columns = gr_table->get_columns( ).

TRY.

        l_column = gr_columns->get_column( columnname = 'MATNR'  ).
        l_column->set_long_text( value = 'Material number' ).
        l_column->set_short_text( value = 'MAT.NO' ).
        l_column->set_medium_text( value = 'Material no' ).

        l_column = gr_columns->get_column( columnname = 'MAKTX' ).
        l_column->set_long_text( value = 'Material Desc' ).
        l_column->set_short_text( value = 'MAT.DESC' ).
        l_column->set_medium_text( value = 'Material Desc' ).

*        l_column = gr_columns->get_column( columnname = 'WERKS' ).
*        l_column->set_long_text( value = 'Plant' ).
*        l_column->set_short_text( value = 'PLANT' ).
*        l_column->set_medium_text( value = 'Plant' ).

        l_column = gr_columns->get_column( columnname = 'MEINS' ).
        l_column->set_long_text( value = 'Uom' ).
        l_column->set_short_text( value = 'UOM' ).
        l_column->set_medium_text( value = 'Uom' ).

        l_column = gr_columns->get_column( columnname = 'EISBE' ).
        l_column->set_long_text( value = 'Safety stock' ).
        l_column->set_short_text( value = 'SAF.STOCK' ).
        l_column->set_medium_text( value = 'Safety stock' ).

        l_column = gr_columns->get_column( columnname = 'BSTRF' ).
        l_column->set_long_text( value = 'Rounding value' ).
        l_column->set_short_text( value = 'ROUN.VALUE' ).
        l_column->set_medium_text( value = 'Round.value' ).

        l_column = gr_columns->get_column( columnname = 'BSTMI' ).
        l_column->set_long_text( value = 'Minimum Lot Size' ).
        l_column->set_short_text( value = 'MIN.LOT' ).
        l_column->set_medium_text( value = 'Min.Lot.Size' ).

        l_column = gr_columns->get_column( columnname = 'BSTMA' ).
        l_column->set_long_text( value = 'Maximum Lot Size' ).
        l_column->set_short_text( value = 'MAX.LOT' ).
        l_column->set_medium_text( value = 'Max.Lot.Size' ).

        l_column = gr_columns->get_column( columnname = 'REQ_QTY' ).
        l_column->set_long_text( value = 'Requirement qty' ).
        l_column->set_short_text( value = 'REQ.QTY' ).
        l_column->set_medium_text( value = 'Req.qty' ).

        l_column = gr_columns->get_column( columnname = 'STK' ).
        l_column->set_long_text( value = 'Stock' ).
        l_column->set_short_text( value = 'STOCK' ).
        l_column->set_medium_text( value = 'Stock' ).

        l_column = gr_columns->get_column( columnname = 'OPEN_PO' ).
        l_column->set_long_text( value = 'Open PO qty' ).
        l_column->set_short_text( value = 'OPEN.PO' ).
        l_column->set_medium_text( value = 'Open.PO.qty' ).

        l_column = gr_columns->get_column( columnname = 'MRP_PR' ).
        l_column->set_long_text( value = 'MRP PR qty' ).
        l_column->set_short_text( value = 'MRP.PR' ).
        l_column->set_medium_text( value = 'MRP PR qty' ).

        l_column = gr_columns->get_column( columnname = 'MAN_PR' ).
        l_column->set_long_text( value = 'Manual PR qty' ).
        l_column->set_short_text( value = 'MAN.PR' ).
        l_column->set_medium_text( value = 'Man.PR.qty' ).

        l_column = gr_columns->get_column( columnname = 'STK_1' ).
        l_column->set_long_text( value = 'Excess Stock' ).
        l_column->set_short_text( value = 'EXS.STOCK' ).
        l_column->set_medium_text( value = 'Excess Stock' ).

        l_column = gr_columns->get_column( columnname = 'OPEN_PO_1' ).
        l_column->set_long_text( value = 'Excess Open PO qty' ).
        l_column->set_short_text( value = 'EXS.PO' ).
        l_column->set_medium_text( value = 'Exs.Open.PO.qty' ).

        l_column = gr_columns->get_column( columnname = 'MRP_PR_1' ).
        l_column->set_long_text( value = 'Excess MRP PR qty' ).
        l_column->set_short_text( value = 'EXS.MRP.PR' ).
        l_column->set_medium_text( value = 'Exs.MRP.PR.qty' ).

        l_column = gr_columns->get_column( columnname = 'MAN_PR_1' ).
        l_column->set_long_text( value = 'Excess Manual PR qty' ).
        l_column->set_short_text( value = 'EXS.MAN.PR' ).
        l_column->set_medium_text( value = 'Exs.MAN.PR.qty' ).


CATCH cx_salv_not_found.

ENDTRY.

else.


TRY.
  CALL METHOD cl_salv_table=>factory(
    IMPORTING
     r_salv_table   = gr_table
    CHANGING
      t_table        = IT_DEL_VIEW[] ).

ENDTRY.

  gr_columns = gr_table->get_columns( ).

TRY.

        l_column = gr_columns->get_column( columnname = 'MATNR'  ).
        l_column->set_long_text( value = 'Material number' ).
        l_column->set_short_text( value = 'MAT.NO' ).
        l_column->set_medium_text( value = 'Material no' ).

        l_column = gr_columns->get_column( columnname = 'TXZ01' ).
        l_column->set_long_text( value = 'Material Desc' ).
        l_column->set_short_text( value = 'MAT.DESC' ).
        l_column->set_medium_text( value = 'Material Desc' ).

        l_column = gr_columns->get_column( columnname = 'WERKS' ).
        l_column->set_long_text( value = 'Plant' ).
        l_column->set_short_text( value = 'PLANT' ).
        l_column->set_medium_text( value = 'Plant' ).

        l_column = gr_columns->get_column( columnname = 'SPART' ).
        l_column->set_long_text( value = 'Division' ).
        l_column->set_short_text( value = 'DIV' ).
        l_column->set_medium_text( value = 'Division' ).

        l_column = gr_columns->get_column( columnname = 'EKGRP' ).
        l_column->set_long_text( value = 'Purchase Group' ).
        l_column->set_short_text( value = 'PUR.GROUP' ).
        l_column->set_medium_text( value = 'Purchase Group' ).

        l_column = gr_columns->get_column( columnname = 'EBELN' ).
        l_column->set_long_text( value = 'PO Number' ).
        l_column->set_short_text( value = 'PO.NO' ).
        l_column->set_medium_text( value = 'PO Number' ).

        l_column = gr_columns->get_column( columnname = 'EBELP' ).
        l_column->set_long_text( value = 'PO line item' ).
        l_column->set_short_text( value = 'PO.LINE' ).
        l_column->set_medium_text( value = 'PO line item' ).

        l_column = gr_columns->get_column( columnname = 'EBELP' ).
        l_column->set_long_text( value = 'PO line item' ).
        l_column->set_short_text( value = 'PO.LINE' ).
        l_column->set_medium_text( value = 'PO line item' ).

        l_column = gr_columns->get_column( columnname = 'AEDAT' ).
        l_column->set_long_text( value = 'PO Date' ).
        l_column->set_short_text( value = 'PO DATE' ).
        l_column->set_medium_text( value = 'PO Date' ).

        l_column = gr_columns->get_column( columnname = 'EINDT' ).
        l_column->set_long_text( value = 'Delivery date' ).
        l_column->set_short_text( value = 'DEL.DATE' ).
        l_column->set_medium_text( value = 'Delivery date' ).

        l_column = gr_columns->get_column( columnname = 'LLIEF' ).
        l_column->set_long_text( value = 'Vendor No' ).
        l_column->set_short_text( value = 'VEN.CODE' ).
        l_column->set_medium_text( value = 'Vendor No' ).

        l_column = gr_columns->get_column( columnname = 'LLIEF' ).
        l_column->set_long_text( value = 'Vendor No' ).
        l_column->set_short_text( value = 'VEN.CODE' ).
        l_column->set_medium_text( value = 'Vendor No' ).

        l_column = gr_columns->get_column( columnname = 'NAME1' ).
        l_column->set_long_text( value = 'Vendor Name' ).
        l_column->set_short_text( value = 'VEN.NAME' ).
        l_column->set_medium_text( value = 'Vendor Name' ).

        l_column = gr_columns->get_column( columnname = 'EXS_OPEN_PO' ).
        l_column->set_long_text( value = 'Excess Open PO qty' ).
        l_column->set_short_text( value = 'EXS.PO' ).
        l_column->set_medium_text( value = 'Exs.Open.PO.qty' ).

        l_column = gr_columns->get_column( columnname = 'MENGE_1' ).
        l_column->set_long_text( value = 'PO qty' ).
        l_column->set_short_text( value = 'PO QTY' ).
        l_column->set_medium_text( value = 'PO qty' ).

        l_column = gr_columns->get_column( columnname = 'NETPR' ).
        l_column->set_long_text( value = 'Net value' ).
        l_column->set_short_text( value = 'NET.VALUE' ).
        l_column->set_medium_text( value = 'Net value' ).

        l_column = gr_columns->get_column( columnname = 'NETWR' ).
        l_column->set_long_text( value = 'Total value' ).
        l_column->set_short_text( value = 'TOT.VALUE' ).
        l_column->set_medium_text( value = 'Total value' ).

ENDTRY.

endif.

    lv_xml_type =  if_salv_bs_xml=>c_type_xlsx.
    lv_xml      = gr_table->to_xml( xml_type = lv_xml_type ).

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
  EXPORTING
    buffer                = lv_xml
*   APPEND_TO_TABLE       = ' '
 IMPORTING
    OUTPUT_LENGTH         = gt_len
  tables
    binary_tab            = gt_srctab.

   lv_subject = 'Excess report against Requirement '.

  DATA(lv_msg) = |Please find the attached file for Requirement against Excess report|.


 l_mailtxt-line = 'Dear Team,'.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = ' '.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = lv_msg.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = ' '.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = ' '.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = 'Regards,'.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

      l_mailtxt-line = 'ISD-Team.'.
      APPEND l_mailtxt TO lt_mailtxt.
      CLEAR l_mailtxt.

   lo_send_request = cl_bcs=>create_persistent( ).

* * Create the document
    lo_document = cl_document_bcs=>create_document(
      i_type    = 'RAW'
      i_text    = lt_mailtxt "lv_body
      i_subject = lv_subject ).

    lv_filename = 'Excess report.xlsx'.
    DATA(lv_text_line) = |&SO_FILENAME=| & | { lv_filename }|.
    APPEND lv_text_line TO lt_att_head.


*    Add the attachment
    lo_document->add_attachment(
    i_attachment_type    = 'xls'
    i_attachment_subject = 'Excess report'
    i_attachment_size    =  CONV  so_obj_len( gt_len )
    i_att_content_hex    =    gt_srctab
    i_attachment_header  =  lt_att_head ).


*  * PASS THE DOCUMENT TO SEND REQUEST
    lo_send_request->set_document( lo_document ).

    LOOP AT it_member INTO DATA(wa_member).
      lv_mail_id = wa_member-address.
      IF wa_member-sndcp = ''.
        lo_recipient = cl_cam_address_bcs=>create_internet_address( lv_mail_id ).
        TRY.
            lo_send_request->add_recipient(
            EXPORTING
            i_recipient = lo_recipient
            i_express = 'X' ).
        ENDTRY.
      ENDIF.

      IF wa_member-sndcp = 'X'.
        lo_recipient = cl_cam_address_bcs=>create_internet_address( lv_mail_id ). "HERE RECIPIENT IS EMAIL INPUT P_EMAIL
        TRY.
            lo_send_request->add_recipient(
            EXPORTING
            i_recipient = lo_recipient
              i_copy      = 'X'
            i_express = 'X' ).
        ENDTRY.
      ENDIF.
    ENDLOOP.

 TRY.
* SEND EMAIL
        lo_send_request->send(
        EXPORTING
        i_with_error_screen = 'X' ).
        IF sy-subrc EQ 0.
          COMMIT WORK.
           lv_msg = 'Mail Sent successfully'.
          WRITE:/ lv_msg.
        ELSE.
          lv_msg = 'NO Record Found'.
          WRITE:/ lv_msg.
        ENDIF.
    ENDTRY.
  ENDIF.