HOW TO BLOCK USER TO SEE OTHER DATABASE IN SQL SERVER

AS SALAMO ALAIKUM WA RAHMATULLAH

Create Database DemoDB2;
Create Database DemoDB1;

Create User User1;
Create User User2;

use DemoDB2
go
drop user User2;

ALTER AUTHORIZATION ON DATABASE::DemoDB2 TO User2;

user DemoDB2

use master
go


Deny View ANY DATABASE to User2;
--
Passion 4 Oracle
Ma Asalaam

Trigger Example in Oracle

As Salamo alaikum wa rahmatullah


--Create Table Name T

Create Table T
(
    ID number(10) primary key,
    FName varchar2(25) not null,
    Lname varchar2(25) not null
)

 --Create Table Name T_Audit 

Create Table T_Audit
(
    ID number(10) primary key,
    FName varchar2(25) not null,
    Lname varchar2(25) not null,
    Created_dt Date
)

-- Create Trigger 

Create Or Replace Trigger Trg_Test
Before INSERT ON T
For Each Row
Begin
    Insert into T_Audit (ID,Fname,Lname,Created_dt) values(:new.ID,:new.Fname,:new.Lname,sysdate);
End;

Insert into T Values (1,'Zaheer','Khan')
Insert into T Values (2,'Mohammad','Sami')
Insert into T Values (3,'Iqbal','Abdullah')

commit;

Select * from T


Select * from T_Audit

--
Ma Asalaam
Passion 4 Oracle

Package Example in Oracle

As salamo alaikum wa rahmatullah
------------------------------------------

-- Package Example
-- Package Specification

Create or Replace Package Pck_Test
as
procedure PrcPck_Test(eno IN Number);
end Pck_Test;

-- Package Body 

Create or Replace Package body Pck_Test
as
Procedure PrcPck_Test(eno IN Number)
as
enm varchar(50);
begin
select ename into enm from emp where empno=eno;
dbms_output.put_line('Employee no ->'||eno|| '  Employee name ->'|| enm);
exception
when others then
dbms_output.put_line('No data found'||enm);
end PrcPck_Test;
end Pck_Test;

-- output

begin
PCK_TEST.PRCPCK_TEST(7788);
end;

--


--
Ma Asalaam
Passion 4 Oracle

Procedure Example in Oracle

As Salamo alaikum wa rahmatullah

Select deptno, ename, sal, sum(sal) over(partition by
deptno order by deptno, ename) CUM_SAL
from
scott.emp;



-- exmaple of anonimous body
Declare
vname varchar2(20);
begin
select ename into vname from emp where empno=7788;
dbms_output.put_line(vname);
end;


-- Example of Implicit cursor
Declare
emp_row emp%RowType;
Begin
Select * into emp_row from emp where empno=7790;
if sql%notfound then
dbms_output.put_line('Record not found');
else
dbms_output.put_line('Record found');
end if;
end;
-- Procedure without parameter

create or replace Procedure Proc_Test
as
v_sal number(18,3);
Begin
Select Sal into v_sal from emp where empno=7788;
dbms_output.put_line(v_sal);
end Proc_Test;


-- Procedure with one IN parameter
create or replace Procedure Proc_Test_Inparameter (eno IN number)
as
vname emp.ename%type;
Begin
select ename into vname from emp where empno=eno;
dbms_output.put_line(vname);
Exception
when others then
dbms_output.put_line('Wrong number');
End Proc_Test_Inparameter;


-- Procedure with one IN one Out parameter

Create or replace procedure Proc_Test_InOutParam(eno IN number, enm OUT varchar2)
As
Begin
Select ename into enm from emp where empno=eno;
Exception
when others then
enm:='no data found';
End Proc_Test_InOutParam;


-- Procdure for INOUT parameter
create or replace procedure Proc_Test_InOutParam1(en IN OUT varchar)
As
Begin
Select job into en from emp where ename=en;
End Proc_Test_InOutParam1;



Declare
nm varchar2(100);
Begin
--Proc_Test;
--Proc_Test_Inparameter(7371);
Proc_Test_InOutParam(7788,nm);
dbms_output.put_line(nm);
nm:='SMITH';
Proc_Test_InOutParam1(nm);
dbms_output.put_line(nm);
End;



--
Ma Asalaam
Passion 4 Oracle

Function Example

As Salamo alaikum wa rahmatullah

-- Function 

Create or Replace function Fun_Test
return varchar2 
is
nm varchar(20);
begin
select ename into nm from emp where empno=7788;
return nm;
end Fun_Test;

Select Fun_Test() from dual;

-- Function with In Parameter
Create or Replace Function Fun_Test_In_Param(eno IN Number)
return varchar2
is
enm varchar2(100);
begin
select ename into enm from emp where empno=eno;
return enm;
end Fun_Test_In_Param;

Select Fun_Test_In_Param(7788) from dual;

-- Function with IN OUT Parameter

Create or Replace Function Fun_Test_In_Out_Param(eno IN Number,nm OUT varchar)
return varchar2
is
jb varchar2(20);
begin
select ename into nm from emp where empno=eno;
select job into jb from emp where ename=nm;
return jb;
end  Fun_Test_In_Out_Param;

Declare
rt varchar2(100);
nm1 varchar(100);
begin
rt:=Fun_Test_In_Out_Param(7788,nm1);
dbms_output.put_line('return value'||rt);
dbms_output.put_line('output parameter'||nm1);
end;



--
Ma Aslaam
Passion 4 Oracle

Oracle Packages

As salamo alaikum wa rahmatullah

About the DBMS_PIPE Package

Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a UNIX program.

At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored procedures in an Oracle database.

About the HTF and HTP Packages

Packages HTF and HTP allow your PL/SQL programs to generate HTML tags.

About the UTL_FILE Package

Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.

When you want to read or write a text file, you call the function FOPEN, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.

About the UTL_HTTP Package

Package UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. It can retrieve data from the Internet or call Oracle Web Server cartridges. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in hypertext markup language (HTML) format.

About the UTL_SMTP Package

Package UTL_SMTP allows your PL/SQL programs to send electronic mails (emails) over Simple Mail Transfer Protocol (SMTP). The package provides interfaces to the SMTP commands for an email client to dispatch emails to a SMTP server.

Guidelines for Writing Packages

When writing packages, keep them general so they can be reused in future applications. Become familiar with the Oracle-supplied packages, and avoid writing packages that duplicate features already provided by Oracle.

Design and define package specs before the package bodies. Place in a spec only those things that must be visible to calling programs. That way, other developers cannot build unsafe dependencies on your implementation details.

To reduce the need for recompiling when code is changed, place as few items as possible in a package spec. Changes to a package body do not require recompiling calling procedures. Changes to a package spec require Oracle to recompile every stored subprogram that references the package.

--

Ma Asalaam

Passion 4 Oracle

Invoice Reports

As salamo alaikum wa rahmatullah

Invoice Reports (Oracle Payables Implementation)
------------------------------------------------

Invoice Aging Report

>Use this report to view your unpaid invoices
>The report provides information about invoice payments due within four time periods you specify in the Aging Periods windows.

Invoice Audit Listing

>Use the Invoice Audit Listing  to audit invoices for duplicates. You should audit invoices periodically to ensure control of invoice payments. You can sort this listing in six differenct ways.

$$ For example, you may want to only audit over $1000. you can specify a minimum invoice amount and sort invoices by amount the supplier name and date.

>You can also use this report to obtain a listing of your invoices type. For example, you can submit the report to obtain a listing of just your expenses report invoices of your payments.

Invoice types - PO matched invoice, Quick Invoice, Manaul invoice or imported invoice from external sources.
>It helps 

Invoice History Report

>The Invoice History Report provides information to support the balance due on an invoice.
>It helps you quickly indentify and review a detailed list of all payment activities pertaining to a specific invoice sucan gains , losses and discounts.

>All amounts on the report are shown in the payment currency.

Invoice on Hold Report

> Use the Invoice on Hold Report to identify invoices on hold.
>The report provides you the total number and amount (in your ledger currency) of invoices on hold which can be helpful for your accounts payable metrics.
>Run the Invoice Validation process before submitting this report to obtain the most up-to-date hold information.

Invoice Register

>Use the Invoice Register to review detailed information about invoices.
>Payable ordrs the report by invoice currency and, if you use batch control, by the invoice batch name. Within the currency and batch name, the report orders by the supplier name and invoice number.

Payments Status Report

>Use the Payments Status Report to review the unapplied prepayment and unpaid or partially paid invoices for a supplier. you can compare the invoices and prepayments for a supplier to determine if there are outstanding repayments wich you can apply against unpaid invoices.
>You can submit the report for either Temporary Payment prepayments, or a combination of a specific prepayment type with credit/debit memos and nvoices. You can view the distribution detail for validated and paid prepayment invoices. Payables displays the prepayment item distributions that have an amount remaining greater than zero.


Recurring Invoices Report

>Use this report to review recurring invoice templates you define during a specific time period.You can review this report to determine the amount you have authorized for a recurring invoice template, how much you have released, and the next amount you have scheduled.
>The report also lists the number of periods remaining for a recurring invoice and teh next period you have scheduled.
>The report list recurring invoice templates by supplier and site.
-- 

Ma Asalaam
Passion 4 Oracle

Invoice Reports

As salamo alaikum wa rahmatullah

Invoice Reports (Oracle Payables Implementation)
------------------------------------------------

Invoice Aging Report

>Use this report to view your unpaid invoices
>The report provides information about invoice payments due within four time periods you specify in the Aging Periods windows.

Invoice Audit Listing

>Use the Invoice Audit Listing  to audit invoices for duplicates. You should audit invoices periodically to ensure control of invoice payments. You can sort this listing in six differenct ways.

$$ For example, you may want to only audit over $1000. you can specify a minimum invoice amount and sort invoices by amount the supplier name and date.

>You can also use this report to obtain a listing of your invoices type. For example, you can submit the report to obtain a listing of just your expenses report invoices of your payments.

Invoice types - PO matched invoice, Quick Invoice, Manaul invoice or imported invoice from external sources.
>It helps 

Invoice History Report

>The Invoice History Report provides information to support the balance due on an invoice.
>It helps you quickly indentify and review a detailed list of all payment activities pertaining to a specific invoice sucan gains , losses and discounts.

>All amounts on the report are shown in the payment currency.

Invoice on Hold Report

> Use the Invoice on Hold Report to identify invoices on hold.
>The report provides you the total number and amount (in your ledger currency) of invoices on hold which can be helpful for your accounts payable metrics.
>Run the Invoice Validation process before submitting this report to obtain the most up-to-date hold information.

Invoice Register

>Use the Invoice Register to review detailed information about invoices.
>Payable ordrs the report by invoice currency and, if you use batch control, by the invoice batch name. Within the currency and batch name, the report orders by the supplier name and invoice number.

Payments Status Report

>Use the Payments Status Report to review the unapplied prepayment and unpaid or partially paid invoices for a supplier. you can compare the invoices and prepayments for a supplier to determine if there are outstanding repayments wich you can apply against unpaid invoices.
>You can submit the report for either Temporary Payment prepayments, or a combination of a specific prepayment type with credit/debit memos and nvoices. You can view the distribution detail for validated and paid prepayment invoices. Payables displays the prepayment item distributions that have an amount remaining greater than zero.


Recurring Invoices Report

>Use this report to review recurring invoice templates you define during a specific time period.You can review this report to determine the amount you have authorized for a recurring invoice template, how much you have released, and the next amount you have scheduled.
>The report also lists the number of periods remaining for a recurring invoice and teh next period you have scheduled.
>The report list recurring invoice templates by supplier and site.
-- 

Ma Asalaam
Passion 4 Oracle

Delete Duplicate Row From SQL SERVER

AS SALAMO ALAIKUM WA RAHMATULLAH

WITH CTE (Fname,Lname, DuplicateCount)
AS
(
SELECT Fname,Lname,
ROW_NUMBER() OVER(PARTITION BY Fname,Lname ORDER BY Fname) AS DuplicateCount
FROM tbl_employee
)
DELETE
FROM CTE
WHERE DuplicateCount > 1

GO
--
MA ASALAAM
Passion 4 Oracle

Oracle Fast Formula

As salamo alaikum wa rahmatullah

You can use Oracle FastFormula to:

• Calculate your payrolls
• Define the rules for PTO accrual plans
• Define custom calculations for benefits administration
• Define QuickPaint reports
• Validate element inputs or user tables
• Edit assignment sets
• Calculate absence duration
• Configure people management templates
• Set up business rules and call them from other PL/SQL applications
• Define your Oracle Business Intelligence Systems reports
• Define collective agreements
• Define custom global person number sequences
• Define employment categories for EEO reports (US only)
• Calculate ratings for individual competencies and objectives, and calculate a total score for an appraisal

----

MA ASALAAM

Passion 4 Oracle

What is Profile Options?

AS SALAMO ALAIKUM WA RAHMATULLAH

A user profile is a set of changeable options that affect the way your application runs. The system administrator can set user profiles at different levels:

Site level     These settings apply to all users at an installation site.

Application level     These settings apply to all users of any responsibility associated with the application.

Responsibility level     These settings apply to all users currently signed on under the responsibility.

User level     These settings apply to an individual user, identified by their application username.

Important Profiles

1.1. HR: Business Group

1.2  HR: Security Option

1.3: HR: User Type (FOR accessing HRMS functions)

1.4  HR: Cross Business Group

2.1. GL: Set of Books(11i)

2.1  GL:%Ledger%  (R12)

2.3  GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.

3.1. MO: Operating Unit

3.2. MO: Security Profile (R12)

3.3. MO: Default Operating Unit

4.1 Tax: Allow Override of Tax Code

4.2 Tax: Invoice Freight as Revenue

4.3 Tax: Inventory Item for Freight

5.1 Sequential Numbering

5.2 INV: Intercompany Currency Conversion

6.1 RCV: Processing Mode - Batch, Immediate, Online

6.2 QA: PO Inspection - Oracle Purchasing , Oracle Quality

7.1 Hide Diagnostics menu entry

8.1 OE: Item Flexfield

This profile option indicates the structure of the Item Flexfield (System Items) used by Order Entry. This structure should be the same across all applications in the same database.

This profile option is visible and updatable at the site level.

8.2 OE: Item Validation Organization

This profile option indicates the Oracle Manufacturing organization against which items are validated. You must define all items that can be included in your transactions in this organization.

Set the OE: Item Validation Organization profile at the site level for the inventory organization whose master item number you want to use. This profile option indicates the organization that Receivables uses to validate items.

This profile option is visible and updatable at the site level. 

Values set at a higher level cascade as defaults to the lower levels. Values set at a lower level override any default from a higher level. For profile options that need to differ at the operating unit level, including OE: Item Validation Organization, OE: Set of Books, and GL: Set of Books, you must set the values at the responsibility level. Oracle General Ledger windows use the GL Set of Books profile option to determine your current set of books. If you have different sets of books for your operating units, you should set the GL Set of Books profile option for each responsibility that includes Oracle General Ledger windows.

For profile options that need to differ at the set of books level, including Sequential Numbering, set the values at the responsibility level.

Profile options specify default values that affect system processes, system controls, and data entry. In a multiple organization

environment you may want to confine the effect to a specific operating unit. Therefore, you may want to change your profile options to be visible and updatable at the responsibility level.

1. MO: Operating Unit = {the users Operating Unit name}

     This points the responsibility to the appropriate Operating Unit. 

This the profile which holds the value of operating unit org_id when ever user login into system his org_id is  value is transfered to profile value base on this profile  we get data and put data from databaseUsed primarily in a multiorg environment. 

     Set the site level to the desired default operating  unit. 

     If there is more than 1 Operating Unit Defined, this profile option must be set at the responsibility level for each responsibility.

2. OE: Set of Books and GL: Set of Books

Each Responsibility is identified with a set of books using the profile option GL : Set of Books Name, a responsibility can only see the accounting information for that set of books in orcale GL.

3. HR: Business Group 

Business Group that is linked to the security profile for a responsibility. This option is used online to control access to records that are not related to organization, position, or payroll.

This option is seeded at Site level with the start-up Business Group. It is view only. Values are derived from the HR:Security Profile user profile option.

HR:Security Profile     Restricts access to the organizations, positions, and payrolls defined in the security profile. This option is seeded at Site level with the view-all security profile created for the Startup Business Group.  The business group you define appears in the list of values when you set up the HR: Security Profile profile option.

Security Groups

Security groups are a method of partitioning data. When you use the standard HRMS security model, you do not use security groups. The business group is the only data partition. Responsibilities are linked to business groups. Therefore, to access different business groups, users must change responsibilities.

If you want one responsibility to be enabled for more that one business group, you must use Cross Business Group responsibility security. In this model, security groups are defined to partition data within a business group. Multiple security groups can then be linked to one responsibility, even if they partition different business groups. To use security groups you must set the user profile option Enable Security Groups to Yes and run the Multiple Security Groups process.

HR: Cross Business Group

In the Oracle HRMS model, the business group is at the country level and a top organization encompasses all business groups in a company worldwide. People, projects, jobs, and organizations can be located in different business groups for different countries and all information can be shared throughout the enterprise.

Oracle Projects allows the visibility of all business groups to one another. For example, you can search staff resources on projects across business groups, and charge any project across the enterprise for a resource.

You control access to single or multiple business groups by setting the profile option HR: Cross Business Group:

• Set the profile option to Yes to allow cross business group access.

• Set the profile option to No to allow only single business group access.

--

MA ASALAAM

PASSION 4 ORACLE

Just for Practice

As salamo alaikum wa rahmatullah

TITAN

USA_BG INDIA_BG UK_BG

USA_LED INDIA_LED UK_LED

SW_LE MT_LE FIN_LE

CAR_OU TRUCKS_OU BIKES_OU

INV_ORG



========================================
HRMS RESPONSIBILITY IS USED TO CREATE THE BUSINESS GROUP.

SEEDED HRMS RESPONSIBILITY IS HUMAN RESOURCES VISION ENTERPRISE
MENU - US SHRMS NAVIGATOR
DATA GROUP  - STANDARD APPLICATION - HUMAN RESOURCE
REQUEST GROUP - US SHRMS REPORTS AND PROCESSES
======================================



LEDGER
======
>CURRENCY
>CALENDAR (NORMAL CALENDAR JAN - DEC - FISCAL CALENDAR MAR-APR)
>CHART OF ACCOUNTS
>SUB LEDGER ACCOUNTING


CHART OF ACCOUNT (FORM ACCOUNT IN CHART - IT IS NOT ONLY THE BANK ACCOUNT WHILE IT IS ACCOUNT OF CHART)


STANDARD JOURNAL FORM
======================

COMPANY(TITAN)
-------------------------
COMPANY LEVEL TRANSACTION
----------------------------
SNO DESC CR_AMOUNT (COMING) DR_AMOUNT (GOING)
==== ===== ================== =================
01 COMPANY.CASH ------- 10,0000
02 COMPANY.RENT 10,000 -----
============= ============

10,0000 10,00000
DIVISIONS (CAR DIV/TRUCKS DIV/BIKE DIV)

DIVISION LEVERL TRANSACTION
---------------------------------
SNO DESC CR_AMOUNT (COMING) DR_AMOUNT (GOING)
==== ===== ================== =================
01 COMPANY.CASH ------- 10,0000
02 COMPANY.CARS.RENT 10,000 -----
============= ============
10,0000 10,00000

DEPARTMENTS (PO/AP/INV)
DEPARTMENT LEVERL TRANSACTION
--------------------------------
SNO DESC CR_AMOUNT (COMING) DR_AMOUNT (GOING)
==== ===== ================== =================
01 COMPANY.CASH ------- 10,0000
02 COMPANY.CARS.PO.RENT 10,000 -----
============= ============
10,0000 10,00000

LOCATIONS (HYDR/BUNG/DLH/CHN/PUNE)
LOCATION LEVERL TRANSACTION
-------------------------------------
SNO DESC CR_AMOUNT (COMING) DR_AMOUNT (GOING)
==== ===== ================== =================
01 COMPANY.CASH ------- 10,0000
02 COMPANY.CARS.PO.HYD.RENT 10,000 -----
============= ============
10,0000 10,00000
FOR THIS CHART OF ACCOUNT WE NEED 5 SEGMENT TO MAINTAIN OUR COMPANY CHART OF ACCOUNT BUT ORACLE CO OPERATION PEROVIDE 30 SEGMENT TO MAIN TAIN THE SECURTIY OF YOUR COMPANY.
IF YOUR TRANSACTION IS WORLD WIDE LEVERL IT IS MORE THAN ENOUGH TO HAVE 7 SEGMENT.

THERE ARE TWO TYPE OF FLEX FIELD AVAILABLE IN ORACLE APPLICATION
--------------------------------
1. KEY FLEX FIELD - 39 FLEX FIELD PROVIDES ORACLE
2. DESCRIPTIVE FLEX FIELD

=================================================================================
INVENTORY 
=========
CREATE INVENTORY RESPONSIBILITY (Seeded responsibility - Inventory, Vision Operations (USA))
===============================

Application - Inventory
Data Group - Name - Standard Application - Inventory
Menu - INV_NAVIGATE
Request Group -  All Inclusive GUI - Application Inventory


-----------------------------------------------------------

--
MA ASALAAM
Passion 4 Oracle

General Ledger Table with Navigation Steps

As salamo alaikum wa rahmatullah


-- RESPONSIBILITY TABLE

SELECT * FND_RESPONSIBILITY

-- LOCATIONS TABLE
NAV>WORKSTRUCTURE>LOCATION

SELECT * FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE LIKE 'TITAN%'


-- BUSINESS GROUP
NAV>WORKSTUCTURE>ORGANIZATION>DESCRIPTION

SELECT * FROM HR_ALL_ORGANIZATION_UNITS WHERE  NAME LIKE '%_BG%'

--LEDGER
--========
-- CURRENCY
NAV>SETUP>CURRENCIES>DEFINE

SELECT * FROM FND_CURRENCIES_TL WHERE NAME LIKE 'India%'

-- CALENDAR TYPE
NAV>SETUP>FINANCIAL>CALENDARS>TYPES

SELECT * FROM GL_PERIOD_TYPES WHERE USER_PERIOD_TYPE LIKE 'TITAN%'

-- CALENDAR MONTHS (PERIOD)
-------------------
NAV>SETUP>FINANCIAL>CALENDARS>ACCOUNTING

SELECT * FROM GL_PERIOD_SETS WHERE PERIOD_SET_NAME LIKE 'TITAN%'

SELECT * FROM GL_PERIODS WHERE PERIOD_SET_NAME LIKE 'TITAN%'


-- CHART OF ACCOUNT
--- FLEX FIELD - TWO TYPES OF FLEX FIELD KEY FLEX FIELD AND DESCRIPTIVE FLEX FILED
NAV>SETUP>FINANCIAL>FLEXFIELDS>KEY>SEGMENTS

SELECT * FROM FND_ID_FLEX_STRUCTURES_VL 

SELECT * FROM FND_ID_FLEXS

-- SEGMENTS STRUCTURE STORED
------------------------------
SELECT * FROM FND_ID_FLEX_STRUCTURES_TL WHERE ID_FLEX_STRUCTURE_NAME LIKE 'TITAN%'

SELECT * FROM FND_ID_FLEX_SEGMENTS WHERE ID_FLEX_NUM='53542'

--VALUE SET  TABLEN

SELECT * FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME LIKE 'TITAN%'

--VALUE SET VALUE
-----------------
NAV>SETUP>FINANCIAL>FLEXFIELDS>KEY>VALUES

Select * from FND_FLEX_VALUES

-- ACCOUNTING SETUP MANAGER LEDGER
--LEDGER
==
NVA>SETUP>FINANCIALS>FINANCIAL ACCOUNTING SETUP MANAGER>ACCOUNTING SETUPS

LEGAL ENTITY INFO
----

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME LIKE 'TITAN%'

SELECT * FROM GL_LEDGERS WHERE NAME LIKE 'TITAN%'

OPERATING UNIT 
----------------

SELECT * FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME LIKE 'TITAN%'

ONLY OPERATING UNIT
--------------------

SELECT * FROM HR_OPERATING_UNITS 
WHERE NAME LIKE'TITAN%'

INVENTORY ORGANIZATION
=====================
NAV>SETUP>ORGANIZATIONS>ORGANIZATION

SELECT * FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME LIKE 'TITAN%' 

ONY INVENTORY ORGANIZATION
========================
SELECT * FROM 

SELECT * FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_NAME LIKE 'TITAN%' 


--
MA ASALAAM
Passion 4 Oracle

FIND THE TABLE WHICH HAS ROW LOCK (UPDATE/INSERT)

AS SALAMO ALAIKUM WA RAHMATULLAH

The follwoing Query publish Table name which has Row Lock due to not commit the transaction.

select
  object_name(resource_associated_entity_id) as 'TableName' ,*
from
  sys.dm_tran_locks
where resource_type = 'OBJECT'
  and resource_database_id = DB_ID()

MA ASALAAM
--
Passion 4 Oracle

How many types replication in SQL Server

As Salamo Alaikum Wa Rahmatullah

There are three types of Replication in SQL Server 2012/2014.

1.Snapshot Replication
2. Transaction Replication
3. Merge Replication

--
Ma Asalaam
Passion 4 Oracle

Fixed Asset - Basic

As Salamo Aalaikum wa Rahmatullah

In simple terms, an asset is a resource having future economic benefit owned by an entity. Future benefit could mean in terms of contributing to the profit for profit oriented organizations and providing services for non-profit oriented organizations.

Assets are formally controlled and managed within larger organizations via the use of asset tracking tools. These monitor the purchasing, upgrading, servicing, licensing, disposal etc., of both physical and non-physical assets.

Assets may be classified in many ways. 

1. Current assets (Current deposits, Inventory)
2. Long-term investments (Investments in securities)
3. Fixed assets (land,buildings, machinery, furniture, tools)
4. Intangible assets (patents, copyrights, franchises, goodwill, trademarks)


Fixed assets are those that are expected to keep on providing benefit for more than one year. Some common examples are: scientific equipment, office equipment, photo copiers, land and buildings, vehicles, Machinery, Property held for Investment purpose.

--
Ma Asalaam
Passion 4 Oracle

Oracle Inventory - Basic

As salamo alaikum wa rahmatullah

Oracle Applications uses multiple types of organizations to build the business

execution structure. At the top of the structure is the accounting set of books

SOB), defined in the General Ledger. Next, different types of organizations are

used to further define the organization structure and relationships. All organizations are defined and updated with the Define Organization form.

Set of Books: A General Ledger SOB, linked to the inventory organization,

controls the financial accounting of inventory transactions. A SOB is made up

of a chart of accounts, a financial calendar, and a currency. 
The general ledger secures transactions (journal entries, balances) by SOB.

Legal Entity. A legal entity organization defines the tax and fiscal reporting

level. The legal entity represents the legal company.

Operating Unit: An operating unit organization defines the Purchasing, Order

Entry, Accounts Payable and Accounts Receivable level of operation. An operating unit may span multiple manufacturing facilities, distribution points and sales offices, or it may be limited to a single site.

Inventory Organization: Two flavors of inventory organizations are found in

Oracle Applications. They are defined the same, and both are assigned a set

of books, a legal entity organization, an operating unit organization, and a

location. An item master organization is used for item number maintenance and

validation. This master organization serves as a data repository storing items

and item attributes, master level categories and category sets, master level

cross references, and numerous data defaults. On-hand balances, inventory

movements, and other on-going inventory activities are not performed in an item master organization. Generally, the master organization is used as the

validation organization for Purchasing and Order Entry. It is recommended

that a single item master organization be defined, even in multiple organization,

multiple sets of books environments.

In addition to the item master organization there are one or more non-master

inventory organizations. Like the item master inventory organization, the

non-master organizations are assigned a set of books, a legal entity organization and an operating unit organization. The non-master inventory organization points to a master organization and looks to the master organization for master level item attributes, master level categories, and other master level controlled data.

Note that each organization has its own set of books/legal entity/operating unit

relationship, so inventory organizations with differing SOB’s or operating units

may share the same master organization.

These non-master inventory organizations are the execution level organizations.

They hold on-hand balances and transaction history. Here is where inventory

users execute their daily activities, such as receiving and issuing material,

performing cycle counts, and viewing material availability and transaction

history. A single organization therefore generally represents a single

manufacturing site or distribution center.

Locations: A location code is an address. Each inventory organization must

be assigned at least one location code.

Subinventories: A subinventory is used as a holding point for on-hand

inventory and generally represents a stockroom, stocking area or cage used

for storing material. Subinventories are defined within inventory

organizations. An inventory organization may have any number of

sub inventories, and an asset account is assigned to each sub inventory.

Since the subinventory entity is logical, as there is not an address or

physical location description associated with it, clients may define

sub inventories for any physical or logical grouping of inventory

Stock Locators: Stock locators are an optional entity that may be used to

represent physical locations within a subinventory. You may choose to use

stock locators for selected subinventories or selected items within selected

subinventories. If locators are used, subinventory and locator track on-hand

balances. Therefore, if locators are defined to represent a shelf within a

stockroom, on-hand balances on the system would show the item and quantity

down to the physical location within the facility.

Oracle Inventory uses a key flexfield for stock locators. This presents a few

limitations for its use. Only one locator flexfield definition is allowed per

install. Therefore, if the stockroom (subinventory) wants to track material

by row, bin and shelf, it will likely define a three-segment flexfield with

segments for row, bin, and shelf. If locators are desired for another

sub inventory, even in another organization, the structure will again be 3

segments for row, bin and shelf. In addition to this limitation, locators

must be unique within an organization; you cannot use the same locator in

different sub inventories within an organization, but you can use the same locator in sub inventories in a different organization.

--
Ma Asalaam
Passion 4 Oracle