================
Concurrent Request Status(QUERY)
Concurrent Request Status
SELECT fcr.phase_code,
DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code) phase,
fcr.status_code,
DECODE (fcr.status_code,'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
fcr.status_code) status,
request_date,
fat.description, frt.responsibility_name, fu.user_name,
fu.description, fcpt.user_concurrent_program_name, fcpt.description,
fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
fcr.printer, fcr.parent_request_id, fcr.description,
fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
fcr.outfile_node_name
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_concurrent_programs_tl fcpt
WHERE (fu.user_id = fcr.requested_by)
AND (fat.application_id = fcr.program_application_id)
AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
AND (fcr.responsibility_id = frt.responsibility_id)
AND fat.LANGUAGE = 'US'
AND frt.LANGUAGE = 'US'
AND fcpt.LANGUAGE = 'US'
AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC
====================================
Query for Customer Receipt Details(QUERY)
Query for Customer Receipt Details
SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
'cash', 'cash receipt receipt',
'misc', 'miscellaneous',
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;
=========================================
Query for Supplier Bank Details
Query for Supplier Bank Details
SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site_code,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.currency_code currency_code,
abau.primary_flag primary_flag, abb.bank_name bank_name,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)
=============================
Query for Customer Address Details
Query for Customer Address Details
SELECT DISTINCT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;
======================
Query for Sales Order Details
Query for Sales Order Details
SELECT ooha.header_id order_header_id, ottt.NAME order_type_name,
ooha.order_number, ooha.ordered_date,
ooha.transactional_curr_code order_currency, hp.party_id,
hp.party_number, hp.party_name customer_name,
hca.cust_account_id customer_id, hca.account_number customer_number,
oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
msib.segment1 item_number, msib.description item_desc,
oola.attribute15 superseded_item, oola.order_quantity_uom,
oola.ordered_quantity, oola.unit_selling_price
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_transaction_types_tl ottt,
mtl_system_items_b msib,
mtl_parameters mp,
org_organization_definitions ood,
hz_parties hp,
hz_cust_accounts hca
WHERE ooha.header_id = oola.header_id
AND ottt.transaction_type_id(+) = ooha.order_type_id
AND ottt.LANGUAGE = USERENV ('LANG')
AND hca.cust_account_id(+) = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND ooha.org_id = oola.org_id(+)
AND msib.inventory_item_id = oola.inventory_item_id
AND msib.organization_id = mp.master_organization_id
AND mp.organization_id = ood.organization_id
AND mp.master_organization_id = mp.organization_id
AND ood.operating_unit = fnd_profile.VALUE ('ORG_ID')
AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;
=============================
Query to get Request Group Details Responsibility wise:
Query to get Request Group Details Responsibility wise:
SELECT frg.request_group_name, fat1.application_name, frg.description,
DECODE (frgu.request_unit_type,
'P', 'Program',
'S', 'Set',
'A', 'Application',
frgu.request_unit_type
) TYPE,
DECODE (frgu.request_unit_type,
'P', fcpt.user_concurrent_program_name,
'S', frst.user_request_set_name,
'A', fat3.application_name,
frgu.request_unit_type
) NAME,
fat2.application_name
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt,
fnd_application_tl fat1,
fnd_application_tl fat2,
fnd_application_tl fat3,
fnd_request_sets_tl frst
WHERE frg.request_group_id = frgu.request_group_id
AND frgu.request_unit_id = fcpt.concurrent_program_id(+)
AND fcpt.LANGUAGE(+) = USERENV ('LANG')
AND frg.application_id = fat1.application_id
AND fat1.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = fat2.application_id
AND fat2.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = fcpt.application_id(+)
AND frgu.request_unit_id = frst.request_set_id(+)
AND frst.LANGUAGE(+) = USERENV ('LANG')
AND frgu.request_unit_id = fat3.application_id(+)
AND fat3.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = frst.application_id(+)
AND upper(fat1.application_name) = upper(:application_name)
ORDER BY request_group_name, frgu.request_unit_type, frgu.request_unit_id;
==========================
Query to find who and when update an Oracle Application user's profile
Query to find who and when update an Oracle Application user's profile
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
=========================
Make the concurrent program to complete with warning
Make the concurrent program to complete with warning
To make the program to complete with warning. Through sql
l_submit_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL);
=======================
Which User is Locking the table
Which User is Locking the table
I am new to the blogging world and before I really get into it, I am trying to post some of the queries that I already have .. so may be few of my initial articles may have just queries.
Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tabname_blank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';
=======================================
Database and Application Information
Database and Application Information
Below are some of the queries that can be used to get the database and Application information.
1) Get Product Version
SELECT product
, VERSION
, status
FROM product_component_version
The other way to get this information is by using following query
select * from v$version;
2) Get Applications Version and Patch Information
SELECT SUBSTR (a.application_name, 1, 60) Application_Name
, SUBSTR (i.product_version, 1, 4) Version
, i.patch_level
, i.application_id
, i.last_update_date
FROM apps.fnd_product_installations i
, apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY a.application_name
3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.
SELECT applied_patch_id
, patch_name
, patch_type
, source_code
, creation_date
, last_update_date
FROM ad_applied_patches
4) Check if the application is setup for Multi-Org
SELECT multi_org_flag
FROM fnd_product_groups
=================================
Different Types of Table in Oracle Apps
Different Types of Table in Oracle Apps
Different Types of Table in Oracle Apps
In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.
_B
The Main base tables
_ALL
Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V
View created on base table. Mostly forms are created based on this views
_TL
Tables that support multi language.
_VL
View created on multi language tables. The view generally uses the base table and _tl table
_F
This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S
sequence related tables
_DFV /_KFV
The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF.
Also using this table the values can be queried based on the DFF/KFF name and not attributes column.
_X
Current information table..there is no date tracking .
=============================
Onhand Quantity at given date
Onhand Quantity at given date
Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.
SELECT SUM (target_qty)
, item_id
FROM (SELECT moqv.subinventory_code subinv
, moqv.inventory_item_id item_id
, SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code
, moqv.inventory_item_id
, moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv
, mmt.inventory_item_id item_id
, -SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt
, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code
, mmt.inventory_item_id) oq
GROUP BY oq.item_id
=========================
Calculating Weekdays between two dates
Calculating Weekdays between two dates
Create this function:
CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
RETURN NUMBER IS
totalsundays NUMBER;
totalsaturdays NUMBER;
begin
totalsundays
:= NEXT_DAY (todate - 7, 'sunday')
- NEXT_DAY (fromdate - 1, 'sunday');
totalsaturdays
:= NEXT_DAY (todate - 7, 'saturday')
- NEXT_DAY (fromdate - 1, 'saturday');
RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1);
END totworkdays;
Call this function as follows:
declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays ('01-jan-2009', '31-jan-2009');
dbms_output.put_line('Total Work Days: '||lv_tot_work_days);
end;
==========================================
Create Request Group using oracle api
Create Request Group using oracle api
Recently i got a requirement to create request and attach all the standard reports of GL to these request groups. It seems a lot tedious task to do manually. So i developed this script to copy the request group content to other request groups. This is very useful if you have to create multiple request groups and attach reports to all.
DECLARE
CURSOR c1 IS
SELECT b.concurrent_program_name programname,c.application_name appname
FROM fnd_request_group_units a
,fnd_concurrent_programs b
,fnd_application_tl c
,fnd_request_groups d
WHERE a.request_Group_id=d.request_Group_id
AND a.request_unit_id=b.concurrent_program_id
AND b.application_id=c.application_id
AND d.request_Group_name='GL Concurrent Program Group';
BEGIN
FOR rec IN c1 LOOP
fnd_program.add_to_group(program_short_name =>rec.programname
,program_application=>REC.APPNAME
,request_group=>'XX_GL_REQ_GRP'
,group_application=>'Custom Application');
END LOOP;
END;
============================
RETCODE & ERRBUFF Parameters in Concurrent Program
RETCODE & ERRBUFF Parameters in Concurrent Program
Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
Based on the business process if there is any undefined exeception occured while running concurrent program, we can end the concurrent program with Error/Warning.
Define ERRBUFF as the first parameter and Retcode as the second one. Mention the OUT variable type.
CREATE PROCEDURE PROCEDURE_NAME (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error
we can set the concurrent program to any of the three status by using these values in the retcode parameter
Example:
========
BEGIN
.....
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Unhandled exception occurred in package. ErrMsg: '||SQLERRM);
retcode='2';
END;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.
=================
FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite
FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.
Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.
You can use either sysadmin or use some user who has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get the respective values
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'Order Management Super User';
Another option is Help > Diagnostics > Examine and get the values from $profile session values.
=============================
Organization Setup
Organization Setup
Organization Setup Steps Follow the below steps in the order listed. These are the MINIMUM steps necessary to successfully define an Organization for the Oracle Inventory module. Further information on these steps and other optional steps can be found in the Oracle Manufacturing Implementation Manual under Inventory Setup.
1. Define your set of books (GL function)
2. Define your Key Flexfields in the following order.
a. Navigate to Setup /Flexfields / Key. b. Setup the System Items, Item Categories, Item Catalog Group, Stock Locators, Account Alias and Sales Order flexfields.
3. Define locations (used for a variety of functions including receiving and default delivery locations). Note: If you populate the organization field of this form it will only show on the LOV for that organization.
4. Define a workday calendar, also called the manufacturing calendar. Eachorganization references this calendar for planning and scheduling activities. Optionally; define the calendar exceptions sets. Once this is completed, click on 'Special' and Build the calendar. It is suggested that the calendar start on the first day of the work week. For example, if the primary work schedule consists of working Monday through Friday with Saturday and Sunday off, then make the start date on the calendar coincide with a Monday date and end with a Sunday date. Note: A calendar must have at least 1 shift and 1 workday pattern defined. Use the Dates button to review the calendar for accuracy before building it.
5. Define organizations. Assign and enable the appropriate Organization classifications to each organization defined (based on the desired structure). Note: Must have at least 1 INV and 1 GRE/Legal entity Organization. The Business Group classification should not be used unless multi-org functionality will be used.
6. Complete the minimum required 'other' information for each classification selected.
a. GRE/Legal entity: must define an employer identification number and Set of Books.
b. Operating Unit: must define a legal entity. Operating Units are optional.
c. Inventory Org:
i)Accounting information: Set of Books (SOB), Legal Entity,Operating unit (dependant).
ii) Inventory information: Org code, Item Master Org, and calendar, costing Org and method, and Account information (this is located in the costing, inter-org, and other accounts zones), other settings are optional based on the features the customer intends to use.
iii) The Receiving and Customer/Supplier information are optional.
7. Define the Unit of Measure classes. Then define the Units of Measure. Thendefine the Unit of Measure conversions for the application. Note: Each class can only have one (l) base unit of measure. Base units of measure should generally be the smallest unit of measure in the class. Units of measure should have a logical connection to the class they are assigned. When disabling Units of Measure, disable the conversions first, then the Unit of Measure. I it is a base unit, the class should be disabled also. Caution, once an item has been defined in the Item Master, the primary unit of measure for that item cannot be modified.
8. Define subinventories that represent the physical or logical locations for items within an org.anization. Must complete name and description. All otherinformation is optional based on what features of the application will be used. At least one subinventory per Organization must be defined.
9. The remaining Organization setup sections are optional, depending on what features and modules will be utilized. a. Define locators that represent storage structures (for example, aisles or bins) within subinventories. b. Define Shipping Networks and Methods to facilitate Inter-Org Transfers. c. Define Freight Carriers. d. Define Organization Access. If any access information for an organization is defined in this screen, access MUST be defined for ALL responsibilities that require access to that organization. Failure to do this will cause the unlisted responsibilities to no longer function in that organization! e. Define Inter-Company Relations for inter-company functionality. Once these steps are completed the Organization is setup and usable. The user should then proceed with setting up the rest of the inventory system. See the Oracle Manufacturing Implementation guide, or the Oracle Inventory User's guide for instructions on setting up Items, Categories, Costs, Transaction Defaults, and other features of the Oracle Inventory system. It is always advisable to consult Oracle Worldwide Support Services or Oracle Metalink for the current critical patch list for the Inventory Module prior to implementing a new Inventory system.
Profile Options
HR:User Type = HR User This allows the Inventory responsibility to complete the organization setup.
HR:Business Group= {the users Business Group name}(Setup Business Group by default)This points the responsibility to the appropriate Business Group. When multipleBusiness Groups are defined, each responsibility must be associated with one and ONLY one Business Group. A responsibility cannot see organization data from morethan one Business Group.
MO:Operating Unit= {the users Operating Unit name}Used primarily in a multi-org environment. This points the responsibility to the appropriate Operating unit. Set the site level to the default operating unit. If there is more than one Operating unit defined, this profile must be set at the responsibility level for each responsibility.
===================
R12 : SLA, SLAM, ASM, Ledger Sets, Data Access Sets
R12 : SLA, SLAM, ASM, Ledger Sets, Data Access Sets
What is Subledger Accounting(SLA):
================================
Oracle Subledger Accounting is a rule based engine for Generating accountingentries based on subledger transactions from all oracle applications.
Subledger acounting is a set of services for R12 that significantly enhance accounting support across the E-Business suite.
R12 introduces some of the changes that will be fully developed in Fusion, so close attention
to the impact of these changes is worthwhile. Sub-ledger accounting may be one of these key
changes.
When Release 12 was first released, there was a lot of publicity about the move from the Set
of Books concept to Ledgers being so fundamental, however experience suggests that the
introduction of sub-ledger accounting (SLA) probably has even more impact.
For those not familiar with SLA, it is a new product in Release 12 and a major change, (although it may be almost transparent to many end-users). It is both the rules and the engine that generates the accounting representation of your transactions, and it offers some
wonderful opportunities to meet very specific accounting requirements in supportable ways. Participating subledgers include Payables, Receivables, Projects, Assets, Cash Management ,
Purchasing, Cost Management and Process Manufacturing.
In terms of its mechanics, SLA is a new schema into which all accounting entries are processed before being interfaced to the General Ledger - and so it alters the way reconciliation and month-end processes run. This simple statement is both fundamental and important - because many of the skills we have learnt over years to address accounting issues at period end may have to be modified.
Do not underestimate this change and do invest the time to simulate month-end processes
during Testing (CRP and UAT) . Companies should move from utilising Account Generators in Release 12 to representing their accounting rules in the Sub-ledger Accounting Engine. SLA rules that are in place in Release 12 will be protected in an upgrade to Fusion.
Subledger Accounting Methods (SLAM):
===================================
The Subledger accounting method is required if using Oracle Subledger accounting.
A subledger level secondary ledger requires a subledger accounting method for both the primary ledger and the secondary ledgers.
The accounting method can be changed at any time, it will only affect new journals,additional accounting methods may be defined. This is done in subledger Accounting.
There are 5 Subledger Accounting Methods:
1. Accrual with Encumbrance Accounting
2. Cash with Encumburance Accounting
3. Standard Accrual (Default)
4. Standard Cash
5. US Federal Accounting.
All upgraded ledgers in Release 12 will have a subledger accounting method assigned during the upgrade. Any reporting currencies assigned to the ledger inherit the subledger accounting method from the source ledger. The subledger accounting method enables Oracle General Ledger to integrate with Oracle subledgers using Subledger Accounting.
1.
All upgraded, non-publicsector ledgers will have a subledger accounting method assigned called Standard Accrual or Standard Cash.
2.
All upgraded public sector ledgers will have a subledger accounting method assigned called Encumbrance Accrual or Encumbrance Cash.
3.
For US Federal customers, all upgraded ledgers will have the US Federal Accounting subledger accounting method assigned to them.
What is a subledger:
Subledger are applications to manager operational transactions with financials impact.Subledger stores accounting at transaction level of details. Subledger post summarized activity information to a GL Periodically to maintain centralized account balance for the
company.
What is Accounting Setup Manager (ASM):
===================================
It is one of the main feature of the Ledger Architecutre introduced
in R12, which replaces teh Set of books from using a web based
interface.
Accounting Setup Steps:
==================
Accounting Setup Manager(ASM) is the central place where all the
accounting setup is defined and maintained for:
- Legal Enties.
- Operating Units
- Ledgers.
- Reporting Currencies
- Subledger Accouting.
-Inter and Intra Balancing Segments.
- Sequencing (Accounting and Reporting)
- Other Accounting options like retained earning account, suspense
account, currency conversion types etc.
Accounting Setup Manager Benefits:
*
Centralized Accounting Setup for Financials
–Define all of your accounting-related setup in a central location
–Reduce setup errors
–Have clear view of implementation
*
Increased Efficiency
–Access accounting setup information more quickly now that it appears centrally in one location.
*
Simplified Processes
*
Improved Efficiency
*
Strengthens business’s corporate legal structure
*
Streamlined on-going maintenance
*
User friendly interface
* Facilitates internal control management.
What is a ledger:
The Ledger represents an accounting representation for an organizationthat is accountable in a self- contained way. The ledger represent the core of a company's Financial records where every transaction flow thrugh. " A Legal Entity accounts for itself in a ledger"
Primary Ledger :
===========
-Main , Record keeping ledger
- Defined by 4C's.
- Chart of Accounts
- Accounting Calendar
- Primary Currency
- Subledger Accounting Method - New 4th C (Also Called Convension)
There are 3 types of ledgers: Primary , secondary and reporting currency ledgers.
1. Primary Ledger (PL): it is the main ledger and has the most details of information. It can have more than one secondary ledger assigned.
2. Secondary Ledger(SL): it is optional and differs in one or more of the 4C's from the PL.It provides an additional accounting representation of the PL to comply with legal requirements.
The SL can only be assigned to one PL.
3. Reporting Currency(RC): Used when the only element that differ from the PL is the Currency. it is stored in the tables as a ledger but does not need to be setup as a ledger via ASM.
Primary and secondary ledgers can have RC's assigned. if a RC has not been assigned to a PL or SL, when a transaltion is run in a ledger the reporting currency is automatically added
to the ledger setup.
The Secondary and reporting currency ledgers stores additional accounting representations of the information present in the primary ledger. There are different level of detail in which this information is stored, which are called Conversion levels.
Ledger Sets:
Ledger Sets enables you to group multiple ledgers that share the same COA and Calendar Combination. Essentially, Ledger Sets allow you to treat multiple ledger as one. for example you can open and close periods for multiple ledgers in a ledger set in a single submission by submitting the new Open and Close Perdios Program from the Submit Request Form.
Data Access Sets:
Data Access Sets enable you to specify read only or read and write access for a legal entity, ledger, Balancing Segment Value or Management segment Value.
===========================
R12 Legal Entity Configurator
R12 Legal Entity Configurator
The Legal Entity Configurator allows you to define legal entities and establishments in the Oracle system to achieve legal compliance for business activities handled by the Oracle E-Business Suite.
In Oracle Ebusiness Suite 11i, the Legal Entity is tied closely to a set of books and operating Unit, so your Legal Structure has to be defined in the way you set up your apps partitions (OU, Set of Books etc.).
In R12 financials breaks away from that with the introduction of the Legal Entity Configurator allowing you to model your Legal Structure separately from the partitions in your ERP system. Then you mark certain items with an owning LE, rather than use the OU or set of books to derive the LE.
A Legal Entity is an entity identified through the registration with the legal authority. A Legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other Legal entity information to these types of organizations.
Legal Entities have the right to:
•Own property (assets, inventory, receivable, other LEs)
•Trade (borrow, sell, buy, incur expenses, employ)
Responsibility to:
•Repay debt (liabilities, equity)
•Pay Taxes
•Account for themselves (legal reports, audits) .
What we map Legal Entities to
1. Accounting Structures - Balancing Segment Values and Ledgers
2. Tax Rules in eTax - Who I am and where I am registered/located determines what tax I need to pay
3. Bank Account - Who owns that bank account and the cash in it
4. Payables and Receivalbes Invoicing - the owner of that transaction, sometimes referred to as LE stamping
5. Intercompany Accounts and Intercompany Processing Rules
The Features for Legal Entity are:
1.Legal Entity Setup
2.Establishments Setup
3.Legal Entity Search
4.Legal Authorities and Jurisdictions and
5.Legal Associations
Legal Entity setup : Is the main component within Oracle’s Legal Entity Solution to meet the enterprises’ global Legal requirements. To be able to operate, a Legal Entity may be required to register with different Legislative Authorities depending on the Legal Entity’s activity and locations in order to comply with the law in the territory of operation. Users will use the Registration User Interface pages to create registrations for a Legal Entity in the Oracle System. The Registration User Interface will enable users to enter required registration related information that is needed for legal compliance in a given Oracle Applications instance.
Establishments are made up of a Main Establishment and additional Establishments. Conceptually, the Legal Entity and Main Establishment are tightly coupled. Therefore, every Legal Entity must have a Main Establishment. Whenever a Legal Entity is created, a Main Establishment will automatically be created. A main Establishment is always located in the same Legal Entity’s country. Legal Entities may or may not have additional Establishments.
Additional Establishments are required only in countries where other Legal Entity location needs to register with legal authorities (e.g. India, Brazil, Germany). Some Establishment attributes are required to provide information for current functionality (e.g. Registration Number- SIRET for DAS2 in France). Additional Establishments may or may not be located in the Legal Entity’s country. Main Establishments as well as additional Establishments may follow different set of rules if they do business in different activities or are located in different areas.
Legal Entity Search page : The purpose of the Search Page is to manage Legal Entities, Establishments, and Parties. In order to avoid duplication, users can identify existing Parties of type Organization and update them to be Legal Entities or Establishments.
Users will be able to:
•Update existing Legal Entities or Establishments
•Update an existing Organization Party to be a Legal Entity or a Establishment
•Configure a Trading Community Architecture Party type of Organization as a Legal Entity or Establishment.
The Legal Entity Search Page only displays Trading Community Architecture (TCA) parties of type “Organization.”
All Legal Entities must be registered against a Jurisdiction that is governed by a Legal Authority. The Jurisdiction is a formulation of a legislative category and territory to which legal rules are grounded. The Legal Authority is an executive power that operates within the jurisdiction to enforce legal rules, collect fees/taxes, and make financial appropriations within the territory. In order to support the legal world on which the legal entity is founded, both the legal authority and jurisdiction is clearly defined as part of the Legal Entity Solution.
Legal Associations is part of the Legal Entity Solution which consolidates and provides a centralized solution for Legal Entity related data in Oracle Applications. Various processes like Tax calculation and intercompany transaction processing are dependent on the knowledge of the owning Legal Entity or Establishment. Given that the legal context is not directly stated in Applications transactions today, Legal Associations provides a method to derive the Legal Context by building associations between business and accounting information that are available on transactions and Legal context. Legal Associations will also provide the ability to build associations between Legal Contexts.
A legal entity is the organization unit level at which you report taxes and maintain the corporate banking relationships. The LEGAL_ENTITY_ID column is added to the transaction tables in 12, allowing the ability to track transactions at a Legal Entity level. In R12, you assign a Legal Entity to a Ledger instead of to a Set of Books. It is recommended that you assign one (or more) balancing segment values in your chart of accounts to a legal entity.
Territory:
The territory where the legal entity is registered. This list displays territories at the country level and shows only territories for which the identifying jurisdiction has been defined. Therefore, the territory determines the identifying jurisdiction to which the legal entity needs to register. The territory also determines the context for the information that needs to be displayed in the General Information region. For Canada the BIN number is one such field.
Organization Number:
The organization number is a number used to identify organizations. This field is displayed only when the HZ: Generate Party Number profile option is set to No. In this case, the main establishment organization number is built as a concatenation of the legal entity organization number and ETB (establishment). Otherwise it is not displayed and is generated automatically. For example, if the legal entity organization number entered is 12536, the establishment's organization number will be 12536ETB.
Legal Entity Identifier:
The identification number used to uniquely identify the legal entity. It is displayed only when the LE: Generate Legal Entity Identifier profile option set to No and you must enter it manually. If this option is set to Yes, the legal entity identifier is generated automatically based on the International Organization for Standardization (ISO) code of the country of registration, plus the registration number of the identifying jurisdiction, which qualifies an entity to be a legal entity in that particular territory.
Example:
Territory: Singapore
ISO Country Code: SG
Registration number of the identifying jurisdiction (RCN number) = 23231 (this is a user enterable field)
If the profile option is set to Yes, the legal entity identifier will be SG23231, otherwise you are required to enter the legal entity identifier manually. If you enter an identifier that is not unique, an error message will be displayed.
Registration Number:
The identifying jurisdiction determines the prompt for the registration number (in the U.S., the EIN/TIN). The Establishment registration prompt is displayed if it is defined for the identifying jurisdiction. In this case the main establishment is created with this registration number. The registration number must be unique within a jurisdiction.
Legal Address:
A Legal Address is the address a legal entity uses to register with a legal authority. A legal entity may use different addresses for different authorities and hence, may have more than one registered address. This legal address must be located within the territory governed by the legal authority.
Oracle Legal Entity Configurator has adopted the use of the HR Address formatting and validation Model
Place Of Registration: Optionally enter the place of the legal entity registration.
Inception Date Optionally enter the date of legal entity registration (creation). It can be on or before the system date and on or after legal entity's inception date but must not be a future date.
==============
[PO] Matching Setting in Purchase Order Shipment Line(2-way,3-way,4-way)
[PO] Matching Setting in Purchase Order Shipment Line
Invoice matching can be set in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.
1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region
2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region
3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way
4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way
5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region
Ramification of Invoice Match to PO and Invoice Match to Receipt
Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received. If you are billed for an item over the amount and quantity tolerances you define in the Invoice Tolerance window, during Approval, Oracle Payables applies a hold to the invoice, which prevents payment.
Oracle Payables supports three levels of matching which verify that purchase order and
invoice information match within defined tolerances.
Match Approval Level: 2-Way , 3-Way, 4-Way.
In 2-way: what ever you have ordered for the PO you will make the payment for the suppliers in 2- way i.e we will compare two documents PO and Invoice.
2-way matching verifies that Purchase order and invoice information match within your tolerances:
Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
Eg:Suppose we Had given PO for 100 items ,for that we will receive invoice for 100 items. so that we will make payment for that 100 items.
In 3-Way you will compare 3 documents i.e PO+reciept+Invoice.
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received.
Eg:Suppose we have ordered 100 items in PO. But we had received only 75 items ,But we had received invoice for 100 items. so, we will make payment for only 75 items.
In 4-Way you will compare 4 documents i.e PO+Receipt+Invoice+Inspection.
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.
Eg:Suppose we have 100 items in PO. Suppers send us 75 items We will do inspection on those items what ever we have received, If 15items got damaged. finally, we are going to make payment to the 60 items only.
When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching). Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.
Invoice Match Option:
The Invoice Match Option determines whether or not you intend to match invoices for this supplier against purchase orders or receipts.
Invoice Match option to PO Match:
--------------------------------------------
•Payables must match the invoice to the purchase order.
•If the Invoice is matched to a PO rather than to the Receipt when the AP team do a match they have the full PO available to match rather than just the specific lines on the PO that were received.
•There is a possibility someone in payables matching to the wrong distribution if they use Match to PO.
•The accrual is valued at the PO exchange rate date.
Invoice Match option to PO Match:----------------------------------------------
•Payables must match the invoice to the receipt.
•Receipt Match Option is recommended if you want accounting to use exchange rate information based on the receipt date or if you want to update exchange rate information on the receipt.
•If you use the match to receipt option AP team can't match until the goods are received. If the receiving doesn't happen there is no way to associate the invoice with the PO.
•Invoice processing will be on hold till a receipt is entered into the system.
•Receipt Match option determines the cost with more accuracy i.e. Match to Material item receipt and link other invoice charges to receipt and You can now also associate freight, tax, and miscellaneous charges from invoices to the related receipt.
•Run the Matching Detail Report from Other -> Request -> Run. This report will show you detail of how an invoice, purchase order, or receipt was matched. This report is especially helpful when an invoice is on hold and you are trying determine why the hold was placed.
The Invoice Match Option defaults from the Supplier Sites window. You can change the Invoice Match Option on the shipment until you receive against the shipment.
Financial Options - Invoice Match Option:
For purchase order shipments, indicate whether you want to match invoices to purchase orders or to purchase order receipts. If the supplier was created automatically during Expense Report Import, the default value is Purchase Order, and you can change it to Receipts as appropriate.
The decision to set the Invoice match option to Receipt or Purchase Order depends on Business Needs.
•If you are using Multi Currency Functionality for Purchase Order and if like to have the rate based on Receipt date you need to go with Receipt Match at the invoice match option.
•The main advantage is the Exchange Rate factor i.e. Receipt/Current Date instead of Purchaser Order date.
•Receipt Match gives much closer control of the matching process,Particularly where you have multiple receipts of large purchase order.
If a Business is using Invoice Match option Purhcase Order and like to change from Purchase Order to Receipt Match, then what would be the impact?
•All Supplier sites need to be updated with Invoice Match option to Receipt.
•Purchase orders which are already approved with Invoice Match Option of Purchase order should continue with the same existing process.
•All existing Open PO, which need a change from Purchase Order to Receipt, should be corrected by changing the invoice match option in the purchase order shipment to Receipt. Payables team should match invoices to Receipt for all Purchase Orders created after the cut-off date.
•Training to AP/PO users.
In order for the receipt to use the current exchange rate and not the PO exchange rate, the Invoice Match Option at the system level and on the suppliers should be Receipt, not Purchase Order.
====================================
Concurrent Request Status(QUERY)
Concurrent Request Status
SELECT fcr.phase_code,
DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code) phase,
fcr.status_code,
DECODE (fcr.status_code,'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
fcr.status_code) status,
request_date,
fat.description, frt.responsibility_name, fu.user_name,
fu.description, fcpt.user_concurrent_program_name, fcpt.description,
fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
fcr.printer, fcr.parent_request_id, fcr.description,
fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
fcr.outfile_node_name
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_concurrent_programs_tl fcpt
WHERE (fu.user_id = fcr.requested_by)
AND (fat.application_id = fcr.program_application_id)
AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
AND (fcr.responsibility_id = frt.responsibility_id)
AND fat.LANGUAGE = 'US'
AND frt.LANGUAGE = 'US'
AND fcpt.LANGUAGE = 'US'
AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC
====================================
Query for Customer Receipt Details(QUERY)
Query for Customer Receipt Details
SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
'cash', 'cash receipt receipt',
'misc', 'miscellaneous',
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;
=========================================
Query for Supplier Bank Details
Query for Supplier Bank Details
SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site_code,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.currency_code currency_code,
abau.primary_flag primary_flag, abb.bank_name bank_name,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)
=============================
Query for Customer Address Details
Query for Customer Address Details
SELECT DISTINCT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;
======================
Query for Sales Order Details
Query for Sales Order Details
SELECT ooha.header_id order_header_id, ottt.NAME order_type_name,
ooha.order_number, ooha.ordered_date,
ooha.transactional_curr_code order_currency, hp.party_id,
hp.party_number, hp.party_name customer_name,
hca.cust_account_id customer_id, hca.account_number customer_number,
oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
msib.segment1 item_number, msib.description item_desc,
oola.attribute15 superseded_item, oola.order_quantity_uom,
oola.ordered_quantity, oola.unit_selling_price
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_transaction_types_tl ottt,
mtl_system_items_b msib,
mtl_parameters mp,
org_organization_definitions ood,
hz_parties hp,
hz_cust_accounts hca
WHERE ooha.header_id = oola.header_id
AND ottt.transaction_type_id(+) = ooha.order_type_id
AND ottt.LANGUAGE = USERENV ('LANG')
AND hca.cust_account_id(+) = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND ooha.org_id = oola.org_id(+)
AND msib.inventory_item_id = oola.inventory_item_id
AND msib.organization_id = mp.master_organization_id
AND mp.organization_id = ood.organization_id
AND mp.master_organization_id = mp.organization_id
AND ood.operating_unit = fnd_profile.VALUE ('ORG_ID')
AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;
=============================
Query to get Request Group Details Responsibility wise:
Query to get Request Group Details Responsibility wise:
SELECT frg.request_group_name, fat1.application_name, frg.description,
DECODE (frgu.request_unit_type,
'P', 'Program',
'S', 'Set',
'A', 'Application',
frgu.request_unit_type
) TYPE,
DECODE (frgu.request_unit_type,
'P', fcpt.user_concurrent_program_name,
'S', frst.user_request_set_name,
'A', fat3.application_name,
frgu.request_unit_type
) NAME,
fat2.application_name
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt,
fnd_application_tl fat1,
fnd_application_tl fat2,
fnd_application_tl fat3,
fnd_request_sets_tl frst
WHERE frg.request_group_id = frgu.request_group_id
AND frgu.request_unit_id = fcpt.concurrent_program_id(+)
AND fcpt.LANGUAGE(+) = USERENV ('LANG')
AND frg.application_id = fat1.application_id
AND fat1.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = fat2.application_id
AND fat2.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = fcpt.application_id(+)
AND frgu.request_unit_id = frst.request_set_id(+)
AND frst.LANGUAGE(+) = USERENV ('LANG')
AND frgu.request_unit_id = fat3.application_id(+)
AND fat3.LANGUAGE(+) = USERENV ('LANG')
AND frgu.unit_application_id = frst.application_id(+)
AND upper(fat1.application_name) = upper(:application_name)
ORDER BY request_group_name, frgu.request_unit_type, frgu.request_unit_id;
==========================
Query to find who and when update an Oracle Application user's profile
Query to find who and when update an Oracle Application user's profile
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
=========================
Make the concurrent program to complete with warning
Make the concurrent program to complete with warning
To make the program to complete with warning. Through sql
l_submit_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL);
=======================
Which User is Locking the table
Which User is Locking the table
I am new to the blogging world and before I really get into it, I am trying to post some of the queries that I already have .. so may be few of my initial articles may have just queries.
Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tabname_blank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';
=======================================
Database and Application Information
Database and Application Information
Below are some of the queries that can be used to get the database and Application information.
1) Get Product Version
SELECT product
, VERSION
, status
FROM product_component_version
The other way to get this information is by using following query
select * from v$version;
2) Get Applications Version and Patch Information
SELECT SUBSTR (a.application_name, 1, 60) Application_Name
, SUBSTR (i.product_version, 1, 4) Version
, i.patch_level
, i.application_id
, i.last_update_date
FROM apps.fnd_product_installations i
, apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY a.application_name
3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.
SELECT applied_patch_id
, patch_name
, patch_type
, source_code
, creation_date
, last_update_date
FROM ad_applied_patches
4) Check if the application is setup for Multi-Org
SELECT multi_org_flag
FROM fnd_product_groups
=================================
Different Types of Table in Oracle Apps
Different Types of Table in Oracle Apps
Different Types of Table in Oracle Apps
In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.
_B
The Main base tables
_ALL
Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V
View created on base table. Mostly forms are created based on this views
_TL
Tables that support multi language.
_VL
View created on multi language tables. The view generally uses the base table and _tl table
_F
This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S
sequence related tables
_DFV /_KFV
The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF.
Also using this table the values can be queried based on the DFF/KFF name and not attributes column.
_X
Current information table..there is no date tracking .
=============================
Onhand Quantity at given date
Onhand Quantity at given date
Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.
SELECT SUM (target_qty)
, item_id
FROM (SELECT moqv.subinventory_code subinv
, moqv.inventory_item_id item_id
, SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code
, moqv.inventory_item_id
, moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv
, mmt.inventory_item_id item_id
, -SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt
, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code
, mmt.inventory_item_id) oq
GROUP BY oq.item_id
=========================
Calculating Weekdays between two dates
Calculating Weekdays between two dates
Create this function:
CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
RETURN NUMBER IS
totalsundays NUMBER;
totalsaturdays NUMBER;
begin
totalsundays
:= NEXT_DAY (todate - 7, 'sunday')
- NEXT_DAY (fromdate - 1, 'sunday');
totalsaturdays
:= NEXT_DAY (todate - 7, 'saturday')
- NEXT_DAY (fromdate - 1, 'saturday');
RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1);
END totworkdays;
Call this function as follows:
declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays ('01-jan-2009', '31-jan-2009');
dbms_output.put_line('Total Work Days: '||lv_tot_work_days);
end;
==========================================
Create Request Group using oracle api
Create Request Group using oracle api
Recently i got a requirement to create request and attach all the standard reports of GL to these request groups. It seems a lot tedious task to do manually. So i developed this script to copy the request group content to other request groups. This is very useful if you have to create multiple request groups and attach reports to all.
DECLARE
CURSOR c1 IS
SELECT b.concurrent_program_name programname,c.application_name appname
FROM fnd_request_group_units a
,fnd_concurrent_programs b
,fnd_application_tl c
,fnd_request_groups d
WHERE a.request_Group_id=d.request_Group_id
AND a.request_unit_id=b.concurrent_program_id
AND b.application_id=c.application_id
AND d.request_Group_name='GL Concurrent Program Group';
BEGIN
FOR rec IN c1 LOOP
fnd_program.add_to_group(program_short_name =>rec.programname
,program_application=>REC.APPNAME
,request_group=>'XX_GL_REQ_GRP'
,group_application=>'Custom Application');
END LOOP;
END;
============================
RETCODE & ERRBUFF Parameters in Concurrent Program
RETCODE & ERRBUFF Parameters in Concurrent Program
Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
Based on the business process if there is any undefined exeception occured while running concurrent program, we can end the concurrent program with Error/Warning.
Define ERRBUFF as the first parameter and Retcode as the second one. Mention the OUT variable type.
CREATE PROCEDURE PROCEDURE_NAME (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error
we can set the concurrent program to any of the three status by using these values in the retcode parameter
Example:
========
BEGIN
.....
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Unhandled exception occurred in package. ErrMsg: '||SQLERRM);
retcode='2';
END;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.
=================
FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite
FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.
Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.
You can use either sysadmin or use some user who has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get the respective values
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'Order Management Super User';
Another option is Help > Diagnostics > Examine and get the values from $profile session values.
=============================
Organization Setup
Organization Setup
Organization Setup Steps Follow the below steps in the order listed. These are the MINIMUM steps necessary to successfully define an Organization for the Oracle Inventory module. Further information on these steps and other optional steps can be found in the Oracle Manufacturing Implementation Manual under Inventory Setup.
1. Define your set of books (GL function)
2. Define your Key Flexfields in the following order.
a. Navigate to Setup /Flexfields / Key. b. Setup the System Items, Item Categories, Item Catalog Group, Stock Locators, Account Alias and Sales Order flexfields.
3. Define locations (used for a variety of functions including receiving and default delivery locations). Note: If you populate the organization field of this form it will only show on the LOV for that organization.
4. Define a workday calendar, also called the manufacturing calendar. Eachorganization references this calendar for planning and scheduling activities. Optionally; define the calendar exceptions sets. Once this is completed, click on 'Special' and Build the calendar. It is suggested that the calendar start on the first day of the work week. For example, if the primary work schedule consists of working Monday through Friday with Saturday and Sunday off, then make the start date on the calendar coincide with a Monday date and end with a Sunday date. Note: A calendar must have at least 1 shift and 1 workday pattern defined. Use the Dates button to review the calendar for accuracy before building it.
5. Define organizations. Assign and enable the appropriate Organization classifications to each organization defined (based on the desired structure). Note: Must have at least 1 INV and 1 GRE/Legal entity Organization. The Business Group classification should not be used unless multi-org functionality will be used.
6. Complete the minimum required 'other' information for each classification selected.
a. GRE/Legal entity: must define an employer identification number and Set of Books.
b. Operating Unit: must define a legal entity. Operating Units are optional.
c. Inventory Org:
i)Accounting information: Set of Books (SOB), Legal Entity,Operating unit (dependant).
ii) Inventory information: Org code, Item Master Org, and calendar, costing Org and method, and Account information (this is located in the costing, inter-org, and other accounts zones), other settings are optional based on the features the customer intends to use.
iii) The Receiving and Customer/Supplier information are optional.
7. Define the Unit of Measure classes. Then define the Units of Measure. Thendefine the Unit of Measure conversions for the application. Note: Each class can only have one (l) base unit of measure. Base units of measure should generally be the smallest unit of measure in the class. Units of measure should have a logical connection to the class they are assigned. When disabling Units of Measure, disable the conversions first, then the Unit of Measure. I it is a base unit, the class should be disabled also. Caution, once an item has been defined in the Item Master, the primary unit of measure for that item cannot be modified.
8. Define subinventories that represent the physical or logical locations for items within an org.anization. Must complete name and description. All otherinformation is optional based on what features of the application will be used. At least one subinventory per Organization must be defined.
9. The remaining Organization setup sections are optional, depending on what features and modules will be utilized. a. Define locators that represent storage structures (for example, aisles or bins) within subinventories. b. Define Shipping Networks and Methods to facilitate Inter-Org Transfers. c. Define Freight Carriers. d. Define Organization Access. If any access information for an organization is defined in this screen, access MUST be defined for ALL responsibilities that require access to that organization. Failure to do this will cause the unlisted responsibilities to no longer function in that organization! e. Define Inter-Company Relations for inter-company functionality. Once these steps are completed the Organization is setup and usable. The user should then proceed with setting up the rest of the inventory system. See the Oracle Manufacturing Implementation guide, or the Oracle Inventory User's guide for instructions on setting up Items, Categories, Costs, Transaction Defaults, and other features of the Oracle Inventory system. It is always advisable to consult Oracle Worldwide Support Services or Oracle Metalink for the current critical patch list for the Inventory Module prior to implementing a new Inventory system.
Profile Options
HR:User Type = HR User This allows the Inventory responsibility to complete the organization setup.
HR:Business Group= {the users Business Group name}(Setup Business Group by default)This points the responsibility to the appropriate Business Group. When multipleBusiness Groups are defined, each responsibility must be associated with one and ONLY one Business Group. A responsibility cannot see organization data from morethan one Business Group.
MO:Operating Unit= {the users Operating Unit name}Used primarily in a multi-org environment. This points the responsibility to the appropriate Operating unit. Set the site level to the default operating unit. If there is more than one Operating unit defined, this profile must be set at the responsibility level for each responsibility.
===================
R12 : SLA, SLAM, ASM, Ledger Sets, Data Access Sets
R12 : SLA, SLAM, ASM, Ledger Sets, Data Access Sets
What is Subledger Accounting(SLA):
================================
Oracle Subledger Accounting is a rule based engine for Generating accountingentries based on subledger transactions from all oracle applications.
Subledger acounting is a set of services for R12 that significantly enhance accounting support across the E-Business suite.
R12 introduces some of the changes that will be fully developed in Fusion, so close attention
to the impact of these changes is worthwhile. Sub-ledger accounting may be one of these key
changes.
When Release 12 was first released, there was a lot of publicity about the move from the Set
of Books concept to Ledgers being so fundamental, however experience suggests that the
introduction of sub-ledger accounting (SLA) probably has even more impact.
For those not familiar with SLA, it is a new product in Release 12 and a major change, (although it may be almost transparent to many end-users). It is both the rules and the engine that generates the accounting representation of your transactions, and it offers some
wonderful opportunities to meet very specific accounting requirements in supportable ways. Participating subledgers include Payables, Receivables, Projects, Assets, Cash Management ,
Purchasing, Cost Management and Process Manufacturing.
In terms of its mechanics, SLA is a new schema into which all accounting entries are processed before being interfaced to the General Ledger - and so it alters the way reconciliation and month-end processes run. This simple statement is both fundamental and important - because many of the skills we have learnt over years to address accounting issues at period end may have to be modified.
Do not underestimate this change and do invest the time to simulate month-end processes
during Testing (CRP and UAT) . Companies should move from utilising Account Generators in Release 12 to representing their accounting rules in the Sub-ledger Accounting Engine. SLA rules that are in place in Release 12 will be protected in an upgrade to Fusion.
Subledger Accounting Methods (SLAM):
===================================
The Subledger accounting method is required if using Oracle Subledger accounting.
A subledger level secondary ledger requires a subledger accounting method for both the primary ledger and the secondary ledgers.
The accounting method can be changed at any time, it will only affect new journals,additional accounting methods may be defined. This is done in subledger Accounting.
There are 5 Subledger Accounting Methods:
1. Accrual with Encumbrance Accounting
2. Cash with Encumburance Accounting
3. Standard Accrual (Default)
4. Standard Cash
5. US Federal Accounting.
All upgraded ledgers in Release 12 will have a subledger accounting method assigned during the upgrade. Any reporting currencies assigned to the ledger inherit the subledger accounting method from the source ledger. The subledger accounting method enables Oracle General Ledger to integrate with Oracle subledgers using Subledger Accounting.
1.
All upgraded, non-publicsector ledgers will have a subledger accounting method assigned called Standard Accrual or Standard Cash.
2.
All upgraded public sector ledgers will have a subledger accounting method assigned called Encumbrance Accrual or Encumbrance Cash.
3.
For US Federal customers, all upgraded ledgers will have the US Federal Accounting subledger accounting method assigned to them.
What is a subledger:
Subledger are applications to manager operational transactions with financials impact.Subledger stores accounting at transaction level of details. Subledger post summarized activity information to a GL Periodically to maintain centralized account balance for the
company.
What is Accounting Setup Manager (ASM):
===================================
It is one of the main feature of the Ledger Architecutre introduced
in R12, which replaces teh Set of books from using a web based
interface.
Accounting Setup Steps:
==================
Accounting Setup Manager(ASM) is the central place where all the
accounting setup is defined and maintained for:
- Legal Enties.
- Operating Units
- Ledgers.
- Reporting Currencies
- Subledger Accouting.
-Inter and Intra Balancing Segments.
- Sequencing (Accounting and Reporting)
- Other Accounting options like retained earning account, suspense
account, currency conversion types etc.
Accounting Setup Manager Benefits:
*
Centralized Accounting Setup for Financials
–Define all of your accounting-related setup in a central location
–Reduce setup errors
–Have clear view of implementation
*
Increased Efficiency
–Access accounting setup information more quickly now that it appears centrally in one location.
*
Simplified Processes
*
Improved Efficiency
*
Strengthens business’s corporate legal structure
*
Streamlined on-going maintenance
*
User friendly interface
* Facilitates internal control management.
What is a ledger:
The Ledger represents an accounting representation for an organizationthat is accountable in a self- contained way. The ledger represent the core of a company's Financial records where every transaction flow thrugh. " A Legal Entity accounts for itself in a ledger"
Primary Ledger :
===========
-Main , Record keeping ledger
- Defined by 4C's.
- Chart of Accounts
- Accounting Calendar
- Primary Currency
- Subledger Accounting Method - New 4th C (Also Called Convension)
There are 3 types of ledgers: Primary , secondary and reporting currency ledgers.
1. Primary Ledger (PL): it is the main ledger and has the most details of information. It can have more than one secondary ledger assigned.
2. Secondary Ledger(SL): it is optional and differs in one or more of the 4C's from the PL.It provides an additional accounting representation of the PL to comply with legal requirements.
The SL can only be assigned to one PL.
3. Reporting Currency(RC): Used when the only element that differ from the PL is the Currency. it is stored in the tables as a ledger but does not need to be setup as a ledger via ASM.
Primary and secondary ledgers can have RC's assigned. if a RC has not been assigned to a PL or SL, when a transaltion is run in a ledger the reporting currency is automatically added
to the ledger setup.
The Secondary and reporting currency ledgers stores additional accounting representations of the information present in the primary ledger. There are different level of detail in which this information is stored, which are called Conversion levels.
Ledger Sets:
Ledger Sets enables you to group multiple ledgers that share the same COA and Calendar Combination. Essentially, Ledger Sets allow you to treat multiple ledger as one. for example you can open and close periods for multiple ledgers in a ledger set in a single submission by submitting the new Open and Close Perdios Program from the Submit Request Form.
Data Access Sets:
Data Access Sets enable you to specify read only or read and write access for a legal entity, ledger, Balancing Segment Value or Management segment Value.
===========================
R12 Legal Entity Configurator
R12 Legal Entity Configurator
The Legal Entity Configurator allows you to define legal entities and establishments in the Oracle system to achieve legal compliance for business activities handled by the Oracle E-Business Suite.
In Oracle Ebusiness Suite 11i, the Legal Entity is tied closely to a set of books and operating Unit, so your Legal Structure has to be defined in the way you set up your apps partitions (OU, Set of Books etc.).
In R12 financials breaks away from that with the introduction of the Legal Entity Configurator allowing you to model your Legal Structure separately from the partitions in your ERP system. Then you mark certain items with an owning LE, rather than use the OU or set of books to derive the LE.
A Legal Entity is an entity identified through the registration with the legal authority. A Legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other Legal entity information to these types of organizations.
Legal Entities have the right to:
•Own property (assets, inventory, receivable, other LEs)
•Trade (borrow, sell, buy, incur expenses, employ)
Responsibility to:
•Repay debt (liabilities, equity)
•Pay Taxes
•Account for themselves (legal reports, audits) .
What we map Legal Entities to
1. Accounting Structures - Balancing Segment Values and Ledgers
2. Tax Rules in eTax - Who I am and where I am registered/located determines what tax I need to pay
3. Bank Account - Who owns that bank account and the cash in it
4. Payables and Receivalbes Invoicing - the owner of that transaction, sometimes referred to as LE stamping
5. Intercompany Accounts and Intercompany Processing Rules
The Features for Legal Entity are:
1.Legal Entity Setup
2.Establishments Setup
3.Legal Entity Search
4.Legal Authorities and Jurisdictions and
5.Legal Associations
Legal Entity setup : Is the main component within Oracle’s Legal Entity Solution to meet the enterprises’ global Legal requirements. To be able to operate, a Legal Entity may be required to register with different Legislative Authorities depending on the Legal Entity’s activity and locations in order to comply with the law in the territory of operation. Users will use the Registration User Interface pages to create registrations for a Legal Entity in the Oracle System. The Registration User Interface will enable users to enter required registration related information that is needed for legal compliance in a given Oracle Applications instance.
Establishments are made up of a Main Establishment and additional Establishments. Conceptually, the Legal Entity and Main Establishment are tightly coupled. Therefore, every Legal Entity must have a Main Establishment. Whenever a Legal Entity is created, a Main Establishment will automatically be created. A main Establishment is always located in the same Legal Entity’s country. Legal Entities may or may not have additional Establishments.
Additional Establishments are required only in countries where other Legal Entity location needs to register with legal authorities (e.g. India, Brazil, Germany). Some Establishment attributes are required to provide information for current functionality (e.g. Registration Number- SIRET for DAS2 in France). Additional Establishments may or may not be located in the Legal Entity’s country. Main Establishments as well as additional Establishments may follow different set of rules if they do business in different activities or are located in different areas.
Legal Entity Search page : The purpose of the Search Page is to manage Legal Entities, Establishments, and Parties. In order to avoid duplication, users can identify existing Parties of type Organization and update them to be Legal Entities or Establishments.
Users will be able to:
•Update existing Legal Entities or Establishments
•Update an existing Organization Party to be a Legal Entity or a Establishment
•Configure a Trading Community Architecture Party type of Organization as a Legal Entity or Establishment.
The Legal Entity Search Page only displays Trading Community Architecture (TCA) parties of type “Organization.”
All Legal Entities must be registered against a Jurisdiction that is governed by a Legal Authority. The Jurisdiction is a formulation of a legislative category and territory to which legal rules are grounded. The Legal Authority is an executive power that operates within the jurisdiction to enforce legal rules, collect fees/taxes, and make financial appropriations within the territory. In order to support the legal world on which the legal entity is founded, both the legal authority and jurisdiction is clearly defined as part of the Legal Entity Solution.
Legal Associations is part of the Legal Entity Solution which consolidates and provides a centralized solution for Legal Entity related data in Oracle Applications. Various processes like Tax calculation and intercompany transaction processing are dependent on the knowledge of the owning Legal Entity or Establishment. Given that the legal context is not directly stated in Applications transactions today, Legal Associations provides a method to derive the Legal Context by building associations between business and accounting information that are available on transactions and Legal context. Legal Associations will also provide the ability to build associations between Legal Contexts.
A legal entity is the organization unit level at which you report taxes and maintain the corporate banking relationships. The LEGAL_ENTITY_ID column is added to the transaction tables in 12, allowing the ability to track transactions at a Legal Entity level. In R12, you assign a Legal Entity to a Ledger instead of to a Set of Books. It is recommended that you assign one (or more) balancing segment values in your chart of accounts to a legal entity.
Territory:
The territory where the legal entity is registered. This list displays territories at the country level and shows only territories for which the identifying jurisdiction has been defined. Therefore, the territory determines the identifying jurisdiction to which the legal entity needs to register. The territory also determines the context for the information that needs to be displayed in the General Information region. For Canada the BIN number is one such field.
Organization Number:
The organization number is a number used to identify organizations. This field is displayed only when the HZ: Generate Party Number profile option is set to No. In this case, the main establishment organization number is built as a concatenation of the legal entity organization number and ETB (establishment). Otherwise it is not displayed and is generated automatically. For example, if the legal entity organization number entered is 12536, the establishment's organization number will be 12536ETB.
Legal Entity Identifier:
The identification number used to uniquely identify the legal entity. It is displayed only when the LE: Generate Legal Entity Identifier profile option set to No and you must enter it manually. If this option is set to Yes, the legal entity identifier is generated automatically based on the International Organization for Standardization (ISO) code of the country of registration, plus the registration number of the identifying jurisdiction, which qualifies an entity to be a legal entity in that particular territory.
Example:
Territory: Singapore
ISO Country Code: SG
Registration number of the identifying jurisdiction (RCN number) = 23231 (this is a user enterable field)
If the profile option is set to Yes, the legal entity identifier will be SG23231, otherwise you are required to enter the legal entity identifier manually. If you enter an identifier that is not unique, an error message will be displayed.
Registration Number:
The identifying jurisdiction determines the prompt for the registration number (in the U.S., the EIN/TIN). The Establishment registration prompt is displayed if it is defined for the identifying jurisdiction. In this case the main establishment is created with this registration number. The registration number must be unique within a jurisdiction.
Legal Address:
A Legal Address is the address a legal entity uses to register with a legal authority. A legal entity may use different addresses for different authorities and hence, may have more than one registered address. This legal address must be located within the territory governed by the legal authority.
Oracle Legal Entity Configurator has adopted the use of the HR Address formatting and validation Model
Place Of Registration: Optionally enter the place of the legal entity registration.
Inception Date Optionally enter the date of legal entity registration (creation). It can be on or before the system date and on or after legal entity's inception date but must not be a future date.
==============
[PO] Matching Setting in Purchase Order Shipment Line(2-way,3-way,4-way)
[PO] Matching Setting in Purchase Order Shipment Line
Invoice matching can be set in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.
1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region
2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region
3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way
4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way
5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region
Ramification of Invoice Match to PO and Invoice Match to Receipt
Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received. If you are billed for an item over the amount and quantity tolerances you define in the Invoice Tolerance window, during Approval, Oracle Payables applies a hold to the invoice, which prevents payment.
Oracle Payables supports three levels of matching which verify that purchase order and
invoice information match within defined tolerances.
Match Approval Level: 2-Way , 3-Way, 4-Way.
In 2-way: what ever you have ordered for the PO you will make the payment for the suppliers in 2- way i.e we will compare two documents PO and Invoice.
2-way matching verifies that Purchase order and invoice information match within your tolerances:
Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
Eg:Suppose we Had given PO for 100 items ,for that we will receive invoice for 100 items. so that we will make payment for that 100 items.
In 3-Way you will compare 3 documents i.e PO+reciept+Invoice.
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received.
Eg:Suppose we have ordered 100 items in PO. But we had received only 75 items ,But we had received invoice for 100 items. so, we will make payment for only 75 items.
In 4-Way you will compare 4 documents i.e PO+Receipt+Invoice+Inspection.
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.
Eg:Suppose we have 100 items in PO. Suppers send us 75 items We will do inspection on those items what ever we have received, If 15items got damaged. finally, we are going to make payment to the 60 items only.
When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching). Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.
Invoice Match Option:
The Invoice Match Option determines whether or not you intend to match invoices for this supplier against purchase orders or receipts.
Invoice Match option to PO Match:
--------------------------------------------
•Payables must match the invoice to the purchase order.
•If the Invoice is matched to a PO rather than to the Receipt when the AP team do a match they have the full PO available to match rather than just the specific lines on the PO that were received.
•There is a possibility someone in payables matching to the wrong distribution if they use Match to PO.
•The accrual is valued at the PO exchange rate date.
Invoice Match option to PO Match:----------------------------------------------
•Payables must match the invoice to the receipt.
•Receipt Match Option is recommended if you want accounting to use exchange rate information based on the receipt date or if you want to update exchange rate information on the receipt.
•If you use the match to receipt option AP team can't match until the goods are received. If the receiving doesn't happen there is no way to associate the invoice with the PO.
•Invoice processing will be on hold till a receipt is entered into the system.
•Receipt Match option determines the cost with more accuracy i.e. Match to Material item receipt and link other invoice charges to receipt and You can now also associate freight, tax, and miscellaneous charges from invoices to the related receipt.
•Run the Matching Detail Report from Other -> Request -> Run. This report will show you detail of how an invoice, purchase order, or receipt was matched. This report is especially helpful when an invoice is on hold and you are trying determine why the hold was placed.
The Invoice Match Option defaults from the Supplier Sites window. You can change the Invoice Match Option on the shipment until you receive against the shipment.
Financial Options - Invoice Match Option:
For purchase order shipments, indicate whether you want to match invoices to purchase orders or to purchase order receipts. If the supplier was created automatically during Expense Report Import, the default value is Purchase Order, and you can change it to Receipts as appropriate.
The decision to set the Invoice match option to Receipt or Purchase Order depends on Business Needs.
•If you are using Multi Currency Functionality for Purchase Order and if like to have the rate based on Receipt date you need to go with Receipt Match at the invoice match option.
•The main advantage is the Exchange Rate factor i.e. Receipt/Current Date instead of Purchaser Order date.
•Receipt Match gives much closer control of the matching process,Particularly where you have multiple receipts of large purchase order.
If a Business is using Invoice Match option Purhcase Order and like to change from Purchase Order to Receipt Match, then what would be the impact?
•All Supplier sites need to be updated with Invoice Match option to Receipt.
•Purchase orders which are already approved with Invoice Match Option of Purchase order should continue with the same existing process.
•All existing Open PO, which need a change from Purchase Order to Receipt, should be corrected by changing the invoice match option in the purchase order shipment to Receipt. Payables team should match invoices to Receipt for all Purchase Orders created after the cut-off date.
•Training to AP/PO users.
In order for the receipt to use the current exchange rate and not the PO exchange rate, the Invoice Match Option at the system level and on the suppliers should be Receipt, not Purchase Order.
====================================
No comments:
Post a Comment