EDI CDMO: ECE_CDMO_HEADER_V Deriving PARTY_NUMBER Instead of ACCOUNT_NUMBER

Last updated on OCTOBER 12, 2015

Applies to:

Symptoms

On : 12.1.3 version, Receivables EDI/XML Gateway

The OUT: Credit/Debit Memo(812) is not getting the BILL_TO_CUSTOMER_NUMBER, SHIP_TO_CUSTOMER_NUMBER, or SOLD_TO_CUSTOMER_NUMBER in lines 1010, 1110 and 1210 pos. 141.

This extract program is using view ECE_CDMO_HEADER_V, which is getting the information from the HZ_PARTIES.party_number table, instead of HZ_CUST_ACCOUNTS.account_number.

In 11i, the information is derived from RA_CUSTOMERS view.
In R12, the customer number is stored in the table HZ_CUST_ACCOUNTS, in the account_number column.

Steps to Reproduce:

1. Review the ECE_CDMO_HEADER_V

CREATE OR REPLACE force VIEW ECE_CDMO_HEADER_V as
SELECT 'EDI' communication_method,
 --
  pt3.party_name sold_to_customer_name,
  cas1.party_site_id bill_to_address_id,
  csu1.location bill_to_customer_location,
  pt1.party_number bill_to_customer_number, -- should be ca1.account_number
  cas2.party_site_id ship_to_address_id,
  csu2.location ship_to_customer_location,
  pt2.party_number ship_to_customer_number, -- should be ca2.account_number
  cas3.party_site_id sold_to_address_id,
  csu3.location sold_to_customer_location,
  pt3.party_number sold_to_customer_number, -- should be ca3.account_number
--
--
  aps.receivables_charges_remaining receivables_charges_remaining
FROM hz_parties pt1,
  hz_cust_accounts ca1,
  hz_parties pt2,
  hz_cust_accounts ca2,
  hz_parties pt3,
  hz_cust_accounts ca3,
--
  ra_customer_trx rct2,
  ra_customer_trx rct1
WHERE rct1.complete_flag = 'Y'
 AND rct1.printing_pending = 'Y'
 AND rct1.printing_option = 'PRI'

Cause