Tuesday, 3 April 2018

REMOVE THE \r\n FROM THE COLUMN VALUE IN SQL SERVER

AS SALAMO ALAIKUM WA RAHMATULLAH

REMOVE THE \r\n FROM THE TABLE COLUMN VALUE AND UPDATE.


Select replace(replace(mobile_slno,char(10),''),char(13),'')
from tbl_str_receipt_det
where mobile_slno like'%'+char(13) or mobile_slno like '%'+char(10)

begin transaction a
update tbl_str_receipt_det
set mobile_slno=replace(replace(mobile_slno,char(10),''),char(13),'')
where mobile_slno like'%'+char(13) or mobile_slno like '%'+char(10)
rollback transaction a

MA ASALAAM
Passion 4 Oracle

Monday, 26 February 2018

E-Business Suit Fast Formula

As salamo Alikum wa rahmatullah

Fast Formula in E-business Suit, Please find at the following link

http://www.erpschools.com/articles/fast-formula


--
Ma Asalaam
Passion 4 Oracle


E-Business Suit (Error - Assignment Budget FTE is not equal to Position FTE Capacity)

As salamo alaikum wa rahmatullah

E-Business Suit Error 
Assignment Budget FTE is not equal to Position FTE Capacity
Solution at following link
http://students-of-oracle.blogspot.com/2012/12/position-fte-full-time-equivalent.html

Ma Asalaam
--
Passion 4 Oracle

Tuesday, 15 August 2017

UPDATE ROWS FROM THE SAME TABLE

AS SALAMO ALAIKUM WA RAHMATULLAH

SELECT * FROM UPD_ROW

ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 10 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 11 135

9 8 NOKIA-N30-GL 20 135

UPDATE COMMAND
;WITH ITEM_QTY (ID,QT)
AS
(
SELECT ITEMID,QTY FROM UPD_ROW
WHERE LOCATIONID=5) 
UPDATE UPD_ROW
SET QTY=QTY+ITEM_QTY.QT
FROM ITEM_QTY

WHERE LOCATIONID=135 AND ITEM_QTY.ID=ITEMID
-- RESULT
ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 15 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 21 135

9 8 NOKIA-N30-GL 30 135

--
MA ASALAAM
PASSION 4 ORACLE

Sunday, 16 July 2017

Formula to estimate content database storage

AS SALAMO ALAIKUM WA RAHMATULLAH

The following process describes how to approximately estimate the storage required for content databases, without considering log files:
Use the following formula to estimate the size of your content databases:
Database size = ((D × V) × S) + (10 KB × (L + (V × D)))
NoteNote:
The value, 10 KB, in the formula is a constant that approximately estimates the amount of metadata required by SharePoint Server 2013. If your system requires significant use of metadata, you may want to increase this constant.
Calculate the expected number of documents. This value is known as D in the formula.
How you calculate the number of documents will be determined by the features that you are using. For example, for My Sites or collaboration sites, we recommend that you calculate the expected number of documents per user and multiply by the number of users. For records management or content publishing sites, you may calculate the number of documents that are managed and generated by a process.
If you are migrating from a current system, it may be easier to extrapolate your current growth rate and usage. If you are creating a new system, review your existing file shares or other repositories and estimate based on that usage rate.
Estimate the average size of the documents that you'll be storing. This value is known as S in the formula. It may be worthwhile to estimate averages for different types or groups of sites. The average file size for My Sites, media repositories, and different department portals can vary significantly.
Estimate the number of list items in the environment. This value is known as L in the formula.
List items are more difficult to estimate than documents. We generally use an estimate of three times the number of documents (D), but this will vary based on how you expect to use your sites.
Determine the approximate number of versions. Estimate the average number of versions any document in a library will have. This value will usually be much lower than the maximum allowed number of versions. This value is known as V in the formula.
The value of V must be above zero.
As an example, use this formula and the characteristics in the following table to estimate the required storage space for data files in a content database for a collaboration environment. The result is that you need approximately 105 GB.

Input Value
Number of documents (D)
200,000
Calculated by assuming 10,000 users times 20 documents
Average size of documents (S)
250 KB
List items (L)
600,000
Number of non-current versions (V)
2
Assuming that the maximum versions allowed is 10
Database size = (((200,000 x 2)) × 250) + ((10 KB × (600,000 + (200,000 x 2))) = 110,000,000 KB or 105 GB
NoteNote:

Efficient File I/O in SharePoint Server 2013 is a storage method in which a file is split into pieces that are stored and updated separately. These pieces are streamed together when a user requests the file. This increases the I/O performance but it normally does not increase the file size. However, small files can see a small increase in the disk storage that is required.

--
MA ASALAAM
Passion 4 Oracle

Friday, 14 July 2017

SET TRANSACTION ISOLATION LEVEL

AS SALAMO ALAIKUM WA RAHMATULLAH

This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes.

The syntax is:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
The available options are:

READ UNCOMMITTED – Allows dirty reads, meaning data can be read from a transaction that is not yet complete.
READ COMMITTED – Only reads data from transactions that have already been committed.  Current transactions must wait for transactions referencing common resources to finish.
REPEATABLE READ – Data that is being read is exclusively locked until the transaction completes.
SNAPSHOT – The default for Oracle.  This level allows all data to be read without using locks by maintaining a snapshot of all the data being modified in “versioning” tables.  This is the only isolation level not lock based.
SERIALIZABLE – Data that is being read is exclusive locked and inserts are prevented within this range until the transaction completes.


Snapshot isolation is not available unless enabled at the database level. It’s recommended to know all implications prior to enabling.

---
Ma Asalaam
Passion 4 Oracle

Friday, 26 May 2017

Multi Organization in Oracle Apps R12

As salamo alaikum wa rahmatullah

What is Multi-Org?
It’s a feature used to store the data of multiple organization in a single instance by partitioning the data of human resource, financials, sales, purchases, assets and materials
Examples of Multi Org are Tata Group, Reliance Group, Mahindra Group etc.
Let us consider Tata Group to illustrate more on Multi Org
In the above illustration Tata Finance, TCS, Tata Motors are individual organizations.
NOTE: The complete organization data are maintained at one place.
Financial statements are to be created for individual organization separately this is achieved using E BUZ Suite.
Multi-Org has been categorized into:
1.     Business Group
2.     Ledger
3.     Legal Entities
4.     Operating Unit
5.     Inventory Org
 1.       Business Group:
Business Group is a top level in the org structure. At this level we secure work structures (Job Structure, Position structure, Grades structure) and remuneration policy (Pay roll Policy).
Structure for Jobs, Position, Grades may differ from organization to organization, say position structure in a organization may have clerk, senior clerk, manager, senior manager. Where as in another organization it may only have clerks and managers.
Based on the Work Structure and remuneration the business group are created. If all the 3 organization (Tata Finance, TCS, and Tata Motors) have different Work Structure and remuneration then we need to define 3 business groups else if they have the same Work Structure and remuneration then only one business group is defined.
Module involved is HR
 2.       Ledgers:
Ledgers are used to secure journals and ledger balances of the company. A ledger is a collection of currency, calendar, chart of accounts and sub ledger accounting (SLA) methods.
Currency, here we setup the local currency for the organization where its business is defined say INR (INDIAN Rupee) this is used only to report the balances but the transaction can be done in other foreign currency also including INR.
 Calendar, the financial year is been defined to control the transactions and secure them. We divide the financial year into PERIODS, periods may be defined daily, Monthly, Quarterly this is only to restrict the transaction as transaction can happen only for the period that’s open, say in July we can have transactions for the month of July and August as its open but we can restrict for September by keeping it closed.
 Chart of Accounts, here we define the accounts setup for an organization. For better understanding let us take an example where in we need to create an account when a rent has been paid. The account entry would be
 Rent A/c
To Cash
 Now to differentiate this based on the organization say if the rent is for Tata Finance or TCS or Tata Motors. The entries can be
So for this when the transaction is recorded the entry in the system would be 01.1001
If there are any further division (category) then a new segment is created but if an organization is not having any division then a default segment is also defined.
Now the account for tata motors having division for cars in a particular location say hyd and for payables as an account would be 01.001.0001.1002, if tata finance has no division and location but with rent account then the entry would be 02.000.0000.1001
Note: An account that is defined for an organization is a combination of Company code and Account code, that are defined if there are no segments (Division, Location etc) been defined. We should have minimum two segments and a maximum of 30 segments when defining an account.
Sub Ledger Accounting: (SLA), how to maintain the account is been defined in SLA, the accounts may be accrual based or cash based.
Module involved is GL
3.       Legal Entities:
This is a legal business or a registered firm; at this level we prepare income tax reports.
In our example we would be having 3 legal entities (Tata Motors, TCS, and Tata Finance)
Legal Entries are used to report the Tax Reporting
4.    Operating Unit: (OU)
 An operating unit is division of legal entity, at this level we secure the sales and purchase transactions.
Now we also need to keep in mind that an OU can be set at the division level or location level or branches based on the sectors.
Here in the above illustration Cars and Trucks are division, Hyd, Pune and Bangalore are location.  OU can be set at Division level or Location level or Branch Level.
If the OU is set at branch 1 then branch 2 cannot see the details (Sales and purchases) of branch 1 and vice versa as they are secured. Similarly if set at the location then it is secured with that location and restricted from other locations.
Module involved is AR, AP, PO, OM, CM etc
5.       Inventory Org:
This is used to secure the materials of an organization.
Note: Legal Entity can also be an Operating Unit as there are no further division in a organization.

Ma Asalaam
--
Passion 4 Oracle