Add SOURCE_FLAG and DEBIT_SOURCE fields to IFQ_RECEIPTS_XML table

[neilriley-NOAA]

Add SOURCE_FLAG and DEBIT_SOURCE fields to IFQ_RECEIPTS_XML table to identify debit type for EM.

[neilriley-NOAA]

Required fields requested by Jeff C.

SOURCE_FLAG OD - Observer Discard LB - Lookbook EM - Electronic Monitoring

DEBIT_SOURCE LB - Lookbook EM - Electronic Monitoring

--These are trip level fields that are redundant for each row VESSEL_NAME, VESSEL_NUMBER, IFQ_ACCOUNT_NUMBER, DEPARTURE_DATE, RETURN_DATE, FISH_TICKET_NUMBER, FISH_TICKET_DATE,
FINAL_TRIP_NUMBER, LIMITED_ENTRY_PERMIT_NUMBER, PROCESSED_FLAG

('OD', 'LB', 'EM'), --You need to add AS "SOURCE_FLAG" ('LB', 'EM'), --You need to add AS "DEBIT_SOURCE IFQ_ACCOUNT_NUMBER, FISH_TICKET_NUMBER, FINAL_TRIP_NUMBER, FISH_TICKET_DATE or RETURN_DATE, --LANDING_DATE RETURN_PORT_ID, VESSEL_NUMBER, LIMITED_ENTRY_PERMIT_NUMBER , REPLACE(tripRecs(i).FIRST_RECEIVER, 'FR',''), --Probably don't need this IFQ_FISHING_AREA_ID, IFQ_SPECIES_GROUP_ID, ROUND(tripRecs(i).DISCARDED_CATCH_ESTIMATE), sysdate, 'Standard', FISH_TICKET_DATE or RETURN_DATE

MAP TO IFQ.FISHTICKET_PROCESSED_INTO_IFQ fields:

SOURCE_FLAG, DEBIT_SOURCE, ACCOUNT_NUM, FTIX_NUM, FINAL_TRIP_NUM, LANDING_DATE, PORT_CODE, VESSEL_NUM, GF_PERMIT_NUM, CM_CONTRACT_NUMBER, IFQ_MGT_AREA, OYC_CAT, DISCARD_LBS, PROCESSED_DATE, SUBMIT_TYPE, QUOTA_YEAR

[neilriley-NOAA]

@sethgerou-noaa does your vessel account reporting process ever write to STAGING_XPD_IFQ_WEIGHTS_XML or only IFQ_RECEIPTS_XML? I suspect not but wanted to double check?

[sethgerou-noaa]

only IFQ_RECEIPTS_XML. Should I write to the other one?

[neilriley-NOAA]

I don't think so. the STAGING_XPD_IFQ_WEIGHTS_XML is essentially the same as the "EM Results" page and can pull the details from there.

[neilriley-NOAA]

@sethgerou-noaa I have added these columns to DEV.

alter table ifq_receipts_xml add (SOURCE_FLAG varchar2(10), DEBIT_SOURCE varchar2(10));