Query for Identifying correct trace file for request id

PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

SQL related to Oracle Application Messages

SQL related to Oracle Application Messages

PROMPT ATG
PROMPT Find Messages by Message Text
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where upper(m.message_text) like upper('%&EnterMessageText%')
and m.language_code = 'US'
and m.application_id = a.application_id;

PROMPT Find Messages by Message Short Name
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where m.message_name like '%&EnterMessageName%'
and m.language_code = 'US'
and m.application_id = a.application_id;

Date Columns conversion

Rowid:
Working with Rowids: rowidtochar(rowid) and chartorowid('AAABUeAAQAAACsjAAg')

Date Columns
a. Converting dates to show date with time: to_char( 'DD-MON-YYYY HH24:MI:SS')
For example: select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
b. Comparing date columns -- Sometimes handy to skip the time portion when searching for dates. You can use the trunc command to strip the time off. This resets the time to midnight.
For example: select to_char(trunc(sysdate), 'DD-MON-YYYY HH24:MI:SS') from dual;

Table for Views and Triggers

PROMPT Query view details of a specific view.
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS
WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';

PROMPT Trigger details on a table.
select TABLE_NAME, COLUMN_NAME, trigger_name, trigger_type, TRIGGER_BODY
from dba_TRIGGERS
WHERE TABLE_NAME like '%&EnterTableName%';

Table for Bugs and Patching

PROMPT Find specific package version
select text from dba_source
where
name = upper( '&PackageName')
and line < 3;

PROMPT Find bugs already installed fixed in your system
select bug_number
from ad_bugs
where bug_number ='&Bug_Number';

PROMPT Find patches applied
select substr(patch_name,1,12) patch_num
from ad_applied_patches
where patch_name like '%&Patch_Number%';


NOTE: A specific bug maybe fixed by multiple patches so it might be good to look for the bug number, instead of the patch number to see if that bug is fixed already on your system. Another way is to look at the file version mentioned in the patch and check if you have that version or higher.

Table to check Invalids / Compiling

PROMPT Find Invalids
select object_name, object_type, owner, status
from dba_objects
where status = 'INVALID';

PROMPT Compile Packages
select 'alter package '|| object_name || ' compile '|| decode(object_type, 'PACKAGE', '', 'PACKAGE BODY', 'body')|| ';'
from dba_objects
where status = 'INVALID'
and object_type like 'PACK%';

Tables to Find Objects/Table/Columns

General Objects / Tables / Columns

PROMPT Find Object by Type, Name, Status
select object_name, object_type, owner, status
from dba_objects
where upper(object_name) like upper('%&object_name%')
and object_type like upper('%'|| NVL('&object_type', 'TABLE')|| '%')
and status like upper('%'|| NVL('&Status', '%')|| '%')
order by object_name, object_type;

PROMPT Find table indexes, keys, columns
select index_name,column_position,column_name
from all_ind_columns
where table_name = '&EnterTableName'
order by index_name, column_position;

PROMPT Find tables and columns that include a table and/or column name specified.
select table_name, column_name
from dba_tab_columns
where column_name like NVL(UPPER('&COLUMN_NAME'), column_name)
and table_name like NVL(UPPER('&TABLE_NAME'), table_name);

Oracle Learning Management Tables (Oracle Appraisal Tables)

When you setup the appraisal templates in Core HR, that will be stored in "PER_APPRAISAL_TEMPLATES_V" table.

There are 3 temporary tables that hold data for entries made in SSHR until the approval cycle is complete

1) HR_API_TRANSACTIONS
2) HR_API_TRANSACTIONS_STEPS
3) HR_API_TRANSACTIONS_VALUES
Once the request is approved, data will be updated in "PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V" tables.

Below tables are interlinked:
PER_ALL_ASSIGNMENTS_F, PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V TABLES.

PER_ALL_ASSIGNMENTS_F
PER_ALL_PEOPLE_F
PER_JOBS
PER_GRADES
PER_APPRAISALS
PER_APPRAISAL_TEMPLATES
PER_ASSESSMENTS
PER_ASSESSMENT_GROUPS
PER_ASSESSMENT_TYPES
PER_COMPETENCES
PER_COMPETENCES_TL
PER_COMPETENCE_DEFINITIONS
PER_COMPETENCE_ELEMENTS
PER_PARTICIPANTS
PER_PERFORMANCE_RATINGS
PER_PERFORMANCE_REVIEWS
PER_RATING_LEVELS
PER_RATING_LEVELS_TL
PER_RATING_SCALES
PER_RATING_SCALES_TL
HR_QUESTIONNAIRES
HR_QUEST_ANSWERS
HR_QUEST_ANSWER_VALUES
HR_QUEST_FIELD
HR_ORGANIZATION_UNITS
PER_POSITIONS
---------------------------------------------------------------------------------------------------------------------------------------------
SELECT APPRAISAL_TYPE_M
,A.APPRAISEE
,C.EMPLOYEE_NUMBER
,A.MAIN_APPRAISER
,A.DEPARTMENT
,A.APPRAISAL_STATUS_M APPRAISAL_STATUS
, DECODE (B.APPRAISAL_SYSTEM_STATUS ,'ONGOING','Appraisal Pending With - '||A.MAIN_APPRAISER
,'APPRFEEDBACK','Appraisal Pending With - '||a.APPRAISEE
,'SAVED',' Appraisal Pending With - '||a.APPRAISEE
,'TRANSFER','Appraisal Pending With - '||a.APPRAISEE
,'COMPLETED', 'Appraisal Completed'
,'PENDINGAPPR','Appraisal Pending With - '||A.MAIN_APPRAISER
,B.APPRAISAL_SYSTEM_STATUS ) ownership
FROM APPS.HRFV_APPRAISAL_DETAILS A
,PER_APPRAISALS B
,PER_ALL_PEOPLE_F C
WHERE C.PERSON_ID = A.APPRAISEE_PERSON_ID
AND A.APPRAISAL_ID = B.APPRAISAL_ID
AND A.appraisal_id in ( select max(e.appraisal_id) from per_appraisals e
where e.plan_id is null
group by e.appraisee_person_id
having count(*) >= 1)
AND TRUNC (SYSDATE) BETWEEN TRUNC (C.effective_start_date)
AND TRUNC (C.effective_end_date)
order by A.APPRAISEE



SELECT bgrt.NAME business_group_name, orgt.NAME department,
apse.full_name appraisee, appr.full_name appraiser,
mapr.full_name main_appraiser, apr.appraisal_date appraisal_date,
apr.appraisal_period_start_date appraisal_start_date,
apr.appraisal_period_end_date appraisal_end_date,
apr.next_appraisal_date next_appraisal_date,
apr.comments appraisal_comments, rtt.NAME rating_level_name,
rtl.step_value step_value,
hr_bis.bis_decode_lookup ('APPRAISAL_TYPE',
apr.TYPE) appraisal_type,
hr_bis.bis_decode_lookup
('APPRAISAL_SYSTEM_STATUS',
apr.appraisal_system_status
) appraisal_status,
hr_bis.bis_decode_lookup ('APPRAISEE_ACCESS',
apr.appraisee_access
) appraisee_access,
apr.TYPE appraisal_type_code,
apr.appraisal_system_status appraisal_status_code,
apr.appraisee_access appraisee_access_code,
apr.creation_date creation_date,
apr.last_update_date last_update_date, '_DF:PER:PER_APPRAISALS:APR',
apr.appraisal_id appraisal_id,
apr.business_group_id business_group_id,
apr.appraisal_template_id appraisal_template_id,
apr.appraisee_person_id appraisee_person_id,
apr.appraiser_person_id appraiser_person_id,
apr.group_initiator_id group_initiator_id,
apr.overall_performance_level_id overall_performance_level_id,
apr.main_appraiser_id main_appraiser_id,
apr.assignment_id assignment_id, apr.event_id event_id,
rtl.rating_scale_id rating_scale_id,
rtl.competence_id competence_id,
orgt.organization_id organization_id
FROM per_appraisals apr,
hr_all_organization_units_tl bgrt,
hr_all_organization_units_tl orgt,
per_people_x apse,
per_people_x appr,
per_people_x mapr,
per_rating_levels rtl,
per_rating_levels_tl rtt
WHERE apr.business_group_id = bgrt.organization_id
AND bgrt.LANGUAGE = USERENV ('LANG')
AND apr.assignment_organization_id = orgt.organization_id
AND orgt.LANGUAGE = USERENV ('LANG')
AND apr.appraisee_person_id = apse.person_id
AND apr.appraiser_person_id = appr.person_id
AND apr.main_appraiser_id = mapr.person_id(+)
AND apr.overall_performance_level_id = rtl.rating_level_id(+)
AND rtl.rating_level_id = rtt.rating_level_id(+)
AND rtt.LANGUAGE(+) = USERENV ('LANG')
AND apr.business_group_id =
NVL (hr_bis.get_sec_profile_bg_id, apr.business_group_id)
WITH READ ONLY;

Confirm Receipts Workflow

Confirm Receipts workflow

Using Confirm Receipts, employees can view and receive orders in an extremely simple self service interface through Oracle Web Employees or by responding to workflow notifications. Confirm Receipts has the following major components:

A Confirm Receipts workflow process initiated after the due date of open orders
A series of web pages that enable employees to confirm receipt of orders and navigate to relevant documents
The Receiving Transaction Manager which fully validates the receipt information and completes the transaction in Purchasing
Confirm Receipts Workflow Process
The Confirm Receipt workflow process periodically polls for orders that are past due and sends workflow notifications to employees. The workflow engine drives a transaction through a process performing automated steps and invoking the appropriate agents when external processing is required. The engine monitors workflow states and coordinates routing of activities.

Confirm Receipts Notifications
The workflow engine polls for overdue orders and sends workflow notifications to the different users - typically the requester or the buyer. These notifications can be:

workflow generated notification (e.g. email)
viewed through a web browser in Oracle Web Employees
With Web-enabled notifications (email mail attachments or the inbox in Oracle Self-Service Web Applications), the user can drill down from the attached notification Web Page to the specific Receive Orders web page to view orders which are due and confirm receipt of these orders.
Confirm Receipts workflow generates notifications for an entire document if all items on that order have the same due date. If the document includes different due dates, the Confirm Receipts workflow generates a single notification per purchase order per requester per due date.


Review and Respond to Confirm Receipts Notifications
Oracle Web Employees automatically notifies employees of past due orders that have not been received. Valid responses to the notification include:
Fully Received You have fully received the items on the order.
Partially / Over Received You have received less than or more than the quantity of items you requested. In this case, you also drill down from the notification into the Receive Orders web page to record the receipt details. Oracle Web Employees automatically displays the open line items for this order. You can enter the quantity you have received.
Not Received You have not received the items included in the notification.
If the user fails to respond, it will be treated as a time out and a separate exception sub-process will be initiated.


Confirm Receipts Using "Receive Orders" Employee Self Service Web Pages
The user can confirm receipt of goods by using the Oracle Web Employees "Receive Orders" function or by responding to the workflow notification. Using the Receive Orders function in Oracle Web Employees, you can review all open orders that are past due.
However, if the page is accessed from a Confirm Receipts workflow notification, only order information associated with the notification will display on the page. Oracle Purchasing displays the unit of measure from the original purchase order or requisition.

Once you select the order lines you want to receive, press the "Submit" button. You can also identify which items you have partially or over-received, and then update the displayed quantity to reflect the quantity you have actually received.


Update Receiving in Oracle Purchasing
Once you confirm receipt of an order, either through the self-service Receive Orders web page, or by responding directly to the workflow notification, Oracle Web Employees automatically processes your receipt in Oracle Purchasing through the Receiving Transaction Processor.
The receiving transaction processor validates receipt transactions and updates the receiving history and purchase order tables in Oracle Purchasing. If the system encounters an error while processing the receiving transaction, notifications are sent to the requester and the buyer indicating the likely cause for failure.


Exception Handling
Oracle Web Employees handles the following exceptions as part of the Confirm Receipts workflow process:
Time Out Once a notification is sent, the process will be in a wait state to get a response from the user. If there is no response from the user, the system will treat it as a timeout and resend the notification after one week as a first reminder. If there is no response for another week, the notification is resent as a second reminder. If the user does not respond to the second reminder within twenty-four hours, a notification is sent to the user's manager.
Receiving Controls The receipt confirmation process enforces the receiving controls supported in Oracle Purchasing. For example, the system rejects received quantities that are greater than the tolerance level.


Configure the Receive Orders Web Page
Oracle Web Employees allows you to use the Web Applications Dictionary technology (a.k.a. Object Navigator) to tailor the layout and content of the Receive Orders self service web page. Use the Web Applications Manager to select the fields to display, the field, prompts, length, formatting characteristics, and include certain input fields as part of the receipt confirmation.

Setting Up Web Users
Each employee that will confirm receipts through Oracle Web Employees must be defined as a web user. To setup the Receive Orders Web Page for an employee you must:

Define a web user for employees who will receive orders through the web
Assign the Receive Orders functions to that user's list of responsibilities/functions
Respond to Receipt Confirmation Notifications
To respond to workflow generated receipt confirmation requests:

Login to Oracle Web Employees and select the "View Notifications" function.
Select a response to the receipt confirmation request:
Fully Received.
Over/Partially Received.
Not Received.
For partially/over received orders, drill down from the notification into the Receive Orders web page. Record your receipt details.
Select the "Submit Response" button to complete the response and initiate the next workflow activity.

R12: How to enable Personalization Page option

R12: How to enable Personalization Page option

FND: Personalization Region Link Enabled
Personalize Self-Service Defn

Credit Card Setup

How to setup Credit Cards Process in Oracle, below note id's will give detailed explanation


Credit Card Payments, Refunds and Chargebacks: Overview and Setup for Oracle Receivables Release 12 [ID 1357967.1]

Oracle Payments Minimum/Dummy Setup For Credit Card/Purchase Card Funds Capture Processing [ID 553614.1]

How to Cancel Cost Manager (CMCTCM ) and Relaunch It

Applies to:
Oracle Cost Management - Version: 11.5.9 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
CMCTCM
Purpose
How to cancel cost manager, in order to avoid :

- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"
A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."

Troubleshooting Details
How to cancel cost manager, in order to avoid :
- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."



1 - To cancel the Cost Manager : use one of the following method:

a) use 'System Administrator' responsibility
Navigate to Concurrent/Request/view
Set : specific Request=on
Name=Cost Manager.

In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days.
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.

From here Cancel the Cost Manager which is Pending Scheduled.

or

b) Use the following SQL script:


SELECT request_id RequestId,
request_date RequestDt, concurrent_program_name,
phase_code Phase,
status_code Status FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name in ('CMCTCM', 'CMCMCW', 'CMCACW') AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702 AND fcr.phase_code <> 'C'
Cancel all the requests from the above output by navigating to, System Administrator> Concurrent> Request. Query for the RequestId returned from the select above.

In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.

Cancel the request id other than running. Let the running request get over.

2 - Run the cmclean.sql script available from MOS Note 134007.1

3 - Restart the Cost Manager
Inventory> Setup : Transactions> Interface Managers
Select 'Cost Manager' - choose Tools > Launch Manager

Also review << Note 304313.1>>

NOTE:134007.1 - Concurrent Processing - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
NOTE:304313.1 - Understanding Cost Manager

R12 Ap Invoice Data Fix for Tax Amount

Please execute the following data-fix to make Tax Amount Zero on the AP Invoice in R12

UPDATE ZX_LINES
SET UNROUNDED_TAX_AMT = 0,
TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
CAL_TAX_AMT = 0,
CAL_TAX_AMT_TAX_CURR = 0,
CAL_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;

COMMIT;

UPDATE ZX_LINES_SUMMARY
SET TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
TOTAL_REC_TAX_AMT = 0,
TOTAL_REC_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;

UPDATE AP_INVOICE_LINES_ALL
SET AMOUNT = 0,
BASE_AMOUNT = 0
WHERE INVOICE_ID = 90484
AND LINE_TYPE_LOOKUP_CODE = 'TAX';

COMMIT;

UPDATE AP_INVOICES_ALL
SET TOTAL_TAX_AMOUNT = 0
WHERE INVOICE_ID = 90484;

COMMIT;

How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table

How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table

Solution
Record may be resubmitted through the Application or using SQL*Plus.

Note: Back up the data in the table prior to making any changes using SQL*Plus.

1.Resubmission through the Application
Navigation Inventory:Transactions:Transaction Open Interface
Click in the check box under the "Submit" column
then Save


2. Resubmission through SQL*Plus.

Update MTL_TRANSACTIONS_INTERFACE Set PROCESS_FLAG = 1,
LOCK_FLAG = 2, TRANSACTION_MODE = 3,
VALIDATION_REQUIRED = 1, ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL Where PROCESS_FLAG IN (1,3);

Records Stuck In Table Mtl_Transactions_Interface , Negative Balances Not Allowed

Run the following script, which will find items that do not have sufficient quantity in the STAGING subinventory.

Note: if you have given some name other than 'STAGE' to your staging subinventory, you will need to modify the lines in bold type

SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, MAX (oq.onhand_qty) onhand_qty,
SUM (wdd.requested_quantity) - MAX (oq.onhand_qty) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi,
(SELECT NVL (SUM (moq.transaction_quantity), 0) onhand_qty,
moq.organization_id, moq.inventory_item_id,
moq.subinventory_code
FROM apps.mtl_onhand_quantities moq
WHERE moq.containerized_flag = 2
AND moq.subinventory_code = 'STAGE'
GROUP BY moq.organization_id,
moq.inventory_item_id,
moq.subinventory_code) oq
WHERE wdd.released_status IN ('C', 'Y')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.inventory_item_id = oq.inventory_item_id
AND wdd.organization_id = oq.organization_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND oq.subinventory_code = 'STAGE'
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
HAVING SUM (wdd.requested_quantity) > MAX (oq.onhand_qty)
UNION ALL
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, 0,
SUM (wdd.requested_quantity) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi
WHERE wdd.released_status IN ('Y', 'C')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND NOT EXISTS (
SELECT 1
FROM mtl_onhand_quantities oq
WHERE oq.inventory_item_id = wdd.inventory_item_id
AND oq.organization_id = wdd.organization_id
AND oq.subinventory_code = 'STAGE')
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
ORDER BY 1, 2


2. Perform subinventory transfers as needed to transfer the quantity identified by the column "change_qty" into the staging subinventory

R12: Change Application user password and end date from sqlplus

R12: Change Application user password and end date from sqlplus

DECLARE
v_user_name VARCHAR2(80) :'XXABC';
v_new_password VARCHAR2(80) := 'oracle123';
v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => 'N');
-- Call the procedure
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_unencrypted_password => v_new_password
,x_end_date => SYSDATE + 10000);
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
RAISE;
END;

OM Useful Tips

Allocation Error During Transact Move Order
"Error: The material sourcing process failed to create picking suggestions for line 2 of move order number 59641464"
"APP-INV-05297: Invalid transaction and serial control combination"
Encountered the above error message after clicked on "Allocate" button in Transact Move Orders form. Even after I backordered the line, I could not proceed to Pick Release, failed Pick Release.
Root Cause: Item lot reserved for the order line is no more valid.
Action: Unreserve the invalid item lot, pick release, transact move order & ship confirm successfully.

Cannot Perform Shipping Transactions At Shipping Transactions form, Action combo box is disabled. Can't perform pick release, create delivery, etc.
Grant role to user at OM responsibility. At Shipping Execution Grants form, add record, enter user name & save.
Setup -> Shipping -> Grants and Role Definitions -> Grants
Default role is "Upgrade Role", which you can maintain it in
Setup -> Shipping -> Grants and Role Definitions -> Define Roles


Launch Pick Release Failed My Sales Order (SO) has been booked successfully. Order header status is Booked, order line status is Awaiting Shipping. Quantity on-hand is sufficient. At Shipping Transactions form, I performed "Launch Pick Release", order line status remained Awaiting Shipping, it did not change to Picked.
No warning or error was prompted.
In my case, it was due to Credit Hold. Go Order Header -> Actions -> Additional Order Information, I saw Hold with message "Credit check hold applied. Overall limit exceeded.". GoActions -> Release Holds to release hold before proceed to Pick Release.
In order to perform Release Holds action, you need to be given authorities. Get the Holds Name and check the authorizations at Setup -> Orders -> Holds.


Where to Setup New Sales Order Type Under Order Management responsibility, Setup -> Transactions Types -> Define

Pick List Ship-To Address Not Get Updated
It is an address issue.
The Ship-to Address appears on the Pick List (for ISO) is retrieved from wsh_locations table. As for IR-ISO setup, ship-to-address comes from Customer address instead of Location address.

When we updates the customer address, hz_locations table get update. By right, bothhz_locations table and wsh_locations table should be synchronized automatically. But, in my case, it was not. Business event "oracle.apps.ar.hz.Location.update" is enabled, everything else is fine. But the synchronize is not happening.

Logged a tar and Oracle came back with suggestion to run "Import Shipping Location" program in OM responsibility to synchronize the table. These was their reply (copied from some another article).

"...The 'Import Shipping locations' concurrent program imports all of the internal and external
locations from HR/HZ LOCATIONS tables into the WSH_LOCATIONS tables.

This program should be executed at least once when upgrading to 11.5.9. After the
upgrade, it should not be required to rerun this concurrent program since any
changes in the HR/HZ LOCATIONS tables are automatically synced up with WSH
tables.

There are a few exceptions when Import Shipping Locations concurrent program should be run. These exceptions would be only when any updates to a
Customer's information does not get reflected when creating a new sales order
or when you find any inconsistencies in Location records when interfaced from
HR to WSH Locations, such as an incorrect country code..."

Question is the auto-sync is not happening and Oracle can't give reason why.

End up I have to run "Import Shipping Location" program to solve the issue (and it took 3.5 hours to complete with the Start-Date value = sysdate-4).


HL Pick Slip SRS Report -> Completed Warning After Pick Release action, pick slip report will get submitted, but it often completed warning.
How to know if actual picking taken place? Picking could be failed or success even the program completed warning. E.g. For printing issue, even it completed warning, but the stock has been picked.
From Requests list, click on the request, view Output. If there is records there, it was successfully picked. Another faster way, observe the request in the list, if the 4th and 5th parameters have value, it is successful.



Unable to Pick Release Internal Sales Order (ISO)
ISO was created from Internal Requisition (IR), order line status is "Awaiting Shipping", shipment line status is "Ready to Release", next step is "Pick Release". Performed Pick Release, shipment line status is not changed. As usual, no error shown in the log.
After investigation, found onhand no issue, inventory period is opened, what else could be the cause?
Ans: The ISO has tied to subinventory ABC, but the manual Pick Release is picked from subinventory DEF. So, change the "Pick From" subinventory to ABC, problem solved. Note: ISO subinventory is defaulted from IR subinventory.


Meaning of wsh_new_deliveries.released_status
Meaning of wsh_new_deliveries.released_status field value, reproduced from Document406189.1.
B Backordered Line failed to be allocated in Inventory
C Shipped Line has been shipped
D Cancelled Line is Cancelled
N Not ready for release Line is not ready to be released
R Ready to release Line is ready to be released
S Released to Warehouse Line has been released to Inventory for processing
X Not Applicable Line is not applicable for Pick Release
Y Staged Line has been picked and staged by Inventory


Order Shipping Line Status Stuck in "Shipped", Next Step "Run Interfaces" Obviously "Interface Trip Stop" program has been ran, but status is not change. What could be the reason behind?
Check error records in Transaction Open Interface (Transactions->Transaction Open Interface). Any error stuck with error in the interface will cause this issue.
Table:
mtl_transactions_interface
Fields:
process_flag => 3, if error
organization_id => Inventory Organization
error_code

OE_ORDER_HEADERS_ALL -> SOLD_TO_ORG_ID and SHIP_TO_ORG_ID At the Order Header table, SOLD_TO_ORG_ID refers to customer_id whereas SHIP_TO_ORG_ID is the ORGANIZATION_ID from OE_SHIP_TO_ORGS_V. From the SHIP_TO_ORG_ID field, we can trace back the actual ship-to address for a particular order.
Before we can use OE_SHIP_TO_ORGS_V view in sql, we have to first initialize the view. However, we can skip it by going direct to the tables.

--to get actual ship to address for a Sales Order
select hl.*
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123
order by cs.site_use_id desc

--to get order base on ship to address
-- cs.site_use_id equals oe_order_headers_all.ship_to_org_id
select * from apps.oe_order_headers_all where ship_to_org_id in (
select cs.site_use_id
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 )
and order_type_id=2345



Validation failed for the field - Ship To
Encountered this error during Order Copy (at header level).
Root Cause:
Customer Site Location is deactivated.
Fix:
Activate it. Customer->Standard. Choose the correct address base on the ship to address shown in the sale order, click "Open". Identify "Ship To" underUsage column, check the Active checkbox.
Some useful backend table for these:
Take oe_order_headers_all.ship_to_org_id to hz_cust_site_uses_all.site_use_id, hz_cust_site_uses_all.location field refers Customer Site Location.
We can get the Customer Site Number by using
select ps.party_site_number
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 and
cs.site_use_id = 12345678


Query Manager for Shipping Transactions Form
Org Code is not defaulted to the OU's inventory organization, but it is defaulted to another inventory organization which has no relationship with current OU. Somemore, Org Code field is disabled. Why?
Ans: Setup->Shipping->Grants and Role Definitions->Grants. Grant to your Oracle user id, the Org Code will be enabled with blank. Set the Org Code from there (the field is optional).



Shipping Method - Lookups
To look at the complete list of Shipping Method, you need to go to Setup->Organizations->Shipping Methods in Inventory responsibility. It is a System Lookups, type: SHIP_METHOD.You can add/modify/remove Shipping Method there.

Picked Quantity Auto Transferred Back After Backordered?
Picked quantity (Pick Release process) will NOT be transferred back automatically to original locator/subinventory after Backordered transaction.


1 Delivery Has Many Delivery Lines, How to Backorder Only 1 Delivery Line
At Shipping Transactions form, query the delivery line by order number and item code (assuming this item has only 1 delivery line), enter the Shipped Qty as 0, Backordered Qty as the total quantity at the delivery line.Click Delivery tab, select "Ship Entered Quantities", press OK. If a delivery has multiple lines, only the selected line will be backordered.

Setting Up Internal Customer for IR-ISO
How does customer get recognized during Internal Sales Order (ISO) creation?
Ans: Location. In IR, there is a Ship-to-Location, and this internal location must tie to the same location defined in Customer Address (Customers->Addresses->Location->Internal Location). Each unique location can only assigned to only 1 customer and 1 address.Table for customer address and location: RA_SITE_USES_ALL

Auto Allocation in Transact Move Order for a Sales Order If the SO shipment status is now "Released to Warehouse", we need to perform Transact Move Orders. For the Move Order, which setup trigger auto allocation?
Ans: In the Release Rules form, Inventory tab, Auto Allocate selection (Yes/No)


No Delivery Detail Found After Ship-Confirm action, shipping line status is Closed, Interface Trip Stop program completed Warning, with warning message "No Delivery Detail Found" in the log file.
Where to rectify this?
Ans: Go Inventory responsibility, Transactions->Transaction Open Interface, check for any errors in the shipping organization. Rectify from there.


Pick Release in Shipping Transactions Form This is an alternate way to perform pick release at a place other than Release Sales Ordersform.
Shipping->Transactions
Only Lines/LPNs tab is enabled, other tabs like "Delivery", "Path by Stop" & "Path by Trip" are disabled as picking is not done yet. Go "Action" LOV, choose "Launch Pick Release", clickGo button.


Internal Requisition Approved, Internal Sales Order Created and Picked, Need to Cancel All, How? Ans: First perform backorder for ISO, cancel the ISO, then cancel the IR. Do remember to move back the inventory from staging to picking.

Collection from unknown author ( oraclea2z )

Concurrent Manager Queries

/* Managers with their corresponding Oracle and System Process Id''s*/


SELECT DISTINCT concurrent_process_id cpid, gvs.inst_id INSTANCE,
gvp.pid opid, os_process_id osid,
q.user_concurrent_queue_name manager, p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p,
fnd_concurrent_queues_vl q,
gv$process gvp,
gv$session gvs
WHERE q.application_id = queue_application_id
AND (q.concurrent_queue_id = p.concurrent_queue_id)
AND (gvs.process = os_process_id)
AND (gvp.addr = gvs.paddr)
AND process_status_code NOT IN ('K', 'S')
ORDER BY gvs.inst_id,
p.node_name,
concurrent_process_id,
q.user_concurrent_queue_name;


/*Managers that are having problems*/

SELECT DISTINCT concurrent_process_id cpid, oracle_process_id opid,
os_process_id osid, user_concurrent_queue_name manager,
p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p, fnd_concurrent_queues_vl q
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND p.concurrent_queue_id <> 1
AND (os_process_id) NOT IN (SELECT gvs.process
FROM gv$session gvs
WHERE gvs.process IS NOT NULL)
AND ( process_status_code = 'A'
OR process_status_code = 'R'
OR process_status_code = 'T'
)
ORDER BY os_process_id,
concurrent_process_id,
q.user_concurrent_queue_name;



/*Managers that are not active at this instant*/

SELECT application_name, concurrent_queue_id qid,
user_concurrent_queue_name manager, node_name node
FROM fnd_concurrent_queues_vl q, fnd_application_vl a
WHERE a.application_id = q.application_id
AND max_processes = 0
AND running_processes = 0
AND q.concurrent_queue_id <> 1
ORDER BY application_name, user_concurrent_queue_name;

Setup to perform OA Page personalization

To perform OA Page personalization a profile option (“Personaliz Self-Service Defn”) has to be assigned to a user login.
Navigation: “Application Administrator” Responsibility --> Application Administrator -->Profile
Here in this screen select ‘User’ Checkbox and then select your login user name and Select the Profile option “Personaliz Self-Service Defn” in the Profile text box. After selecting the profile option, click on Find button. This action opens System profile options screen.
In the screen select ‘Yes’ Option from the LOV corresponding to the user column.
Then save and close the form.

How to get table information? How to get About this Page link in OA page in R12

How to get table information? How to get About this Page link in OA page in R12

Applies to:
OA Pages Version: 12.0.0
Information in this document applies to any platform.

Goal
Q1: How to find supplier related view / table information in web page environment?
Q2: How to get the About this Page link in OA page?

Solution
A1: Please follow the following steps to get the Supplier related table information:
> Navigate to the Supplier Page
> Click on the About this page link
> Click Expand All
> You will be seeing SuppSrchVO, SupplierVO and SitesVO
> If you click on that view it will show you the query used.

A2: To get the About this Page Link:

Please ensure the below Profile option is set to Yes at site level:
FND: Diagnostics
System Administrator > Profile > System

The FND: Diagnostics profile option controls whether the Diagnostics button is rendered.
It also controls the display of the About this Page link.

You may also set below

Personalize Self-Service Defn : Yes
FND: Personalization Region Link Enabled : Yes
FND: Diagnostics : Yes

Add the below responsibilities to the user:
System Administrator > Security > User > Define

1. FND Html Forms
2. Functional Administrator
3. Functional Developer
4. Once done bounce the apache
5. Retest the issue
6. Migrate the solution to other environments as appropriate.