Skip to Content
0
Former Member
Dec 07, 2006 at 10:04 AM

SAP PS Module Report, Help needed for the Right Logic.

405 Views

Hi,

I have to develop a PS module Report . This spec of this report is when you enter , project # , month , date , proj type it should display all the projects and only the billing levels with different fiels like ITD cost, ITD sales, ytd cost, sales, awards etc etc.

Right now Iam getting the O/P : in the following manner, There are many fields I am just showing the desired ones which have to be changed.

Proj, Title, award, ITDsales, ITD cost , ytd sales, ytd costs,

14148, New prj 123 -- --- -


-


14148-B01, bill1 -


234 -


456 -


14148-B02, bill2 -


789 -


123 -


14148.001 wbs1 -


-


123 -


456

14148.001 wbs2 -


-


567 -


890

But for me the desired output must be only for project and the billing levels. It shouldn’t be for WBS elements xxxx.001 and xxxx.002.

Desired output:

Proj, Title, award, ITDsales, ITD cost , ytd sales, ytd costs,

14148, New prj 123 -- 690 -


1346

14148-B01, bill1 -


234 -


456 -


14148-B02, bill2 -


789 -


123 -


The ITD costs and YTD costs must be added 123 + 567 = 690 and

456+890 = 1346.

And these values must come on the project level.

P.S: I can restrict the values for wbs elements , means I can filter them at PRPS table by giving prps-stufe LE ‘2’. Then I wont get the wbs level, But I want the values of these to be added up and then show it on the project level.

Please go through this code and let me know where exactly I need to put the right logic to work in the desired manner.

Thanks in advance

Dan

  • Code Below************************

[code]TABLES: PROJ, "Project Definition

PRPS, "WBS Elements

prhi,

CEPC, " Profit Center w/ Group Head info

BPGE, "Plans (12, 13, etc.)

COSP, "Primary Cost Element (Bill Sales, DPC)

COSS, "Secondary Cost Element (Shrd Sls,Sal,etc)

COSB, "Results Analysis Cost Elements (Unbil Sls)

COOI, "Commitments management: Line items

JEST, "Project Status

TCJ04, "Person Responsible for Project

zpclasses, "Project classes.

  • New Added

CRCO, " Assignment of Work Center to Cost Center

CRHD. " Work Center Header

  • Record Layout of extracted data

TYPES: BEGIN OF EXDEF, "Data definition of extract data

X_BU(2) TYPE C, "Business Unit

X_CNTR(10) TYPE C, " Profit Center

X_PROJ(24) TYPE C, "Project def (Prj)

X_CREP(30) TYPE C, "Contract Rep.

X_BDREP(25) TYPE C, "BD Rep. name

X_GRPH(15) TYPE C, "Tech Rep employee name

X_ACTREP(15) TYPE C, "Acct Rep employee name

X_PMGR(15) TYPE C, "PM employee name

X_TTLE(35) TYPE C, "Title

X_CLNT(20) TYPE C, "Client

X_POCN(20) TYPE C, "Prime Contract Number

X_TYPE(13) TYPE C, "Order Type Sarnoff

X_OPEN(10) TYPE C, "Open Date

X_CLOSE(10) TYPE C, "Close Date

X_CGT(4) TYPE C, "CO, GV, TC

X_DISTR(2) TYPE C, "Distribution Channel

X_CONTR(6) TYPE C, "Percentage contribution

X_AWRD(14) TYPE C, "Award (Plan 11)

X_P12R(14) TYPE C, "Auth Revenue (Plan 12 @ Rev)

X_P12C(14) TYPE C, "Auth Cost (Plan 12 @ Cost)

X_P13R(14) TYPE C, "Auth Revenue (Plan 13 @ Rev)

X_P13C(14) TYPE C, "Auth Cost (Plan 13 @ Cost)

X_ITDS(14) TYPE C, "ITD Sales

X_ITDC(14) TYPE C, "ITD Costs

X_YTDS(14) TYPE C, "YTD Sales

X_YTDC(14) TYPE C, "YTD Costs

X_MTHS(14) TYPE C, "Current Month Sales

X_MTHC(14) TYPE C, "Current Month Costs

X_ITDB(14) TYPE C, "ITD Billed Sales

X_YTDB(14) TYPE C, "YTD Billed Sales

X_MTHB(14) TYPE C, "Current Month Billed Sales

X_COMM(14) TYPE C, "Commitments

X_STAT(6) TYPE C, "Active or Inactive (REL, TECO, or CLSD)

X_ZPROJCLASS(12),

X_MTHIC(14) TYPE C, "Current Month Incremental Costs

X_YTDIC(14) TYPE C, "YTD Incremental Costs

X_ITDIC(14) TYPE C, "ITD Incremental Costs

X_ZSCURVE(10) TYPE C, "S-CURVE Status

  • New Added

X_CM_COST(20) TYPE C,

X_AM_COST(20) TYPE C,

X_TL_COST(20) TYPE C,

X_KP_COST(20) TYPE C,

X_PM_COST(20) TYPE C,

END OF EXDEF.

  • Data declarations

FIELD-SYMBOLS <MNT> .

DATA: ETAB TYPE EXDEF OCCURS 100 WITH HEADER LINE.

DATA: X1_PM_COST(20) TYPE C,

X1_TL_COST(20) TYPE C,

X1_AM_COST(20) TYPE C,

X1_CM_COST(20) TYPE C,

X1_KP_COST(20) TYPE C.

DATA: KOKRS_FIXED LIKE PROJ-VKOKR VALUE 'DSRC', "Controlling Area

TOTAL_AWARD LIKE BPGE-WTGES,

TOTAL_AUTHR12 LIKE BPGE-WTGES,

TOTAL_AUTHC12 LIKE BPGE-WTGES,

TOTAL_AUTHR13 LIKE BPGE-WTGES,

TOTAL_AUTHC13 LIKE BPGE-WTGES,

TOTAL_ITDSALES LIKE BPGE-WTGES,

TOTAL_ITDCOSTS LIKE BPGE-WTGES,

TOTAL_ITDICOSTS LIKE BPGE-WTGES,

TOTAL_YTDSALES LIKE BPGE-WTGES,

TOTAL_YTDCOSTS LIKE BPGE-WTGES,

TOTAL_YTDICOSTS LIKE BPGE-WTGES,

TOTAL_MTHSALES LIKE BPGE-WTGES,

TOTAL_MTHCOSTS LIKE BPGE-WTGES,

TOTAL_MTHICOSTS LIKE BPGE-WTGES,

TOTAL_ITDBILLED LIKE BPGE-WTGES,

TOTAL_YTDBILLED LIKE BPGE-WTGES,

TOTAL_MTHBILLED LIKE BPGE-WTGES,

TOTAL_COMMITMENT LIKE COOI-WTGBTR,

HOLD_PRART LIKE PRPS-PRART,

COUNT_PROJECTS TYPE I,

MNT_INDX(20) TYPE C,

HLD_INDX(3) TYPE N,

YYYY_MM(10) TYPE C,

TEMP-DATE(8) TYPE C.

  • Selection Screen

SELECTION-SCREEN SKIP 3.

SELECTION-SCREEN BEGIN OF BLOCK XEXTRPM WITH FRAME TITLE TEXT-001.

SELECTION-SCREEN SKIP 2.

PARAMETERS MONTH(2) TYPE N OBLIGATORY.

PARAMETERS YEAR(4) TYPE N OBLIGATORY.

SELECT-OPTIONS: EXTRPRJ FOR PROJ-PSPID. " Range of Projects

SELECT-OPTIONS: EXTRTYP FOR PRPS-PRART. "Project Types (CO,GV,etc.*)

SELECT-OPTIONS: EXTRPCTR FOR PROJ-PRCTR. " Range of Profit centers

SELECTION-SCREEN SKIP 2.

PARAMETERS: OPT1 RADIOBUTTON GROUP OPTN,

OPT2 RADIOBUTTON GROUP OPTN DEFAULT 'X'.

select-options : s_pc for proj-zprojclass. "project class

SELECTION-SCREEN END OF BLOCK XEXTRPM.

  • Select Tables

START-OF-SELECTION.

  • write: / 'Sales data (Unshared) extracted on: ', sy-datum.

  • uline.

CLEAR: COUNT_PROJECTS,

TOTAL_AWARD,

TOTAL_AUTHR12,

TOTAL_AUTHC12,

TOTAL_AUTHR13,

TOTAL_AUTHC13,

TOTAL_ITDSALES,

TOTAL_ITDCOSTS,

TOTAL_YTDSALES,

TOTAL_YTDCOSTS,

TOTAL_MTHSALES,

TOTAL_MTHCOSTS,

TOTAL_ITDBILLED,

TOTAL_YTDBILLED,

TOTAL_MTHBILLED,

TOTAL_COMMITMENT,

ETAB.

  • PROJ Table Project Definition

SELECT * FROM PROJ "Project Definition

WHERE PSPID IN EXTRPRJ AND " Range of Project Numbers

PRCTR IN EXTRPCTR and " Range of Profit center

zprojclass in s_pc. "project class

SELECT * FROM PRPS WHERE "WBS Elements

PRART IN EXTRTYP " Range of Proj.Types (CO,GV,TC..

and PSPHI = PROJ-PSPNR.

HOLD_PRART = ' '. "clears project type

CLEAR ETAB.

ETAB-X_PROJ = PRPS-POSID.

ETAB-X_BU = PRPS-PRCTR(2). "Business Unit

ETAB-X_PMGR = PROJ-VERNA. "Project Manager

IF PRPS-PRCTR(2) = '01'.

ETAB-X_CNTR = PRPS-PRCTR. " Profit Center

CONCATENATE SPACE PRPS-PRCTR INTO ETAB-X_CNTR SEPARATED BY SPACE.

ELSE.

ETAB-X_CNTR = PRPS-PRCTR. " Profit Center

ENDIF.

ETAB-X_TTLE = PRPS-POST1. "Title

CLEAR TEMP-DATE.

TEMP-DATE = PROJ-PLFAZ. "Project planned start date

CONCATENATE TEMP-DATE4(2) '/' TEMP-DATE6(2) '/'

TEMP-DATE+0(4) INTO ETAB-X_OPEN.

TEMP-DATE = PROJ-PLSEZ. "Project planned finish date

CONCATENATE TEMP-DATE4(2) '/' TEMP-DATE6(2) '/'

TEMP-DATE+0(4) INTO ETAB-X_CLOSE.

  • Tech Lead

  • will comment below

SELECT SINGLE VERNA FROM TCJ04 "Tech Lead name from TCJ04

INTO (ETAB-X_GRPH) WHERE VERNR = PROJ-ZGRPHD.

  • End Of Tech Lead

*

  • Account Manager

*

SELECT SINGLE VERNA FROM TCJ04 "Acct Manager from TCJ04

INTO (ETAB-X_BDREP) WHERE VERNR = PROJ-ZBUSDEV.

*

******End Of Account Manager

*

  • Key Personnel

*

SELECT SINGLE VERNA FROM TCJ04 "Account Rep name from TCJ04

INTO (ETAB-X_ACTREP) WHERE VERNR = PROJ-ZRESPDIR.

*

*

********End of Key Personnel

*

**Contract Manager

*

SELECT SINGLE VERNA FROM TCJ04 "Contract Rep name from TCJ04

INTO (ETAB-X_CREP) WHERE VERNR = PROJ-ZCNTREP.

  • End of Contract Manager

*----


  • PRPS Table WBS's attached to the Project

  • SELECT * FROM PRPS WHERE "WBS Elements

  • PRART IN EXTRTYP "Range of Proj.Types (CO,GV,TC..

  • AND PSPHI = PROJ-PSPNR.

  • order by posid stufe. "Sort by WBS & Level (needed?)

*

  • get info from level 1 of the WBS

  • IF PRPS-STUFE EQ 1. "top-level WBS element

HOLD_PRART = PRPS-PRART. "save project type for later

ETAB-X_STAT = '????'. "set ? as the default status

  • get Status for level 1

SELECT * FROM JEST "use JEST to determine Status

WHERE OBJNR = PRPS-OBJNR

AND INACT EQ SPACE. "space denotes an active status

CASE JEST-STAT. "find the system status (Ixxx)

WHEN 'I0001'.

ETAB-X_STAT = 'CRTD'. "Created

WHEN 'I0002'.

ETAB-X_STAT = 'REL'. "Released

WHEN 'I0045'.

ETAB-X_STAT = 'TECO'. "Technically Complete

WHEN 'I0046'.

ETAB-X_STAT = 'CLSD'. "Closed

ENDCASE.

ENDSELECT.

  • Select only active projects (ie; those not with TECO or CLSD)

IF OPT2 EQ 'X'

AND ( ETAB-X_STAT EQ 'TECO'

OR ETAB-X_STAT EQ 'CLSD' ).

EXIT.

ENDIF.

*comment Contract Rep and Proj man because we get them from PROJ now

  • etab-x_crep = prps-zzcont. "Contract Rep.

  • etab-x_pmgr = prps-verna. "Program Manager Name

ETAB-X_CLNT = PRPS-ZZMANDT. "Client Name

ETAB-X_CGT = PRPS-PRART. "CO, GV, TC, etc

ETAB-X_DISTR = PRPS-ZZVTWEG. "Distrib Channel (ie; GP, etc.)

ETAB-X_CONTR = PRPS-USR04. "Contribution %

ETAB-X_POCN = PRPS-ZZPOCN. "Contract Number

ETAB-X_TYPE = PRPS-ZZORDT. "Order Type Sarnoff

  • Award and Authorization (Revenue & Cost)

SELECT * FROM BPGE

WHERE OBJNR = PRPS-OBJNR

AND LEDNR = '0001' "Budget / Planning Ledger

AND TRGKZ = 'N' "Object indicator

AND WRTTP = '01' "Value Type

AND ( VERSN = '011' "011 = Award

OR VERSN = '012' "012 = Billing Authorization

OR VERSN = '013' ). "013 = RA Authorization

"010 = Funding Authorization

  • Award Plan 11

IF BPGE-VERSN = '011'. "Plan 11 = Award

CASE BPGE-VORGA.

WHEN 'KSTP'. "KSTP = Cost level

ETAB-X_AWRD = ETAB-X_AWRD + BPGE-WTGES.

ENDCASE.

ENDIF.

IF prps-stufe eq '2'.

  • Authorization (Revenue & Cost plan 12) Plan 12

IF BPGE-VERSN = '012'. "Plan 12 = RA Authorization

CASE BPGE-VORGA.

WHEN 'KSTR'. "KSTR = Revenue level

ETAB-X_P12R = ETAB-X_P12R - BPGE-WTGES.

WHEN 'KSTP'. "KSTP = Cost level

ETAB-X_P12C = ETAB-X_P12C + BPGE-WTGES.

ENDCASE.

ENDIF.

  • Authorization (Revenue & Cost plan 13) Plan 13

IF BPGE-VERSN = '013'. "Plan 13 = RA Authorization

CASE BPGE-VORGA.

WHEN 'KSTR'. "KSTR = Revenue level

ETAB-X_P13R = ETAB-X_P13R - BPGE-WTGES.

WHEN 'KSTP'. "KSTP = Cost level

ETAB-X_P13C = ETAB-X_P13C + BPGE-WTGES.

ENDCASE.

ENDIF.

endif.

ENDSELECT. "BPGE

  • ENDIF. "end of if prps-stufe eq 1. top-level WBS element

*----


  • COSP Table (External Postings) Awards

  • COSP Table (External Postings) Billed Sales

  • COSP Table (External Postings) DPC's & Legacy Load

SELECT * FROM COSP

WHERE OBJNR = PRPS-OBJNR AND

GJAHR LE YEAR

AND (

  • ( kstar = '0000400999' and "old CE for Award $

( KSTAR BETWEEN '0000400990' AND '0000400999' AND

VERSN = '011' AND "11 = Award

WRTTP = '01' ) "01 = Planned

OR ( ( KSTAR BETWEEN '0000400001' AND '0000400989' OR

KSTAR BETWEEN '0000999001' AND '0000999003' ) AND

WRTTP = '04' ) "04 = Actuals

OR ( ( KSTAR BETWEEN '0000998001' AND '0000998010' OR

KSTAR BETWEEN '0000600001' AND '0000601999' ) AND

WRTTP = '04' ) "04 = Actuals

).

*------- Awards

  • if cosp-kstar = '0000400999' and "old 400999 = Awards

IF COSP-KSTAR BETWEEN '0000400990' AND '0000400999' AND

COSP-VERSN = '011' AND "11 = Award

COSP-WRTTP = '01'. "01 = Planned

IF YEAR = COSP-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSP-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_AWRD = ETAB-X_AWRD + <MNT>.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSP-GJAHR.

ETAB-X_AWRD = ETAB-X_AWRD +

COSP-WKG001 + COSP-WKG002 +

COSP-WKG003 + COSP-WKG004 +

COSP-WKG005 + COSP-WKG006 +

COSP-WKG007 + COSP-WKG008 +

COSP-WKG009 + COSP-WKG010 +

COSP-WKG011 + COSP-WKG012 +

COSP-WKG013 + COSP-WKG014 +

COSP-WKG015 + COSP-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

*------- Billed Sales

IF ( COSP-KSTAR BETWEEN '0000400001' AND '0000400989' OR

COSP-KSTAR BETWEEN '0000999001' AND '0000999003' ) AND

COSP-WRTTP = '04'.

"0000999001 = ITD Sales

"0000999003 = ITD Billed Revenue

IF YEAR = COSP-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSP-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDB = ETAB-X_ITDB - <MNT>.

ETAB-X_YTDB = ETAB-X_YTDB - <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHB = ETAB-X_MTHB - <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSP-GJAHR.

ETAB-X_ITDB = ETAB-X_ITDB -

COSP-WKG001 - COSP-WKG002 -

COSP-WKG003 - COSP-WKG004 -

COSP-WKG005 - COSP-WKG006 -

COSP-WKG007 - COSP-WKG008 -

COSP-WKG009 - COSP-WKG010 -

COSP-WKG011 - COSP-WKG012 -

COSP-WKG013 - COSP-WKG014 -

COSP-WKG015 - COSP-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

*------- DPC's & Legacy Load

IF ( COSP-KSTAR BETWEEN '0000998001' AND '0000998010' OR

COSP-KSTAR BETWEEN '0000600001' AND '0000601999' ) AND

COSP-WRTTP = '04'.

IF YEAR = COSP-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSP-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDC = ETAB-X_ITDC + <MNT>.

ETAB-X_YTDC = ETAB-X_YTDC + <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHC = ETAB-X_MTHC + <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSP-GJAHR.

ETAB-X_ITDC = ETAB-X_ITDC +

COSP-WKG001 + COSP-WKG002 +

COSP-WKG003 + COSP-WKG004 +

COSP-WKG005 + COSP-WKG006 +

COSP-WKG007 + COSP-WKG008 +

COSP-WKG009 + COSP-WKG010 +

COSP-WKG011 + COSP-WKG012 +

COSP-WKG013 + COSP-WKG014 +

COSP-WKG015 + COSP-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

ENDSELECT.

*----


  • COOI Table (Commitments) commitments for the WBS elements

SELECT * FROM COOI

WHERE OBJNR = PRPS-OBJNR.

ETAB-X_COMM = ETAB-X_COMM + COOI-WTGBTR.

ENDSELECT.

*----


  • COSS Table (External Postings) G&A on Commitments

  • COSS Table (External Postings) Shared Billed Sales

  • COSS Table (External Postings) Shared DPC's

  • COSS Table (External Postings) Salary,ESE,OH, & G&A

SELECT * FROM COSS

WHERE

( OBJNR = PRPS-OBJNR AND

WRTTP = '22' ) "G&A on Commitments

OR

( OBJNR = PRPS-OBJNR AND

GJAHR LE YEAR AND

WRTTP = '04' AND "04 = Actuals

( "Shared Billed Sales

( KSTAR BETWEEN '0000400001' AND '0000400989' OR

KSTAR BETWEEN '0000999001' AND '0000999003' )

OR "Shared DPC's

( KSTAR BETWEEN '0000600001' AND '0000601999' )

OR "Salary,ESE,OH, & G&A

( ( KSTAR BETWEEN '0000001000' AND '0000001999' OR

KSTAR BETWEEN '0000002100' AND '0000002199' OR

KSTAR BETWEEN '0000002500' AND '0000002599' OR

KSTAR BETWEEN '0000002800' AND '0000002899' ) )

  • and parob not like 'PR%' ) "commented out on 4/19/2000

)

).

*------- G&A on Commitments (NOTE: data is always as of the RUN date)

IF COSS-WRTTP = '22'.

ETAB-X_COMM = ETAB-X_COMM +

COSS-WKG001 + COSS-WKG002 +

COSS-WKG003 + COSS-WKG004 +

COSS-WKG005 + COSS-WKG006 +

COSS-WKG007 + COSS-WKG008 +

COSS-WKG009 + COSS-WKG010 +

COSS-WKG011 + COSS-WKG012 +

COSS-WKG013 + COSS-WKG014 +

COSS-WKG015 + COSS-WKG016.

ENDIF.

*------- Shared Billed Sales

IF ( COSS-KSTAR BETWEEN '0000400001' AND '0000400989' OR

COSS-KSTAR BETWEEN '0000999001' AND '0000999003' ) AND

COSS-WRTTP = '04'.

IF YEAR = COSS-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSS-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDB = ETAB-X_ITDB - <MNT>.

ETAB-X_YTDB = ETAB-X_YTDB - <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHB = ETAB-X_MTHB - <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSS-GJAHR.

ETAB-X_ITDB = ETAB-X_ITDB -

COSS-WKG001 - COSS-WKG002 -

COSS-WKG003 - COSS-WKG004 -

COSS-WKG005 - COSS-WKG006 -

COSS-WKG007 - COSS-WKG008 -

COSS-WKG009 - COSS-WKG010 -

COSS-WKG011 - COSS-WKG012 -

COSS-WKG013 - COSS-WKG014 -

COSS-WKG015 - COSS-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

*------- Shared DPC's

IF COSS-KSTAR BETWEEN '0000600001' AND '0000601999' AND

COSS-WRTTP = '04'.

IF YEAR = COSS-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSS-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDC = ETAB-X_ITDC + <MNT>.

ETAB-X_YTDC = ETAB-X_YTDC + <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHC = ETAB-X_MTHC + <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSS-GJAHR.

ETAB-X_ITDC = ETAB-X_ITDC +

COSS-WKG001 + COSS-WKG002 +

COSS-WKG003 + COSS-WKG004 +

COSS-WKG005 + COSS-WKG006 +

COSS-WKG007 + COSS-WKG008 +

COSS-WKG009 + COSS-WKG010 +

COSS-WKG011 + COSS-WKG012 +

COSS-WKG013 + COSS-WKG014 +

COSS-WKG015 + COSS-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

*------- Salary,ESE,OH,& G&A

IF ( COSS-KSTAR BETWEEN '0000001000' AND '0000001999' OR

COSS-KSTAR BETWEEN '0000002100' AND '0000002199' OR

COSS-KSTAR BETWEEN '0000002500' AND '0000002599' OR

COSS-KSTAR BETWEEN '0000002800' AND '0000002899' ) AND

COSS-WRTTP = '04'.

IF YEAR = COSS-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSS-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDC = ETAB-X_ITDC + <MNT>.

ETAB-X_YTDC = ETAB-X_YTDC + <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHC = ETAB-X_MTHC + <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSS-GJAHR.

ETAB-X_ITDC = ETAB-X_ITDC +

COSS-WKG001 + COSS-WKG002 +

COSS-WKG003 + COSS-WKG004 +

COSS-WKG005 + COSS-WKG006 +

COSS-WKG007 + COSS-WKG008 +

COSS-WKG009 + COSS-WKG010 +

COSS-WKG011 + COSS-WKG012 +

COSS-WKG013 + COSS-WKG014 +

COSS-WKG015 + COSS-WKG016.

ELSE.

EXIT.

ENDIF.

ENDIF.

ENDSELECT.

*----


  • COSB Table (Results from running RA) Unbilled Sales

SELECT * FROM COSB

WHERE OBJNR = PRPS-OBJNR

AND KSTAR BETWEEN '0000003004' AND '0000003005'

"0000003004 = Unbilled Revenue

"0000003005 = Unearned Revenue

AND GJAHR LE YEAR

AND WRTTP = '32'. "32 = Results Analysis

IF YEAR = COSB-GJAHR.

DO.

IF SY-INDEX LE MONTH.

MOVE SY-INDEX TO HLD_INDX.

MNT_INDX = 'COSB-WKGXXX'.

REPLACE 'XXX' WITH HLD_INDX INTO MNT_INDX.

ASSIGN (MNT_INDX) TO <MNT>.

ETAB-X_ITDS = ETAB-X_ITDS - <MNT>.

ETAB-X_YTDS = ETAB-X_YTDS - <MNT>.

IF SY-INDEX = MONTH.

ETAB-X_MTHS = ETAB-X_MTHS - <MNT>.

ENDIF.

ELSE.

EXIT.

ENDIF.

ENDDO.

ELSEIF YEAR > COSB-GJAHR.

ETAB-X_ITDS = ETAB-X_ITDS -

COSB-WKG001 - COSB-WKG002 -

COSB-WKG003 - COSB-WKG004 -

COSB-WKG005 - COSB-WKG006 -

COSB-WKG007 - COSB-WKG008 -

COSB-WKG009 - COSB-WKG010 -

COSB-WKG011 - COSB-WKG012 -

COSB-WKG013 - COSB-WKG014 -

COSB-WKG015 - COSB-WKG016.

ELSE.

EXIT.

ENDIF.

ENDSELECT.

*----


  • ENDSELECT. "End of WBS records (from PRPS) for project

*----


  • Get Total Sales by adding Billed Sales to Unbilled Sales.

ETAB-X_ITDS = ETAB-X_ITDS + ETAB-X_ITDB.

ETAB-X_YTDS = ETAB-X_YTDS + ETAB-X_YTDB.

ETAB-X_MTHS = ETAB-X_MTHS + ETAB-X_MTHB.

IF HOLD_PRART IN EXTRTYP. "Matches the user requested project type

IF OPT1 EQ 'X' OR "All projects

( OPT2 EQ 'X' AND "Only active projects

ETAB-X_STAT NE 'TECO' AND

ETAB-X_STAT NE 'CLSD' ).

TOTAL_AWARD = TOTAL_AWARD + ETAB-X_AWRD.

TOTAL_AUTHR12 = TOTAL_AUTHR12 + ETAB-X_P12R .

TOTAL_AUTHC12 = TOTAL_AUTHC12 + ETAB-X_P12C.

TOTAL_AUTHR13 = TOTAL_AUTHR13 + ETAB-X_P13R.

TOTAL_AUTHC13 = TOTAL_AUTHC13 + ETAB-X_P13C.

TOTAL_ITDSALES = TOTAL_ITDSALES + ETAB-X_ITDS.

TOTAL_ITDCOSTS = TOTAL_ITDCOSTS + ETAB-X_ITDC.

TOTAL_YTDSALES = TOTAL_YTDSALES + ETAB-X_YTDS.

TOTAL_YTDCOSTS = TOTAL_YTDCOSTS + ETAB-X_YTDC.

TOTAL_MTHSALES = TOTAL_MTHSALES + ETAB-X_MTHS.

TOTAL_MTHCOSTS = TOTAL_MTHCOSTS + ETAB-X_MTHC.

TOTAL_ITDBILLED = TOTAL_ITDBILLED + ETAB-X_ITDB.

TOTAL_YTDBILLED = TOTAL_YTDBILLED + ETAB-X_YTDB.

TOTAL_MTHBILLED = TOTAL_MTHBILLED + ETAB-X_MTHB.

TOTAL_COMMITMENT = TOTAL_COMMITMENT + ETAB-X_COMM.

PERFORM SHIFT_SIGN.

APPEND ETAB. "Write data to internal table

CLEAR ETAB.

COUNT_PROJECTS = COUNT_PROJECTS + 1. "Count # of Projects

ENDIF.

ENDIF.

ENDSELECT.

*----


ENDSELECT. "End of Projects from PROJ table

*----


PERFORM GRAND_TOTALS.

SORT ETAB BY X_BU X_PROJ.

ETAB-X_BU = 'BU'.

ETAB-X_CNTR = 'PrftCntr'.

ETAB-X_PROJ = 'Proj'.

ETAB-X_CREP = 'Contract Mgr'.

ETAB-X_BDREP = 'Account Mgr'.

ETAB-X_GRPH = 'Technical Lead'.

ETAB-X_ACTREP = 'Key Person1'.

ETAB-X_PMGR = 'Program Mgr'.

ETAB-X_TTLE = 'Title'.

ETAB-X_CLNT = 'Client'.

ETAB-X_POCN = 'Contract #'.

ETAB-X_TYPE = 'Type'.

ETAB-X_OPEN = 'Open'.

ETAB-X_CLOSE = 'Close'.

ETAB-X_CGT = 'CGT'.

ETAB-X_DISTR = 'DC'.

ETAB-X_CONTR = 'Contr%'.

ETAB-X_AWRD = 'Award'.

ETAB-X_P12R = 'Auth12 Sales'.

ETAB-X_P12C = 'Auth12 Cost'.

ETAB-X_P13R = 'Auth13 Sales'.

ETAB-X_P13C = 'Auth13 Cost'.

ETAB-X_ITDS = 'ITD Sales'.

ETAB-X_ITDC = 'ITD Costs'.

ETAB-X_YTDS = 'YTD Sales'.

ETAB-X_YTDC = 'YTD Costs'.

ETAB-X_MTHS = 'Mth Sales'.

ETAB-X_MTHC = 'Mth Costs'.

ETAB-X_ITDB = 'ITD Billed'.

ETAB-X_YTDB = 'YTD Billed'.

ETAB-X_MTHB = 'Mth Billed'.

ETAB-X_COMM = 'Commitments'.

ETAB-X_STAT = 'Status'.

INSERT ETAB INDEX 1. "Write a report header line to internal table

CLEAR ETAB.

CALL FUNCTION 'Z_RH_START_EXCEL_DATA_OLE'

EXPORTING

  • data_name = 'C:\WINNT\Profiles\cbraswell\Desktop\TestTemp.xlt'

DATA_NAME = 'ZSales.XLS'

DATA_PATH_FLAG = 'W'

DATA_TYPE = 'DAT'

DATA_BIN_FILE_SIZE = 'ZSales'

CHART_TITLE = 'ZSales'

CHART_TYPE = 2

CATEGORY_TITLE = 'ZSales'

VALUE_TITLE = 'ZSales'

DATA_XTOP_COR = 0

DATA_YTOP_COR = 0

DATA_XBOT_COR = 0

DATA_YBOT_COR = 0

CATEGORY_XTOP_COR = 0

CATEGORY_YTOP_COR = 0

CATEGORY_XBOT_COR = 0

CATEGORY_YBOT_COR = 0

TABLES

DATA_TAB = ETAB

EXCEPTIONS

NO_BATCH = 1

DOWNLOAD_ERROR = 2

INVALID_TYPE = 3

INTERNAL_ERROR = 4

NO_OLE_SUPPORT = 5

OLE_FILE_ERROR = 6

OLE_ERROR = 7

NO_DATA = 8

COORDINATE_ERROR = 9

OTHERS = 10.

  • Negative # sign in the front of number for downloading to Excel

FORM SHIFT_SIGN.

IF ETAB-X_AWRD CA '-'. SHIFT ETAB-X_AWRD RIGHT CIRCULAR. ENDIF.

IF ETAB-X_P12R CA '-'. SHIFT ETAB-X_P12R RIGHT CIRCULAR. ENDIF.

IF ETAB-X_P12C CA '-'. SHIFT ETAB-X_P12C RIGHT CIRCULAR. ENDIF.

IF ETAB-X_P13R CA '-'. SHIFT ETAB-X_P13R RIGHT CIRCULAR. ENDIF.

IF ETAB-X_P13C CA '-'. SHIFT ETAB-X_P13C RIGHT CIRCULAR. ENDIF.

IF ETAB-X_ITDS CA '-'. SHIFT ETAB-X_ITDS RIGHT CIRCULAR. ENDIF.

IF ETAB-X_ITDIC CA '-'. SHIFT ETAB-X_ITDIC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_ITDC CA '-'. SHIFT ETAB-X_ITDC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_YTDS CA '-'. SHIFT ETAB-X_YTDS RIGHT CIRCULAR. ENDIF.

IF ETAB-X_YTDC CA '-'. SHIFT ETAB-X_YTDC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_YTDIC CA '-'. SHIFT ETAB-X_YTDIC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_MTHS CA '-'. SHIFT ETAB-X_MTHS RIGHT CIRCULAR. ENDIF.

IF ETAB-X_MTHC CA '-'. SHIFT ETAB-X_MTHC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_MTHIC CA '-'. SHIFT ETAB-X_MTHIC RIGHT CIRCULAR. ENDIF.

IF ETAB-X_ITDB CA '-'. SHIFT ETAB-X_ITDB RIGHT CIRCULAR. ENDIF.

IF ETAB-X_YTDB CA '-'. SHIFT ETAB-X_YTDB RIGHT CIRCULAR. ENDIF.

IF ETAB-X_MTHB CA '-'. SHIFT ETAB-X_MTHB RIGHT CIRCULAR. ENDIF.

IF ETAB-X_COMM CA '-'. SHIFT ETAB-X_COMM RIGHT CIRCULAR. ENDIF.

ENDFORM.

**

FORM GRAND_TOTALS.

SKIP 1. .

WRITE: / '----


'.

WRITE: / 'System ID = ', SY-SYSID,

/ 'Client = ', SY-MANDT,

/ 'User = ', SY-UNAME,

/ 'Date of data extract = ', SY-DATUM ,

/ '----


',

/ 'Number of Projects = ', COUNT_PROJECTS,

/ 'total Award (Plan 11) = ', TOTAL_AWARD,

/ 'total Auth Sales (Plan 12) = ', TOTAL_AUTHR12,

/ 'total Auth Cost (Plan 12) = ', TOTAL_AUTHC12,

/ 'total Auth Sales (Plan 13) = ', TOTAL_AUTHR13,

/ 'total Auth Cost (Plan 13) = ', TOTAL_AUTHC13,

/ 'total ITD Sales = ', TOTAL_ITDSALES,

/ 'total ITD Costs = ', TOTAL_ITDCOSTS,

/ 'total YTD Sales = ', TOTAL_YTDSALES,

/ 'total YTD Costs = ', TOTAL_YTDCOSTS,

/ 'total Mth Sales = ', TOTAL_MTHSALES,

/ 'total Mth Costs = ', TOTAL_MTHCOSTS,

/ 'total ITD Billed = ', TOTAL_ITDBILLED,

/ 'total YTD Billed = ', TOTAL_YTDBILLED,

/ 'total Mth Billed = ', TOTAL_MTHBILLED,

/ 'total Commitment = ', TOTAL_COMMITMENT.

WRITE: / '----


'.

CLEAR ETAB.

CONCATENATE YEAR ' - ' MONTH INTO YYYY_MM.

ETAB-X_BU = 'zz'.

ETAB-X_CNTR = 'YYYY-MM ='.

ETAB-X_PROJ = YYYY_MM.

ETAB-X_PMGR = 'ZSALES'.

ETAB-X_TTLE = 'Version 4.3'. "*** Used by ZSALES Macro ***

ETAB-X_CLNT = 'Date of extract -->'.

TEMP-DATE = SY-DATUM.

CONCATENATE TEMP-DATE4(2) '/' TEMP-DATE6(2) '/' TEMP-DATE+0(4)

INTO ETAB-X_OPEN.

ETAB-X_AWRD = TOTAL_AWARD.

ETAB-X_P12R = TOTAL_AUTHR12.

ETAB-X_P12C = TOTAL_AUTHC12.

ETAB-X_P13R = TOTAL_AUTHR13.

ETAB-X_P13C = TOTAL_AUTHC13.

ETAB-X_ITDS = TOTAL_ITDSALES.

ETAB-X_ITDC = TOTAL_ITDCOSTS.

ETAB-X_YTDS = TOTAL_YTDSALES.

ETAB-X_YTDC = TOTAL_YTDCOSTS.

ETAB-X_MTHS = TOTAL_MTHSALES.

ETAB-X_MTHC = TOTAL_MTHCOSTS.

ETAB-X_ITDB = TOTAL_ITDBILLED.

ETAB-X_YTDB = TOTAL_YTDBILLED.

ETAB-X_MTHB = TOTAL_MTHBILLED.

ETAB-X_COMM = TOTAL_COMMITMENT.

PERFORM SHIFT_SIGN.

APPEND ETAB. "Write last record to internal table

ENDFORM.

[/code]

  • end of code *********************************