Loading transactions and source codes from Allegiance involves generating a source code, then parsing that same source code to leverage other Frakture features.

Step 1) We run this SQL to get a list of transactions:

select
[HUPAY#] as remote_transaction_id,
concat(HSTATN,'-',HMEMBR) as remote_person_id,
PAYAM-HCURAM as amount,
HCURAM as shipping_fee,
OOPAYHST.HMODE as mode_of_payment,
OOPAYHST.HSUST as hsust,
OOPAYHST.HPTDAM as paid_to_date_amount,

datefromparts(HDTYY,HDTMM,HDTDD) as ts,
HSOURC as source,
OOMBRSTA.STDESC as affiliation,
sltyp as solicitation_type,
slmth as solicitation_channel,
FNDESC as fund

from OOPAYHST
left join OOMBRSTA on (OOPAYHST.HSTATN=OOMBRSTA.STN)
left join OOFUND on (OOPAYHST.HFUND=OOFUND.FNCODE)
left join OOSOURCE on (hsourc = source)
where try_cast(concat(HDTMM,'/',HDTDD,'/',HDTYY) as date) is not null
and HDTYY>0

1. Since we are a 1 source code per transaction setup, we then have to generate a source code based on that, using this mapping: let source_code=['AGDB2',o.source,o.affiliation,o.fund,o.solicitation_type,o.solicitation_channel]

That gets it into our extensive source coding world

1. After loading transactions, we then reparse that using our normal parsing, which breaks out the source/fund/affiliation/solicitation_type/solicitation channel fields. NOTE: There is NOT a source_code_channel in this parsing — we determined it didn’t quite match up with our normal source_code_channel work.
2. Labels — Our optional source code labeling setup is then used. Labels are human readable versions of elements in a source code. We populate the source_code_label table with data from Allegiance, specifically just taking the FNDESC from fund and affiliation tables above, and turning them into human legible fields. This creates entries such as:

The summary views will then expose those as _label fields, defaulting to the value if no label is specified.