Oracle Cash Management Setups – Bank Creation

Bank Creation
Steps:
          1.   N: Setup-> Banks -> Banks

2.    Click on Create button


3.    Enter the Country as United States and Bank Name then Click on Save and Next Button


4.    Click on Create button for bank address if required or click on Save and Next button


5.    Click on Create contract button for bank contract concern person if required or click on Finish button
Posted in Labels: cash management in oracle apps r12, oracle cash management, oracle cash management interview questions, oracle cash management setup steps | Leave a comment

Oracle Cash Management Setups – System Parameters

System Parameters

Use the System Parameters window to configure your Cash Management system to meet your business needs. For example, system parameters determine the default options for manual reconciliation windows and the control settings for the Auto Reconciliation program.
Steps:
1. Switch Responsibility: – Ivas_CashManagement
2. N: – Setup-> System-> System Parameters

2.1. Basic System Parameters
Legal Entity: The name of legal entity that system parameters are applied to.
Ledger: The name of the Ledger for Cash Management accounting transactions. This value defaults from the GL Data Access Sets profile option.
Cash Management supports multiple ledgers. Each legal entity is tied to a ledger. If you deal with multiple legal entities, you set up System Parameters for each legal entity
Begin Date: Cash Management will not display transactions dated earlier than this date.
2.1.1. Reconciliation Controls
Show Cleared Transactions: If you select this check box, cleared transactions remain available for reconciliation. Otherwise, only uncleared transactions will appear for reconciliation.
Show Void Payments: You must select this check box if you want voided payments available for reconciliation.
Allow Adding Lines to Imported Statements: If you do not select this check box, you cannot add lines to automatically loaded bank statements.
General Parameters
Archive/Purge: You can set the following options to automatically archive or purge imported bank statement information:
Purge: If you select this check box, the Bank Statement Import program automatically purges all information from the Bank Statement Open Interface tables, once the information has been successfully transferred to the Bank Statement tables. If you import intra-day bank statements, the Intra-Day Bank Statement Import program automatically purges imported bank statement information from the Bank Statement Open Interface tables. You can purge bank statements without first archiving them.
Archive: If you select this check box, the Bank Statement Import program automatically archives all information from the Bank Statement Interface tables, once the information has been successfully transferred to the Bank Statement tables. If you import intra-day bank statements, the Intra-Day Bank Statement Import program automatically archives imported bank statement information from the Bank Statement Open Interface tables. You cannot archive bank statements without also purging them.
Note: You cannot use these two options to automatically archive or purge information from the Bank Statement tables or the Intra-Day Bank Statement tables. You must run the Archive/Purge Bank Statements program.
Lines per Commit: This is the number of lines that Auto Reconciliation will save at a time. It controls the number of transaction rows that Auto Reconciliation locks at any given time. The greater the number of lines, the faster Auto Reconciliation processes. However, the number of lines you can specify is limited by the amount of memory available in your system.
Signing Authority Approval
Through the Signing Authority Approval system parameter, you can decide if you want to manually approve signing authority or use a workflow process. The following options are:
Workflow Enabled. This option enables the seeded business event to change the status on the Bank Account Signing Authority window from Pending to Approved, when a record is entered and saved.
Manually Controlled. This option allows the user to configure the workflow approval process.

2.1.2. Cash Management Transactions Parameters

Accounting



Exchange Rate Type: Bank Statement Cash flows and Bank Account Transfers programs select the exchange rates for the transaction currency using the exchange rate type you specify. You can choose any exchange rate type defined in Oracle General Ledger
Bank Statement Cash flow Date: Bank Statement Cash flows program selects the exchange rates for the transaction currency using the date source you specify. You can choose any of the following exchange rate dates:
Cash flow Date. The date the cash flow is created by the Bank Statement Cash flows program.
Bank Statement Date. The closing date of the statement.
GL Date. The accounting date used to clear the statement.
Cleared Date. The date the transaction cleared the bank. 

Bank Transfer Rate Date: Bank Account Transfers program selects the exchange rates for the transaction currency using the date source you specify. You can choose any of the following exchange rate dates:

Transfer Date. The date the bank account transfer occurred.
Statement Line Date. The bank statement line date.
Bank Statement Date. The closing date of the statement.
GL Date. The accounting date used to clear the statement.
Cleared Date. The date the transaction cleared the bank.
Actual Value Date. The statement line value date.
Cash flow Date. The date the cash flow is created from the bank account transfer.
Bank Account Transfers
Authorization: This parameter provides two options:
Not Required. Authorization is not required for bank account transfer to be settled. Bank Account transfer is changed to settled status right after the creation.
Required. Authorization is required for bank account transfers to be settled. 
Posted in cash management in oracle apps r12, oracle cash management, oracle cash management interview questions, oracle cash management setup steps | Leave a comment

Happy New Year – 2013

Image | Posted on by | Leave a comment

Oracle OM Internal Requisition (IR) & Internal Sales Order (ISO) Synchronization

Internal Requisition (IR) and Internal Sales Order (ISO) Synchronization:
Before R12.1.1, when even a change happened on an approved Internal Requisition the change is manually done in the corresponding Internal Sales order.
Oracle has come with a solution for this in R12.1.1. When an update happens on the following field’s it will cascade from IR to ISO or ISO to IR.
·         Order quantity changes
·         Date Changes
·         Cancellations

Order quantity Changes & Date Changes
·         Change on requested quantity and need-by date of IR line reflect a change on ordered quantity and schedule ship date/schedule arrival date of ISO line respectively.
Cancellations:
·         Cancellation of Internal requisition header/line cancels internal sales order line automatically.
·         Cancellation of Internal sales order line cancels internal requisition lines automatically.
For this to happen, we need to setup the following profile:
POR: Sync up Need by date on IR with OM
The profile option has to be set to ‘Yes’ to enable IR-ISO synchronization.
We will check this with an example:
The Sales Order line’s schedule arrival date (05-MAY-2010) matches with the need by date (05-MAY-2010) on Internal Requisition line.

Now if the Schedule arrival date is updated on Internal Sales order line, a message will appear confirming the update of corresponding internal requisition need-by-date.
We can see that the schedule arrival data change cascades to internal requisition Need-By date.
Check out our video on IR-ISO Synchronization:
Posted in internal requisition, internal sales order in oracle apps, IR ISO, IR-ISO Synchronization, Oracle order Management, Order management tutorials, purchasing in oracle apps, R12 Features | Leave a comment

Oracle Order Management Item Orderability rule in R12

There is a new interface provided by oracle to define rules to restrict certain kind of items to be ordered based on certain rules / criteria.
Example:
Customer A may purchase all items except for repair items and promotional items.
Alternatively, Customer B buys only repair parts but is restricted from buying promotional items.

Such kind of rules can be created with the new functionality in R12.1.1.
Following are the attributes that can be used to define rules:
·         Order type
·         Customer
·         Customer class
·         Customer category
·         End customer
·         Sales Channel
·         Sales Person
·         Ship to Location
·         Bill to Location
·         Deliver to Location
·         Regions

In the above screen, a rule is setup on the order type: MIXED to restrict item CM15140 in the sales order screen.

So now when an order is placed with the following details:
Order type: ‘Mixed’
Item: CM15140
 We will get a message saying that the item is restricted for ordering under the order type MIXED.
Similarly we can define rules for other attributes specified above.
One of the most common business scenarios is to restrict a particular customer ordering certain items and this can be achieved by customer level Orderability rule.
Here in the below setup, we are restricting ordering of the item – for the customer – ‘Business World’
We have a profile option required to be setup for Item Orderability:
·         OM: Use Materialized View for Items LOV (Honors Item Orderability Rules) = No or NULL
Restricted Item is visible in ordered item list of values at sales order line level, but will not be saved.
·         OM: Use Materialized View for Items LOV (Honors Item Orderability Rules) = Yes
The ordered item LOV displays only those items which are not restricted by the Orderability rules
The concurrent program – “Refresh Order Management Materialized views” has to be run whenever changes are made to the above profile option.
The concurrent program restricts the items or makes the items available in the LOV of the Sales order based on the above profile option.
The concurrent program is new introduced in R12.1.1
Posted in item orderability, OM, Oracle apps, Order Management, R12 New Features, R12.1.1 | Leave a comment

Oracle Apps R12 Multi Org Structure

What is a Multi Org Structure?
If an enterprise or a business wants to implement multiple organizations such as multiple Ledgers (Sets of Books), or Legal Entities, or Business Groups within a single installation of Oracle Applications, then we can summarize that the enterprise is planning to implement a multi org setup.

Example:
Before we dive into this topic, let us draw a multi org structure on a whiteboard. It would help to analyze a real picture, as we pick at the concepts that go into designing a multi org structure.

The above is the organization structure for Office Smart Solutions, which is a major office supplies retailer, headquartered in Naperville, Illinois, USA. The organization operates in three countries – the US, Canada and India.
Office Smart has an organization structure with the following:
·               2 Business Groups – one in the US, which controls the organization structure in North America, and one in India
·               3 Legal Entities – one in the US, one in Canada, and one in India
·               3 Primary Ledgers – one in the US, one in Canada, and one in India
·               3 Operating Units – one in the US, one in Canada, and one in India
·               5 Inventory organizations – two in the US, one in Canada, and two in India
·               Subinventories and locators exist beneath the inventory organizations, but they are not relevant for the session on multi org structures.
With this, let us step back and reflect…
The way it was in 11i
In 11i, a user working with a specific responsibility, under a given operating unit, would need to switch responsibilities, if she were to access a sales order that was created from a different operating unit. For this to happen, the user had to be assigned a second responsibility that was linked to the second operating unit.
From an implementation perspective, this implied that each responsibility could be linked to one and only one operating unit. Thus, if a user in Office Smart Canada, needed access to data in Office Smart US, then she would need to be assigned a responsibility that was tied to the US Operating Unit – Office Smart Operations.
Responsibilities were tied to operating units through the profile option MO: Operating Unit.
What R12 brings to the table
Release 12 brought with it, the philosophy of Multi Org Access Control (MOAC).
“Globalization is unstoppable. Regardless of geography, industry or income, companies are globalizing to gain new customers and access new markets. Is this a good thing? Nearly two-thirds of the CEOs we surveyed are positive about the impact that globalization will have on their organizations over the next three years.”

Source: 9th Annual Global CEO Survey – Globalization and Complexity; PwC 2006

With Release 12, Oracle Applications had to ensure that certain aspects of the applications were redesigned to meet the inevitable advance of Globalization.
Organizational changes in R12
The Set of Books evolved into Ledgers and Ledger Sets. The philosophy of Multiple Organization Access Control (MOAC) introduced in R12, ensured that the same user could perform multiple tasks across operating units without changing responsibilities. The use of Security Profiles was extended beyond HR to make MOAC possible.
Organization Access Control in R12
In a multi org environment, securing the data in each organization becomes a key task and concern for management and the implementation team. By creating custom responsibilities, management ensures that employees are given access to only those menus and functions that they need to perform their routine activities. However, an addition layer of security needs to be designed to ensure that using those menus and forms given to them, employees cannot trespass into an organization that they should not have access to.
As mentioned above, in 11i access to organizations was compartmentalized based on operating units. This ensured data security, but at the expense of making it a little cumbersome for the user to switch between organizations that belong to different operating units.
The Multi Org Access Control (MOAC) feature in R12 retains the data security aspect between organizations and users. However, it also brings with it a certain degree of user friendliness in navigating between different operating units.
How does R12 implement this change?
This series is designed to highlight all that it takes to implement a Multi Org Structure in Release 12.
Posted in multi org concept in oracle apps, multi org structure in oracle r12, multi-org in oracle applications, operating unit in oracle apps, oracle multi org structure, r12 multi org structure | Leave a comment

Oracle SQL Practice exercises

emp
ename
Salary
supno
dno
Write an SQL statement for each of the following requests.
INSERT INTO emp values (22, “Ahmad”, 20000, 2, 3);
INSERT INTO emp(eno, ename, salary, supno, dno) VALUES (22,”Ahmad”, 20000, 2, 3);

INSERT INTO emp(ename, eno, salary, supno, dno) VALUES (“Ahamd”,22, 20000, 2, 3);
2.             Insert the following record to emp table:    23, “Ali”, , 1, 3 (salary is null)
INSERT INTO emp values (23, “Ali”, NULL, 1, 3);
INSERT INTO emp values (23, “Ali”, , 1, 3);
INSERT INTO emp(eno, ename, supno, dno) VALUES (23, “Ali”, 1, 3);
3.             List all the data in the emp table.                   SELECT * FROM emp;
4.             List employee number and salary from emp table.
SELECT eno, salary FROM emp;
5.             Change the department of employ # 10 to be 2.
                UPDATE emp SET dno = 2 WHERE eno = 10;
6.             Change the department of employ # 13 to be 2 and the supervisor to be 3.
UPDATE emp SET    supno = 3, dno = 2 WHERE  eno = 13;
7.             Delete the employee whose name “irwin“.
DELETE FROM emp WHERE  ename = “irwin”;
8.             What does the following query do?  DELETE FROM emp;
9.             What does the following query do?               INSERT INTO proj SELECT * FROM project;
10.          Retrieve eno, ename, salary for all employees in department 3.
SELECT eno, ename, salary     FROM emp    WHERE dno = 3;
11.          Retrieve ename, dno for all employees who has salary greater that 25000.
SELECT eno, ename, dno  FROM emp    WHERE salary > 25000;
12.          Display the salary of each employee with an increase 10%.
SELECT eno, ename, salary*1.1 as newsal FROM emp;
13.          Retrieve ename for employees in dept # 2 who has salary less than 250.
SELECT ename FROM emp WHERE dno = 2 AND salary < 250;
14.          List all employees that are not in department 1.
SELECT * FROM emp WHERE NOT(dno = 1);
SELECT * FROM emp WHERE dno <> 1;
15.          List all employees that has salary between 100 and 250.
SELECT eno, ename FROM emp WHERE salary>=100 AND salary<=250;
SELECT eno, ename FROM emp WHERE salary BETWEEN 100 AND 250;
16.          List all employees that do no have a supervisor.
SELECT eno, ename FROM emp    WHERE supno IS NULL;
17.          List all employees whose names start by letter “s”.
SELECT eno, ename FROM emp WHERE ename LIKE “s%”;    ORACLE
18.          List all employees whose names do not start with letter “a”.
SELECT eno, ename FROM emp WHERE ename NOT LIKE “a%”;  ORACLE
19.          List all employees who are working at department 1 or 2.
SELECT eno, ename FROM emp WHERE dno = 1 OR dno = 2;
SELECT eno, ename FROM emp WHERE dno IN (1, 2);
20.          List all employees sorted by their names.
SELECT eno, ename FROM emp ORDER BY ename;
21.          List all employees sorted by their names descending.
SELECT eno, ename FROM emp ORDER BY ename DESC;
22.          List all employees ordered by their dno and then by supno.
SELECT eno, ename FROM emp ORDER BY dno, supno;
23.          List all salary values.                        SELECT salary FROM emp;
24.          List all unique salary values.          SELECT DISTINCT salary FROM emp;
25.          Display the count, maximum, minimum, average of salary.
SELECT COUNT(*), MAX(salary), MIN(salary),AVG(salary) FROM emp;
26.          Display the count, max, and sum of salary of dno = 2.
SELECT COUNT(*), MAX(sal), SUM(sal) FROM emp WHERE dno = 2;
27.          Display the ename for employees that have maximum salary.
SELECT eno, ename FROM emp WHERE salary = Select MAX(salary) from emp);


1.             List all data about employees.                          select _______ from _________;
2.             Insert new record to the dept table with the following values: deptno = 30, dname = “housing” and location = “Zalaq”.
insert _________ dept ________ (30, “housing”, “Zalaq”);
3.             Delete all departments that are located in “Zalaq”. delete _ dept _ location=”Zalaq”;
4.             Give a salary increase by 10% for all employees in department #3.
________ emp ________ salary = salary*1.1 where deptno=3;
5.             List empno, ename, and salary for all employees who have commission 10.
_________ empno, ename, salary from emp where ______________;
6.             List empno and salary for all employees who have salary greater that 2000.
select empno, salary from __________ where ________________;
7.             List empno and salary of each employee with an increase 10%. Name the increased salary as newsal.
select empno, salary*_______ as ______ from emp;
8.             List empno, ename for employees in department # 2 who has salary less than 1500.
select empno, ename from emp where deptno = 2 ______ salary _____;
9.             List all data about employees that are not in department #1.
select _______ from emp where ________ (deptno = 1);
10.          List all data about employees that have salary between 1000 and 2000.
select * from emp WHERE salary _________ 1000 _______ 2000;
11.          List all data about employees that do not have a manager.
select * from emp where ___________ is ___________;
12.          List all data about employees whose names end with the letter “d”.
select * from emp where ename ________”___”;
13.          List all data about employees who have commission 10 or 40.
select * from emp where ________  ________ (10, 40);
14.          List all data about employees sorted by their salaries descending.
select * from emp _________ salary ________;
15.          List all unique commission values.  select ___________ comm ________ emp;
16.          List the maximum salary value.       select _______(_________) from emp;
17.          List the average of salary values of employees in department #3.
select ________(________) from emp where deptno = 3;
18.          For each department, list deptno and the sum of salary values of its employees.
select deptno, sum(salary) from emp ____________ _______;
19.          List the deptno for departments that have more than 3 employees.
select deptno from emp group by deptno _______ _______ > 3;
20.          What is the output of the following query?
select * from emp where comm < (select min(comm) from emp);


Consider the following table descriptions. Solve in SQL the following queries.
CUSTOMERS   A table containing information about customers
cid                  Unique identifier for a customer
cname  Name of a customer
city                City where the customer is located
discnt            Each customer has a discount percent
AGENTS                       A table containing information about agent employees
aid                  Unique identifier for an agent
aname  Name of agent
city                City where agent is based
percent     Percent commission each agent receives on each sale
PRODUCTS                  A table containing information about products for sale
pid                  Unique identifier for a product
pname  Name of product
city                City where this product is warehoused
quantity    Quantity on hand for sale
price Wholesale price for each unit product
ORDERS                      A table containing information about orders
ordno  Unique identifier for this order
month  Month the order was placed
cid         This customer …
aid         … purchased through this agent …
pid         … this specific product …
qty         … in this total quantity …
dollars     … at this dollar cost
a)         Find all (ordno, pid) pairs for ordersof quantity equal to 1000 or more.
            SELECT            ordno, pid         FROM               ORDERS                      WHERE            qty >= 1000;
b)         Find all product names of products priced between $0.5 and $1.00 inclusive.
            SELECT            pname  FROM PRODUCTS  WHERE price between 0.5 and 1;
c)         Find all (ordno, cname) pairs for orders of dollar value less than $500. Use one join here.
            SELECT            ordno, cname    FROM               ORDERS o, CUSTOMERS c
            WHERE            dollars < 500 and          o.cid = c.cid;
d)         Find all (ordno, aname) pairs for orders in March month. Use one join here.
            SELECT            ordno, aname    FROM               ORDERS o, AGENTS a
            WHERE            month = ‘March’ and      o.aid = a.aid;
e)         Find all (ordno, cname, aname) triples for orders in March month. Use two joins here.
            SELECT            ordno, cname, aname    FROM ORDERS o, CUSTOMERS c, AGENTS a
            WHERE            month = ‘March’ and      o.cid = c.cid and o.aid = a.aid;
f)          Find all the names of agents in Amman who placed orders with value less than $500.
            SELECT            a.aname            FROM               ORDERS o, AGENTS a
            WHERE            a.city = ‘Amman’ and o.dollars < 500 and           o.aid = a.aid;
g)         Find all the products names of productsin Ammancity ordered in March month.
            SELECT            p.pname           FROM               ORDERS o, PRODUCTS p
            WHERE            p.city = ‘Amman’ and o.month = ‘March’ and      o.pid = p.pid;
h)         Find all (cid, aid, pid) triples for customer, agent, product combinations that are all in the same city.
SELECT            c.cid, a.aid, p.pid          FROM               CUSTOMERS c, AGENTS a, PRODUCTS p
            WHERE            c.city = a.city and a.city = p.city;
i)          Display all pairs of aids for agentwho live in the same city.
            SELECT            a1.aid, a2.aid    FROM               AGENTS a1, AGENTS a2
            WHERE            a1.city = a2.city and a1.aid < a2.aid;
j)          Find pids of product ordered through agent a03 but not through agent a06.
            SELECT            Distinct pid                   FROM               ORDERS
            WHERE            aid = ‘a03’ and pid not in (Select pid from orders where aid = ‘a06’)
k)         Get aids of agents who place individual orders greater than $500 for customers living in Ammancity.
            SELECT            aid        FROM               CUSTOMERS c, ORDERS o
            WHERE            o.dollars > 500 and c.city = ‘Amman’ and           c.cid = o.cid;
Posted in exercises, Practice, sample SQL, sample SQL queries, sql | Leave a comment