ABAP, DATABASE TESTING, SQL

Campaign for buttons

2014-03-26T15:14:00.000-07:00

REPORT zctreatment.

INCLUDE zctreatment_top.

START-OF-SELECTION.

DATA: lwa_p1000 TYPE hrp1000,

lwa_p0001 TYPE pa0001,

lv_msg TYPE string,

gv_stop TYPE char1.

* Campaign validity check

SELECT SINGLE * FROM zpftcamp_head INTO gwa_camp_head WHERE campaign = s_campid-low AND process = 'HP' AND camp_begda LE sy-datum AND camp_endda GE sy-datum.

IF sy-subrc NE 0.

MESSAGE ID '00' TYPE 'I' NUMBER 2.

EXIT.

ENDIF.

* Employee number validity check.

CLEAR: lv_msg, lwa_p1000.

LOOP AT s_pernr.

SELECT SINGLE * FROM pa0001 INTO lwa_p0001 WHERE pernr = s_pernr-low AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc NE 0.

MESSAGE ID 'LR' TYPE 'I' NUMBER 22 WITH s_pernr-low.

gv_stop = 'X'.

EXIT.

ENDIF.

ENDLOOP.

IF gv_stop EQ 'X'.

CLEAR gv_stop.

EXIT.

ENDIF.

* Brach validity check

CLEAR: lv_msg, lwa_p1000.

LOOP AT s_branch.

SELECT SINGLE * FROM hrp1000 INTO lwa_p1000 WHERE plvar = '01' AND otype = '9Y' AND objid = s_branch-low AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc NE 0.

CONCATENATE text-031 s_branch-low text-030 INTO lv_msg SEPARATED BY space.

MESSAGE lv_msg TYPE 'I'.

gv_stop = 'X'.

ENDIF.

ENDLOOP.

IF gv_stop EQ 'X'.

CLEAR gv_stop.

EXIT.

ENDIF.

* Organizational Unit check.

CLEAR: lv_msg, lwa_p1000.

LOOP AT s_ou.

SELECT SINGLE * FROM hrp1000 INTO lwa_p1000 WHERE plvar = '01' AND otype = 'O' AND objid = s_ou-low AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc NE 0.

CONCATENATE text-029 s_ou-low text-030 INTO lv_msg SEPARATED BY space.

MESSAGE lv_msg TYPE 'I'.

gv_stop = 'X'.

ENDIF.

ENDLOOP.

IF gv_stop EQ 'X'.

CLEAR gv_stop.

EXIT.

ENDIF.

DATA: gv_answer.

IF ck_test IS INITIAL.

CALL FUNCTION 'POPUP_TO_CONFIRM'

EXPORTING

titlebar = 'Confirmation'

text_question = text-025

text_button_1 = 'Yes'(026)

* ICON_BUTTON_1 = ' '

text_button_2 = 'No'(027)

* ICON_BUTTON_2 = ' '

default_button = '2'

display_cancel_button = ' '

IMPORTING

answer = gv_answer

EXCEPTIONS

text_not_found = 1

OTHERS = 2.

IF sy-subrc EQ 0.

IF gv_answer EQ '2'.

EXIT.

ENDIF.

REFRESH gt_camp_head[].

SELECT * FROM zpftcamp_head INTO TABLE gt_camp_head WHERE campaign IN s_campid AND camp_begda LE sy-datum AND camp_endda GE sy-datum.

REFRESH: gt_1001_ou[],

gt_1001_emp[].

IF NOT s_branch[] IS INITIAL.

* Get all Org Units based on branch.

SELECT objid sobid FROM hrp1001 INTO CORRESPONDING FIELDS OF TABLE gt_1001_ou

WHERE plvar = '01'

AND otype = '9Y'

AND objid IN s_branch

AND rsign = 'B'

AND relat = 'Z96'

AND sclas = 'O'

AND sobid IN s_ou

AND begda LE sy-datum

AND endda GE sy-datum.

IF sy-subrc EQ 0.

* Filter Org Units based on the selection criteria.

SELECT objid sobid FROM hrp1001 INTO CORRESPONDING FIELDS OF TABLE gt_1001_emp

FOR ALL ENTRIES IN gt_1001_ou

WHERE plvar = '01'

AND otype = 'P'

AND rsign = 'B'

AND relat = 'Z97'

AND sclas = 'O'

AND sobid = gt_1001_ou-sobid

AND begda LE sy-datum

AND endda GE sy-datum.

IF sy-subrc EQ 0.

LOOP AT gt_1001_emp INTO gwa_1001_emp.

READ TABLE gt_1001_ou INTO gwa_1001_ou WITH KEY sobid = gwa_1001_emp-sobid.

IF sy-subrc EQ 0.

gwa_oubr-emp = gwa_1001_emp-objid.

gwa_oubr-br = gwa_1001_ou-objid.

gwa_oubr-ou = gwa_1001_emp-sobid.

APPEND gwa_oubr TO gt_oubr.

CLEAR gwa_oubr.

ENDIF.

ENDLOOP.

ENDIF.

ELSE.

* if only Org Unit it seleceted.

IF NOT s_ou IS INITIAL.

* Filter Org Units based on the selection criteria.

REFRESH: gt_1001_emp[],

gt_oubr.

SELECT objid sobid FROM hrp1001 INTO CORRESPONDING FIELDS OF TABLE gt_1001_emp

WHERE plvar = '01'

AND otype = 'P'

AND rsign = 'B'

AND relat = 'Z97'

AND sclas = 'O'

AND sobid IN s_ou

AND begda LE sy-datum

AND endda GE sy-datum.

IF sy-subrc EQ 0.

LOOP AT gt_1001_emp INTO gwa_1001_emp.

gwa_oubr-emp = gwa_1001_emp-objid.

gwa_oubr-ou = gwa_1001_emp-sobid.

APPEND gwa_oubr TO gt_oubr.

CLEAR gwa_oubr.

ENDLOOP.

ENDIF.

ELSE.

* No branch and No orgunit

ENDIF.

REFRESH: r_employee[].

LOOP AT gt_oubr INTO gwa_oubr.

wa_emp-sign = 'I'.

wa_emp-option = 'EQ'.

wa_emp-low = gwa_oubr-emp.

APPEND wa_emp TO r_employee.

CLEAR wa_emp.

ENDLOOP.

REFRESH: gt_9020[],

gt_9021[],

gt_all[].

DATA: lt_1001 TYPE TABLE OF p1001,

ls_1001 TYPE p1001,

lv_objid TYPE plog-objid.

IF rb_all EQ 'X' OR rb_hpgr EQ 'X'.

SELECT * FROM pa9020 INTO TABLE gt_9020 WHERE pernr IN s_pernr AND cmpgn IN s_campid AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc EQ 0.

LOOP AT gt_9020 INTO gwa_9020.

IF gwa_9020-pernr IN r_employee.

* Get Campaign details.p

READ TABLE gt_camp_head INTO gwa_camp_head WITH KEY campaign = gwa_9020-cmpgn.

IF sy-subrc EQ 0.

IF gwa_9020-endda NE gwa_camp_head-camp_endda.

* Move record to output table.

* pernr

gwa_all-pernr = gwa_9020-pernr.

* Read IT0002 data.

REFRESH gt_0002[].

CLEAR gwa_0002.

CALL FUNCTION 'RH_READ_INFTY'

EXPORTING

authority = 'DISP'

plvar = '01'

otype = 'P'

objid = gwa_9020-pernr

infty = '0002'

begda = sy-datum

endda = sy-datum

TABLES

innnn = gt_0002

EXCEPTIONS

all_infty_with_subty = 1

nothing_found = 2

no_objects = 3

wrong_condition = 4

wrong_parameters = 5

OTHERS = 6.

READ TABLE gt_0002 INTO gwa_0002 INDEX 1.

IF sy-subrc EQ 0.

* name2

gwa_all-vorna = gwa_0002-vorna.

* nachn

gwa_all-nachn = gwa_0002-nachn.

ENDIF.

READ TABLE gt_oubr INTO gwa_oubr WITH KEY emp = gwa_9020-pernr. "#EC WARNOK

IF sy-subrc EQ 0.

* branch

gwa_all-branch = gwa_oubr-br.

* org_unit

gwa_all-org_unit = gwa_oubr-ou.

ELSE.

* No branch and No OU selected.

* Get OU

CLEAR lv_objid.

lv_objid = gwa_9020-pernr.

CALL FUNCTION 'RH_READ_INFTY_1001'

EXPORTING

authority = 'DISP'

with_stru_auth = 'X'

plvar = '01'

otype = 'P'

objid = lv_objid

istat = '1'

subty = 'BZ97'

begda = sy-datum

endda = sy-datum

TABLES

i1001 = lt_1001

* OBJECTS =

EXCEPTIONS

nothing_found = 1

wrong_condition = 2

wrong_parameters = 3

OTHERS = 4.

IF sy-subrc EQ 0.

READ TABLE lt_1001 INTO ls_1001 WITH KEY otype = 'P' objid = lv_objid sclas = 'O'.

IF sy-subrc EQ 0.

gwa_all-org_unit = ls_1001-sobid.

* Get Branch

CLEAR lv_objid.

lv_objid = ls_1001-sobid.

CALL FUNCTION 'RH_READ_INFTY_1001'

EXPORTING

authority = 'DISP'

with_stru_auth = 'X'

plvar = '01'

otype = 'O'

objid = lv_objid

istat = '1'

subty = 'AZ96'

begda = sy-datum

endda = sy-datum

TABLES

i1001 = lt_1001

* OBJECTS =

EXCEPTIONS

nothing_found = 1

wrong_condition = 2

wrong_parameters = 3

OTHERS = 4.

IF sy-subrc EQ 0.

READ TABLE lt_1001 INTO ls_1001 WITH KEY otype = 'O' objid = lv_objid sclas = '9Y'.

IF sy-subrc EQ 0.

gwa_all-branch = ls_1001-sobid.

ENDIF.

* cmpgn

gwa_all-cmpgn = gwa_9020-cmpgn.

* camp_endda

gwa_all-camp_endda = gwa_camp_head-camp_endda.

* oldgr_endda

gwa_all-oldgr_endda = gwa_9020-endda.

* newgr_endda

gwa_all-newgr_endda = gwa_camp_head-camp_endda.

* oldbr_endda

* newbr_endda

* comment(100)

*Move campaign enddate to 9020 enddate

CLEAR: gv_old_endda,

gwa_p9020.

gv_old_endda = gwa_9020-endda.

MOVE-CORRESPONDING gwa_9020 TO gwa_p9020.

gwa_p9020-infty = '9020'.

gwa_p9020-endda = gwa_camp_head-camp_endda.

gwa_p9020-aedtm = sy-datum.

gwa_p9020-uname = sy-uname.

* Update Infotype 9020.

IF ck_test IS INITIAL.

* CLEAR gv_nocommit.

* ELSE.

* gv_nocommit = 'X'.

* ENDIF.

CALL FUNCTION 'HR_EMPLOYEE_ENQUEUE'

EXPORTING

number = gwa_p9020-pernr.

CLEAR: gwa_return.

CALL FUNCTION 'HR_INFOTYPE_OPERATION'

EXPORTING

infty = '9020'

number = gwa_p9020-pernr

objectid = gwa_p9020-objps

validityend = gwa_9020-endda

validitybegin = gwa_p9020-begda

recordnumber = gwa_p9020-seqnr

record = gwa_p9020

operation = 'MOD'

dialog_mode = '0'

* nocommit = gv_nocommit

IMPORTING

return = gwa_return.

IF gwa_return-number = '000' OR gwa_return-type = 'S'.

IF ck_test IS INITIAL.

gwa_all-status = '@08@'.

gwa_all-comment = 'End date modification could be performed'(019).

ELSE.

* gwa_all-status = '@09@'.

* gwa_all-comment = 'End date modification could be performed'(019).

ENDIF.

ELSE.

IF gwa_return-type = 'E'.

gwa_all-status = '@0A@'.

CONCATENATE text-020 gwa_p9020-pernr text-021 INTO gwa_all-comment SEPARATED BY space.

ENDIF.

CALL FUNCTION 'HR_EMPLOYEE_DEQUEUE'

EXPORTING

number = gwa_p9020-pernr.

ENDIF.

IF NOT ck_test IS INITIAL.

gwa_all-status = '@09@'.

gwa_all-comment = 'End date modification could be performed'(019).

ENDIF.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDIF.

ENDLOOP.

ENDIF.

IF rb_all EQ 'X' OR rb_hpbr EQ 'X'.

SELECT * FROM pa9021 INTO TABLE gt_9021 WHERE pernr IN s_pernr AND cmpgn IN s_campid AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc EQ 0.

LOOP AT gt_9021 INTO gwa_9021.

IF gwa_9021-pernr IN r_employee.

* Get Campaign details.p

READ TABLE gt_camp_head INTO gwa_camp_head WITH KEY campaign = gwa_9021-cmpgn.

IF sy-subrc EQ 0.

IF gwa_9021-endda NE gwa_camp_head-camp_endda.

* Move record to output table.

* pernr

gwa_all-pernr = gwa_9021-pernr.

* Read IT0002 data.

REFRESH gt_0002[].

CLEAR gwa_0002.

CALL FUNCTION 'RH_READ_INFTY'

EXPORTING

authority = 'DISP'

plvar = '01'

otype = 'P'

objid = gwa_9020-pernr

infty = '0002'

begda = sy-datum

endda = sy-datum

TABLES

innnn = gt_0002

EXCEPTIONS

all_infty_with_subty = 1

nothing_found = 2

no_objects = 3

wrong_condition = 4

wrong_parameters = 5

OTHERS = 6.

READ TABLE gt_0002 INTO gwa_0002 INDEX 1.

IF sy-subrc EQ 0.

* name2

gwa_all-vorna = gwa_0002-vorna.

* nachn

gwa_all-nachn = gwa_0002-nachn.

ENDIF.

READ TABLE gt_oubr INTO gwa_oubr WITH KEY emp = gwa_9021-pernr. "#EC WARNOK

IF sy-subrc EQ 0.

* branch

gwa_all-branch = gwa_oubr-br.

* org_unit

gwa_all-org_unit = gwa_oubr-ou.

ENDIF.

* cmpgn

gwa_all-cmpgn = gwa_9021-cmpgn.

* camp_endda

gwa_all-camp_endda = gwa_camp_head-camp_endda.

* oldgr_endda

* newgr_endda

* oldbr_endda

gwa_all-oldbr_endda = gwa_9021-endda.

* newbr_endda

gwa_all-newbr_endda = gwa_camp_head-camp_endda.

* comment(100)

*Move campaign enddate to 9021 enddate

CLEAR: gv_old_endda,

gwa_p9021.

gv_old_endda = gwa_9021-endda.

MOVE-CORRESPONDING gwa_9021 TO gwa_p9021.

gwa_p9021-infty = '9021'.

gwa_p9021-endda = gwa_camp_head-camp_endda.

gwa_p9021-aedtm = sy-datum.

gwa_p9021-uname = sy-uname.

* *************

* Update Infotype 9021.

IF ck_test IS INITIAL.

* CLEAR gv_nocommit.

* ELSE.

* gv_nocommit = 'X'.

* ENDIF.

CALL FUNCTION 'HR_EMPLOYEE_ENQUEUE'

EXPORTING

number = gwa_9021-pernr.

CLEAR: gwa_return.

CALL FUNCTION 'HR_INFOTYPE_OPERATION'

EXPORTING

infty = '9021'

number = gwa_p9021-pernr

objectid = gwa_p9021-objps

validityend = gwa_9021-endda

validitybegin = gwa_p9021-begda

recordnumber = gwa_p9021-seqnr

record = gwa_p9021

operation = 'MOD'

dialog_mode = '0'

* nocommit = gv_nocommit

IMPORTING

return = gwa_return.

IF gwa_return-number = '000' OR gwa_return-type = 'S'.

IF ck_test IS INITIAL.

gwa_all-status = '@08@'.

gwa_all-comment = 'End date modification could be performed'(019).

ELSE.

gwa_all-status = '@09@'.

gwa_all-comment = 'End date modification could be performed'(019).

ENDIF.

ELSE.

IF gwa_return-type = 'E'.

gwa_all-status = '@0A@'.

CONCATENATE text-020 gwa_p9020-pernr text-021 INTO gwa_all-comment SEPARATED BY space.

ENDIF.

CALL FUNCTION 'HR_EMPLOYEE_DEQUEUE'

EXPORTING

number = gwa_9021-pernr.

ENDIF.

IF NOT ck_test IS INITIAL.

gwa_all-status = '@09@'.

gwa_all-comment = 'End date modification could be performed'(019).

ENDIF.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDIF.

ENDLOOP.

ENDIF.

IF gt_all IS INITIAL.

MESSAGE text-028 TYPE 'I'.

EXIT.

ENDIF.

END-OF-SELECTION.

CALL SCREEN 100.

*&---------------------------------------------------------------------*

*& Module ALV_GRID_0100 OUTPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE alv_grid_0100 OUTPUT.

PERFORM free.

IF go_custom_container_100 IS INITIAL.

CREATE OBJECT go_custom_container_100

EXPORTING

container_name = 'GV_CONTAINER'.

ENDIF.

CLEAR go_grid_100.

IF go_grid_100 IS INITIAL.

CREATE OBJECT go_grid_100

EXPORTING

i_parent = go_custom_container_100.

ENDIF.

PERFORM build_fieldcat_100.

IF rb_all EQ 'X'.

gs_layout_100-grid_title = 'Group Potential and Branch Potential Updation'(022)."'PA9020 and PA9021 Updation'.

ELSEIF rb_hpgr EQ 'X'.

gs_layout_100-grid_title = 'Group Potential Updation'(023)."'PA9020 Updation'.

ELSEIF rb_hpbr EQ 'X'.

gs_layout_100-grid_title = 'Branch Potential Updation'(024)."'PA9021 Updation'.

ENDIF.

gs_layout_100-cwidth_opt = 'X'.

PERFORM outputlog_alv_disp CHANGING gt_all.

ENDMODULE. " ALV_GRID_0100 OUTPUT

*&---------------------------------------------------------------------*

*& Form FREE

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM free .

IF go_grid_100 IS NOT INITIAL.

CALL METHOD go_grid_100->free

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

OTHERS = 3.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

FREE go_grid_100.

ENDIF.

IF go_custom_container_100 IS NOT INITIAL.

CALL METHOD go_custom_container_100->free

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

OTHERS = 3.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

FREE go_custom_container_100.

ENDIF.

ENDFORM. " FREE

*&---------------------------------------------------------------------*

*& Form BUILD_FIELDCAT_100

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM build_fieldcat_100 .

DATA:ls_fcat TYPE lvc_s_fcat.

REFRESH: gt_fieldcat_100[].

* IF rb_all EQ 'X'. "Fieldcatlog for All

ls_fcat-col_pos = 1.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'STATUS'.

ls_fcat-coltext = 'Status'.

ls_fcat-outputlen = 7.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 2.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'PERNR'.

ls_fcat-coltext = 'Personal Number'(006).

ls_fcat-outputlen = 17.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 3.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'VORNA'.

ls_fcat-coltext = 'First Name'(007).

ls_fcat-outputlen = 25.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 4.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NACHN'.

ls_fcat-coltext = 'Last name'(008).

ls_fcat-outputlen = 25.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 5.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'BRANCH'.

ls_fcat-coltext = 'Branch'(009).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 6.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'ORG_UNIT'.

ls_fcat-coltext = 'Organisational unit'(010).

ls_fcat-outputlen = 26.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 7.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CMPGN'.

ls_fcat-coltext = 'Campaign Id selected'(011).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 8.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CAMP_ENDDA'.

ls_fcat-coltext = 'End date campaign'(012).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 9.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'OLDGR_ENDDA'.

ls_fcat-coltext = 'Old end date HP Group'(013).

ls_fcat-outputlen = 33.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 10.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NEWGR_ENDDA'.

ls_fcat-coltext = 'New end date HP Group'(014).

ls_fcat-outputlen = 34.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 11.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'OLDBR_ENDDA'.

ls_fcat-coltext = 'Old end date HP Branch'(015).

ls_fcat-outputlen = 37.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 12.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NEWBR_ENDDA'.

ls_fcat-coltext = 'New end date HP Branch'(016).

ls_fcat-outputlen = 35.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 13.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'COMMENT'.

ls_fcat-coltext = 'Comment'(017).

ls_fcat-outputlen = 100.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

* ELSEIF rb_hpgr EQ 'X'. "Fieldcatlog for Branch

* ls_fcat-col_pos = 1.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'PERNR'.

* ls_fcat-coltext = 'Personal Number'(006).

* ls_fcat-outputlen = 17.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 2.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'NAME2'.

* ls_fcat-coltext = 'First Name'(007).

* ls_fcat-outputlen = 25.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 3.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'NACHN'.

* ls_fcat-coltext = 'Last name'(008).

* ls_fcat-outputlen = 25.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 4.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'BRANCH'.

* ls_fcat-coltext = 'Branch'(009).

* ls_fcat-outputlen = 10.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 5.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'ORG_UNIT'.

* ls_fcat-coltext = 'Organisational unit'(010).

* ls_fcat-outputlen = 26.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 6.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'CAMPAIGN'.

* ls_fcat-coltext = 'Campaign Id selected'(011).

* ls_fcat-outputlen = 24.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 7.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'CAM_ENDDA'.

* ls_fcat-coltext = 'End date campaign'(012).

* ls_fcat-outputlen = 24.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 8.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'OLDGR_ENDDA'.

* ls_fcat-coltext = 'Old end date HP Group'(013).

* ls_fcat-outputlen = 33.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 9.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'NEWGR_ENDDA'.

* ls_fcat-coltext = 'New end date HP Group'(014).

* ls_fcat-outputlen = 34.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 10.

* ls_fcat-tabname = 'GT_BRANCH'.

* ls_fcat-fieldname = 'COMMENT'.

* ls_fcat-coltext = 'Comment'(017).

* ls_fcat-outputlen = 100.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ELSEIF rb_hpbr EQ 'X'. "Fieldcatlog for Org unit

* ls_fcat-col_pos = 1.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'PERNR'.

* ls_fcat-coltext = 'Personal Number'(006).

* ls_fcat-outputlen = 17.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 2.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'NAME2'.

* ls_fcat-coltext = 'First Name'(007).

* ls_fcat-outputlen = 25.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 3.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'NACHN'.

* ls_fcat-coltext = 'Last name'(008).

* ls_fcat-outputlen = 25.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 4.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'BRANCH'.

* ls_fcat-coltext = 'Branch'(009).

* ls_fcat-outputlen = 10.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 5.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'ORG_UNIT'.

* ls_fcat-coltext = 'Organisational unit'(010).

* ls_fcat-outputlen = 26.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 6.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'CAMPAIGN'.

* ls_fcat-coltext = 'Campaign Id selected'(011).

* ls_fcat-outputlen = 24.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 7.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'CAM_ENDDA'.

* ls_fcat-coltext = 'End date campaign'(012).

* ls_fcat-outputlen = 24.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 8.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'OLDBR_ENDDA'.

* ls_fcat-coltext = 'Old end date HP Branch'(015).

* ls_fcat-outputlen = 37.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 9.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'NEWGR_ENDDA'.

* ls_fcat-coltext = 'New end date HP Branch'(016).

* ls_fcat-outputlen = 35.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ls_fcat-col_pos = 10.

* ls_fcat-tabname = 'GT_ORGUNIT'.

* ls_fcat-fieldname = 'COMMENT'.

* ls_fcat-coltext = 'Comment'(017).

* ls_fcat-outputlen = 100.

* APPEND ls_fcat TO gt_fieldcat_100.

* CLEAR ls_fcat.

* ENDIF.

ENDFORM. " BUILD_FIELDCAT_100

*&---------------------------------------------------------------------*

*& Form OUTPUTLOG_ALV_DISP

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM outputlog_alv_disp CHANGING i_disp_table TYPE STANDARD TABLE.

CALL METHOD go_grid_100->set_table_for_first_display

EXPORTING

* it_toolbar_excluding = gt_exclude_100

is_layout = gs_layout_100

CHANGING

it_fieldcatalog = gt_fieldcat_100

it_outtab = i_disp_table[].

* Set editable cells to ready for input initially

CALL METHOD go_grid_100->set_ready_for_input

EXPORTING

i_ready_for_input = 0.

ENDFORM. " OUTPUTLOG_ALV_DISP

*&---------------------------------------------------------------------*

*& Module STATUS_0100 OUTPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE status_0100 OUTPUT.

SET PF-STATUS 'STATUS100'.

SET TITLEBAR 'TITLE100'.

ENDMODULE. " STATUS_0100 OUTPUT

*&---------------------------------------------------------------------*

*& Module USER_COMMAND_0100 INPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE user_command_0100 INPUT.

CASE sy-ucomm.

WHEN 'BACK'.

LEAVE TO SCREEN 0.

WHEN 'CANCEL'.

LEAVE TO SCREEN 0.

WHEN 'EXIT'.

LEAVE TO SCREEN 0.

ENDCASE.

ENDMODULE. "user_command_0100 INPUT

*******************************************************************************

* A D M I N I S T R A T I O N *

*******************************************************************************

* Program ID : HP_CO03 *

* Version : Initial *

* SAP Version : ECC 6.0 *

* Program Name : zphp_ctreatment *

* Created by : Rajasekhar Puppala *

* Created on : 02.19.2014 *

* Description : HP End Campaign Treatment *

*******************************************************************************

* C H A N G E H I S T O R Y *

*******************************************************************************

* Change Id : *

* Author : *

* Changed On : *

* Request No : *

* Description : *

*******************************************************************************

*&---------------------------------------------------------------------*

*& Include ZPHP_CTREATMENT_TOP

*&---------------------------------------------------------------------*

INCLUDE .

TABLES: zpftcamp_head,

hrp1002,

hrp1000,

pa0002,

pa0003,

pa9020,

pa9021,

zphp_potentbran.

*******Internal table declaration******************

DATA: gt_9020 TYPE STANDARD TABLE OF pa9020,

gt_9021 TYPE STANDARD TABLE OF pa9021,

gwa_9020 TYPE pa9020,

gwa_9021 TYPE pa9021,

gwa_p9020 TYPE p9020,

gwa_p9021 TYPE p9021.

DATA: gwa_return TYPE bapireturn1.

DATA: gt_0002 TYPE p0002_tab,

gwa_0002 TYPE p0002,

gv_return TYPE symsgno,

gv_nocommit LIKE bapi_stand-no_commit.

TYPES: BEGIN OF ty_all,

status TYPE icon_d, "c,

pernr TYPE pa9020-pernr,

vorna TYPE pa0002-vorna,

nachn TYPE pa0002-nachn,

branch TYPE hrp1000-objid,

org_unit TYPE hrp1000-objid,

cmpgn TYPE zpftcamp_head-campaign,

camp_endda TYPE zpftcamp_head-camp_endda,

oldgr_endda TYPE pa9020-begda,

newgr_endda TYPE pa9020-begda,

oldbr_endda TYPE pa9021-begda,

newbr_endda TYPE pa9021-begda,

comment(100) TYPE c,

END OF ty_all.

DATA: gwa_all TYPE ty_all,

gt_all TYPE TABLE OF ty_all.

*TYPES: BEGIN OF ty_branch,

* pernr TYPE pa9020-pernr,

* name2 TYPE pa0002-vorna,

* nachn TYPE pa0002-nachn,

* branch TYPE hrp1000-objid,

* org_unit TYPE hrp1000-objid,

* cmpgn TYPE zpftcamp_head-campaign,

* camp_endda TYPE zpftcamp_head-camp_endda,

* oldgr_endda TYPE pa9020-begda,

* newgr_endda TYPE pa9020-begda,

* comment(100) TYPE c,

*END OF ty_branch.

*DATA: gwa_branch TYPE ty_branch,

* gt_branch TYPE TABLE OF ty_branch.

*TYPES: BEGIN OF ty_orgunit,

* pernr TYPE pa9020-pernr,

* name2 TYPE pa0002-vorna,

* nachn TYPE pa0002-nachn,

* branch TYPE hrp1000-objid,

* org_unit TYPE hrp1000-objid,

* campaign TYPE zpftcamp_head-campaign,

* cam_endda TYPE zpftcamp_head-camp_endda,

* oldbr_endda TYPE pa9021-begda,

* newbr_endda TYPE pa9021-begda,

* comment(100) TYPE c,

*END OF ty_orgunit.

*DATA: gwa_orgunit TYPE ty_orgunit,

* gt_orgunit TYPE TABLE OF ty_orgunit.

*...............ALV.............................

DATA: gt_fieldcat_100 TYPE lvc_t_fcat.

DATA: gv_disp_table TYPE string.

DATA: go_grid_100 TYPE REF TO cl_gui_alv_grid.

DATA: go_custom_container_100 TYPE REF TO cl_gui_custom_container.

DATA: gs_layout_100 TYPE lvc_s_layo,

gr_alv TYPE REF TO cl_salv_table,

gr_columns TYPE REF TO cl_salv_columns_table,

gr_column TYPE REF TO cl_salv_column_table.

TYPES: BEGIN OF ty_1001_ou,

objid TYPE p1001-objid,

sobid TYPE p1001-sobid,

END OF ty_1001_ou.

TYPES: BEGIN OF ty_oubr,

emp TYPE objid,

br TYPE objid,

ou TYPE objid,

END OF ty_oubr.

DATA: gt_oubr TYPE STANDARD TABLE OF ty_oubr,

gwa_oubr TYPE ty_oubr.

DATA: gt_1001_ou TYPE STANDARD TABLE OF ty_1001_ou,

gwa_1001_ou LIKE LINE OF gt_1001_ou,

gt_1001_emp TYPE STANDARD TABLE OF ty_1001_ou,

gwa_1001_emp LIKE LINE OF gt_1001_ou.

DATA: r_employee TYPE RANGE OF objid,

wa_emp LIKE LINE OF r_employee.

DATA: gt_camp_head TYPE STANDARD TABLE OF zpftcamp_head,

gwa_camp_head TYPE zpftcamp_head.

DATA: gv_old_endda TYPE p0001-endda.

******selection screen declaration****************

SELECTION-SCREEN:BEGIN OF BLOCK selection WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_campid FOR zpftcamp_head-campaign MATCHCODE OBJECT zphp_campaign OBLIGATORY NO-EXTENSION NO INTERVALS.

**Radio buttons

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN BEGIN OF LINE.

PARAMETERS: rb_all RADIOBUTTON GROUP auth USER-COMMAND rusr.

SELECTION-SCREEN COMMENT 7(5) text-003 FOR FIELD rb_all.

PARAMETERS: rb_hpgr RADIOBUTTON GROUP auth.

SELECTION-SCREEN COMMENT 14(18) text-004 FOR FIELD rb_hpgr.

PARAMETERS: rb_hpbr RADIOBUTTON GROUP auth.

SELECTION-SCREEN COMMENT 41(18) text-005 FOR FIELD rb_hpbr.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP 1.

SELECT-OPTIONS: s_pernr FOR pa0003-pernr. "hrp1002-objid.

SELECT-OPTIONS: s_branch FOR zphp_potentbran-zbranch MATCHCODE OBJECT zh_branch.

SELECT-OPTIONS: s_ou FOR hrp1000-objid MATCHCODE OBJECT zp_e_orgunit.

SELECTION-SCREEN END OF BLOCK selection.

SELECTION-SCREEN:BEGIN OF BLOCK other WITH FRAME TITLE text-002.

PARAMETERS: ck_test AS CHECKBOX DEFAULT 'X'.

SELECTION-SCREEN END OF BLOCK other.

---------------------------------------------------------------------------

**********************

*** CALL SCREEN 100***

**********************

PROCESS BEFORE OUTPUT.

MODULE status_0100.

MODULE alv_grid_0100.

PROCESS AFTER INPUT.

MODULE user_command_0100.

authrozations

2014-03-26T14:51:00.000-07:00

REPORT report.

INCLUDE zpautreport_top.

DATA: lv_continue TYPE char1.

* At Selection screen output

AT SELECTION-SCREEN OUTPUT.

LOOP AT SCREEN.

IF rb_all EQ 'X'.

screen-active = '1'.

MODIFY SCREEN.

ELSEIF rb_pa EQ 'X'.

IF screen-group1 EQ 'TA' OR screen-group1 EQ 'CMA'.

screen-active = '0'.

ELSE.

screen-active = '1'.

ENDIF.

MODIFY SCREEN.

ELSEIF rb_ta EQ 'X'.

IF screen-group1 EQ 'PA' OR screen-group1 EQ 'CMA'.

screen-active = '0'.

ELSE.

screen-active = '1'.

ENDIF.

MODIFY SCREEN.

ELSEIF rb_cma EQ 'X'.

IF screen-group1 EQ 'PA' OR screen-group1 EQ 'TA'.

screen-active = '0'.

ELSE.

screen-active = '1'.

ENDIF.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

************START-OF-SELECTION********************

START-OF-SELECTION.

SELECT * FROM zsft_user_actor INTO TABLE gt_user_actor

WHERE bname IN s_bname

AND actor IN s_actor

AND start_date IN s_begda

AND end_date IN s_endda.

IF NOT gt_user_actor[] IS INITIAL.

SELECT * FROM zsft_actor_t INTO TABLE gt_actort

FOR ALL ENTRIES IN gt_user_actor

WHERE actor = gt_user_actor-actor

AND spras = sy-langu.

IF rb_all EQ 'X' OR rb_pa EQ 'X'.

SELECT * FROM zsft_user_popul INTO TABLE gt_user_popul

FOR ALL ENTRIES IN gt_user_actor

WHERE uname = gt_user_actor-bname

AND actor = gt_user_actor-actor

AND pop_ia IN s_pop_ia

AND pop_ic IN s_pop_ic

AND pop_pa IN s_pop_pa

AND pop_pc IN s_pop_pc

AND begda IN s_begda

AND endda IN s_endda.

SELECT * FROM zpftpopulation_t INTO TABLE gt_popul_t." WHERE spras = sy-langu.

ENDIF.

IF rb_all EQ 'X' OR rb_pa EQ 'X' OR rb_ta EQ 'X'.

SELECT * FROM zsft_user_populf INTO TABLE gt_user_populf

FOR ALL ENTRIES IN gt_user_actor

WHERE uname = gt_user_actor-bname

AND actor = gt_user_actor-actor

AND begda IN s_begda

AND endda IN s_endda.

SELECT * FROM hrp1001 INTO TABLE gt_1001_ta

WHERE plvar = gc_plvar

AND otype = 'O'

AND rsign = 'A'

AND ( relat EQ 'ZRF' OR

relat EQ 'ZAF' OR

relat EQ 'ZSF' OR

relat EQ 'ZGF' OR

relat EQ 'ZAR' )

AND ( sclas = 'P' OR sclas = 'H' )

AND begda IN s_tbegda

AND endda IN s_tendda.

SELECT * FROM hrp1001 INTO TABLE gt_1001_p

WHERE plvar = gc_plvar

AND otype = '9P'

AND rsign = 'B'

AND relat EQ 'ZCP'

AND ( sclas = 'P' OR sclas = 'H' )

AND begda IN s_pbegda

AND endda IN s_pendda.

SELECT * FROM hrp1000 INTO TABLE gt_1000

WHERE plvar = gc_plvar

AND ( otype = 'O' OR otype = '9P' )

AND begda LE sy-datum

AND endda GE sy-datum.

ENDIF.

IF rb_all EQ 'X' OR rb_cma EQ 'X'.

SELECT * FROM hrp1001 INTO TABLE gt_1001

WHERE plvar = gc_plvar

AND otype = 'S'

AND rsign = 'A'

AND relat EQ 'ZAC'

AND ( sclas = 'P' OR sclas = 'H' )

AND begda IN s_cbegda

AND endda IN s_cendda.

ENDIF.

SELECT * FROM zsft_scrn_maintn INTO TABLE gt_sft_scrn_maintn

WHERE actor_id IN s_actor

AND screen EQ '2' OR screen EQ '3'.

SELECT * FROM hrp9314 INTO TABLE gt_9314

WHERE plvar = gc_plvar

AND otype = 'H'

AND begda LE sy-datum

AND endda GE sy-datum.

SORT gt_user_actor BY bname actor.

SORT gt_actort BY actor.

SORT gt_user_popul BY uname actor.

SORT gt_user_populf BY uname actor.

SORT gt_popul_t BY population.

SORT gt_user_popul BY begda DESCENDING.

DATA lv_tabnr TYPE hrtabnr.

DATA lv_tseqnr TYPE hrtabseqnr.

REFRESH: gt_population_actors[],

gt_training_actors[],

gt_cma[],

gt_all[].

DATA: lv_pernr TYPE p0105-pernr,

lv_user TYPE p0105-usrid.

DATA: lv_usr TYPE syuname.

DATA: lv_relat TYPE hrp1001-relat.

DATA: lv_bname TYPE usr01-bname.

DATA lv_pos TYPE hrp1001-objid.

DATA: lv_uname2 TYPE syuname,

lt_objects TYPE STANDARD TABLE OF hrobject,

lwa_objects TYPE hrobject,

lv_objid_h TYPE hrobject,

lv_person TYPE char1,

lv_ex_person TYPE char1.

LOOP AT gt_user_actor INTO gwa_user_actor.

CLEAR lv_continue.

CLEAR gwa_common_data.

CLEAR gwa_population_actors.

CLEAR gwa_training_actors.

CLEAR gwa_cma.

gwa_common_data-bname = gwa_user_actor-bname.

CLEAR: lv_pernr,

lv_user.

lv_user = gwa_common_data-bname.

CALL FUNCTION 'RP_GET_PERNR_FROM_USERID'

EXPORTING

begda = sy-datum

endda = sy-datum

usrid = lv_user

usrty = 'US'

IMPORTING

usr_pernr = lv_pernr

EXCEPTIONS

retcd = 1

OTHERS = 2.

IF sy-subrc <> 0.

CLEAR lv_uname2.

lv_uname2 = lv_user.

REFRESH: lt_objects[].

CALL FUNCTION 'ZPACTIN_RH_GET_H_FROM_USER'

EXPORTING

* I77PR =

uname = lv_uname2

TABLES

obj_tab = lt_objects.

READ TABLE lt_objects INTO lwa_objects

WITH KEY otype = 'H'.

IF sy-subrc = 0.

CLEAR: lv_objid_h,

lv_ex_person.

lv_objid_h = lwa_objects-objid.

* -------------------------------------------

lv_pernr = lwa_objects-objid.

* -------------------------------------------

lv_ex_person = 'X'.

READ TABLE gt_9314 INTO gwa_9314 WITH KEY objid = lwa_objects-objid.

IF sy-subrc EQ 0.

gwa_common_data-name1 = gwa_9314-vorna.

gwa_common_data-name2 = gwa_9314-nachn.

ENDIF.

ELSE.

CLEAR lv_person.

lv_person = 'X'.

* Get User's Last Name and First Name

REFRESH: gt_usr03[],

gt_addr[].

CALL FUNCTION 'SUSR_USER_ADDRESS_READ'

EXPORTING

user_name = gwa_common_data-bname

* READ_DB_DIRECTLY = ' '

* CACHE_RESULTS = 'X'

IMPORTING

user_address = gwa_addr

user_usr03 = gwa_usr03

EXCEPTIONS

user_address_not_found = 1

OTHERS = 2.

IF sy-subrc EQ 0.

gwa_common_data-name1 = gwa_usr03-name1.

gwa_common_data-name2 = gwa_usr03-name2.

ENDIF.

*Start date

gwa_common_data-begda = gwa_user_actor-start_date.

* End date

gwa_common_data-endda = gwa_user_actor-end_date.

gwa_common_data-actor = gwa_user_actor-actor.

READ TABLE gt_actort INTO gwa_actort WITH KEY actor = gwa_user_actor-actor BINARY SEARCH.

IF sy-subrc EQ 0.

* Actor Description.

gwa_common_data-text = gwa_actort-text.

ENDIF.

IF rb_all EQ 'X' OR rb_pa EQ 'X' OR rb_ta EQ 'X' .

IF rb_all EQ 'X' OR rb_pa EQ 'X'.

* Population Actor table

READ TABLE gt_user_popul INTO gwa_user_popul WITH KEY uname = gwa_user_actor-bname actor = gwa_user_actor-actor.

IF sy-subrc EQ 0.

MOVE-CORRESPONDING gwa_common_data TO gwa_population_actors.

gwa_population_actors-pop_ia = gwa_user_popul-pop_ia.

READ TABLE gt_popul_t INTO gwa_popul_t WITH KEY population = gwa_population_actors-pop_ia.

IF sy-subrc EQ 0.

gwa_population_actors-ia_pop_name = gwa_popul_t-pop_name.

ENDIF.

gwa_population_actors-pop_ic = gwa_user_popul-pop_ic.

READ TABLE gt_popul_t INTO gwa_popul_t WITH KEY population = gwa_population_actors-pop_ic.

IF sy-subrc EQ 0.

gwa_population_actors-ic_pop_name = gwa_popul_t-pop_name.

ENDIF.

gwa_population_actors-pop_pa = gwa_user_popul-pop_pa.

READ TABLE gt_popul_t INTO gwa_popul_t WITH KEY population = gwa_population_actors-pop_pa.

IF sy-subrc EQ 0.

gwa_population_actors-pa_pop_name = gwa_popul_t-pop_name.

ENDIF.

gwa_population_actors-pop_pc = gwa_user_popul-pop_pc.

READ TABLE gt_popul_t INTO gwa_popul_t WITH KEY population = gwa_population_actors-pop_pc.

IF sy-subrc EQ 0.

gwa_population_actors-pc_pop_name = gwa_popul_t-pop_name.

ENDIF.

ELSE.

IF rb_pa EQ 'X'.

CONTINUE.

ENDIF.

CLEAR lv_no_flag.

IF ck_np10 NE 'X' AND ck_sexec NE 'X' AND ck_hra NE 'X' AND ck_spos NE 'X' AND ck_mgc NE 'X'.

lv_no_flag = 'X'.

ENDIF.

READ TABLE gt_user_populf INTO gwa_user_populf WITH KEY uname = gwa_user_actor-bname actor = gwa_user_actor-actor BINARY SEARCH.

IF sy-subrc EQ 0.

IF ck_np10 EQ 'X' AND gwa_user_populf-executive IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

IF ck_sexec EQ 'X' AND gwa_user_populf-senior_exec IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

IF ck_hra EQ 'X' AND gwa_user_populf-hr_actor IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

IF ck_spos EQ 'X' AND gwa_user_populf-sensitive_pos IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

IF ck_mgc EQ 'X' AND gwa_user_populf-managed_contrac IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

gwa_population_actors-executive = gwa_user_populf-executive.

gwa_population_actors-senior_exec = gwa_user_populf-senior_exec.

gwa_population_actors-hr_actor = gwa_user_populf-hr_actor.

gwa_population_actors-sensitive_pos = gwa_user_populf-sensitive_pos.

gwa_population_actors-managed_contrac = gwa_user_populf-managed_contrac.

ENDIF.

IF lv_no_flag EQ 'X'.

MOVE-CORRESPONDING gwa_population_actors TO gwa_all.

APPEND gwa_population_actors TO gt_population_actors.

ELSE.

CONTINUE.

ENDIF.

CLEAR gwa_population_actors.

ENDIF.

IF rb_all EQ 'X' OR rb_ta EQ 'X'.

CLEAR lv_no_flag.

IF ck_nphp EQ 'X' AND gwa_user_populf-nphp IS NOT INITIAL.

lv_no_flag = 'X'.

ENDIF.

IF ck_nphp NE 'X'.

lv_no_flag = 'X'.

ENDIF.

READ TABLE gt_sft_scrn_maintn INTO gwa_sft_scrn_maintn WITH KEY actor_id = gwa_user_actor-actor screen = '2'.

IF sy-subrc EQ 0.

CLEAR lv_relat.

IF gwa_common_data-actor EQ 'TM'.

lv_relat = 'ZRF'.

ELSEIF gwa_common_data-actor EQ 'TA'.

lv_relat = 'ZAF'.

ELSEIF gwa_common_data-actor EQ 'STM'.

lv_relat = 'ZSF'.

ELSEIF gwa_common_data-actor EQ 'TSP'.

lv_relat = 'ZGF'.

ELSEIF gwa_common_data-actor EQ 'ARC'.

lv_relat = 'ZAR'.

ENDIF.

LOOP AT gt_1001_ta INTO gwa_1001_ta WHERE relat = lv_relat AND sobid = lv_pernr.

lv_continue = 'X'.

IF ck_nphp EQ 'X' AND gwa_user_populf-nphp IS INITIAL.

CONTINUE.

ENDIF.

gwa_training_actors-nphp = gwa_user_populf-nphp.

MOVE-CORRESPONDING gwa_common_data TO gwa_training_actors.

gwa_training_actors-tdept = gwa_1001_ta-objid.

IF NOT gwa_training_actors-tdept IN s_tdept.

CONTINUE.

ENDIF.

gwa_training_actors-tr_begda = gwa_1001_ta-begda.

gwa_training_actors-tr_endda = gwa_1001_ta-endda.

*** TDEPT DESCRIPTION

READ TABLE gt_1000 INTO gwa_1000 WITH KEY objid = gwa_training_actors-tdept.

IF sy-subrc EQ 0.

gwa_training_actors-o_tline = gwa_1000-stext.

ENDIF.

IF lv_no_flag = 'X'.

IF NOT gwa_training_actors-tdept IN s_tdept OR NOT gwa_training_actors-pool IN s_pool.

CLEAR gwa_training_actors.

CONTINUE.

ENDIF.

MOVE-CORRESPONDING gwa_training_actors TO gwa_all.

APPEND gwa_training_actors TO gt_training_actors.

CLEAR gwa_training_actors.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDIF.

ENDLOOP.

IF lv_continue IS INITIAL.

IF gwa_training_actors-tdept IN s_tdept." AND NOT gwa_training_actors-tdept IS INITIAL.

IF ck_nphp EQ 'X' AND gwa_user_populf-nphp IS INITIAL.

CONTINUE.

ENDIF.

gwa_training_actors-nphp = gwa_user_populf-nphp.

MOVE-CORRESPONDING gwa_common_data TO gwa_training_actors.

MOVE-CORRESPONDING gwa_training_actors TO gwa_all.

APPEND gwa_training_actors TO gt_training_actors.

CLEAR gwa_training_actors.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

CLEAR lv_continue.

CONTINUE.

ENDIF.

** POOL

LOOP AT gt_1001_p INTO gwa_1001_p WHERE sobid = lv_pernr.

lv_continue = 'X'.

IF ck_nphp EQ 'X' AND gwa_user_populf-nphp IS INITIAL.

CONTINUE.

ENDIF.

gwa_training_actors-nphp = gwa_user_populf-nphp.

MOVE-CORRESPONDING gwa_common_data TO gwa_training_actors.

gwa_training_actors-pool = gwa_1001_p-objid.

IF NOT gwa_training_actors-pool IN s_pool.

CONTINUE.

ENDIF.

gwa_training_actors-p_begda = gwa_1001_p-begda.

gwa_training_actors-p_endda = gwa_1001_p-endda.

*** POOL DESCRIPTION

READ TABLE gt_1000 INTO gwa_1000 WITH KEY objid = gwa_training_actors-pool.

IF sy-subrc EQ 0.

gwa_training_actors-p_tline = gwa_1000-stext.

ENDIF.

IF lv_no_flag = 'X'.

IF NOT gwa_training_actors-pool IN s_pool OR NOT gwa_training_actors-tdept IN s_tdept.

CONTINUE.

ENDIF.

MOVE-CORRESPONDING gwa_training_actors TO gwa_all.

APPEND gwa_training_actors TO gt_training_actors.

CLEAR gwa_training_actors.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

CONTINUE.

ENDIF.

ENDLOOP.

IF lv_continue IS INITIAL.

IF gwa_training_actors-pool IN s_pool." AND NOT gwa_training_actors-pool IS INITIAL.

IF ck_nphp EQ 'X' AND gwa_user_populf-nphp IS INITIAL.

CONTINUE.

ENDIF.

gwa_training_actors-nphp = gwa_user_populf-nphp.

* IF lv_no_flag = 'X'.

MOVE-CORRESPONDING gwa_common_data TO gwa_training_actors.

MOVE-CORRESPONDING gwa_training_actors TO gwa_all.

APPEND gwa_training_actors TO gt_training_actors.

CLEAR gwa_training_actors.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

CLEAR lv_continue.

CONTINUE.

ENDIF.

IF rb_all EQ 'X' OR rb_cma EQ 'X'.

READ TABLE gt_sft_scrn_maintn INTO gwa_sft_scrn_maintn WITH KEY actor_id = gwa_user_actor-actor screen = '3'.

IF sy-subrc EQ 0.

CLEAR lv_pos.

IF lv_person EQ 'X'.

SELECT SINGLE plans FROM pa0001 INTO lv_pos WHERE pernr = lv_pernr AND begda LE sy-datum AND endda GE sy-datum.

IF sy-subrc EQ 0.

LOOP AT gt_1001 INTO gwa_1001 WHERE objid = lv_pos.

MOVE-CORRESPONDING gwa_common_data TO gwa_cma.

gwa_cma-objid = gwa_1001-sobid.

IF NOT gwa_cma-objid IN s_objid.

CONTINUE.

ENDIF.

IF rb_cma EQ 'X'.

IF gwa_cma-objid IS INITIAL.

CONTINUE.

ENDIF.

gwa_cma-cm_begda = gwa_1001-begda.

gwa_cma-cm_endda = gwa_1001-endda.

CLEAR: lv_usr.

CALL FUNCTION 'CATSXT_GET_USER_ID_OF_PERNR'

EXPORTING

im_personnel_number = gwa_cma-objid

IMPORTING

ex_user_id = lv_usr

EXCEPTIONS

failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

* Implement suitable error handling here

ENDIF.

* Get User's Last Name and First Name

REFRESH: gt_usr03[],

gt_addr[].

CLEAR lv_bname.

lv_bname = lv_usr.

CALL FUNCTION 'SUSR_USER_ADDRESS_READ'

EXPORTING

user_name = lv_bname

* READ_DB_DIRECTLY = ' '

* CACHE_RESULTS = 'X'

IMPORTING

user_address = gwa_addr

user_usr03 = gwa_usr03

EXCEPTIONS

user_address_not_found = 1

OTHERS = 2.

IF sy-subrc EQ 0.

gwa_cma-cm_name1 = gwa_usr03-name1.

gwa_cma-cm_name2 = gwa_usr03-name2.

ENDIF.

MOVE-CORRESPONDING gwa_cma TO gwa_all.

APPEND gwa_cma TO gt_cma.

CLEAR gwa_cma.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDLOOP.

IF sy-subrc NE 0.

IF gwa_cma-objid IN s_objid.

MOVE-CORRESPONDING gwa_common_data TO gwa_cma.

APPEND gwa_cma TO gt_cma.

CLEAR gwa_cma.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDIF.

ELSEIF lv_ex_person EQ 'X'.

* lv_objid_h

MOVE-CORRESPONDING gwa_common_data TO gwa_cma.

DATA: gt_1001_h TYPE TABLE OF hrp1001,

gwa_1001_h TYPE hrp1001.

DATA: lv_cma TYPE hrp1001-sobid.

SELECT SINGLE * FROM hrp1001 INTO gwa_1001_h

WHERE objid = lv_objid_h

AND plvar = gc_plvar

AND otype = 'H'

AND rsign = 'A'

AND relat EQ 'ZAC'

AND sclas = 'H'

AND begda IN s_pbegda

AND endda IN s_pendda.

IF sy-subrc EQ 0.

gwa_cma-objid = gwa_1001_h-sobid.

gwa_cma-cm_begda = gwa_1001_h-begda.

gwa_cma-cm_endda = gwa_1001_h-endda.

* Get First name and Last name of CMA of External Person.

READ TABLE gt_9314 INTO gwa_9314 WITH KEY objid = gwa_1001_h-sobid.

IF sy-subrc EQ 0.

gwa_cma-cm_name1 = gwa_9314-vorna.

gwa_cma-cm_name2 = gwa_9314-nachn.

ENDIF.

MOVE-CORRESPONDING gwa_cma TO gwa_all.

APPEND gwa_cma TO gt_cma.

CLEAR gwa_cma.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

ENDIF.

ELSE.

IF gwa_common_data-actor EQ 'TM' OR gwa_common_data-actor EQ 'TA' OR gwa_common_data-actor EQ 'STM' OR gwa_common_data-actor EQ 'TSP' OR gwa_common_data-actor EQ 'ARC'."lv_relat.

CONTINUE.

ENDIF.

* IF lv_ex_person EQ 'X'.

* MOVE-CORRESPONDING gwa_common_data TO gwa_all.

* APPEND gwa_all TO gt_all.

* CLEAR gwa_all.

* ENDIF.

* IF lv_person EQ 'X'.

MOVE-CORRESPONDING gwa_common_data TO gwa_all.

APPEND gwa_all TO gt_all.

CLEAR gwa_all.

* ENDIF.

ENDIF.

ENDLOOP.

END-OF-SELECTION.

CALL SCREEN 100.

*&---------------------------------------------------------------------*

*& Module ALV_GRID_0100 OUTPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE alv_grid_0100 OUTPUT.

PERFORM free.

IF go_custom_container_100 IS INITIAL.

CREATE OBJECT go_custom_container_100

EXPORTING

container_name = 'GV_CONTAINER'.

ENDIF.

CLEAR go_grid_100.

IF go_grid_100 IS INITIAL.

CREATE OBJECT go_grid_100

EXPORTING

i_parent = go_custom_container_100.

ENDIF.

PERFORM build_fieldcat_100.

IF rb_all EQ 'X'.

gs_layout_100-grid_title = 'All Fields'. "#EC NOTEXT

PERFORM outputlog_alv_disp CHANGING gt_all.

ELSEIF rb_pa EQ 'X'.

gs_layout_100-grid_title = 'PA'.

PERFORM outputlog_alv_disp CHANGING gt_population_actors.

ELSEIF rb_ta EQ 'X'.

gs_layout_100-grid_title = 'TA'.

PERFORM outputlog_alv_disp CHANGING gt_training_actors.

ELSEIF rb_cma EQ 'X'.

gs_layout_100-grid_title = 'CMA'.

PERFORM outputlog_alv_disp CHANGING gt_cma.

ENDIF.

ENDMODULE. " ALV_GRID_0100 OUTPUT

*&---------------------------------------------------------------------*

*& Form FREE

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM free .

IF go_grid_100 IS NOT INITIAL.

CALL METHOD go_grid_100->free

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

OTHERS = 3.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

FREE go_grid_100.

ENDIF.

IF go_custom_container_100 IS NOT INITIAL.

CALL METHOD go_custom_container_100->free

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

OTHERS = 3.

IF sy-subrc NE 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

FREE go_custom_container_100.

ENDIF.

ENDFORM. " FREE

*&---------------------------------------------------------------------*

*& Form BUILD_FIELDCAT_100

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM build_fieldcat_100 .

DATA:ls_fcat TYPE lvc_s_fcat.

REFRESH gt_fieldcat_100.

IF rb_all EQ 'X'. "All radiobutton(Common data)

ls_fcat-col_pos = 1.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'BNAME'.

ls_fcat-coltext = 'IGG'(008).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 2.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NAME2'.

ls_fcat-coltext = 'Name'(009).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 3.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NAME1'.

ls_fcat-coltext = 'First name'(010).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 4.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'ACTOR'.

ls_fcat-coltext = 'Actor'(011).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 5.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'TEXT'.

ls_fcat-coltext = 'Actor description'(012).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 6.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'BEGDA'.

ls_fcat-coltext = 'Start date'(013).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 7.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'ENDDA'.

ls_fcat-coltext = 'End date'(014).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 8. "All radiobutton(Population output)

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'POP_IA'.

ls_fcat-coltext = 'IA object ID'(015).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 9.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'IA_POP_NAME'.

ls_fcat-coltext = 'IA Population description'(016).

ls_fcat-outputlen = 26.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 10.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'POP_IC'.

ls_fcat-coltext = 'IC object ID'(017).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 11.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'IC_POP_NAME'.

ls_fcat-coltext = 'IC Population description'(018).

ls_fcat-outputlen = 27.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 12.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'POP_PA'.

ls_fcat-coltext = 'PA object ID'(019).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 13.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'PA_POP_NAME'.

ls_fcat-coltext = 'PA Population description'(020).

ls_fcat-outputlen = 30.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 14.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'POP_PC'.

ls_fcat-coltext = 'PC object ID'(021).

ls_fcat-outputlen = 12.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 15.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'PC_POP_NAME'.

ls_fcat-coltext = 'PC Population description'(022).

ls_fcat-outputlen = 27.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 16.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'EXECUTIVE'.

ls_fcat-coltext = 'NP 10 +'(023).

ls_fcat-outputlen = 8.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 17.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'SENIOR_EXEC'.

ls_fcat-coltext = 'Senior Executive'(024).

ls_fcat-outputlen = 15.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 18.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'HR_ACTOR'.

ls_fcat-coltext = 'HR Domain'(025).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 19.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'SENSITIVE_POS'.

ls_fcat-coltext = 'Sensitive'(026).

ls_fcat-outputlen = 9.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 20.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'MANAGED_CONTRAC'.

ls_fcat-coltext = 'Managed contractor'(027).

ls_fcat-outputlen = 18.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 21. "All radiobutton(Training output)

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'TDEPT'.

ls_fcat-coltext = 'Training Service object ID'(028).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 22.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'O_TLINE'.

ls_fcat-coltext = 'O object description'(029).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 23.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'TR_BEGDA'.

ls_fcat-coltext = 'Start date training service'(030).

ls_fcat-outputlen = 23.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 24.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'TR_ENDDA'.

ls_fcat-coltext = 'End date training service'(031).

ls_fcat-outputlen = 23.

* ls_fcat-coltext = 'Training End date'(031).

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 25.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'NPHP'.

ls_fcat-coltext = 'NP/HP'(032).

ls_fcat-outputlen = 7.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 26.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'POOL'.

ls_fcat-coltext = 'Pool Assignment object ID'(033).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 27.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'P_TLINE'.

ls_fcat-coltext = 'Pool description'(034).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 28.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'P_BEGDA'.

ls_fcat-coltext = 'Start Date pool assignment'(035).

ls_fcat-outputlen = 22.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 29.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'P_ENDDA'.

ls_fcat-coltext = 'End date pool assignment'(036).

ls_fcat-outputlen = 22.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 30.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'OBJID'.

ls_fcat-coltext = 'Career manager'(037).

ls_fcat-outputlen = 15.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 31.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CM_NAME2'.

ls_fcat-coltext = 'Name of the CM'(038).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 32.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CM_NAME1'.

ls_fcat-coltext = 'First name of CM'(039).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 33.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CM_BEGDA'.

ls_fcat-coltext = 'Start date of the relationship'(040).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 34.

ls_fcat-tabname = 'GT_ALL'.

ls_fcat-fieldname = 'CM_ENDDA'.

ls_fcat-coltext = 'End date of the relationship'(041).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ELSEIF rb_pa EQ 'X'. "Check only Population actors

ls_fcat-col_pos = 1.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'BNAME'.

ls_fcat-coltext = 'IGG'(008).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 2.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'NAME2'.

ls_fcat-coltext = 'Name'(009).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 3.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'NAME1'.

ls_fcat-coltext = 'First name'(010).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 4.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'ACTOR'.

ls_fcat-coltext = 'Actor'(011).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 5.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'TEXT'.

ls_fcat-coltext = 'Actor description'(012).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 6.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'BEGDA'.

ls_fcat-coltext = 'Start date'(013).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 7.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'ENDDA'.

ls_fcat-coltext = 'End date'(014).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 8. "All radiobutton(Population output)

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'POP_IA'.

ls_fcat-coltext = 'IA object ID'(015).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 9.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'IA_POP_NAME'.

ls_fcat-coltext = 'IA Population description'(016).

ls_fcat-outputlen = 26.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 10.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'POP_IC'.

ls_fcat-coltext = 'IC object ID'(017).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 11.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'IC_POP_NAME'.

ls_fcat-coltext = 'IC Population description'(018).

ls_fcat-outputlen = 27.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 12.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'POP_PA'.

ls_fcat-coltext = 'PA object ID'(019).

ls_fcat-outputlen = 13.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 13.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'PA_POP_NAME'.

ls_fcat-coltext = 'PA Population description'(020).

ls_fcat-outputlen = 30.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 14.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'POP_PC'.

ls_fcat-coltext = 'PC object ID'(021).

ls_fcat-outputlen = 12.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 15.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'PC_POP_NAME'.

ls_fcat-coltext = 'PC Population description'(022).

ls_fcat-outputlen = 27.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 16.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'EXECUTIVE'.

ls_fcat-coltext = 'NP 10 +'(023).

ls_fcat-outputlen = 8.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 17.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'SENIOR_EXEC'.

ls_fcat-coltext = 'Senior Executive'(024).

ls_fcat-outputlen = 15.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 18.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'HR_ACTOR'.

ls_fcat-coltext = 'HR Domain'(025).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 19.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'SENSITIVE_POS'.

ls_fcat-coltext = 'Sensitive'(026).

ls_fcat-outputlen = 9.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 20.

ls_fcat-tabname = 'GT_POPULATION_ACTORS'.

ls_fcat-fieldname = 'MANAGED_CONTRAC'.

ls_fcat-coltext = 'Managed contractor'(027).

ls_fcat-outputlen = 18.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ELSEIF rb_ta EQ 'X'. "Training actors

ls_fcat-col_pos = 1.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'BNAME'.

ls_fcat-coltext = 'IGG'(008).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 2.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'NAME2'.

ls_fcat-coltext = 'Name'(009).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 3.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'NAME1'.

ls_fcat-coltext = 'First name'(010).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 4.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'ACTOR'.

ls_fcat-coltext = 'Actor'(011).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 5.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'TEXT'.

ls_fcat-coltext = 'Actor description'(012).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 6.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'BEGDA'.

ls_fcat-coltext = 'Start date'(013).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 7.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'ENDDA'.

ls_fcat-coltext = 'End date'(014).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 8. "Training output

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'TDEPT'.

ls_fcat-coltext = 'Training Service object ID'(028).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 9.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'O_TLINE'.

ls_fcat-coltext = 'O object description'(029).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 10.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'TR_BEGDA'.

ls_fcat-coltext = 'Start date training service'(030).

ls_fcat-outputlen = 23.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 11.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'TR_ENDDA'.

ls_fcat-coltext = 'End date training service'(031).

ls_fcat-outputlen = 23.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 12.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'NPHP'.

ls_fcat-coltext = 'NP/HP'(032).

ls_fcat-outputlen = 7.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 13.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'POOL'.

ls_fcat-coltext = 'Pool Assignment object ID'(033).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 14.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'P_TLINE'.

ls_fcat-coltext = 'Pool description'(034).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 15.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'P_BEGDA'."'POOL_BEGDA'.

ls_fcat-coltext = 'Start Date pool assignment'(035).

ls_fcat-outputlen = 22.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 16.

ls_fcat-tabname = 'GT_TRAINING_ACTORS'.

ls_fcat-fieldname = 'P_ENDDA'."'POOL_ENDDA'.

ls_fcat-coltext = 'End date pool assignment'(036).

ls_fcat-outputlen = 22.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ELSEIF rb_cma EQ 'X'. "Career manager assistant

ls_fcat-col_pos = 1.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'BNAME'.

ls_fcat-coltext = 'IGG'(008).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 2.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'NAME2'.

ls_fcat-coltext = 'Name'(009).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 3.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'NAME1'.

ls_fcat-coltext = 'First name'(010).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 4.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'ACTOR'.

ls_fcat-coltext = 'Actor'(011).

ls_fcat-outputlen = 10.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 5.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'TEXT'.

ls_fcat-coltext = 'Actor description'(012).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 6.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'BEGDA'.

ls_fcat-coltext = 'Start date'(013).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 7.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'ENDDA'.

ls_fcat-coltext = 'End date'(014).

ls_fcat-outputlen = 11.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 8.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'OBJID'.

ls_fcat-coltext = 'Career manager'(037).

ls_fcat-outputlen = 15.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 9.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'CM_NAME2'.

ls_fcat-coltext = 'Name of the CM'(038).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 10.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'CM_NAME1'.

ls_fcat-coltext = 'First name of CM'(039).

ls_fcat-outputlen = 20.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 11.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'CM_BEGDA'.

ls_fcat-coltext = 'Start date of the relationship'(040).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ls_fcat-col_pos = 12.

ls_fcat-tabname = 'GT_CMA'.

ls_fcat-fieldname = 'CM_ENDDA'.

ls_fcat-coltext = 'End date of the relationship'(041).

ls_fcat-outputlen = 24.

APPEND ls_fcat TO gt_fieldcat_100.

CLEAR ls_fcat.

ENDIF.

ENDFORM. " BUILD_FIELDCAT_100

*&---------------------------------------------------------------------*

*& Form OUTPUTLOG_ALV_DISP

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM outputlog_alv_disp CHANGING i_disp_table TYPE STANDARD TABLE.

CALL METHOD go_grid_100->set_table_for_first_display

EXPORTING

* it_toolbar_excluding = gt_exclude_100

is_layout = gs_layout_100

CHANGING

it_fieldcatalog = gt_fieldcat_100

it_outtab = i_disp_table[].

* Set editable cells to ready for input initially

CALL METHOD go_grid_100->set_ready_for_input

EXPORTING

i_ready_for_input = 0.

ENDFORM. " OUTPUTLOG_ALV_DISP

*&---------------------------------------------------------------------*

*& Module STATUS_0100 OUTPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE status_0100 OUTPUT.

SET PF-STATUS 'STATUS100'.

SET TITLEBAR 'TITLE100'.

ENDMODULE. " STATUS_0100 OUTPUT

*&---------------------------------------------------------------------*

*& Module USER_COMMAND_0100 INPUT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

MODULE user_command_0100 INPUT.

CASE sy-ucomm.

WHEN 'BACK'.

LEAVE TO SCREEN 0.

WHEN 'CANCEL'.

LEAVE TO SCREEN 0.

WHEN 'EXIT'.

LEAVE TO SCREEN 0.

ENDCASE.

ENDMODULE. " USER_COMMAND_0100 INPUT

*&---------------------------------------------------------------------*

*& Include ZPAUTREPORT_TOP

*&---------------------------------------------------------------------*

TABLES: zsft_actor,

zpeia_doc,

zsft_user_actor,

zsft_user_popul,

zsft_user_populf,

hrp1002.

*******Internal table declaration******************

******Internal table Common data declaration******************

TYPES: BEGIN OF ty_common_data,

bname TYPE zsft_user_actor-bname,

name2 TYPE xuname1,

name1 TYPE xuname1 ,

actor TYPE zsft_user_actor-actor,

begda TYPE zsft_user_actor-start_date,

endda TYPE zsft_user_actor-end_date,

text TYPE zsft_actor_t-text,

END OF ty_common_data.

DATA: gwa_common_data TYPE ty_common_data,

gt_common_data TYPE TABLE OF ty_common_data.

TYPES: BEGIN OF ty_all,

* Common data:

bname TYPE zsft_user_actor-bname, "IGG

name2 TYPE xuname1, "Name

name1 TYPE xuname1 , "First name

actor TYPE zsft_user_actor-actor, "Actor

text TYPE zsft_actor_t-text, "Actor description

begda TYPE zsft_user_actor-start_date, "Start date

endda TYPE zsft_user_actor-end_date, "End date

* Population output:

pop_ia TYPE zsft_user_popul-pop_ia,

ia_pop_name TYPE zpftpopulation_t-pop_name, "IA Population description

pop_ic TYPE zsft_user_popul-pop_ic,

ic_pop_name TYPE zpftpopulation_t-pop_name, "IC Population description

pop_pa TYPE zsft_user_popul-pop_pa,

pa_pop_name TYPE zpftpopulation_t-pop_name, "PA Population description

pop_pc TYPE zsft_user_popul-pop_pc,

pc_pop_name TYPE zpftpopulation_t-pop_name, "PC Population description

executive TYPE zsft_user_populf-executive, "Can manage NP10+

senior_exec TYPE zsft_user_populf-senior_exec, "Senior Executive

hr_actor TYPE zsft_user_populf-hr_actor, "HR Domain

sensitive_pos TYPE zsft_user_populf-sensitive_pos, "Sensitive

managed_contrac TYPE zsft_user_populf-managed_contrac,"Managed contractor

* Training

tdept TYPE hrp1002-objid, "Training Service object ID

o_tline TYPE hrt1002-tline, "O object description

tr_begda TYPE zsft_user_actor-start_date, "Start date of the training service

tr_endda TYPE zsft_user_actor-end_date, "End date of the training service

nphp TYPE zsft_user_populf-nphp, "NP/HP

pool TYPE hrp1002-objid, "Pool Assignment object ID

p_tline TYPE hrt1002-tline, "Pool description

p_begda TYPE zsft_user_actor-start_date, "Start date pool assignment

p_endda TYPE zsft_user_actor-end_date, "End date pool assignment

* Career manager

objid TYPE hrp1002-objid, "Career manager

cm_name2 TYPE xuname1, "Name of the CM (Name of the CM The name is retrieved through the user profile (SU01D))

cm_name1 TYPE xuname1 , "First name of CM (The name is retrieved through the user profile (SU01D))

cm_begda TYPE zsft_user_actor-start_date, "Start date of the relationship

cm_endda TYPE zsft_user_actor-end_date, "End date of the relationship

END OF ty_all.

DATA: gwa_all TYPE ty_all,

gt_all TYPE TABLE OF ty_all.

*******Internal table Population actors data declaration******************

TYPES: BEGIN OF ty_population_actors,

* Common data:

bname TYPE zsft_user_actor-bname, "IGG

name2 TYPE xuname1, "Name

name1 TYPE xuname1 , "First name

actor TYPE zsft_user_actor-actor, "Actor

text TYPE zsft_actor_t-text, "Actor description

begda TYPE zsft_user_actor-start_date, "Start date

endda TYPE zsft_user_actor-end_date, "End date

* Population output:

pop_ia TYPE zsft_user_popul-pop_ia,

ia_pop_name TYPE zpftpopulation_t-pop_name, "IA Population description

pop_ic TYPE zsft_user_popul-pop_ic,

ic_pop_name TYPE zpftpopulation_t-pop_name, "IC Population description

pop_pa TYPE zsft_user_popul-pop_pa,

pa_pop_name TYPE zpftpopulation_t-pop_name, "PA Population description

pop_pc TYPE zsft_user_popul-pop_pc,

pc_pop_name TYPE zpftpopulation_t-pop_name, "PC Population description

executive TYPE zsft_user_populf-executive,

senior_exec TYPE zsft_user_populf-senior_exec,

hr_actor TYPE zsft_user_populf-hr_actor,

sensitive_pos TYPE zsft_user_populf-sensitive_pos,

managed_contrac TYPE zsft_user_populf-managed_contrac,

END OF ty_population_actors.

DATA: gwa_population_actors TYPE ty_population_actors,

gt_population_actors TYPE TABLE OF ty_population_actors.

********Internal table Training actors data declaration******************

TYPES: BEGIN OF ty_training_actors,

* Common data:

bname TYPE zsft_user_actor-bname, "IGG

name2 TYPE xuname1, "Name

name1 TYPE xuname1 , "First name

actor TYPE zsft_user_actor-actor, "Actor

text TYPE zsft_actor_t-text, "Actor description

begda TYPE zsft_user_actor-start_date, "Start date

endda TYPE zsft_user_actor-end_date, "End date

* Training

tdept TYPE hrp1002-objid, "Training Service object ID

o_tline TYPE hrt1002-tline, "O object description

tr_begda TYPE zsft_user_actor-start_date, "Start date of the training service

tr_endda TYPE zsft_user_actor-end_date, "End date of the training service

nphp TYPE zsft_user_populf-nphp, "NP/HP

pool TYPE hrp1002-objid, "Pool Assignment object ID

p_tline TYPE hrt1002-tline, "Pool description

p_begda TYPE zsft_user_actor-start_date, "Start date pool assignment

p_endda TYPE zsft_user_actor-end_date, "End date pool assignment

END OF ty_training_actors.

DATA: gwa_training_actors TYPE ty_training_actors,

gt_training_actors TYPE TABLE OF ty_training_actors.

********Internal table Career manager assistant data declaration******************

TYPES: BEGIN OF ty_cma,

* Common data:

bname TYPE zsft_user_actor-bname, "IGG

name2 TYPE xuname1, "Name

name1 TYPE xuname1 , "First name

actor TYPE zsft_user_actor-actor, "Actor

text TYPE zsft_actor_t-text, "Actor description

begda TYPE zsft_user_actor-start_date, "Start date

endda TYPE zsft_user_actor-end_date, "End date

* Career manager

objid TYPE hrp1002-objid, "Career manager

cm_name2 TYPE xuname1, "Name of the CM

cm_name1 TYPE xuname1 , "First name of CM

cm_begda TYPE zsft_user_actor-start_date, "Start date of the relationship

cm_endda TYPE zsft_user_actor-end_date, "End date of the relationship

END OF ty_cma.

DATA: gwa_cma TYPE ty_cma,

gt_cma TYPE TABLE OF ty_cma.

DATA: gt_user_actor TYPE STANDARD TABLE OF zsft_user_actor,

gwa_user_actor TYPE zsft_user_actor,

gt_actort TYPE STANDARD TABLE OF zsft_actor_t,

gwa_actort TYPE zsft_actor_t,

gt_user_popul TYPE STANDARD TABLE OF zsft_user_popul,

gwa_user_popul TYPE zsft_user_popul,

gt_popul_t TYPE STANDARD TABLE OF zpftpopulation_t,

gwa_popul_t TYPE zpftpopulation_t,

gt_user_populf TYPE STANDARD TABLE OF zsft_user_populf,

gwa_user_populf TYPE zsft_user_populf,

gt_usr03 TYPE STANDARD TABLE OF usr03,

gwa_usr03 TYPE usr03,

gt_addr TYPE STANDARD TABLE OF addr3_val,

gwa_addr TYPE addr3_val,

gt_1001 TYPE STANDARD TABLE OF hrp1001,

gwa_1001 TYPE hrp1001,

gt_9314 TYPE STANDARD TABLE OF hrp9314,

gwa_9314 TYPE hrp9314,

gt_1000 TYPE STANDARD TABLE OF hrp1000,

gwa_1000 TYPE hrp1000,

gt_1001_ta TYPE STANDARD TABLE OF hrp1001,

gwa_1001_ta TYPE hrp1001,

gt_1001_p TYPE STANDARD TABLE OF hrp1001,

gwa_1001_p TYPE hrp1001,

gwa_sft_scrn_maintn TYPE zsft_scrn_maintn,

gt_sft_scrn_maintn TYPE TABLE OF zsft_scrn_maintn..

DATA: lv_no_flag TYPE char1.

DATA: gt_fieldcat_100 TYPE lvc_t_fcat.

DATA: gv_disp_table TYPE string.

DATA: go_grid_100 TYPE REF TO cl_gui_alv_grid.

DATA: go_custom_container_100 TYPE REF TO cl_gui_custom_container.

DATA: gs_layout_100 TYPE lvc_s_layo.

CONSTANTS:

lc_act1 TYPE char2 VALUE 'TM',

lc_act2 TYPE char2 VALUE 'TA',

lc_act3 TYPE char3 VALUE 'STM',

lc_act4 TYPE char3 VALUE 'TSP',

lc_act5 TYPE char3 VALUE 'ARC',

gc_plvar TYPE hrp1000-plvar VALUE '01'.

******selection screen declaration****************

SELECTION-SCREEN:BEGIN OF BLOCK rad1 WITH FRAME TITLE text-001.

* Common data.

SELECTION-SCREEN BEGIN OF BLOCK common WITH FRAME TITLE text-002.

SELECT-OPTIONS : s_bname FOR zsft_user_actor-bname MATCHCODE OBJECT user_comp OBLIGATORY.

SELECT-OPTIONS : s_actor FOR zsft_user_actor-actor.

SELECT-OPTIONS : s_begda FOR zsft_user_actor-start_date.

SELECT-OPTIONS : s_endda FOR zsft_user_actor-end_date.

SELECTION-SCREEN END OF BLOCK common.

SELECTION-SCREEN BEGIN OF BLOCK options WITH FRAME TITLE text-003.

SELECTION-SCREEN BEGIN OF LINE.

**Radio buttons

PARAMETERS: rb_all RADIOBUTTON GROUP auth USER-COMMAND rusr.

SELECTION-SCREEN COMMENT 7(5) text-004 FOR FIELD rb_all.

PARAMETERS: rb_pa RADIOBUTTON GROUP auth.

SELECTION-SCREEN COMMENT 14(18) text-005 FOR FIELD rb_pa.

PARAMETERS: rb_ta RADIOBUTTON GROUP auth.

SELECTION-SCREEN COMMENT 41(19) text-006 FOR FIELD rb_ta.

PARAMETERS: rb_cma RADIOBUTTON GROUP auth.

SELECTION-SCREEN COMMENT 62(36) text-007 FOR FIELD rb_cma.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK options.

SELECTION-SCREEN BEGIN OF BLOCK pa WITH FRAME TITLE text-005.

SELECT-OPTIONS: s_pop_ia FOR zsft_user_popul-pop_ia MATCHCODE OBJECT zpft_populid MODIF ID pa,

s_pop_ic FOR zsft_user_popul-pop_ic MATCHCODE OBJECT zpft_populid MODIF ID pa,

s_pop_pa FOR zsft_user_popul-pop_pa MATCHCODE OBJECT zpft_populid MODIF ID pa,

s_pop_pc FOR zsft_user_popul-pop_pc MATCHCODE OBJECT zpft_populid MODIF ID pa.

PARAMETERS: ck_np10 AS CHECKBOX MODIF ID pa,

ck_sexec AS CHECKBOX MODIF ID pa,

ck_hra AS CHECKBOX MODIF ID pa,

ck_mgc AS CHECKBOX MODIF ID pa,

ck_spos AS CHECKBOX MODIF ID pa.

SELECTION-SCREEN END OF BLOCK pa.

SELECTION-SCREEN BEGIN OF BLOCK ta WITH FRAME TITLE text-006.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-048 MODIF ID ta.

SELECT-OPTIONS: s_tdept FOR hrp1002-objid MATCHCODE OBJECT zpsft_training_s MODIF ID ta.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-030 MODIF ID ta.

SELECT-OPTIONS: s_tbegda FOR zsft_user_actor-start_date MODIF ID ta.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-031 MODIF ID ta.

SELECT-OPTIONS: s_tendda FOR zsft_user_actor-end_date MODIF ID ta.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-045 MODIF ID ta.

SELECT-OPTIONS: s_pool FOR hrp1002-objid MATCHCODE OBJECT zpsft_pool MODIF ID ta.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-046 MODIF ID ta.

SELECT-OPTIONS: s_pbegda FOR zsft_user_actor-start_date MODIF ID ta.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(38) text-047 MODIF ID ta.

SELECT-OPTIONS: s_pendda FOR zsft_user_actor-end_date MODIF ID ta.

SELECTION-SCREEN END OF LINE.

PARAMETERS: ck_nphp AS CHECKBOX MODIF ID ta.

SELECTION-SCREEN END OF BLOCK ta.

SELECTION-SCREEN BEGIN OF BLOCK cma WITH FRAME TITLE text-007.

SELECT-OPTIONS: s_objid FOR hrp1002-objid MATCHCODE OBJECT zpsft_pernr_cm MODIF ID cma.

SELECT-OPTIONS: s_cbegda FOR zsft_user_actor-start_date MODIF ID cma.

SELECT-OPTIONS: s_cendda FOR zsft_user_actor-end_date MODIF ID cma.

SELECTION-SCREEN END OF BLOCK cma.

SELECTION-SCREEN END OF BLOCK rad1.

How To Create an Index on a View?

2013-09-10T00:44:00.003-07:00

If you need to search and sort data in a view with a large number of row, you may want to create an index on the view to speed up your search process.

The tutorial exercise below shows you how to create a unique clustered index on a view.

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, UrlReversed)
   AS SELECT id, REVERSE(url)
   FROM fyi_links_copy WHERE counts > 1000;
GO

CREATE UNIQUE CLUSTERED INDEX date_string 
   ON fyi_links_view (ID);
GO
Cannot create index on view 'fyi_links_view' 
   because the view is not schema bound.

ALTER VIEW fyi_links_view (ID, UrlReversed)
   WITH SCHEMABINDING
   AS SELECT id, REVERSE(url)
   FROM dbo.fyi_links_copy WHERE counts > 1000;
GO

CREATE UNIQUE CLUSTERED INDEX date_string 
   ON fyi_links_view (ID);
GO

EXEC SP_HELP fyi_links_view;
GO
index_name  index_description                    index_keys
----------- ------------------------------------ ----------
date_string clustered, unique located on PRIMARY ID

How To Bind a View to the Schema of the Underlying Tables?

2013-09-10T00:44:00.001-07:00

By default, views are not bound to the schema of the underlying tables. This means that SQL Server will allow you to change underlying table's schema any time. For example, you can drop the underlying table while keep the view. Of course, this will make the view not valid any more.

If you don't want anyone to change underlying table's schema once a view has been defined, you can create a binding view with the "WITH SCHEMABINDING" clause to bind the view to the schema of underlying tables. A binding view has the following features:

Changing of underlying table's schema is not allowed as long as there exists one binding view.
Indexes can be created only on binding views.

The tutorial exercise below shows you how to create a binding with "WITH SCHEMABINDING":

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM fyi_links_copy WHERE counts > 1000;
GO
Msg 4512, Level 16, State 3, Procedure fyi_links_view, 
   Line 3
Cannot schema bind view 'fyi_links_view' because name 
   'fyi_links_copy' is invalid for schema binding. 
   Names must be in two-part format and an object 
   cannot reference itself.

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM dbo.fyi_links_copy WHERE counts > 1000;
GO
Command(s) completed successfully.

DROP TABLE fyi_links_copy;
GO
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'fyi_links_copy' because it is being 
   referenced by object 'fyi_links_view'.

The error received in the first CREATE VIEW statement says that you must prefix the table name with the schema name.

The error received in the DROP TABLE statement proves that you can not change the underlying tables if there is binding view.

How Column Data Types Are Determined in a View?

2013-09-10T00:43:00.002-07:00

When you define a view, its columns are defined through a list of expressions in the underlying SELECT statement. Their data types will be determined implicitly by the expressions.

For example, if the column expression is a column name of a underlying table, the data type of the view column will be the same of the underlying table column.

If the column expression is a function, the data type of the view column will be the function return data type.

If the column expression is an operation, the data type of the view column will be the expression result data type.

The following tutorial exercise shows you some examples of view column data types:

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, DateString, CountUrl) AS
   SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM fyi_links WHERE counts > 1000
GO

SELECT TOP 3 * FROM fyi_links_view;
GO
ID     DateString    CountUrl
------ ------------- ---------------------------------------
7600   Jun 06, 1891  237946 -   eyfndw jdt  lee ztejeyx l q 
19437  May 30, 1833  222337 -   eypx u x
55924  Dec 29, 1956  1877 -   eyq ntohxe i rtnlu riwaskzp  c

EXEC SP_HELP fyi_links_view;
GO
Column_name  Type     Length      Prec  Scale 
------------ -------- ----------- ----- ----- 
ID           int      4           10    0     
DateString   varchar  16                      
CountUrl     varchar  103                     

In view, fyi_links_view, defined in this exercise:


Column "ID" has a data type INT, same as the data type of "id" in the underlying table.
Column "DateString" has a data type of VARCHAR(16), returned by the CONVERT() function.

Column "CountUlr" has a data type of VARCHAR(103), returned by the concatenation 
operations.

How To Assign New Column Names in a View?

2013-09-10T00:42:00.003-07:00

By default, column names in a view are provided by the underlying SELECT statement.

But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this case, you need to assign new names for the view's columns. The tutorial exercise below creates a view to merge several table columns into a single view column with a format called CSV (Comma Separated Values):

CREATE VIEW fyi_links_dump AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ', ' + CONVERT(VARCHAR(20),counts) 
      + ', ''' + url + ''''
   FROM fyi_links WHERE counts > 1000
GO
Msg 4511, Level 16, State 1, Procedure fyi_links_dump, 
   Line 2
Create View or Function failed because no column name 
   was specified for column 1.

CREATE VIEW fyi_links_dump (Line) AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ', ' + CONVERT(VARCHAR(20),counts) 
      + ', ''' + url + ''''
   FROM fyi_links WHERE counts > 1000
GO

SELECT TOP 3 * FROM fyi_links_dump
GO
Line
------------------------------------------------------------
7600, 237946, '  eyfndw jdt  lee ztejeyx l q  jdh k '
19437, 222337, '  eypx u x'
55924, 1877, '  eyq ntohxe i rtnlu riwaskzp  cucoa dva c rc'

The first CREATE VIEW gives you an error, because the SELECT statement returns no column for the concatenated value, and no view column name is specified explicitly.

Can You Delete Data from a View?

2013-09-10T00:42:00.001-07:00

Can you delete data in a view? The answer is no.

But if the question is "Can you delete data from the underlying table through view?" The answer is then yes. SQL Server will allow you to delete data from the underlying table through a view. The tutorial exercise below is a good example:

DELETE FROM fyi_links_top WHERE id = 100001;
GO

SELECT * FROM fyi_links_top;
GO
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url                                         ...
------ ------------------------------------------- ...
36470  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb ...

Can You Update Data in a View?

2013-09-10T00:41:00.003-07:00

Can you update data in a view? The answer is no.

But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exercise below is a good example:

UPDATE fyi_links_top SET url = REVERSE(url) 
   WHERE id = 100001;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 moc.retneciyf.abd
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 moc.retneciyf.abd NULL  1000001     2007-05-19

Can You Insert Data into a View?

2013-09-10T00:41:00.001-07:00

Can you insert data into a view? The answer is no.

But if the question is "Can you insert data into the underlying table through view?" The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition:

The insert columns must be limited to columns of a single underlying table.

The tutorial exercise below shows you how to insert data into a underlying table through a view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

INSERT INTO fyi_links_top 
   VALUES(100001, 1000001, 'dba.fyicenter.com');
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 dba.fyicenter.com
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 dba.fyicenter.com NULL  1000001     2007-05-19

How To Modify the Underlying Query of an Existing View?

2013-09-10T00:40:00.001-07:00

If you have an existing view, and want to change the underlying SELECT statement, you can use the "ALTER VIEW ..." statement to redefine the view. The tutorial exercise below shows you how modify an existing view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od

Can You Use ORDER BY When Defining a View?

2013-09-10T00:39:00.005-07:00

Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view.

But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise below shows you what error you will get when using ORDER BY in a CREATE VIEW statement:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_top AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO
Msg 1033, Level 15, State 1, Procedure fyi_links_top, Line 4
The ORDER BY clause is invalid in views, inline functions, 
   derived tables, subqueries, and common table expressions,
   unless TOP or FOR XML is also specified.

CREATE VIEW fyi_links_top AS
   SELECT TOP 100 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT TOP 3 * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od

The view seems to be sorted correctly.

What Happens If You Delete a Table That Is Used by a View?

2013-09-10T00:39:00.002-07:00

Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble.

But that view will become invalid. The tutorial exercise below shows you what happens to the view, when the underlying table is deleted:

USE FyiCenterData;
GO

SELECT * INTO fyi_links_copy 
   FROM fyi_links WHERE counts > 0;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
50015

DROP TABLE fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fyi_links_copy'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'fyi_links_view' 
   because of binding errors.

Can You Create a View using Data from Another View?

2013-09-10T00:38:00.002-07:00

Can You Create a View with Data from Another View? The answer is yes. A view can be used as a table to build other views. The tutorial exercise below shows you how to create a view using data from another view:

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderTop AS
   SELECT SalesOrderNumber, TotalDue, CompanyName
   FROM SalesOrderView 
   WHERE TotalDue > 10000.0
GO

SELECT TOP 10 * FROM SalesOrderTop;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71780          42452.6519  Nearby Cycle Shop
SO71782          43962.7901  Professional Sales and Service
SO71783          92663.5609  Eastside Department Store
SO71784          119960.824  Action Bicycle Specialists
SO71796          63686.2708  Extreme Riding Supplies
SO71797          86222.8072  Riding Cycles
SO71832          39531.6085  Closest Bicycle Store
SO71845          45992.3665  Trailblazing Sports
SO71858          15275.1977  Thrilling Bike Tours
SO71897          14017.9083  Paints and Solvents Company

Can You Create a View with Data from Multiple Tables?

2013-09-10T00:37:00.002-07:00

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
   SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
      c.FirstName, c.LastName, c.CompanyName
   FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
   WHERE o.CustomerID = c.CustomerID
GO

SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName 
   FROM SalesOrderView;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71915          2361.6403   Aerobic Exercise Company
SO71938          98138.2131  Bulk Discount Store
SO71783          92663.5609  Eastside Department Store
SO71899          2669.3183   Coalition Bike Company
SO71898          70698.9922  Instruments and Parts Company
SO71902          81834.9826  Many Bikes Store
SO71832          39531.6085  Closest Bicycle Store
SO71776          87.0851     West Side Mart
SO71797          86222.8072  Riding Cycles
SO71895          272.6468    Futuristic Bikes

How To Get the Definition of a View Out of the SQL Server?

2013-09-10T00:36:00.004-07:00

If you want get the definition of an existing view back from the SQL Server, you can use the system view called sys.sql_modules, which stores defitions of views and procedures.

The sys.sql_modules holds view definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of view, "fyi_link_view" by joining sys.sql_modules and sys.views:

USE FyiCenterData;
GO

SELECT m.definition 
   FROM sys.sql_modules m, sys.views v
   WHERE m.object_id = v.object_id
   AND v.name = 'fyi_links_top';   
GO
definition
-------------------------------------------
CREATE VIEW fyi_links_top (LinkText) AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ' - ' + CONVERT(VARCHAR(20),counts) 
      + ' - ' + url 
   FROM fyi_links WHERE counts > 1000
(1 row(s) affected)

How To Generate CREATE VIEW Script on an Existing View?

2013-09-10T00:36:00.001-07:00

If you want to know how an existing view was created, you can use SQL Server Management Studio to automatically generate a "CREATE VIEW" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > FyiCenterData > Views > dbo.fyi_links_top.

3. Click right mouse button on dbo.fyi_links_top. The context menu shows up.

4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [FyiCenterData]
GO
/****** Object:  View [dbo].[fyi_links_top]
    Script Date: 05/19/2007 15:07:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[fyi_links_top] AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100;

How To Get a List of Columns in a View using the "sp_help" Stored Procedure?

2013-09-09T23:55:00.000-07:00

Another way to get a list of columns from a view is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the view information, the column information, the identity column, the row GUID column. The tutorial exercise belwo shows you what you will get with sp_help:

EXEC SP_HELP fyi_links_top;
GO
Name           Owner  Type  Created_datetime
-------------- ------ ----- -----------------------
fyi_links_top  dbo    view  2007-05-19 13:43:46.983
 
Column_name  Type     Computed  Length  Prec  Scale Nullable
------------ -------- --------- ------- ----- ----- --------
id           int      no        4       10    0     yes     
counts       int      no        4       10    0     yes     
url          varchar  no        80                  no      
 
Identity                   
---------------------------
No identity column defined.

RowGuidCol
-----------------------------
No rowguidcol column defined.

How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?

2013-09-09T23:54:00.002-07:00

If you have an existing table, but you don't remember what are the columns defined in the view, you can use the "sp_columns" stored procedure to get a list of all columns of the specified view. The following tutorial script shows you a good example:

EXEC SP_COLUMNS fyi_links_top;
GO
TABLE_OWNER TABLE_NAME    COLUMN_NAME TYPE_NAME LENGTH
----------- ------------- ----------- --------- ------
dbo         fyi_links_top id          int       4     
dbo         fyi_links_top counts      int       4     
dbo         fyi_links_top url         varchar   80    

(3 row(s) affected)

The "sp_columns" stored procedure returns a long list of properties for each column of the specified view.

How To Get a List of Columns in a View using "sys.columns"?

2013-09-09T23:53:00.000-07:00

If you have an existing view, but you don't remember what are the columns defined in the view, you can use the "sys.columns" system view to get a list of all columns of all views in the current database.

In order to a list of columns of a single view, you need to join sys.columns and sys.views as shown in the tutorial example below:

SELECT * FROM sys.columns c, sys.views v
   WHERE c.object_id = v.object_id
   AND t.name = 'fyi_links_top'
GO
object_id   name    column_id  user_type_id max_length
----------- ------- ---------- ------------ ----------
1205579333  id      1          56           4         
1205579333  counts  2          56           4         
1205579333  url     3          167          80

You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.

How To Drop Existing Views from a Database?

2013-09-09T23:32:00.001-07:00

If you don't need a specific view any more, you can use the DROP VIEW statement to delete it from the database. The following tutorial exercise shows you how to delete the view, fyi_links_view:

USE FyiCenterData;
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_view  1189579276  1          V    VIEW      
fyi_links_top   1205579333  1          V    VIEW      
(2 row(s) affected)

DROP VIEW fyi_links_view; 
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_top   1205579333  1          V    VIEW      
(1 row(s) affected)

How To See Existing Views?

2013-09-09T23:31:00.002-07:00

If you want to know how many views you have created in a database, you use the system view called sys.views to get a list of views defined in the current database. The tutorial exercise shows you how many views in database FyiCenterData:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links;
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_view  1189579276  1          V    VIEW      
fyi_links_top   1205579333  1          V    VIEW      
(2 row(s) affected)

How To Create a View on an Existing Table?

2013-09-09T23:26:00.004-07:00

If you want to a view on an existing table, you can use the CREATE VIEW statement in a simple syntax:

CREATE VIEW view_name AS SELECT ...

The tutorial exercise below shows you how to create a view to represent sub set of data stored in fyi_links table:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_top AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100;
GO

SELECT TOP 10 * FROM fyi_link_top;
GO
id     counts  url
------ ------- -----------------------------------------
7600   237946  eyfndw jdt  lee ztejeyx l q  jdh k 
19437  222337  eypx u x
55924  1877    eyq ntohxe i rtnlu riwaskzp  cucoa dva c 
63742  121330  ezdaeh mmgmo vaai  meytbjjv f jixfsdjw pw
92455  945262  ezlmyenrw   dyeb
36391  41386   f 
87433  977726  f
7180   559314  f   kqbqlej   s xixuurcgg lh r dqqvqsstxw
2252   702033  f  bkh  jy   sqrkttuoarxmfp idqyhyy tme d
1228   146283  f  m asukh

What Are Views?

2013-09-09T23:25:00.001-07:00

A view is a database object that represents the data in one or more tables in the same structure as a separate table. Here are some basic rules about views:

Tables store real data.
Views do not store real data.
Views must have underlying tables to provide data.
Each view is based on a single SELECT statement to control what data to collect from tables, and how data should be represented.
View's columns can be mapped directly to columns in underlying tables.
View's columns can be created expressions based multiple columns in underlying tables.
Views can be used in same way as tables in queries.

Enhance SPRO to add customized views

2013-09-05T06:19:00.001-07:00

Objective:

Enhance SPRO to add customized views to SPRO and make documentation of the relevant Z customization.

Use in Projects:

The customized entries and documentation will be with in SPRO and understandable to everyone.

Entire Customization including ‘Z’ objects comes under one tree(SPRO)
Reduces the time to become familiar with the system & processes.
Easy to maintain.
KT process becomes easier.

Procedure:

To add this maintenance in SPRO, we need to enhance SPRO.

Run the TA : S_IMG_EXTENSION from the easy access:

EDIT IMG STRUCTURE Screen:

Select the relevant IMG Structure from the F4 Button.

In this case, the IMG structure is Sales.

Press F4 on Enhancement ID, It shows all the existing Enhancement ID’s. We can create our own enhancement ID.

Cilck ‘ SAVE’ Button.

Object directory entry screen is displayed. Give the package name. Workbench request gets created.

Once saved, Click on continue button

Now we can see the ZALU as enhancement ID Original List:

Double click ‘ZALU’ or set the cursor on ‘ZLAU’ Click on Continue.

“ZALU” ‘ALU Enhancement ID’ gets filled at Enhancement ID.

Now click on button Enhance Structure on the application bar.

Change IMG screen is displayed.

As we have selected IMG structure as SALES, we get all the sub nodes under sales.

Now we are going to add a node as a sub node Under Sales:

Here 4 application bar buttons:

The above two buttons describes at which level (Parent node or Child node) to add the node.

We are going to add ‘ALU Individual Customizing’ as Sub node to Sales,

So set the cursor on Sales and click on

Pop up comes with asking for Node text: ‘Node text is given as ‘ALU Individual Customizing’

Click Continue.

Now the structure displayed like below:

Now set the cursor on ALU Individual Customizing and click on the button Activity:

Following screen is displayed.

Give IMG ID (Any Name) and document name and click on create:

Add the documentation:

Save and click on ‘BACK’ button. Will be directed to initial screen.

Click on SAVE button.

Workbench request gets created:

Now Click on the Attributes TAB:

Following screen is displayed:

Here is the list of ASAP Roadmap ID:

Selected 201 as ASAP Roadmap ID (Select the appropriate Roadmap ID, where this customization comes)

Mandatory/ Optional as “Optional”,

Critical/ Non_Critical as “Non Critical”.

Coming to the components on right side:

Press F4 on components, We get a list. Add the appropriate component.

Click on the SAVE button at the bottom:

Gets saved under workbench Request:

Now click on the Tab: Maintain Objects:

Give the maintain Object ID and Name:

Assign the customizing Object Here:

In our example, the table name is ZEMPLY and the transaction code for the table maintenance:

Click on the ‘SAVE’ Button:

Saved under workbench request and click on Back Button.

We are directed to Initial IMG Screen. And screen shows as below:

Click on SAVE button on the Top:

Changes logged under workbench Request:

Now Run the transaction SPRO:

Now we can ZUNBU in the path

The documentation shown as below:

When executed, it shows the Maintenance Screen,

Working with screen variants

2013-09-05T06:17:00.001-07:00

Screen Variants

Screen variants allow you to simplify screen editing by:

Providing default values on the screen fields
Hiding and changing the ready for input status of fields
Hiding and changing the attributes of table control columns

A screen variant contains field values and attribute for exactly one screen. A screen variant may be assigned to multiple transaction variants. Screen variants are always cross-client; they may, however, be assigned to a client-specific transaction. They can also be called at runtime by a program. The different possibilities for calling screen variants guarantee great flexibility of use.

Normally when we create sales order, we can change the “ship to party” which comes based on sold to party, now for the demo purpose, we would disable “ship to party” so that the end-user cant make changes to it.

Transaction code: SHD0.

Enter the transaction code, for which you want the field to be disabled, give a transaction variant name, this transaction variant will be assigned to order type later.

Here in this case transaction code is ‘VA01’.

Click on Create button

Enter the order type and press enter.

If you want to hide a field in sales order, then in this screen you can make necessary changes, according to our scenario we are just going to make changes only to ship to party so do not make any changes in the screen, Click on enter

Click on enter

Click on enter until you get the below screen where you are going to make modifications to ship to party field

Check the ship to party checkbox in output only (means display purpose) as shown below.

After checking the checkbox, just click on EXIT and SAVE button shown in the below screen shot.

Enter the short description

Click on save

Assign it to a package.

Assign it to a transport request, and click on enter, you may get the same package and transport request, just click on save and go on until you encounter with the last screen.

Now this is the final screen.

Now go to transaction VOV8.

After selecting the order type, either double click or click on details button, it will take you to the next screen where you have to maintain the variant name.

Now when you try to create a sales order using the same document type then you can see that the ship to party field is in display mode.