, ,

Initialize Apps,

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.
/by
,

Credit Card Number (of different format) from specific column

Following query can be used to get the Credit Card Number (of different format) from specific column.

Column would have credit card Number in the text date. Date is not in any fixed format. And Credit Card Number would also not in any specific Format. In the following query, we have considered few credit card formats. In can include other formats accordingly as your requirement.

SELECT jtf_note_id, creation_date, LANGUAGE, notes,
CASE
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘99999999999999999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘99999999999999999’
 ),
LENGTH (‘99999999999999999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘999999999999999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘999999999999999’
 ),
LENGTH (‘999999999999999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999 9999 9999 9999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999 9999 9999 9999’
 ),
LENGTH (‘9999 9999 9999 9999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999 999999 99999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999 999999 99999’
 ),
LENGTH (‘9999 999999 99999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999-9999-9999-9999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999-9999-9999-9999’
 ),
LENGTH (‘9999-9999-9999-9999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999-999999-99999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999-999999-99999’
 ),
LENGTH (‘9999-999999-99999’)
)
ELSE ‘No Credit card Number’
END “Credit card Number”
FROM jtf_notes_tl
WHERE 1 = 1
AND (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%999999999999999 %’) ) — 15 digit—
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999999999999999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999 9999 9999 9999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999 999999 99999 %’)
— below are different formats with ‘-‘ instead of ‘ ”
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999-9999-9999-9999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999-999999-99999 %’)

I hope the above information would be helpful to you.
/by
, ,

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

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’.

Cause

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.

Solution

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.

/by
,

know the Instance Name of oracle apps

Following Query can be used to know the Instance Name you are working from the Backend.

SELECT UPPER(sys_context(‘USERENV’,’DB_NAME’)) “Instance”
FROM DUAL;
or
select instance_name from v$instance;

/by
, , ,

On-hand inventory information

The following select statement would extracts all on-hand inventory information from Oracle
Applications base tables.

Note:- You may need to modify the query to match with your instance conditions and your requirements.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
— AND hou.type = ‘DC’
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

I hope the above information would be helpful to you.

/by