Interlock Manager Not Creating Replenishment SO After Upgrade To 12.2.5

(Doc ID 2195424.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Outsourced Mfg for Discrete Industries - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

12.2.5

After upgrade from 12.2.2

PROBLEM
-------------
!) Completed all Setups needed for Full Subcontracting (OEM, MP, shipping Network, Customers, Suppliers, etc)
2) Created Items, BOM,, ASL, SR, etc for subcontracting assembly
3) Ran 'Subcontracting: Interlock Manager' concurrent program.
4) Saw that WIP Job, Replenishment PO created in MP, But Replenishment SO is not created in OEM

NOTE: This processed worked fine in 12.2.2 when we created all setups and ran the process for few subcontracting assemblies. It was creating WIP Job, PO in MP and Sales order in OEM.
We upgraded to 12.2.5 this month and ran interlock manager for example subcontracting assemblies and see the issue.
Turned Statement level debug and found error in the log files. See attached log file for details

Same error listed for times in the FND_LOG_MESSAGES

JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO 4 Unexpected error at stmt:50
----- PL/SQL Call Stack -----
 object line object
 handle number name
0x522ced4c8 73 package body APPS.FND_LOG
0x522ced4c8 216 package body APPS.FND_LOG
0x4edde8710 1608 package body APPS.JMF_SHIKYU_ONT_PVT
0x4b5e69f00 3471 package body APPS.JMF_SUBCONTRACT_ORDERS_PVT
0x4b5e69f00 4459 package body APPS.JMF_SUBCONTRACT_ORDERS_PVT
0x4ce849860 92 package body APPS.JMF_INTERLOCK_SHIKYU_CP
0x4d36b7188 1 anonymous block"


"ORA-06510: PL/SQL: unhandled user-defined exception
ORA-01422: exact fetch returns more than requested number of rows"

SO THEN I REVIEWED AND SENT

SEARCH FOR STMT
find stmt 50 where error is reported in fnd_log_messages

  560 lStmtNo := 50;
  561
  562 -- Get customer id
  563 SELECT TO_NUMBER(org_information1)
  564 INTO l_header_rec.sold_to_org_id
  565 FROM HR_ORGANIZATION_INFORMATION
  566 WHERE organization_id = p_tp_organization_id
  567 AND org_information_context = 'Customer/Supplier Association';

So we can run
SELECT *
FROM HR_ORGANIZATION_INFORMATION
WHERE organization_id = 2834 --p_tp_organization_id based on message in line 12
AND org_information_context = 'Customer/Supplier Association';

based on message in line 12
>> Process_Replenishment_SO: p_action = Q, p_subcontract_po_shipment_id = 6552595, p_item_id = 1160371, p_quantity = 1, p_replen_po_shipment_id = , p_oem_organization_id = 1390, p_tp_organization_id = 2834

FURTHER CUSTOMER UPDATE
--------------------------------------------
They sent file export-of-select-for-oracle-SR-24AUG16.xlsx. for above

Then also two other files with comments:
Ashok-Debug-SQL2-error-output-24AUG16.xlsx.
Ashok-Debug-SQL1-error-output-24AUG16.xlsx.

Debugging pointed to following statement in JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO package

JMF_SHIKYU_UTIL.get_mp_cust_info_at_oem(oem_org_id => p_oem_organization_id,
mp_org_id => p_tp_organization_id ,
x_cust_id => l_header_rec.sold_to_org_id,
x_cust_site_id => l_header_rec.ship_to_org_id,
x_cust_bill_to_site_id => l_header_rec.invoice_to_org_id);

The selects in above package is returning two rows.

  SELECT
  -- /*+ MATERIALIZE*/
  rac.party_name party_name,
  mcsv.address address
  FROM
  apps.hr_organization_information hoi,
  apps.HR_ORGANIZATION_INFORMATION mpou,
  apps.hz_parties rac,
  apps.hz_cust_accounts hca,
  apps.mrp_customer_sites_v mcsv
  WHERE
  hoi.organization_id = 2834 --mp_org_id
  AND hoi.org_information_context = 'Customer/Supplier Association'
  AND hoi.org_information1 = TO_CHAR(hca.cust_account_id)
  AND hca.party_id = rac.party_id
  AND hoi.org_information2 = TO_CHAR(mcsv.site_use_id)
  AND mcsv.customer_id = hca.cust_account_id
  AND mpou.org_information_context = 'Accounting Information'
  AND hoi.organization_id = mpou.organization_id
  AND mcsv.operating_unit_id = to_number(mpou.org_information3)

select * from apps.mrp_customer_sites_v
select * from apps.hz_cust_accounts where party_id = 43914

SELECT rac.party_name,
  rac.party_id,
  mcsv.address,
  mcsv.site_use_id customer_site_id,
  mcsv.customer_id customer_id,
  csua.bill_to_site_use_id customer_bill_to_id
  FROM
  apps.HR_ORGANIZATION_INFORMATION oemou,
  apps.hz_parties rac,
  apps.hz_cust_accounts hca,
  apps.mrp_customer_sites_v mcsv,
  --mpcustinfo,
  apps.hz_cust_site_uses_all csua,
  apps.hz_cust_acct_sites_all casa
  WHERE
  oemou.organization_id = 1390--oem_org_id
  AND oemou.org_information_context = 'Accounting Information'
  AND mcsv.operating_unit_id = to_number(oemou.org_information3)
  AND hca.party_id = rac.party_id
  AND mcsv.customer_id = hca.cust_account_id
  AND rac.party_name = 'FAIRFIELD MFG COMPANY INC' --mpcustinfo.PARTY_NAME
  AND mcsv.address = '2309 Concord Road (Ship To)' --mpcustinfo.ADDRESS
  AND casa.cust_acct_site_id = csua.cust_acct_site_id
  AND csua.site_use_id = mcsv.site_use_id

Above select statement is returning two rows. What i see is we have one active and another inactive customer

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