load_submission

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 Broker submission.submission_id to be loaded.
• --test is an optional switch that causes the loader to utilize a "phony cursor" that reads data from usaspending_api/etl/tests/etl_test_data.json rather than attempting to pull data from Broker. As the name implies, this is strictly for testing.

Submission Chaining

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.

Basic load_submission processing

1. Attempt to find USAspending submission using broker_submission_id.
2. If it does not exist:
   1. Create a stub.
3. If it does exist:
   1. Drop and recalculate tas_program_activity_object_class_quarterly values for the submission in question and all of its downstream chained submissions. (See! I told you chaining would be important!)
   2. Delete the existing submission using rm_submission.
4. Find the agency to which this submission belongs.
5. Find the previous submission for submission chaining.
   • There is a little bit of confusing logic here for handling edge cases where there are chaining collisions. This would be a good place for a little cleanup. I would rather see us just always renumber all of the submissions for an agency for the entire fiscal year than attempt to track which need to be fixed. At most, there should only be 12, so it should be a relatively cheap operation.
6. Fill in the stub or recreate the submission record if it was deleted.
7. Fix submission chaining collsions.
8. Retrieve File A data from Broker's certified_appropriation table.
   1. Generate any new TAS records if we found any in File A that do not exist in USAspending's treasury_appropriation_account table.
   2. Save File A record in USAspending's appropriation_account_balances table, reversing the sign of the gross_outlay_amount_by_tas_cpe column.
   3. Calculate final_of_fy for appropriation_account_balances.
   4. Calculate quarterly balances for the appropriation_account_balances_quarterly table.
9. Retrieve File B data from Broker's certified_object_class_program_activity table.
   1. Generate any new TAS records if we found any in File B that do not exist in USAspending's treasury_appropriation_account table.
   2. Save File B record in USAspending's financial_accounts_by_program_activity_object_class table, reversing the sign of the transaction_obligated_amount column or any columns that end in _cpe or fyb.
   3. Calculate final_of_fy for financial_accounts_by_program_activity_object_class.
   4. Calculate quarterly balances for the tas_program_activity_object_class_quarterly table.
10. Retrieve File C data from Broker's certified_award_financial table where rows have a non-zero, non-null transaction_obligated_amou amount.
    1. Uppercase all text fields in the certified_award_financial record.
    2. Generate any new TAS records if we found any in File C that do not exist in USAspending's treasury_appropriation_account table.
    3. Look up File D award using piid, fain, or uri.
    4. Save File C record in USAspending's financial_accounts_by_awards table, reversing the sign of the transaction_obligated_amount column or any columns that end in _cpe or fyb.