ORIGINAL - create a new document
ADD - add new lines onto an existing document
UPDATE - update existing line information for a document
REPLACE - Replace the existing document
Which interface tables are involved?
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)
On Release 12.0, the "Import Standard Purchase Orders" concurrent program fails with the following error:
ERROR
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure init_sys_parameters.0
ORA-01422: exact fetch returns more than requested number of rows in Package
po.plsql.PO_PDOI_PVT. Procedure init_startup_values.10
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure start_process.50
User-Defined Exception in Package po.plsql.PO_PDOI_Concurrent. Procedure POXPDOI.30
Steps To Reproduce:
1. Populate the interface table with the PO details.
2. Navigate to Requests -> Run -> Single request.
3. Select 'Import Standard Purchase Orders'.
The multi_org_category flag was not set correctly.
Verify by running the following script:
Select multi_org_category
from fnd_concurrent_programs
where concurrent_program_name='POXPOPDOI';
Ideally, the above script should return a value 'S', which indicates it is set to single org. In problematic case it returns no rows.
To implement the solution, please execute one of the following set of steps:
1. Ensure that you have taken a backup of your system before applying the recommended solution.
2. Run the following scripts in a TEST environment first:
Update fnd_concurrent_programs
set multi_org_category = 'S'
where concurrent_program_name='POXPOPDOI';
3. Commit the transaction using 'commit' command.
4. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:
Select multi_org_category from fnd_concurrent_programs where concurrent_program_name='POXPOPDOI';
-- should return a value 'S'.
5. Confirm that the data is corrected, run the "Import Standard Purchase Orders" concurrent program.
6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.
*** Alternately, this can be achieved via the application with the following steps:
1. Assign yourself the responsibility System Administration. (note it is not system administrator).
2. Navigate to responsibility System Administration - Concurrent Programs form
- Search on POXPOPDOI as short name
- Choose Update
- Move to Request tab
- Off to the right it shows - Operating Unit Mode
- Ensure this is single
Make sure it is S - using this sql -
Select multi_org_category from fnd_concurrent_programs where concurrent_program_name='POXPOPDOI';
After saving.
3. Retest the import and confirm if that has properly corrected the problem.
===========================================================================
PROBLEM DESCRIPTION
===========================================================================
** DESCRIPTION OF PROBLEM, INCLUDING ALL ERRORS:
There is no column that accepts the value for match_option (invoice
matching 'P'or'R') in PO_LINES_INTERFACE. (The enhanced PDOI now supports
standard PO import.) If there is a method to populate the matching
option value via the PDOI, it should be documented in the PDOI update
release note. "matching option" is stored in the following table in EBS.
PO_LINE_LOCATIONS_ALL.MATCH_OPTION
===========================================================================
ADDITIONAL DETAILS
===========================================================================
** TAR NUMBER (ALSO ENSURE BUG NUMBER FIELD IS UPDATED IN TAR):
** LIST ADDITIONAL DOCUMENTATION AVAILABLE (LOG FILE, REPORT, TRACE, ETC.):
N/A
** HOW WILL DEVELOPMENT RECEIVE THE ADDITIONAL DOCUMENTATION?:
Via Email or ess30 upon request.
** DESCRIBE ANY WORKAROUND(S) AVAILABLE TO THE CUSTOMER:
Open POXPOEPO and update the option for all imported POs, which is rediculous.
** LIST NAME & VERSION OF ALL MODULES INVOLVED (FORM,REPORT,PACKAGE,ETC.):
EBS 11.5.3
Please ask if you need specific file versions.
** IS THE PROBLEM OCCURRING IN TEST OR PRODUCTION?
Production.
** DOES THE CUSTOMER HAVE ANY CUSTOMIZATIONS OR 3RD PARTY PRODUCTS?
No.
===========================================================================
HISTORY
===========================================================================
** WAS THE CUSTOMER ABLE TO COMPLETE THE SAME PROCESS PREVIOUSLY?:
No.
** LIST PATCHES APPLIED RECENTLY WHICH COULD AFFECT THIS PROBLEM:
N/ADefaulting of match_option is as follows
1. From Supplier Site
2. From Supplier
3. From Financials System Parameters
The HLD for STD PO do not mention anything about this column.
Will be an ER to create the column in CASE and to add the necessary
validations.PREMCOR REFINING GROUP is requesting this ER be changed to a priority 3 bug so
a fix can be included in a future PO Family Pack. They have 100's of PO's
that they import from a 3rd party system (Maximo) using the PDOI. These
multi-line POs are primarily Service Type POs and whether the line can either
be Invoice Match Option to Receipt or Invoice Match Option to Purchase Order
needs to be controlled at the Line level when inserting into the PO Interface
tables.
Here is the solution for the invoice matching, someone needs to update the
bug to include this solution:
Invoice matching, populate the following columns in PO_LINES_INTERFACE table:
'2WAY' inspection_required_flag = 'N'
receipt_required_flag = 'N'
'3WAY' inspection_required_flag = 'N'
receipt_required_flag = 'Y'
'4WAY' inspection_required_flag = 'Y'
receipt_required_flag = 'Y'
Sometime I'm quite annoyed by the typo mistake when creating a DFF context. The DFF segment screen doesn't allow deletion of context. Fortunately, Oracle has internal API to do such thing. Following is a sample.
--*******************************************
--* Delete a descriptive flexfield
--*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_application_id NUMBER := 0;
l_descriptive_flexfield_name VARCHAR2(100) := 'FND_COMMON_LOOKUPS' ;
l_descriptive_flex_context_cod VARCHAR2(100) := 'XFND_CLWW_PURGE_FOLDER';
BEGIN
--FND_DESCRIPTIVE_FLEXS_PKG --this package is for DFF
--FND_DESCR_FLEX_CONTEXTS_PKG --this package is for DFF Context
--FND_DESCR_FLEX_COL_USAGE_PKG --this package is for DFF Column useage
--When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
--so when deleting a DFF Context, both the context and column usage should be deleted.
FOR c IN (SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE application_id = l_application_id
AND descriptive_flexfield_name = l_descriptive_flexfield_name
AND descriptive_flex_context_code = l_descriptive_flex_context_cod)
LOOP
fnd_descr_flex_col_usage_pkg.delete_row(
x_application_id => l_application_id
,x_descriptive_flexfield_name => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod => l_descriptive_flex_context_cod
,x_application_column_name => c.application_column_name
);
END LOOP;
fnd_descr_flex_contexts_pkg.delete_row(
x_application_id => l_application_id,
,x_descriptive_flexfield_name => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod => l_descriptive_flex_context_cod
);
--commit;
end;
