-
Notifications
You must be signed in to change notification settings - Fork 106
load_submission
Provide a walkthrough of the load_submission
management command.
load_submission
is responsible for pulling File A, B, C data and Submission metadata from Broker for a single DABS submission. It is executed from the command line via ./manage.py load_submission submission_id
and is an integral part of the nightly USAspending pipeline where its execution is initiated indirectly via the load_multiple_submissions
command, which is the entrypoint of the Broker Submission Loader.
load_submission
accepts two command line arguments:
-
submission_id
is a required positional argument used to indicate a single Brokersubmission.submission_id
to be loaded. -
--test
is an optional switch that causes the loader to utilize a "phony cursor" that reads data fromusaspending_api/etl/tests/etl_test_data.json
rather than attempting to pull data from Broker. As the name implies, this is strictly for testing.
- Attempt to find USAspending submission using
broker_submission_id
. - If it does not exist:
- Create a stub.
- If it does exist:
- Delete the existing submission using
rm_submission
.
- Delete the existing submission using
- Find the agency to which this submission belongs.
- Fill in the stub or recreate the submission record if it was deleted.
- Retrieve File A data from Broker's
certified_appropriation
table.- Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
- If the referred to TAS did not exist, save it in a
skipped_tas
dictionary for logging later, and SKIP THISFile A
RECORD
- If the referred to TAS did not exist, save it in a
- Save File A record in USAspending's
appropriation_account_balances
table, reversing the sign of thegross_outlay_amount_by_tas_cpe
column. - Calculate
final_of_fy
forappropriation_account_balances
. - Log count of File C rows skipped due to missing TAS
- Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
- Retrieve File B data from Broker's
certified_object_class_program_activity
table.- Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
- If the referred to TAS did not exist, save it in a
skipped_tas
dictionary for logging later, and SKIP THISFile B
RECORD
- If the referred to TAS did not exist, save it in a
- Save File B record in USAspending's
financial_accounts_by_program_activity_object_class
table, reversing the sign of thetransaction_obligated_amount
column or any columns that end in_cpe
orfyb
. - Calculate
final_of_fy
forfinancial_accounts_by_program_activity_object_class
. - Log count of File B rows skipped due to missing TAS
- Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
- Retrieve File C data from Broker's
certified_award_financial
table where rows have a non-zero, non-nulltransaction_obligated_amou
amount.- Uppercase all text fields in the
certified_award_financial
record. - Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
- If the referred to TAS did not exist, save it in a
skipped_tas
dictionary for logging later, and SKIP THISFile C
RECORD
- If the referred to TAS did not exist, save it in a
- Look up an
award
record from previously loaded award data usingpiid
,fain
, oruri
, by way of the C-to-D linkage process, and link this File C record to it if 1 is found, otherwise leaveNULL
. - Save File C record in USAspending's
financial_accounts_by_awards
table, reversing the sign of thetransaction_obligated_amount
column or any columns that end in_cpe
orfyb
. - Log count of File C rows skipped due to missing TAS
- Return an array of award IDs that were linked to File C records (which appear to have nothing done with them)
- Uppercase all text fields in the
A quick word about submission chaining. This is a concept that exists in USAspending that is not found in Broker. Many numeric values in submissions are reported as CPE (Current Period End) which means they are cumulative for the fiscal year right up until the end of the period they represent. In order to calculate periodic net values, we link submissions to their previous submission in the fiscal year which allows us to quickly calculate a period's net values by subtracting period N-1's CPE values from period N's. This will be important in a minute.
- Home
- The DATA Act
-
Data
- Account Data (A/B/C)
-
Award Data (D1/D2)
- Contract Data (D1)
- Assistance Data (D2)
- Recipient Data (E)
- Subaward Data (F)
- File C to D Linkage
- Agency Data (Federal Hierarchy)
- Location Data
- Management Commands
- Release Notes
- Resources