Trading Ptnr Setup Does Not See Freight Carrier Having Multiple Classification Categories

(Doc ID 2239603.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Order Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

== Product: Oracle Shipping Execution (996) ==
==============================================
PROBLEM STATEMENT
=================
Problem Description/Question: The root cause of the problem therefor is that
the WSHFXCSM form puts an end-date on the hz_code_assignment for unknown
reason but doesn't provide the ability to remvoe the end-date.

Steps taken to debug issue:

WSHFXCSM.fmb 12.0.28.12010000.3

Did some investigation and coming to an alternate conclusion. From the trace
file DEV1_ora_6625718_MSCHOUTE.trc I isolated the LOV SQL (see below)

select hp.party_name party_name, hp.party_id party_id, 'CARRIER' party_type,
hp.party_number party_number from hz_parties hp, hz_code_assignments hca
where hp.status = 'A' and hp.party_id = hca.owner_table_id and
hca.owner_table_name = 'HZ_PARTIES' and hca.class_category =
'TRANSPORTATION_PROVIDERS' and hca.class_code = 'CARRIER' and
nvl(hca.start_date_active, sysdate) <= sysdate and nvl(hca.end_date_active,
sysdate) >= sysdate order by party_name


BEGIN HZ_CLASS_CATEGORIES "TRANSPORTATION_PROVIDERS"
ALLOW_MULTI_ASSIGN_FLAG = "Y" <----
ALLOW_MULTI_PARENT_FLAG = "N"
ALLOW_LEAF_NODE_ONLY_FLAG = "Y"
OWNER = "SEED"

BEGIN HZ_CLASS_CATEGORY_USES "HZ_PARTIES"
COLUMN_NAME = "PARTY_ID"
ADDITIONAL_WHERE_CLAUSE = "WHERE PARTY_TYPE = 'ORGANIZATION'"
OWNER = "SEED"
END HZ_CLASS_CATEGORY_USES

END HZ_CLASS_CATEGORIES
i then discovered that the hz_code_assignment table is tested against the
start_date_active and end_date_active columns. Running the SQL 'as is' does
not show the carrier METRO. However, when eliminating the start- and end-date
in the where clause the METRO carrier does appear in tje result. I then
proceeded to run an update statement (see below)

update hz_code_assignments hca
set hca.end_date_active = null
where 1=1
and hca.owner_table_name = 'HZ_PARTIES'
and hca.class_category = 'TRANSPORTATION_PROVIDERS'
and hca.code_assignment_id = 2633038;

After committing the update the trading partner setup works!!!!

Conclusion so far is the the problem does no exist in the form ECXTPDEF but
in the form WSHFXCSM (Shipping Execution).



Oracle Applications : 12.1.3
Oracle Forms Version : 10.1.2.3.0
Application Object Library : 12.0.0

Carrier form
Form Name : WSHFXCSM
Form Version : 12.0.28.12010000.3

Trading partner form
Form Name : ECXTPDEF
Form Version : 12.0.5.12010000.2

Database
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production



Adding a secondary classification category in the Carriers form (WSHFXCSM)
end dates the hz_code_assignments join with class category
'TRANSPORTATION_PROVIDERS' with the carrier party (i.e.
hz_code_assignments.end_date_active is set with a date)
Form WSHFXCSM subsequently does not provide the ability to remove this end
date. Event though the category is checked as enabled.
Basically the addition of the category disables the Carrier as a
transportation provider which cannot be undone.
Evidentially the check box does not control the
hz_code_assignments.end_date_active column but only the
hz_code_assignments.status column.

The consequence of this is that this Carrier can no longer be used as a XML
gateway trading partner (ECXTPDEF) because the Carrier is effectively no
longer seen as a transportation provider. When re-enabling the code
assignment using the below update statement the problem is resolved.

update hz_code_assignments hca
set hca.end_date_active = null
where 1=1
and hca.owner_table_name = 'HZ_PARTIES'
and hca.class_category = 'TRANSPORTATION_PROVIDERS'
and hca.class_code = 'CARRIER'
and hca.code_assignment_id = 2633038;

This proves that the issue is driven by the end date of the party code
assignment.

Below the SQL that is used in the ECXTPDEF form (Trading Partner Setup) in
the LOV to find the Carrier.

SELECT hp.party_name party_name
, hp.party_id party_id
, 'CARRIER' party_type
, hp.party_number party_number
FROM hz_parties hp
, hz_code_assignments hca
WHERE hp.status = 'A'
AND hp.party_id = hca.owner_table_id
AND hca.owner_table_name = 'HZ_PARTIES'
AND hca.class_category = 'TRANSPORTATION_PROVIDERS'
AND hca.class_code = 'CARRIER'
AND NVL(hca.start_date_active, sysdate) <= sysdate
AND NVL(hca.end_date_active, sysdate) >= sysdate
ORDER BY party_name;

As you can see the SQL uses both the code assignment's start and end date
columns as well as the status column to determine if it is active.

This leaves us with the following question:

1. Should the problem be resolved in the form WSHFXCSM by making sure the end
date is not set when the status of that classification is 'A' (Active)?
2. Should the problem be resolved in the form ECXTPDEF by removing the
clauses checking the start and end date of the code assignment?
3. Are both form partially wrong and both should remove the (redundant?) use
of the code assignment's start and end date columns?

WORKAROUNDS
===========
update hz_code_assignments hca
set hca.end_date_active = null
where 1=1
and hca.owner_table_name = 'HZ_PARTIES'
and hca.class_category = 'TRANSPORTATION_PROVIDERS'
and hca.code_assignment_id = 2633038;

After committing the update the trading partner setup works!!!!

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms