Subcontracting reports
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:
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
· 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).