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.

iRecruitment workflow

How to view Item Key and Workflow internal name for iRecruitment vacancy related workflows

SELECT transaction_document
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9876;

(drop the 'IRC' prefix when using transaction_ref_id).

The following query will return you item_type,item_key thorugh which you can search the workflow

SELECT item_type,item_key
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9204;

select * from PER_ALL_VACANCIES
where name ='IRC9204'

How to clear the Cache in R12 JSP Pages

How to clear the Cache in R12 JSP Pages

Logon to oracle applications than navigate to

Functional Administrator-->Home--> Click on 'Core Services'--> click on 'Caching Framework'

Here you can clear Cache.

This is useful when you add responsibilities to your user or want to clear any cache

Operating Unit and Inventory Organization link

Query which gives Operating Unit Information and corresponding Inventory Orgs related information also

SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

How to Delegate to enter the expense for some other person

Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.

Tehnical Flow of OM

Understanding data flow for “Standard Order”


1. Order Entry
This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.
•oe_order_headers_all (Here the flow_status_code as entered)
•oe_order_lines_all (flow_status_code as entered) ( order number is generated)

2.Order Booking
This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.
•oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
•oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
•wsh_new_deliveries (status_code OP open)
•wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.
Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
•oe_order_lines_all (flow_status_code ‘PICKED’ )
•wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
•mtl_txn_request_headers
•mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
•Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

5.Pick Confirm
Items are transferred from saleble to staging Subinventory.
•mtl_material_transactions
•mtl_transaction_accounts
•wsh_delivery_details (released_status ‘Y’‘Released’ )
•wsh_delivery_assignments

6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries
•oe_order_lines_all (flow_status_code ‘shipped’)
•wsh_delivery_details (released_status ‘C’ ‘Shipped’)
•mtl_transaction_interface
•mtl_material_transactions(linked through Transaction source header id)
•mtl_transaction_accounts
•Data deleted from mtl_demand,mtl_reservations
•Item deducted from mtl_onhand_quantities

7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details.
•Invoicing workflow activity transfers shipped item information to Oracle Receivables.
•ra_interface_lines_all (interface table into which the data is transferred from order management)T
•Then Autoinvoice program imports data from this
•Table which get affected into this stage are recievables base table.
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.
•oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.
These are the table get affected in this step.
•oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)
These are the typically data flow of a order to cash model for a standard order.

Oracle XML Publisher Bursting Sample Program

Here we are trying to send the PDF output to Server Output Folder.

xapi:delivery - Explains where the output should be delivered
xapi:filesystem - explains the output is destined to filesystem
xapi:template - specifies the template shortcode prefixed with app short code, for eg: AR.INVOICE - AR-app short code and INVOICE is the xml template short code.

location="xdo://AR.INVOICE.en.US/?getSource=true"

Above line is very important, it takes care of pulling the latest template uploaded in the XML Publisher Administrator responsibility, you don't need to put your template hanging in any UNIX servers.

en - Language
US - Territory code (when you create template definition, you make sure that you select United States or other country)

The same above example holds good for sending the output through email, fax, etc.,
By: chandramouly

How to Create a Barcode in Oracle Reports

How to Create a Barcode in Oracle Reports please view below link


http://www.youtube.com/watch?v=vvQOp1YmOIc

views in Oracle Property Manager Module

List of views in Oracle Property Manager Module

PN_ACT_RENT_DETAILS_V
PN_ADDRESSES_ALL_DFV
PN_ADJUSTMENT_DETAILS_V
PN_AEL_BILL_GL_V
PN_AEL_PAY_GL_V
PN_BUILDINGS_V
PN_COMPANIES_ALL_DFV
PN_COMPANIES_V
PN_COMPANY_SITES_ALL_DFV
PN_COMPANY_SITES_V
PN_CONTACT_ASSIGN_HISTORY_V
PN_CONTACT_ASSIGNMENTS_ALL_DFV
PN_CONTACT_ASSIGNMENTS_V
PN_CONTACTS_ALL_DFV
PN_CONTACTS_V
PN_DISTRIBUTIONS_ALL_DFV
PN_DISTRIBUTIONS_HISTORY_V
PN_DISTRIBUTIONS_V
PN_EXCLUDE_TERMS_V
PN_EXP_PAYMENT_ITEMS_V
PN_FLOORS_V
PN_FOR_RENT_DETAILS_V
PN_GL_PERIOD_V
PN_INDEX_EXCLUDE_TERM_V
PN_INDEX_HISTORY_HEADERS_DFV
PN_INDEX_HISTORY_HEADERS_V
PN_INDEX_HISTORY_LINES_V
PN_INDEX_LEASE_CONSTRAINTS_DFV
PN_INDEX_LEASE_CONSTRAINTS_V
PN_INDEX_LEASE_PERIODS_ALL_DFV
PN_INDEX_LEASE_PERIODS_V
PN_INDEX_LEASES_ALL_DFV
PN_INDEX_LEASES_V
PN_INSUR_REQUIRE_HISTORY_V
PN_INSURANCE_REQUIREMENTS__DFV
PN_INSURANCE_REQUIREMNTS_V
PN_JE_CATEGORIES_V
PN_LANDLORD_SERVICE_HISTORY_V
PN_LANDLORD_SERVICES_ALL_DFV
PN_LANDLORD_SERVICES_V
PN_LEASE_CHANGES_ALL_DFV
PN_LEASE_CHANGES_V
PN_LEASE_CONTACT_ASSIGN_V
PN_LEASE_DETAILS_HISTORY_V
PN_LEASE_DETAILS_V
PN_LEASE_MILESTONES_ALL_DFV
PN_LEASE_MILESTONES_V
PN_LEASE_TRANSACTIONS_V
PN_LEASES_V
PN_LOC_ACC_MAP_HDR_V
PN_LOC_ACC_MAP_V
PN_LOCATION_CONTACT_ASSIGN_V
PN_LOCATION_FEATURES_ALL_DFV
PN_LOCATION_FEATURES_V
PN_LOCATION_PARKS_DFV
PN_LOCATION_PARKS_VL
PN_LOCATIONS_ALL_DFV
PN_LOCATIONS_ITF_V
PN_LOCATIONS_PUB_V
PN_LOCATIONS_V
PN_NOTE_DETAILS_VL
PN_NOTE_HEADERS_V
PN_OFFICES_V
PN_OPTIONS_ALL_DFV
PN_OPTIONS_HISTORY_V
PN_OPTIONS_V
PN_PAY_GROUP_BYS_V
PN_PAY_GROUP_RULES_V
PN_PAYMENT_ITEMS_V
PN_PAYMENT_SCHEDULES_ALL_DFV
PN_PAYMENT_SCHEDULES_V
PN_PAYMENT_TERMS_ALL_DFV
PN_PAYMENT_TERMS_HISTORY_V
PN_PAYMENT_TERMS_V
PN_PBUILDING_V
PN_PHONES_ALL_DFV
PN_PHONES_V
PN_PLAND_V
PN_PROPERTIES_ALL_DFV
PN_PROPERTIES_V
PN_RIGHTS_ALL_DFV
PN_RIGHTS_HISTORY_V
PN_RIGHTS_V
PN_SET_MILESTONES_VL
PN_SET_TYPES_VL
PN_SPACE_ALLOCATIONS_ALL_DFV
PN_SPACE_ALLOCATIONS_V
PN_SPACE_ASSIGN_CUST_ALL_DFV
PN_SPACE_ASSIGN_CUST_PUB_V
PN_SPACE_ASSIGN_CUST_V
PN_SPACE_ASSIGN_EMP_ALL_DFV
PN_SPACE_ASSIGN_EMP_PUB_V
PN_SPACE_ASSIGN_EMP_V
PN_TENANCIES_ALL_DFV
PN_TENANCIES_HISTORY_V
PN_TENANCIES_V
PN_TERM_HISTORY_HEADER_V
PN_TERM_TEMPLATES_ALL_DFV
PN_TERM_TEMPLATES_V
PN_TRX_LEASE_EXPENSE
PN_TRX_LEASE_REVENUE
PN_VAR_ABAT_DEFAULTS_V
PN_VAR_BKDT_DEFAULTS_V
PN_VAR_BKHD_DEFAULTS_V
PN_VAR_BKPTS_DET_ALL_DFV
PN_VAR_BKPTS_DET_V
PN_VAR_BKPTS_HEAD_ALL_DFV
PN_VAR_BKPTS_HEAD_V
PN_VAR_CONSTR_DEFAULTS_V
PN_VAR_CONSTRAINTS_ALL_DFV
PN_VAR_CONSTRAINTS_V
PN_VAR_DEDUCTIONS_ALL_DFV
PN_VAR_DEDUCTIONS_V
PN_VAR_LINE_DEFAULTS_V
PN_VAR_LINE_TEMPLATES_V
PN_VAR_LINES_ALL_DFV
PN_VAR_LINES_DATES_V
PN_VAR_LINES_V
PN_VAR_PERIODS_ALL_DFV
PN_VAR_PERIODS_DATES_V
PN_VAR_PERIODS_V
PN_VAR_RENT_ADJ_V
PN_VAR_RENT_DATES_V
PN_VAR_RENT_DETAILS_V
PN_VAR_RENT_INV_ALL_DFV
PN_VAR_RENT_INV_V
PN_VAR_RENT_SUMM_V
PN_VAR_RENTS_ALL_DFV
PN_VAR_RENTS_V
PN_VAR_SUMM_DET_V
PN_VAR_TEMPLATES_V
PN_VAR_TERMS_V
PN_VAR_VOL_HIST_ALL_DFV
PN_VAR_VOL_HIST_V
PN_XLA_EXTRACT_HEADERS_V
PN_XLA_EXTRACT_LINES_V

Oracle Property Manager Tables

Oracle Property Manager Tables:

PN_ACCOUNTING_EVENTS_ALL
PN_ADDRESSES_ALL
PN_ADJUSTMENT_DETAILS
PN_ADJUSTMENT_SUMMARIES
PN_AE_HEADERS_ALL
PN_AE_LINES_ALL
PN_COMPANIES_ALL
PN_COMPANY_SITES_ALL
PN_CONTACTS_ALL
PN_CONTACT_ASSIGNMENTS_ALL
PN_CONTACT_ASSIGN_HISTORY
PN_CURRENCIES
PN_DISTRIBUTIONS_ALL
PN_DISTRIBUTIONS_HISTORY
PN_EMP_SPACE_ASSIGN_ITF
PN_INDEX_EXCLUDE_TERM_ALL
PN_INDEX_HISTORY_HEADERS
PN_INDEX_HISTORY_LINES
PN_INDEX_LEASES_ALL
PN_INDEX_LEASE_CONSTRAINTS_ALL
PN_INDEX_LEASE_PERIODS_ALL
PN_INDEX_LEASE_TERMS_ALL
PN_INSURANCE_REQUIREMENTS_ALL
PN_INSUR_REQUIRE_HISTORY
PN_LANDLORD_SERVICES_ALL
PN_LANDLORD_SERVICE_HISTORY
PN_LEASES_ALL
PN_LEASE_CHANGES_ALL
PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY
PN_LEASE_MILESTONES_ALL
PN_LEASE_OPTIONS_ITF
PN_LEASE_TRANSACTIONS_ALL
PN_LOCATIONS_ALL
PN_LOCATIONS_ITF
PN_LOCATION_FEATURES_ALL
PN_LOCATION_PARKS
PN_LOC_ACC_MAP_ALL
PN_LOC_ACC_MAP_HDR_ALL
PN_MILESTONES_ITF
PN_NOTE_DETAILS
PN_NOTE_HEADERS
PN_OPTIONS_ALL
PN_OPTIONS_HISTORY
PN_PAYMENT_ITEMS_ALL
PN_PAYMENT_ITEMS_EFC
PN_PAYMENT_SCHEDULES_ALL
PN_PAYMENT_TERMS_ALL
PN_PAYMENT_TERMS_EFC
PN_PAYMENT_TERMS_HISTORY
PN_PAY_GROUP_BYS
PN_PAY_GROUP_RULES
PN_PHONES_ALL
PN_PROPERTIES_ALL
PN_REC_AGREEMENTS_ALL
PN_REC_AGR_LINABAT_ALL
PN_REC_AGR_LINAREA_ALL
PN_REC_AGR_LINCONST_ALL
PN_REC_AGR_LINES_ALL
PN_REC_AGR_LINEXP_ALL
PN_REC_ARCL_ALL
PN_REC_ARCL_DTLLN_ALL
PN_REC_ARCL_DTL_ALL
PN_REC_ARCL_EXC_ALL
PN_REC_CALC_PERIODS_ALL
PN_REC_EXPCL_ALL
PN_REC_EXPCL_DTLACC_ALL
PN_REC_EXPCL_DTLLN_ALL
PN_REC_EXPCL_DTL_ALL
PN_REC_EXPCL_INC_ALL
PN_REC_EXPCL_TYPE_ALL
PN_REC_EXP_ITF
PN_REC_EXP_LINE_ALL
PN_REC_EXP_LINE_DTL_ALL
PN_REC_LINBILL_ALL
PN_REC_PERIOD_BILL_ALL
PN_REC_PERIOD_LINES_ALL
PN_RENT_ROLL_LEASE_EXP_ITF
PN_RIGHTS_ALL
PN_RIGHTS_HISTORY
PN_SET_MILESTONES
PN_SET_TYPES
PN_SPACE_ALLOCATIONS_ALL
PN_SPACE_ALLOC_ITF
PN_SPACE_ASSIGN_CUST_ALL
PN_SPACE_ASSIGN_EMP_ALL
PN_SPACE_ASSIGN_LEASE_ITF
PN_SPACE_ASSIGN_LOC_ITF
PN_SPACE_UTIL_LEASE_ITF
PN_SPACE_UTIL_LOC_ITF
PN_SYSTEM_SETUP_OPTIONS
PN_TENANCIES_ALL
PN_TENANCIES_HISTORY
PN_TERM_TEMPLATES_ALL
PN_VAR_ABATEMENTS_ALL
PN_VAR_BKPTS_DET_ALL
PN_VAR_BKPTS_HEAD_ALL
PN_VAR_CONSTRAINTS_ALL
PN_VAR_DEDUCTIONS_ALL
PN_VAR_GRP_DATES_ALL
PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL
PN_VAR_VOL_HIST_ALL
PN_VOL_HIST_BATCH_ITF
PN_VOL_HIST_LINES_ITF

Combining and Compressing Files at UNIX

Combining and Compressing Files

Create a tarfile command : tar cf file.tar file1 file2 ...fileN
tar combines files but does not compress
Create a zipfile zip filename
Unzip a file unzip filename


In Unix, how do I create or decompress zip files?
To create a zip file, at the Unix prompt, enter:
zip filename inputfile1 inputfile2

Replace filename with the name you want to give the zip file. The .zip extension is automatically appended to the end of the filename.
Replace inputfile1 and inputfile2 with the names of the files you wish to include in the zip archive. You can include any number of files here,
or you may use an asterisk (*) to include all files in the current directory.

To include the contents of a directory or directories in a zip archive, use the -r flag:
zip -r filename directory

Replace directory with the name of the directory you want to include. This will create the archive filename.zip that contains the files and
subdirectories of directory.

Files created by zip can normally be decoded by programs such as WinZip and StuffIt Expander.

To decompress a zip file in Unix, use the unzip command. At the Unix prompt, enter:
unzip filename Replace filename with the name of the zip archive.

For more information about zip and unzip, see their manual pages:
man zip
man unzip

R12 Banks Queries

R12 Banks Queries

SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;


SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

Delete Duplicate Records in Oracle

Delete Duplicate Records in Oracle

There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);

INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);

SELECT * FROM dup_test;
Use subquery to delete duplicate rows:

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:

You can also detect and delete duplicate rows using Oracle analytic functions:

DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:

This is the simplest method to remove duplicity.

CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.

Supplier Conversion in R12

Supplier Conversion in R12
Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.

Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).

The Interface Tables are:

AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIERS_INT:

This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.

Mandatory Columns:

VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
VENDOR_NAME – Supplier name
Other important columns:

SEGMENT1 – Supplier Number
VENDOR_TYPE_LOOKUP_CODE – Supplier type
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
TERMS_NAME – Payment terms name
TAX_VERIFICATION_DATE – Tax verification date(1099)
VAT_REGISTRATION_NUM – Tax registration number
ATTRIBUTE1 -15 – Descriptive Flexfield Segments
PAY_GROUP_LOOKUP_CODE – Payment group type
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
NUM_1099 – Tax identification number (1099)
VAT_CODE – Default invoice tax code
HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
SUMMARY_FLAG – Key flexfield summary flag
ENABLED_FLAG – Key flexfield enable flag
EMPLOYEE_ID – Employee unique identifier if supplier is an employee
AP_SUPPLIER_SITES_INT:

This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.

Mandatory Columns:

VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:

ADDRESS_LINE1 – First line of supplier address
ADDRESS_LINE2 – Second line of supplier address
ADDRESS_LINE3 – Third line of supplier address
CITY – City name
STATE – State name or abbreviation
ZIP – Postal code
COUNTRY – Country name
PHONE – Phone number
FAX – Supplier site facsimile number
SHIP_TO_LOCATION_CODE – Default ship-to-location name
BILL_TO_LOCATION_CODE – Default bill-to-location name
PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
VAT_CODE – Invoice default tax code
PAY_GROUP_LOOKUP_CODE – Payment group type
TERMS_NAME – Payment terms name
INVOICE_CURRENCY_CODE – Default currency unique identifier
PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
EMAIL_ADDRESS – E-mail address of the supplier contact
PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold
AP_SUP_SITE_CONTACT_INT:

This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE and ORG_ID.

Mandatory Columns:

VENDOR_INTERFACE_ID – Supplier interface record unique identifier
VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
VENDOR_SITE_CODE – Supplier Site name
Other important columns:

FIRST_NAME – Contact First name
LAST_NAME – Contact last name
AREA_CODE – Area code of contact phone number
PHONE – Contact phone number
FIRST_NAME_ALT – Alternate Supplier contact first name
LAST_NAME_ALT – Alternate Supplier contact last name
EMAIL_ADDRESS – Email address for the Supplier Site contact
FAX – Facsimile number for the Supplier Site contact
VENDOR_ID – Supplier unique identifier
Validations:

Vendor Number (Check for duplicate records in ap_suppliers table)
Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
Terms Name (Check for proper record in ap_terms_tl table)
Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
Employee Id (Check for proper employee record in per_all_people_f table)
Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
Country Code ( Check for proper country code in fnd_territories_vl table)
Payment Method (Check for proper payment method in iby_payment_methods_vl table)
Interface programs:

Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
The data inserted via these interfaces are automatically populated into TCA tables.

Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.

Oracle R12 Banks Accounts Query

Oracle R12 Bank Accounts Query

SELECT owners.account_owner_party_id, asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM hr_all_organization_units
WHERE TYPE = 'OU' AND organization_id = asa.org_id) ou_name,
asa.vendor_site_code, ieb.country_code
--, (select bank_name from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_name
--, (select bank_number from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_number
--, ieb.branch_id
, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name,
cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code,
ieb.bank_account_num, ieb.currency_code, ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt bankacct_fromdata
FROM iby_pmt_instr_uses_all instrument,
iby_account_owners owners,
iby_external_payees_all payees,
iby_ext_bank_accounts ieb,
ap_supplier_sites_all asa,
ap_suppliers asp,
ce_bank_branches_v cbbv
WHERE owners.primary_flag = 'Y'
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id = instrument.ext_pmt_party_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
AND cbbv.branch_party_id(+) = ieb.branch_id
--and asa.org_id in (125)
AND asp.created_by = 1451
ORDER BY 4, 1, 3

Debugging Concurrent Programs

Concurrent Programs

For concurrent programs you can place in Log file / output file / fnd_log_messages table


For Log File
FND_FILE.PUT_LINE(FND_FILE.LOG,'your log statement');


For Output File
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'your output statement');


For Tracing it in a table FND_LOG_MESSAGES


fnd_log.string(log_level => fnd_log.level_statement
,module => 'custom module'
,message => 'you log message here');

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions
This article containts various Workflow and Business Event debugging scripts.


--Checking workflow Components status wheather are they running or stopped.
select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components
where component_type like 'WF%'
order by 1 desc,2,3;


--Query to get the log file of active workflow mailer and workflow agent listener Container
--Note All Workflow Agent Components logs will stored in single file ie. container log file.
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;



--Linux Shell script Command to get outbound error in Mailer
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

--Linux Shell script Command to get inbound processing error in Mailer
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;


---Query to Check Workflow Mailer Backlog
--State=Ready implies that emails are not being sent & Waiting mailer to sent emails
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


--Check any particular Alert Message email has be pending by Mailer
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%%';


--Check The Workflow notification has ben sent or not
select mail_status, status from wf_notifications where notification_id=
--If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by loggin in application + click on preference + the notification preference


--Check Wheather workflow background Engine is workfing for given workflow or not in last 2 days
-- Note: Workflow Deferred activities are run by workflow background engine.
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1=''
order by last_update_date desc

-- Check wheather any business event is pending to process
-- ie. Query to get event status & parameters value of particular event in wf_deferred table.
select wd.user_Data.event_name,wd.user_Data.event_key,
rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,
n.NAME Parameter_name, N.value Parameter_value ,
decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
where lower(wd.user_data.event_name)=''
order by wd.user_Data.event_name, wd.user_Data.event_key, n.name

Debugging OA pages

Debugging OA pages


a. Enable the profile option : FND: Debug Log Enabled -- Set to Yes
b. Enable the profile option : FND: Debug Log Level -- Set to Statement level
c. Add the below piece of code in your OA page
boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT);
if (isLoggingEnabled)
{
pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
}
To see log stmt on browser append below string to browser url and click on enter


&aflog_level=statement

R12 Suppliers Query

SELECT DISTINCT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
ASp.vendor_name "Supplier Name" ,
ASP.ATTRIBUTE15 "HDRS LEGACY SUPP NO" ,
ass.vendor_site_code "SITE NAME" ,
hou.NAME "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
ASS.ATTRIBUTE15 "SITES LEGACY SUPP NO" ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.EMAIL_ADDRESS
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE 1 = 1
--AND ASP.VENDOR_NAME = PERSON.PARTY_NAME
AND ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.ORG_PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASP.ATTRIBUTE15 IN ('1240')
ORDER BY hou.NAME

HRMS Technical Site

Below site has HRMS Technical related stuff

http://wenku.baidu.com/view/d0a1d13383c4bb4cf7ecd10a.html

R12 Supplier Banks

In order to load Supplier external bank, branches and bank accounts, one needs to use the Oracle Supplied Package IBY_EXT_BANKACCT_PUB.

Below API's are used to create Bank and Branch

IBY_EXT_BANKACCT_PUB.create_ext_bank:

It is used to create the External Bank, please note that the Bank name and Home Country Name are mandatory for creating an External Bank. Once you create the Bank, Bank Party ID gets created and you can check it from IBY_EXT_BANKS_V view.

IBY_EXT_BANKACCT_PUB.create_ext_bank
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_rec => x_bank_rec
,x_bank_id => x_bank_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

It is used to create a Bank Branch, so that an account could be created in the same branch. Once a Bank Branch is created, a record gets inserted into IBY_EXT_BANK_BRANCHES_V view.

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_branch_rec => x_bank_branch_rec
,x_branch_id => x_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

After the bank and branches are created, the table iby.iby_temp_ext_bank_accounts can be populated to create the bank accounts and associate to the supplier or supplier site

Note: Table must be populated prior to running the supplier interface

Below API's are used internally by oracle to create payee and associate bank account to supplier or supplier site.

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_acct_rec => x_bank_acct_rec
,x_acct_id => x_acct_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_ext_payee_tab => v_external_payee_tab_type,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_ext_payee_id_tab => x_ext_payee_id_tab,
x_ext_payee_status_tab => x_ext_payee_status_tab);

IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_payee => x_rec
,p_assignment_attribs => x_assign
,x_assign_id => x_assign_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec);

From Amit Chintawar

R12 Supplier Payment Method Table/View

IBY_EXTERNAL_PAYEES_ALL : This stores supplier information and customer information
IBY_EXT_BANK_ACCOUNTS : This storage for bank accounts
IBY_EXT_PARTY_PMT_MTHDS : This storage for payment method usage rules.
IBY_CREDITCARD : stores the credit card information for a customer
IBY_EXT_BANK_ACCOUNTS :This Stores external bank accounts . These records have bank_account_type = Supplier
IBY_ACCOUNT_OWNERS :stores the joint account owners of a bank account
IBY_PMT_INSTR_USES_ALL : This stores data from AP_BANK_ACCOUNT_USES_ALL for payment instruments assignments .This information is stored in the following iPayment (IBY) tables:

New MetaLink eTRM Path

Knowledge (tab) --> Online Documentation (left side bottom) --> E-Business Suite Documentation --> Electronic Technical Reference Manual (eTRM)

How to execute VIEWS (AP_INVOICE_V) and see data in toad for R12.

How to execute VIEWS (AP_INVOICE_V) and see data in toad for R12.

begin
MO_GLOBAL.SET_ORG_CONTEXT('125',NULL,'SQLAP');
end;

commit

125--- Operating Unit

CUSTOM FORM DEVELOPMENT

On the CLIENT machine create a FOLDER as say: c:\custom_test

In custom_test folder creates 2 folders forms, resource

Copy TEMPLATE.fmb, APPSTAND.fmb, .pll files to CLIENT

• Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to c:\custom_test\ forms directory copy all .pll files from /Applvis/visappl/au/11.5.0/resource to c:\custom_test\resource using ftp
• On windows go to command prompt
• Cd c:\custom_test\forms
• ftp cloneserver
• username: applmgr password: applmgr
• now you are at ftp prompt
• bin
• prompt
• cd visappl/au/11.5.0/forms/US ( here apparently cd $AU_TOP does not work)
• get TEMPLATE.fmb
• (file copied)
• get APPSTAND.fmb
• (file copied)
• lcd ./resource (check this. Basically you need to be in c:\custom_test\resource. You can go to that directory and then run ftp)
• cd visappl/au/11.5.0/resource
• mget *.pll
• ( now all .pll files are copied to c:custom_test/resource)

SET env variable FORMS60_PATH through regedit

• Regedit/HKEY_LOCAL_MACHINE/software/oracle
• Double click FORMS60_PATH
• At the end of existing value data add: ;c:\custom_test\froms;c:\custom_test\resource

Now open TEMPLATE.fmb and make the following changes

• DELETE BLOCKNAME &DETAIL BLOCK FROM DATABLOCK,DELETE BLOCKNAME FROM CANVAS AND WINDOW
• Create a window NEW_WIN, canvas NEW_CAN
• Create a new block based on the table you created in your custom schema
• In pre-form trigger: app_windows.set_window_position(‘NEW_WIN’)…….
• In program units open custom_package.AP_cutom_pacakge body
• Change : if (wind=’NEW_WIN’);
• Template name = TEST_FORM
• Save as TEST_FROM to c:\custom_test\forms
• (????????SET THE WINDOW NAME AS U HAVE CREATED NEW WINDOW IN PRE-FORM TRIGGER BY BLOCKNAME?????)

DEPLOY the FORM (upload it to AU_TOP/forms/US)

• Go to command prompt (on client)
• cd c:\cutom_test\forms
• ftp cloneserver
• cd visappl/au/11.5.0/froms/US
• bin
• prompt
• put TEST_FORM.fmb


Changing ORACLE_HOME (/Visdb/visdb/9.2.0 to /Applvis/visora/8.0.6)

• thru putty login as applmgr
• pwd: /Applvis
• echo $ORACLE_HOME: shows /Visdb/visdb/9.2.0
• cd visora
• cd 8.0.6
• . VIS_cloneserver.env (this changes ORACLE_HOME apparently based on pwd?)
• echo $ORACLE_HOME: shows /Applvis/visora/8.0.6
• now ORACLE_HOME is 8.0.6 (forms/reports home)
• pwd : gives /Applvis/visora/8.0.6

COMPILE and generate FMX

• (now you are in /Applvis/visora/8.0.6 directory and ORACLE_HOME is set to /Applvis/visora/8.0.6)
• pwd: gives /Applvis/visora/8.0.6
• f60gen module=$AU_TOP/forms/US/TEST_FORMS.fmb module_type=form user=apps output_file=$TEST_TOP/forms/US/TEST_FORM.fmx compile_all=special batch=no
• this generates TEST_FORM.fmx and puts in TEST_TOP/forms/US

FORM REGISTRATION

• Login to applications with application developer responsibility
• Application/form
• Enter the following details
o Form: the fmx name (TEST_FORM)
o Application: Oracle Receivables (as per Amer) –give appropriate name based the intended use of this form
o user form name: TEST_FORM_U (this will appear in LOV)
o SAVE

Attach the FORM to FUNCTION
(Create a new function)
Application/function
Enter the following details
o Function: TEST_FUNCT
o User function name: TEST FUNCTION
o Form: TEST_FORM (previously registered)
o SAVE

Attach FUNCTION to MENU
Application/menu
• Enter the following details
o Menu: test_menu
o User menu name: test menu
o Seq: 1
o Prompt: test form
o Function: TEST_FUNCT( previously deined)
o SAVE

Attach MENU to RESPONSIBILITY

Attach RESPONSIBILITY to USER

Login as the new USER

SQL Interview Questions

1. The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
TRUNCATE - cleans all data
RENAME- renames a table name

2. Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern

3. SELECT statements:
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.

4. The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value

5. The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

6. The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value

7. The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name

8. Sort the Rows:
SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC

9. The IN operator may be used if you know the exact value you want to return for at least one of the columns.
SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

10. BETWEEN ... AND
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.

11. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

12. Why does the following command give a compilation error?
DROP TABLE &TABLE NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.

13. Which system tables contain information on privileges granted and privileges obtained? USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

14. Which system table contains information on constraints on all the tables created?obtained? USER_CONSTRAINTS.

15. State true or false. !=, <>, ^= all denote the same operation?
True.

16. State true or false. EXISTS, SOME, ANY are operators in SQL?
True.

17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?

18. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;?
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.

19. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

20. Which command executes the contents of a specified file?
START or @.

21. What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
sal = 11000, comm = 1000.

22. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN.

23. What command is used to get back the privileges offered by the GRANT command?
REVOKE.

24. What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );? NO.
Explanation : The query checks whether a given string is a numerical digit.

26. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN.

27. What operator performs pattern matching?
LIKE operator.

28. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.

29. What operator tests column for the absence of data?
IS NULL operator.

30. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all.

31. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR.

32. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL).

33. What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

34. What command is used to create a table by copying the structure of another table?
CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

35. TRUNCATE TABLE EMP;DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP..

36. What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.

37. What are the wildcards used for pattern matching.?
_ for single character substitution and % for multi-character substitution.

38. What is the parameter substitution symbol used with INSERT INTO command?
&

39. What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.

40. What is difference between TRUNCATE & DELETE
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

41. What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

42. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.

43. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.

44. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.

45. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

46. Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.

47. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

48. What is the fastest way of accessing a row in a table?
Using ROWID.
CONSTRAINTS

49. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.

50. What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

51. What is the usage of SAVEPOINTS?
SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

52. What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

53. What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

54. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

55. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

56. What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?
- To modify the datatype of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

57. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.

58. How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

59. If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
It won't, Because SYSDATE format contains time attached with it.

60. What is a database link?
Database link is a named path through which a remote database can be accessed.

61. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

62.What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

63. What are the advantages of VIEW?
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

64. Can a view be updated/inserted/deleted? If Yes - under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

65. If a view on a single base table is manipulated will the changes be reflected on the base table?
If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

66. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.

67. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

66. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.

67. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype

68. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT

69. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.

70. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.

71. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values

72. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
73. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)

74. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None

77. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger

78. Read the following code:
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;
RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;

79. When invoking this procedure, you encounter the error:
ORA-000:Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated
with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error
code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.

80. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END; You are about to add an argument to CALCULATE_BUDGET.
What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.

81. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR

82. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;

83. Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt

84. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN RETURN TRUEl; ELSE RETURN FALSE; END IF; COMMIT; END; Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.

85. Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs

86. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed

87. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE

88. What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section,the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.

89. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END; For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.

90. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL

91 How to implement ISNUMERIC function in SQL *Plus ? Method
1: Select length (translate(trim (column_name),'+-.0123456789',''))from dual; Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters) Method 2: select instr(translate('wwww','abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXX'),'X') FROM dual; It returns 0 if it is a number, 1 if it is not.

92 How to Select last N records from a Table? select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm) where a > ( select (max(rownum)-10) from clm) Here N = 10
The following query has a Problem of performance in the execution of the following
query where the table ter.ter_master have 22231 records. So the results are obtained
after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution
path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that
your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;

93. What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it
only checks for the existence (and status) of another foreign key Pointing to the
table. If one exists and is enabled, then you will get The following error. This
is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing
the TRUNCATE command, then re-enable them afterwards.