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));