Re: join query

Former Member — Wed, 13 Jun 2007 06:00:12 GMT

see the sample code for fetching data from HRP1001 and do accordingly by changing the fields

report zporgr0040

line-size 108

line-count 60(1)

no standard page heading

message-id zndc.

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

Database Tables

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

tables: hrp1001, " HR Master Record (Orgn. Assignment)

pa0002, " Personal Data

t528t, " Position Texts

t527x, " Org Unit Text

t528b, " Positions

csks, " Cost Center

cskt. " Cost Center Texts

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

Declaration of Internal Tables

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

Internal Table for Orgn Object Data HRP1000

data: begin of obj_tab occurs 0,

otype like hrp1000-otype, " Object Type

objid like hrp1000-objid, " Object ID

plvar like hrp1000-plvar, " Plan Version

istat like hrp1000-istat, " Planning Status

begda like hrp1000-begda, " Begin date

endda like hrp1000-endda, " End date

end of obj_tab.

Internal Table for Active Employees data PA0000

data: begin of stat_tab occurs 0,

pernr like pa0000-pernr, " Object Type

begda like pa0000-begda, " Begin date

endda like pa0000-endda, " End date

stat2 like pa0000-stat2, " Employment Status

end of stat_tab.

Internal Table for Actual Orgn Assignment Data PA0001

data: begin of act_tab occurs 0,

pernr like pa0001-pernr, " Object Type

begda like pa0001-begda, " Begin date

endda like pa0001-endda, " End date

kostl like pa0001-kostl, " Cost Center

orgeh like pa0001-orgeh, " Org Unit

plans like pa0001-plans, " Position

natio like pa0002-natio, " Nationality

end of act_tab.

Internal Table for Orgn Data HRP1001

data: begin of org_tab occurs 0,

otype like hrp1001-otype, " Object Type

objid like hrp1001-objid, " Object ID

plvar like hrp1001-plvar, " Plan Version

istat like hrp1001-istat, " Planning Status

begda like hrp1001-begda, " Begin date

endda like hrp1001-endda, " End date

subty like hrp1001-subty, " Sub Type

sclas like hrp1001-sclas, " Type of Related Object

sobid like hrp1001-sobid, " Id of Related Object

kostl like pa0001-kostl, " Cost Center

plstx like t528t-plstx, " Position Text

orgtx like t527x-orgtx, " Org Unit Text

end of org_tab.

Internal Table for Position Calculations

data: begin of org1_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

objid like hrp1001-objid, " Object ID

end of org1_tab.

Internal Table for Position Calculations

data: begin of org2_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count type i, " No of Positions

end of org2_tab.

Internal Table for Actual Manpower Calculations

data: begin of act1_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

plans like pa0001-plans, " Position

end of act1_tab.

Internal Table for Actual Manpower Calculations(UAE)

data: begin of act2_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

end of act2_tab.

Internal Table for Actual Manpower Calculations(Others)

data: begin of act3_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

natio like pa0002-natio, " Nationality

end of act3_tab.

Internal Table for Actual Manpower Calculations(UAE)

data: begin of act4_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count1 type i, " No of Positions(UAE)

end of act4_tab.

Internal Table for Actual Manpower Calculations(OTHERS)

data: begin of act5_tab occurs 0,

kostl like pa0001-kostl, " Cost Center

count2 type i, " No of Positions(Others)

end of act5_tab.

Internal Table for Vaccancies Data

data: begin of vac_tab occurs 0,

plvar like hrp1007-plvar, " Plan Version

otype like hrp1007-otype, " Object Type

objid like hrp1007-objid, " Object ID

istat like hrp1007-istat, " Planning Status

begda like hrp1007-begda, " Begin date

endda like hrp1007-endda, " End date

vacan like hrp1007-vacan, " Vacancy Indicator

status like hrp1007-status, " Status of Vacancy

end of vac_tab.

Internal Table to store the Report Output data

data : begin of rep_tab occurs 0 ,

kostl(10) type n, " Cost Cente

plans like pa0001-plans, " Position

vacan like hrp1007-vacan, " Vacancy Indicator

status like hrp1007-status, " Status of Vacancy

plstx like t528t-plstx, " Position Text

orgtx like t527x-orgtx, " Org Unit Text

end of rep_tab.

Internal Table to store the Report Output data

data : begin of rep1_tab occurs 0 ,

kostl(10) type n, " Cost Cente

cnt type i, " No of Vacancies

end of rep1_tab.

Internal Table to store the Report Output data

data : begin of rep2_tab occurs 0 ,

kostl(10) type n, " Cost Cente

app_no type i, " Approved

nat_no type i, " National

exp_no type i, " Expatriate

tot_no type i, " Total

vac_no type i, " Vacancies

end of rep2_tab.

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

Declaration of Variables

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

data: v_sobid like hrp1001-sobid, " Object Id

v_sobid1 like hrp1001-sobid, " Object Id

v_sobid2 like hrp1001-sobid, " Object Id

v_sclas like hrp1001-sclas, " Related Obj Type

v_subty like hrp1001-subty, " Related Object Id

v_otype like hrp1007-otype, " Object Type

v_str(9) type c, " Text
v_str1(10) type c, " Text

v_cnt like sy-tabix. " Table Index

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

Declaration of Constants

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

constants : c_x type c value 'X', " Flag

c_stat1 like pa0000-stat2 value '3', " Emp Status

c_plan like hrp1001-plvar value '01', " Plan Version

c_name like hrp1000-uname value 'SAP', " Uname

c_uae like pa0002-natio value 'AE', " Country

c_type like hrp1007-otype value 'O', " Object Type

c_otype like hrp1007-otype value 'S', " Object Type

c_stat like hrp1007-status value '0', " Status

c_pstat like hrp1007-istat value '1', " Plang Status

c_sclas like hrp1001-sclas value 'K', " Rel. Obj Type

c_subty like hrp1001-subty value 'A002', " Related ObjId

c_subty2 like hrp1001-subty value 'A003', " Related ObjId

c_sclas1 like hrp1001-sclas value 'O', " Rel.Obj Type

c_subty1 like hrp1001-subty value 'A011', " Rel.Object Id

c_kokrs like coej-kokrs value '1000'. " Controlling Area

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

Selection Screen

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

selection-screen begin of block b1 with frame title text-001.

parameters :

p_date like pa0001-begda obligatory default sy-datum. " Date

select-options :

s_kostl for csks-kostl, " Cost Center

s_plans for t528b-plans. " Position ID

selection-screen end of block b1.

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

At selection-screen

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

at selection-screen.

Validate the Selection Screen fields

perform validate_screen.

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

Start-of-Selection

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

start-of-selection.

Get the Orgn data from database Table HRP1001

perform get_org_data.

Get the Actual Manpower from PA0001

perform get_act_data.

Get the Vaccancies data from database Table HRP1007

perform get_vac_data.

Append the data into final Internal Table

perform append_data.

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

Top-of-page

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

top-of-page.

Write the Report and Column Headings

perform top_of_page.

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

End-of-Page

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

end-of-page.

write /1(108) sy-uline.

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

End-of-Selection

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

end-of-selection.

Display the Output Report.

perform display_report.

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

Form-Routines

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

&----

*& Form validate_screen

&----

Validation of selection Screen fields

----

form validate_screen.

Validation of Position ID

clear t528b.

if not s_plans[] is initial.

select single plans

into t528b-plans

from t528b

where plans in s_plans.

if sy-subrc <> 0.

message e999 with 'Invalid Position ID'(009).

endif.

Validation of Cost Center

clear csks.

if not s_kostl[] is initial.

select single kostl

into csks-kostl

from csks

where kostl in s_kostl.

if sy-subrc <> 0.

message e999 with 'Invalid Cost Center'(008).

endif.

endform. "validate_screen

&----

*& Form get_org_data

&----

Get the Orgn Data from Database Table HRP1001

*----

form get_org_data.

v_str = 'Developee'(002).
v_str1 = 'Deployment'(003).

Select All the Positions from HRP1000

select otype " Object Type

objid " Object ID

plvar " Plan Version

istat " Planning Status

begda " Begin date

endda " End date

into table obj_tab

from hrp1000

where otype = c_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

objid in s_plans and

uname ne c_name.

sort obj_tab by otype objid.

Get the Orgn and Cost Center for all the above Positions

if not obj_tab[] is initial.

select otype " Object Type

objid " Object ID

plvar " Plan Version

istat " Planning Status

begda " Begin date

endda " End date

subty " Sub Type

sclas " Type of Related Object

sobid " Id of Related Object

into table org_tab

from hrp1001

for all entries in obj_tab

where otype = c_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

sclas eq c_sclas1 and

subty eq c_subty2 and

objid eq obj_tab-objid.

endif.

sort org_tab by otype objid.

Get the Cost Center

loop at org_tab.

v_otype = c_otype. " S

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

org_tab-kostl = hrp1001-sobid+0(10).

else.

v_otype = c_otype. " S

v_sclas = c_sclas1. " O

v_subty = c_subty2. " A003

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid1 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid1.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid2 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid2.

perform get_org using v_otype v_sclas v_subty

v_sobid.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

else.

v_otype = c_otype. " S

v_sclas = c_otype. " S

v_subty = c_subty. " A002

v_sobid = org_tab-objid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_otype. " S

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid1 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_otype. " S

v_sclas = c_sclas1. " O

v_subty = c_subty2. " A003

v_sobid = v_sobid1.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc = 0.

v_otype = c_type. " O

v_sclas = c_sclas. " K

v_subty = c_subty1. " A011

v_sobid2 = v_sobid.

perform get_org using v_otype v_sclas v_subty

v_sobid.

if sy-subrc <> 0.

v_otype = c_type. " O

v_sclas = c_sclas1. " O

v_subty = c_subty. " A002

v_sobid = v_sobid2.

perform get_org using v_otype v_sclas v_subty

v_sobid.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

else.

org_tab-kostl = hrp1001-sobid+0(10).

endif.

Get the Position Text

clear t528t.

select single plstx from t528t into t528t-plstx

where sprsl = sy-langu and

otype = c_otype and

plans = org_tab-objid.

org_tab-plstx = t528t-plstx.

Get the Org Unit Text

clear t527x.

select single orgtx from t527x into t527x-orgtx

where sprsl = sy-langu and

orgeh = org_tab-sobid+0(8).

org_tab-orgtx = t527x-orgtx.

modify org_tab index sy-tabix.

clear v_sobid.

endloop.

sort org_tab by otype objid.

Calculate the Approved No of Positions

loop at org_tab.

if org_tab-kostl in s_kostl.

move-corresponding org_tab to org1_tab.

append org1_tab.

clear org1_tab.

endif.

endloop.

sort org1_tab by kostl objid.

clear v_cnt.

loop at org1_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

org2_tab-kostl = org1_tab-kostl.

org2_tab-count = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append org2_tab.

clear:org2_tab.

endat.

endloop.

sort org2_tab by kostl.

endform. "get_org_data

&----

*& Form get_act_data

&----

Get the Actual Manpower Data from Database Table PA0001

*----

form get_act_data.

clear : stat_tab, act_tab.

refresh: stat_tab, act_tab.

Get all the Active Employees

select pernr " Object Type

begda " Begin date

endda " End date

stat2 " Emp Status

into table stat_tab

from pa0000

where stat2 eq c_stat1 and

begda le p_date and

endda ge p_date.

sort act_tab by pernr.

if not stat_tab[] is initial.

select pernr " Object Type

begda " Begin date

endda " End date

kostl " Cost Center

orgeh " Org Unit

plans " Position

into table act_tab

from pa0001

for all entries in stat_tab

where pernr eq stat_tab-pernr and

kostl in s_kostl and

plans in s_plans and

persg eq '1' and

begda le p_date and

endda ge p_date.

endif.

sort act_tab by pernr.

loop at act_tab.

move-corresponding act_tab to act1_tab.

clear pa0002-natio.

select single natio into pa0002-natio from pa0002

where pernr = act_tab-pernr.

if sy-subrc = 0.

act1_tab-natio = pa0002-natio.

endif.

append act1_tab.

clear act1_tab.

endloop.

sort act1_tab by kostl natio.

Calculate the Local and Expatriate Manpower

loop at act1_tab.

if act1_tab-natio = c_uae.

act2_tab-kostl = act1_tab-kostl.

act2_tab-natio = act1_tab-natio.

else.

act3_tab-kostl = act1_tab-kostl.

act3_tab-natio = act1_tab-natio.

endif.

append: act2_tab, act3_tab.

clear : act2_tab,act3_tab.

endloop.

sort act2_tab by kostl.

sort act3_tab by kostl.

Actual UAE Manpower

clear v_cnt.

loop at act2_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

act4_tab-kostl = act2_tab-kostl.

act4_tab-count1 = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append act4_tab.

clear:act4_tab.

endat.

endloop.

sort act4_tab by kostl.

Other Countries

clear v_cnt.

loop at act3_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

act5_tab-kostl = act3_tab-kostl.

act5_tab-count2 = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append act5_tab.

clear:act5_tab.

endat.

endloop.

sort act5_tab by kostl.

endform. "get_act_data

&----

*& Form get_org

&----

Get the Cost Center from Parent Org Id

----

form get_org using p_v_otype p_v_sclas p_v_subty p_v_sobid.

clear hrp1001.

select single * from hrp1001

where otype = p_v_otype and

plvar = c_plan and

istat = c_pstat and

begda le p_date and

endda ge p_date and

sclas eq p_v_sclas and

subty eq p_v_subty and

objid eq p_v_sobid.

v_sobid = hrp1001-sobid+0(8).

endform. " get_org

&----

*& Form get_vac_data

&----

Get the Vaccancies Data from Database Table HRP1007

----

form get_vac_data.

if not org_tab[] is initial.

select plvar " Plan Version

otype " Object Type

objid " Object ID

istat " Planning Status

begda " Begin date

endda " End date

vacan " Vacancy Indicator

status " Status of Vacancy

into table vac_tab

from hrp1007

for all entries in org_tab

where plvar = c_plan and

otype = c_otype and

istat eq c_pstat and

objid = org_tab-objid and

begda le p_date and

endda ge p_date and

vacan eq c_x and

status eq c_stat.

endif.

sort vac_tab by plvar otype objid.

Get the HR data into the rep_tab Internal Table

loop at vac_tab.

rep_tab-plans = vac_tab-objid.

rep_tab-vacan = vac_tab-vacan.

rep_tab-status = vac_tab-status.

read table org_tab with key objid = vac_tab-objid.

if sy-subrc = 0.

rep_tab-kostl = org_tab-kostl.

rep_tab-plstx = org_tab-plstx.

rep_tab-orgtx = org_tab-orgtx.

endif.

append rep_tab.

clear rep_tab.

endloop.

loop at rep_tab.

if not rep_tab-kostl in s_kostl.

delete rep_tab index sy-tabix.

endif.

endloop.

sort rep_tab by kostl plans.

delete rep_tab where plstx cs v_str.
delete rep_tab where orgtx cs v_str1.

Calculate the Count of Vacancies in each Division

clear v_cnt.

loop at rep_tab.

at end of kostl.

sy-tabix = sy-tabix - v_cnt.

rep1_tab-kostl = rep_tab-kostl.

rep1_tab-cnt = sy-tabix.

v_cnt = v_cnt + sy-tabix.

append rep1_tab.

clear:rep1_tab.

endat.

endloop.

sort rep1_tab by kostl.

endform. "get_vac_data

&----

*& Form append_data

&----

Append the data into final Internal Table

----

form append_data.

loop at org2_tab.

rep2_tab-kostl = org2_tab-kostl.

rep2_tab-app_no = org2_tab-count.

read table act4_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-nat_no = act4_tab-count1.

endif.

read table act5_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-exp_no = act5_tab-count2.

endif.

read table rep1_tab with key kostl = org2_tab-kostl

binary search.

if sy-subrc = 0.

rep2_tab-vac_no = rep1_tab-cnt.

endif.

rep2_tab-tot_no = rep2_tab-nat_no + rep2_tab-exp_no.

append rep2_tab.

clear rep2_tab.

endloop.

sort rep2_tab by kostl.

endform. "append_data

&----

*& Form top_of_page

&----

Write the Report and Column Headings

----

form top_of_page.

skip.

format color col_heading on.

write: /1(108) 'NATIONAL DRILLING COMPANY'(010) centered,

/1(108) 'Summary of Manning Level Report'(011) centered.

format color off.

skip.

write: /2 'Key Date :'(013), p_date,

75 'Report Run Date:'(041), sy-datum.

if not s_kostl[] is initial.

if s_kostl-high is initial.

write: /2 'Cost Center :'(023), s_kostl-low,

75 'Time :'(042), sy-uzeit.

else.

write: /2 'Cost Center From:'(024), s_kostl-low+7(3),

'To'(022), s_kostl-high,

75 'Time :'(042), sy-uzeit.

endif.

else.

write: /75 'Time :'(042), sy-uzeit.

endif.

if not s_plans[] is initial.

if s_plans-high is initial.

write: /2 'Position Id :'(019), s_plans-low,

75 'User :'(043), sy-uname.

else.

write: /2 'Position Id From:'(021), s_plans-low,

'To'(022), s_plans-high,

75 'User :'(043), sy-uname.

endif.

else.

write: /75 'User :'(043), sy-uname.

endif.

write: /75 'Page No :'(044), sy-pagno.

skip.

write /1(108) sy-uline.

format color col_heading.

write:/1 sy-vline,

12 sy-vline, 53 sy-vline,

64 sy-vline, 65(32) 'Actual'(018) centered,

97 sy-vline,108 sy-vline.

write:/1 sy-vline, 2(10) 'CostCenter'(014),

12 sy-vline, 13(40) 'Description'(026) centered,

53 sy-vline, 54(10) 'Approved'(025) centered,

64 sy-vline, 65(32) sy-uline,

97 sy-vline, 98(10) 'Vacancies'(017),

108 sy-vline.

write:/1 sy-vline,

12 sy-vline, 53 sy-vline,

64 sy-vline, 65(10) 'National'(015) centered,

75 sy-vline, 76(10) 'Expatriate'(020) centered,

86 sy-vline, 87(10) 'Total'(016) centered,

97 sy-vline,108 sy-vline.

format color off.

write /1(108) sy-uline.

endform. "top_of_page

&----

*& Form Display_report

&----

Write the Report Output

----

form display_report.

if rep2_tab[] is initial.

message i999 with

'No Data found for the entered Selection'(035).

else.

loop at rep2_tab.

Authorization Check for the Orgn and Cost Centers

perform auth_check_org.

if sy-subrc = 0.

clear cskt.

select single ltext into cskt-ltext from cskt

where spras = sy-langu and

kokrs = c_kokrs and

kostl = rep2_tab-kostl.

format color col_normal.

write: /1 sy-vline, 2(10) rep2_tab-kostl+7(3),

12 sy-vline, 13(40) cskt-ltext,

53 sy-vline, 54(10) rep2_tab-app_no,

64 sy-vline, 65(10) rep2_tab-nat_no,

75 sy-vline, 76(10) rep2_tab-exp_no,

86 sy-vline, 87(10) rep2_tab-tot_no,

97 sy-vline, 98(10) rep2_tab-vac_no,

108 sy-vline.

format color off.

at last.

sum.

format color 3.

write /1(108) sy-uline.

write: /1 sy-vline, 2(10) 'Total'(027),

12 sy-vline,

53 sy-vline, 54(10) rep2_tab-app_no,

64 sy-vline, 65(10) rep2_tab-nat_no,

75 sy-vline, 76(10) rep2_tab-exp_no,

86 sy-vline, 87(10) rep2_tab-tot_no,

97 sy-vline, 98(10) rep2_tab-vac_no,

108 sy-vline.

format color off.

endat.

endif.

endloop.

endif.

write /1(108) sy-uline.

endform. " Display_report

&----

*& Form auth_check_org

&----

Authorization Check for the Orgn and Cost Center

*----

form auth_check_org.

authority-check object 'ZNDCHROM'

id 'PLVAR' dummy

id 'OTYPE' dummy

id 'KOSTL' field rep2_tab-kostl.

endform. "auth_check_org

Reward points for useful Answers

Regards

Anji

topic join query in Application Development and Automation Discussions

join query

Re: join query

Re: join query