Queryies for Inventory Transactions Pending

Inventory Transactions

a. Stuck interface transactions (Group By)

PROMPT Stuck Transactions - GroupBy MTI
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_transactions_interface
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);

b. Stuck pending transactions (Group By)

PROMPT Stuck Transactions - GroupBy MMTT
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_material_transactions_temp
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);

c. Stuck move order transactions (Group By)

PROMPT Stuck Transactions - GroupBy Move Order
select transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation,
count(*)
from mtl_material_transactions_temp
where organization_id = &Org_id
group by transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation;

d. Uncosted transactions (Group By)

PROMPT Uncosted Transactions - GroupBy MMT
select transaction_type_id, organization_id, costed_flag,
to_char(transaction_date, 'YYYY-MM'), error_code, substr(error_explanation, 1, 50),
count(*)
from mtl_material_transactions
where costed_flag IN ('N','E')
group by transaction_type_id, organization_id, costed_flag, to_char(transaction_date, 'YYYY-MM'),
error_code, substr(error_explanation, 1, 50);

e. Dump information about transaction tables

PROMPT Stuck Transactions Dump - MTI
select transaction_interface_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date, transaction_type_id,
transaction_source_id, transfer_subinventory, transfer_locator,
trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_transactions_interface
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMTT
select transaction_temp_id, inventory_item_id, organization_id, subinventory_code, locator_id, revision,
transaction_quantity, transaction_date, transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_to_location, trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_material_transactions_temp
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMT
select
transaction_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date,
transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_locator_id, trx_source_line_id, cost_group_id,
error_explanation, error_code,
from mtl_material_transactions
where costed_flag IN ('N','E')
order by transaction_source_id, trx_source_line_id;

query to check profile options at all levels

Values of a profile option

It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. The following prompts for the profile name that a user sees. You could also query for the internal profile code instead by using the column a.profile_option_name instead.
select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

query to find users who have a responsibility

PROMPT Find users who have a responsibility
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;

Query to check Active users

PROMPT Active Users
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;

Ensure trace is disabled

Ensure trace is disabled
The best way is to check via the forms System Administrator > Concurrent > Programs Define. This is just a quick update to change trace enabled to no.
Update Fnd_Concurrent_Programs
Set Enable_Trace = 'N'
where ENABLE_TRACE = 'Y';

Concurrent program statuses

Concurrent program statuses

PROMPT Concurrent program values

Select distinct lookup_code, meaning From Fnd_Lookup_Values
Where Lookup_Type = 'CP_STATUS_CODE'
order by lookup_code;

A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I Normal
M No Manager
P Scheduled
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

query for various output of concurrent managers

PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;

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;