Subcontracting reports

 

 

The 294 (Subcontracting Report for Individual Contracts)  and 295 (Summary subcontract Report) reports are run against data extracted from the data warehouse, plus additional information from Coeus (goal information is not stored in the warehouse).

The 294 report is run for an individual award and includes expenditures from the beginning of time. 

 

The 295 report groups the awards by sponsor (or prime sponsor) according to level 1 in the sponsor hierarchy for ‘Administering Activity’.  This is run on expenditures for a specific fiscal period range.

 

The base table for these reports (OSP$SUBCONTRACT_EXP_CAT_DET) can be populated by an authorized Coeus.user who has the Feed Maintainer role.  The table will contain the 001 level awards and the sum of expenditures (PO invoices) for all child awards with the sub_plan flag set to ‘Y’.

This query that populates this table is run for a specific fiscal year range.

 

Process

When Tom Egan wants to run a report for a new quarter, he should do the validation checks and make sure that Julie updates coeus appropriately…then Julie will run the feed to SAP, which will update the warehouse.  Then I will repopulate the subcontracting table for the desired period.

If tom finds that an award is not there because it’s sub flag is not checked, Julie will update it, and do the feed.  When I repopulate the tables, I have to look at the award’s start date and repopulate starting then (that’s when there are expenses for the award,which is what the fiscal date is looking at)

 

 

 

Draw Forms

 

We created the 999999 sponsor and loaded the forms for this sponsor. This sponsor code is hard coded in the application (in object w_print_294_295)

       

FORM

VARIABLE INFORMATION

294.drw

Reporting period          – based on today’s date

Regular/Final/Revised – just results in correct box being checked – no special processing

Gets amounts from OSP$SUBCONTRACT_EXP_CAT

295.drw

Gets info from OSP$SUB_EXP_CAT_BY_FY

295_attach.drw

list sponsor names of sponsors included in the ‘Other’ category

 

 

Tables:

 

 

OSP$SUBCONTRACT_EXP_CAT_DET

This table contains expenditures  for each category for every award that has the sub_plan flag set to ‘Y’ in the fiscal periods requested.  Only 001 level awards are inserted in the table, but the amounts are summed for all children of the 001s (if the children have the sub_plan flag set).  Rows are grouped by award_number, fiscal_period, and vendor classification (large business, small business, woman owned, 8A disadvantaged, hub zone, veteran owned, service disabled, historical black college).

 

The stored procedure that populates table is fn_populate_sub_exp_cat.  It deletes rows from OSP$SUBCONTRACT_EXP_CAT_DET in the given date range, and inserts new rows with expenditures for that date range.  It then groups the data from OSP$SUBCONTRACT_EXP_CAT_DET to populate OSP$SUBCONTRACT_EXP_CAT.

 

 

OSP$SUBCONTRACT_EXP_CAT

This has amounts from OSP$SUBCONTRACT_EXP_CAT_DET grouped by vendor classification.  This is the table that the 294 report is run from.

 

OSP$SUB_EXT_CAT_BY_FY

This has amounts from OSP$SUBCONTRACT_EXP_CAT_DET grouped by vendor classification, but is for a specific fiscal year range provided by the user.  This is the table the 295 report is run from.

 

OSP$SUBCONTRACT_EXP_CAT_TEMP – obsolete table

       

 

Validation checks provided in Coeus before running reports

 

·         List child awards that have sub_plan flag <> ‘Y’ if  001 subplan = ‘Y’.  The only child awards that should have the sub_plan flag set to No are cost sharing accounts.

·         Check for missing procurement report contacts

 

Note: The number of rows returned in validation check is limited to 200 because of an error about max array size.

 

Roles Required

 

Action

Role Required

populate Subcontract Expense Data (OSP$SUBCONTRACT_EXP_CAT_DET)

Feed Maintainer

Modify goals

Maintain Subcontracting Goals

Run reports

Maintain Subcontracting Goals or

Modify Subcontract

 

 

Notes:

·         There is a Brio query that is used sometimes by Tom Egan.  The sql for the brio query select is:

SELECT AL2.GOVERNMENT_CONTRACT_NUMBER,

SUM ( AL6.ACTUAL_AMOUNT ),

SUM ( AL6.ACTUAL_AMOUNT ),

AL2.SUPERVISOR,  AL2.MASTER_PROJECT_NUMBER, AL2.MASTER_PROJECT_NAME, AL2.SAP_PRIME_SPONSOR_CODE,

AL2.SAP_PRIME_SPONSOR_NAME,

AL6.POSTING_DATE, AL6.DOCUMENT_NUMBER,

AL6.PURCHASE_ORDER_NUMBER,

AL7.VENDOR_NAME_1, AL7.VENDOR_NUMBER,

AL5.IS_LARGE_BUSINESS, AL5.IS_SMALL_BUSINESS, AL5.IS_WOMAN_OWNED, AL5.IS_8A_DISADVANTAGE, AL5.IS_HUB_ZONE, AL5.IS_VETERAN_OWNED, AL5.IS_SERVICE_DISABLED_VET_OWNED, AL5.IS_HISTORICAL_BLACK_COLLEGE

FROM    WAREUSER.TIME_MONTH AL1,

WAREUSER.PROJECT_OSP AL2,

WAREUSER.DOCUMENT_TYPE AL3,

WAREUSER.GL_ACCOUNT_REPORT AL4, WAREUSER.VENDOR_CLASSIFICATION AL5, WAREUSER.FINANCIAL_DETAIL AL6,

WAREUSER.VENDOR AL7

WHERE (AL2.COST_COLLECTOR_KEY=AL6.COST_COLLECTOR_KEY

AND       AL6.GL_ACCOUNT_KEY=AL4.GL_ACCOUNT_KEY

AND       AL6.TIME_MONTH_KEY=AL1.TIME_MONTH_KEY

AND       AL3.DOCUMENT_TYPE_KEY=AL6.DOCUMENT_TYPE_KEY

AND       AL6.VENDOR_KEY=AL5.VENDOR_KEY

AND       AL7.VENDOR_KEY=AL6.VENDOR_KEY) 

AND       AL2.GOVERNMENT_CONTRACT_NUMBER='959203'

AND       AL1.CALENDAR_PERIOD<='200108'

AND       AL3.DOCUMENT_TYPE_DESCRIPTION='Po Invoice'

AND       AL4.IS_EXPENSE_ACCOUNT='Y'

AND       AL4.REPORT_TYPE='MIT Standard Version 1'

AND       (AL5.IS_LARGE_BUSINESS='Y' OR AL5.IS_SMALL_BUSINESS='Y')

AND       AL2.SUB_PLAN='Y'

 

GROUP BY AL2.GOVERNMENT_CONTRACT_NUMBER,

AL2.SUPERVISOR, AL2.MASTER_PROJECT_NUMBER, AL2.MASTER_PROJECT_NAME, AL2.SAP_PRIME_SPONSOR_CODE, AL2.SAP_PRIME_SPONSOR_NAME, AL6.POSTING_DATE, AL6.DOCUMENT_NUMBER, AL6.PURCHASE_ORDER_NUMBER, AL7.VENDOR_NAME_1, AL7.VENDOR_NUMBER, AL5.IS_LARGE_BUSINESS, AL5.IS_SMALL_BUSINESS, AL5.IS_WOMAN_OWNED, AL5.IS_8A_DISADVANTAGE, AL5.IS_HUB_ZONE, AL5.IS_VETERAN_OWNED, AL5.IS_SERVICE_DISABLED_VET_OWNED, AL5.IS_HISTORICAL_BLACK_COLLEGE

·         Sponsor hierarchy names are hard coded in Oracle package: level one names in ‘Administering Activities’ hierarchy must be:

 

Army

Navy

Air Force

NASA

GSA

DOE

Defense Logistics Agency

DOD

NIH

NSF

NSF

EPA

Jet Propulsion Lab

Other

 

This is currently hard-coded also in the application (in object w_print_294_295).