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.