R12.2.4 - Invalid Cursors : OZF_PRE_PROCESS_PVT.DERIVE_PARTY

(Doc ID 2039173.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Trade Management - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

Nav: Trade Management User > IDSM > Sales Data Upload,

Unable to process the resale batch and eventually its going to DISPUTED status.

The API procedure OZF_PRE_PROCESS_PVT.DERIVE_PARTY has below cursors and are wrongly joined (hp.party_id = hcas.cust_account_id) .Please provide us valid packages.

Few of the problem cursors,

CURSOR get_sf_cust(cv_cust_account_id IN NUMBER) IS
  SELECT hcas.cust_account_id
  , hps.party_site_id
  , hp.party_id
  , hp.party_name
  FROM hz_cust_acct_sites hcas,
  hz_cust_site_uses hcsu,
  hz_party_sites hps,
  hz_parties hp
  WHERE hp.party_id = hcas.cust_account_id
  AND hp.party_id = hps.party_id
  AND hps.party_site_id = hcas.party_site_id
  AND hcas.cust_account_id = cv_cust_account_id
  AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  AND hcsu.primary_flag = 'Y'

--------

CURSOR get_sf_site(cv_party_site_id IN NUMBER) IS
  SELECT hcas.cust_account_id
  , hps.party_site_id
  , hp.party_id
  , hp.party_name
  FROM hz_cust_acct_sites hcas,
  hz_cust_site_uses hcsu,
  hz_party_sites hps,
  hz_parties hp
  WHERE hp.party_id = hcas.cust_account_id
  AND hp.party_id = hps.party_id
  AND hps.party_site_id = hcas.party_site_id
  AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  AND hcas.party_site_id = cv_party_site_id
  AND hcsu.site_use_code = 'BILL_TO';

--

CURSOR get_bt_cust(cv_cust_account_id IN NUMBER) IS
  SELECT hcas.cust_account_id
  , hcsu.site_use_id
  , hps.party_site_id
  , hp.party_id
  , hp.party_name
  FROM hz_cust_acct_sites hcas,
  hz_cust_site_uses hcsu,
  hz_party_sites hps,
  hz_parties hp
  WHERE hp.party_id = hcas.cust_account_id
  AND hp.party_id = hps.party_id
  AND hps.party_site_id = hcas.party_site_id
  AND hcas.cust_account_id = cv_cust_account_id
  AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  AND hcsu.primary_flag = 'Y'
  AND hcsu.site_use_code = 'BILL_TO';

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