CTMS Paragon Interface

The basic Paragon interface has been documented here: ctms:Paragon Interface

This guide provides more technical information

Packages

• DP_PAR_API
• DP_PAR_API_STS

CONFIGURATION - OUTBOUND

-- EDI Interface - FROM EDI_IF_CONTROL EXTERNAL_SYSTEM LIKE 'PAR_API%'

SELECT * FROM EDI_PROCESS_HEADER
--WHERE FTP_USERNAME IS NOT NULL
WHERE PROCESS_NAME LIKE '%PAR%'

-- EDI Process Parameters
SELECT * FROM EDI_PROCESS_TRIGGERS
WHERE PROCESS_NAME LIKE '%PAR%'

-- Records are triggered to be written to Paragon from
-- TRG_GEO_LOCATION_UID - note that this does not use system parameter PAR_API
-- TRG_LOCATIONS_UID
-- TI_SCH_ORD_STATUS
-- TRG_SCH_ORDER_LINE_PAR
-- TRG_SCH_ORD_AUDIT_LOCS
-- TRG_SCH_ORD_CURRENT_DEPOT
-- TRG_SHA_TRUNK_TRIP
--Written to INT_XML_CONTROL - check interface type and order

-- NOTE: The FIX interface (for adding locations to a routing plan, to generate routes/drop-numbers)
-- is no longer in use on STSTPRD - commented out code, but still in TMSDEV - watch out for that
-- They manually add the addresses to Paragon now.

-- Packages that reference PAR_API
-- DP_CTMS_IMPORT
-- DP_FLEXIPOD
-- DP_FLEXIPOD_2
-- DP_PAR_API
-- DP_PAR_API_STP
-- DP_SCHEDULING_ENGINE_STAP
-- DP_SCHEDULING_ENGINE
-- TRM

-- Packages that reference PAR_API parameter
-- TI_SCH_ORD_STATUS
-- TRG_LOCATIONS_UID
-- TRG_SCH_ORDER_LINE_PAR
-- TRG_SCH_ORD_AUDIT_LOCS
-- TRG_SCH_ORD_CURRENT_DEPOT
-- TRG_SCH_ORD_REFERENCE_RUN
-- TRG_SHA_TRUNK_TRIP
-- TRM

-- Packages that write 'LOC' paramgon records (FIX interface)
-- DP_PAR_API_STP
-- TI_SCH_ORD_STATUS
-- TRG_GEO_LOCATION_UID

select * from int_xml_control
where external_system = 'PAR_API'
and event_type = 'ORD'
and OMS_REF = 846511
order by int_xml_seq desc

select * from int_xml_control
where external_system = 'PAR_API'
and event_type = 'LOC'
and OMS_REF = 846511
order by int_xml_seq desc

Parameters

Parameters - PAR_API Also

• PAR_ENDPOINT_URL
• PAR_TENNANT keys
• KEY_FORMAT

-- Searching for parameters
SELECT '|'||PARAM_NAME, '||'||DESCRIPTION, '||'||CONFIG_BY 
FROM ADM_SYSTEM_PARAM
--SELECT * FROM ADM_SYSTEM_PARAM
WHERE PARAM_NAME LIKE '%PAR_API%'

SELECT '|'||PARAM_NAME, '||'||DESCRIPTION, '||'||CONFIG_BY 
FROM ADM_SYSTEM_PARAM
WHERE PARAM_NAME LIKE 'PAR%'
FOR UPDATE

Tenant Keys

Keys matching dates/schedules to Paragon run keys

SELECT * FROM PAR_KEYS

SELECT * FROM PAR_KEYS_ROUTE

Outbound

-- Configuration of outbound processing
-- Orders to paragon
SELECT * FROM PAR_CALL_API
-- locations to paragon
SELECT * FROM PAR_CUST_API
-- Fixed routes to paragon
SELECT * FROM PAR_FIX_API


-- PICKED UP BY (EITHER/OR)
-- DP_PAR_API
-- DP_PAR_API_STS

-- PROCESS_PARAGON

-- That sends the details to Paragon.

Inbound

-- WARNING: You only want to get as few records as possible here - the results are massive.
-- So, use the following to identify a record you want using PAR_ID, the select * where PAR_ID = ?

-- Inbound Tactical
SELECT PAR_ID, PAR_DATE, PAR_PROCESSED, DEPOT_PLAN
FROM PAR_JSON_IN
ORDER BY PAR_DATE DESC

-- Inbound Strategic
SELECT PAR_ID, PAR_DATE, PAR_PROCESSED, RUN_NUMBER 
FROM PAR_JSON_IN_ROUTE
ORDER BY PAR_DATE DESC

-- Where values extracted from JSON are stored for onward processing
-- Written one per order or record
select * from PAR_API_STAGING
select * from PAR_API_STAGING_ROUTE

-- INBOUND STRATEGIC
-- When staged, writes 1 record to INT_XML_CONTROL
select * from int_xml_control
where external_system = 'PARAGON_IN'
order by int_xml_seq desc

-- Typically runs DP_PAR_API_STP.READ_PARAGON_IN passing the process name as a named parameter

-- This process will ONLY process orders where the FROZEN flag is not 0
-- It does do SOME processing against non-frozen orders, but not a lot

-- When complete processing a record (frozen or not) the record is removed from staging.

-- For frozen orders, there is no auditing.
-- For non-frozen orders, there is auditing against the orders and the trips affected.

-- Other Auditing - only audits failures not successes.
SELECT * FROM EPOD_WEB_SERVICE_AUDIT
WHERE TRIP_ID = 'PARAGON'
AND EVENT_TYPE = 'FAIL'


-- INBOUND ROUTE (FIX) Interface
-- Calls Stage_paragon_route which stages to PAR_API_STAGING_ROUTE

-- Then creates an INT_XML_CONTROL record for PARAGON_IN as above (no idea why)

-- Then directly processes the records on PAR_API_STAGING_ROUTE in PROCESS_ROUTE_DATA 
-- not triggered by a background process, so no ides why there is a control record created. No comments to say why.
-- Simply creates Fixed route and stop, created from the PAR_FIX interface
select * from geo_route_dtls

-- Audits:
SELECT * FROM EPOD_WEB_SERVICE_AUDIT
WHERE TRIP_ID = 'PAR_ROUTE';