GSM_NETWORK_PICTURE

FA_Ad_Query_Example

select

adb.asset_number,

adb.tag_number,

adt.description,

bks.date_placed_in_service,

adb.attribute3 ,

adb.attribute4 ,

adb.serial_number,

adb.current_units,

bks.prorate_convention_code,

bks.deprn_method_code,

bks.life_in_months,

bks.original_cost,

to_number(substr(get_asset_depren(bks.asset_id),1,instr(get_asset_depren(bks.asset_id),'-')-1 )) YEAR_To_DATE_DEPRN,

to_number(substr(get_asset_depren(bks.asset_id),instr(get_asset_depren(bks.asset_id),'-')+1 ))   Acc_DEPRN,

bks.original_cost-substr(wtc_get_asset_depren(bks.asset_id),instr(get_asset_depren(bks.asset_id),'-')+1 )NBV  

from 

fa_books bks,

fa_additions_b adb,

fa_additions_tl adt,

fa_categories_b cata,

fa_transaction_headers ts

where 

bks.transaction_header_id_out is null

and bks.date_ineffective  is null

and bks.book_type_code='YourASSETS BOOK'

and bks.asset_id=ts.asset_id

and adb.asset_id=ts.asset_id

and adt.asset_id=bks.asset_id

and adb.asset_category_id=cata.category_id

and ts.transaction_type_code='ADDITION'

and ts.date_effective between (select

to_char(p.period_open_date,'DD-MON-YY')

from 

fa_deprn_periods p 

where

p.calendar_period_open_date>=&fromDate

and p.calendar_period_close_date<=&toDate) and   (select

to_char(p.period_close_date,'DD-MON-YY')

from 

fa_deprn_periods p 

where

p.calendar_period_open_date>=&fromDate

and p.calendar_period_close_date<=&toDate); --  period open date and period close date --period name=NOV-14,OCT-14,SEP-14

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

select bks.asset_id,bks.date_effective

from 

fa_books bks

where bks.date_effective between '22-NOV-13' and '10-DEC-13' --  period open date and period close date --period name

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

select

to_char(p.period_open_date,'DD-MON-YY')

from 

fa_deprn_periods p 

where

p.calendar_period_open_date>=&fromDate

and p.calendar_period_close_date<=&toDate;

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

select

to_char(p.period_close_date,'DD-MON-YY')

from 

fa_deprn_periods p 

where

p.calendar_period_open_date>=&fromDate

and p.calendar_period_close_date<=&toDate;

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

--Note

create or replace function Get_Asset_Depren(xxAssetId in number) return varchar2

is

    l_deprn_reserve             NUMBER;

    l_ytd_deprn                 NUMBER; -- Year-to-Date Depreciation

    l_bonus_deprn_reserve       NUMBER;

    l_bonus_ytd_deprn           NUMBER;

    l_reval_reserve             NUMBER;

    l_real_cost                 NUMBER;

    dummy_num                   NUMBER;

    dummy_char                  VARCHAR2 (100);

    dummy_bool                  BOOLEAN;

    l_run_mode                  VARCHAR2 (20)  := 'STANDARD';

    V_SOB_ID                    NUMBER:=0000;

    V_log_level_rec             APPS.FA_API_TYPES.log_level_rec_type;

   

 begin

    apps.fa_query_balances_pkg.query_balances(x_asset_id                  => xxAssetId,

                                              x_book                      => 'YOUR ASSETS BOOK',

                                              x_period_ctr                =>0,

                                              x_dist_id                   =>0,

                                              x_run_mode                  => l_run_mode,

                                              x_cost                      => l_real_cost,

                                              x_deprn_rsv                 => l_deprn_reserve,

                                              x_reval_rsv                 => l_reval_reserve,

                                              x_ytd_deprn                 => l_ytd_deprn,

                                              x_ytd_reval_exp             => dummy_num,

                                              x_reval_deprn_exp           => dummy_num,

                                              x_deprn_exp                 => dummy_num,

                                              x_reval_amo                 => dummy_num,

                                              x_prod                      => dummy_num,

                                              x_ytd_prod                  => dummy_num,

                                              x_ltd_prod                  => dummy_num,

                                              x_adj_cost                  => dummy_num,

                                              x_reval_amo_basis           => dummy_num,

                                              x_bonus_rate                => dummy_num,

                                              x_deprn_source_code         => dummy_char,

                                              x_adjusted_flag             => dummy_bool,

                                              x_transaction_header_id     => -1,

                                              x_bonus_deprn_rsv           => l_bonus_deprn_reserve,

                                              x_bonus_ytd_deprn           => l_bonus_ytd_deprn,

                                              x_bonus_deprn_amount        => dummy_num,

                                              X_IMPAIRMENT_RSV            => dummy_num,

                                              X_YTD_IMPAIRMENT            => dummy_num,

                                              X_IMPAIRMENT_AMOUNT         => dummy_num,

                                              X_CAPITAL_ADJUSTMENT        => dummy_num,

                                              X_GENERAL_FUND              => dummy_num,

                                              X_MRC_SOB_TYPE_CODE         => NULL,

                                              X_SET_OF_BOOKS_ID           => V_SOB_ID,

                                              p_log_level_rec             => V_log_level_rec

                                              );

 --dbms_output.put_line(l_ytd_deprn||'   '||l_deprn_reserve);

 return  l_ytd_deprn||'-'||l_deprn_reserve;

 END;

--Summary for the above.

/*First get the asset id from the fa_transaction_headers table where transaction_type_code='ADDITION' and also 

where the date_effective of the fa_transaction_headers is between the period_open_date and period_close_date taken from

the fa_deprn_period table i.e; when the fa_derpn period is opened and closed and shown above */

SQL JOINS Exmaples

Example Tables

Employee table

LastName        DepartmentID (FroeignKey)

Rafferty 31

Jones            33

Heisenberg 33

Robinson 34

Smith            34

John            NULL

Department table

DepartmentID DepartmentName

31                        Sales

33                        Engineering

34                        Clerical

35                       Marketing

SELECT *

FROM employee JOIN department

  ON employee.DepartmentID = department.DepartmentID;

We can write equi-join as below,

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID;

Inner Join Result           ensures No Null values be matched

LastName   empid/depttid DepartmentName

Rafferty   31     Sales

Jones      33     Engineering

Heisenberg 33     Enginnering

Robinson   34     Clerical

Smith      34     Clerical

SELECT *

FROM employee LEFT OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

Alternative syntax

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID(+)

Outer Join Result            may contain Null values

LastName   empid/deptid     DepartmentName 

Rafferty             31   Sales

Jones                  33   Engineering

Heisenberg      33   Enginnering

Robinson          34    Clerical

Smith                  34    Clerical

John              NULL   NULL

Note outer Join  A left outer join returns all the values from an inner join plus all values in the left 

table that do not match to the right table.

Right outer Join 

SELECT *

FROM employee RIGHT OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

LastName   empid/deptid    DepartmentName 

Rafferty             31   Sales

Jones                 33   Engineering

Heisenberg     33   Enginnering

Robinson         34    Clerical

Smith                34    Clerical

NULL           NULL Marketing

Note

A right outer join returns all the values from the right table and matched values from the left table 

If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records 

that have no match in B.

Now Full outer Join combines both Left and right outer join results

SELECT *

FROM employee FULL OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

LastName   empid/deptid     DepartmentName 

Rafferty           31   Sales

Jones                33   Engineering

Heisenberg    33   Enginnering

Robinson        34            Clerical

Smith               34            Clerical

NULL           NULL Marketing

John           NULL   NULL

Telecom GSM

Taken from TelecomABC.com

Summary of GSM
The GSM network infrastructure consists of one or more Mobile Switching Centres (MSC) each connected to and controlling 2 or 3 Base Station Controllers (BSC). Each BSC is connected to and controlling a number of Base Transceiver Stations (BTS).

MSC
components of the MSC;
1) AUC (Authentication)
2) EIR (Equipment identity register)
3) VLR (Visiting location register)
4) HLR (Home location register)
5) IWF (Internetwork function)
6) EC (Echo canceller )
VLR and HLR are control data bases and EIR records the IMEIs which to allow and which to block and which is under any problem. AUC used for authentication of the users. Ec used to connect the PSTN with cellular network. IWF used for data rate adoption and protocol conversation.
Note that GMSC is not part of MSC but a type of MSC which used for translation between different networks of point of interconnection for the different networks... 

BSC
The Base Station Controller (BSC) is in control of and supervises a number of Base Transceiver Stations (BTS). The BSC is responsible for the allocation of radio resources to a mobile call and for the handovers that are made between base stations under his control. Other handovers are under control of the MSC.

A base station controller (BSC) is a critical mobile network component that controls one or more base transceiver stations (BTS), also known as base stations or cell sites. Key BSC functions include radio network management (such as radio frequency control), BTS handover management and call setup. 

Taken From Wikipedia, the free encyclopedia

BTS
The Base Transceiver Station (BTS) is a term used to denote a base stationin GSM terminology. A BTS consists of an antenna and the radio equipment necessary to communicate by radio with a Mobile Station (MS). Each BTS covers a defined area, known as a cell. A BTS is under control of a BSC, which is in turn under control of a MSC (Mobile Switching Centre).

A base transceiver station (BTS) is a piece of equipment that facilitates wireless communication between user equipment (UE) and a network. UEs are devices like mobile phones (handsets), WLL phones, computers with wireless Internet connectivity. The network can be that of any of the wireless communication technologies like GSM, CDMA, wireless local loop, Wi-Fi, WiMAX or other wide area network (WAN) technology.

BTS is also referred to as the radio base station (RBS), node B (in 3G Networks) or, simply, the base station (BS). For discussion of theLTE standard the abbreviation eNB for evolved node B is widely used.

Though the term BTS can be applicable to any of the wireless communication standards, it is generally associated with mobile communication technologies like GSM and CDMA. In this regard, a BTS forms part of the base station subsystem (BSS) developments for system management. It may also have equipment for encrypting and decrypting communications, spectrum filtering tools (band pass filters), etc. antennas may also be considered as components of BTS in general sense as they facilitate the functioning of BTS. Typically a BTS will have several transceivers (TRXs) which allow it to serve several different frequencies and different sectors of the cell (in the case of sectorised base stations). A BTS is controlled by a parent base station controller via the base station control function (BCF). The BCF is implemented as a discrete unit or even incorporated in a TRX in compact base stations. The BCF provides an operations and maintenance (O&M) connection to the network management system (NMS), and manages operational states of each TRX, as well assoftware handling 

and alarm collection. The basic structure and functions of the BTS remains the same regardless of the wireless technologies

Picture of BTS

following is taken from link 

http://www.engineersgarage.com/contribution/difference-between-2g-and-3g

Difference between 2G and 3G Network and Technology

Second Generation (2G) technology was launched in the year 1991 in Finland. It is based on the technology known as global system for mobile communication or in short we can say GSM. This technology enabled various networks to provide services like text messages, picture messages and MMS. In this technology all text messages are digitally encrypted due to which only the intended receiver receives message. These digital signals consume less battery power, so it helps in saving the battery of mobiles.

 

The technologies used in 2G are either TDMA (Time Division Multiple Access) which divides signal into different time slots or CDMA (Code Division Multiple Access) which allocates a special code to each user so as to communicate over a multiplex physical channel.

 

3G technology generally refers to the standard of accessibility and speed of mobile devices. It was first used in Japan in the year 2001. The standards of the technology were set by the International Telecommunication Union (ITU). This technology enables use of various services like GPS (Global Positioning System), mobile television and video conferencing. It not only enables them to be used worldwide, but also provides with better bandwidth and increased speed.

 

This technology is much more flexible as it can support 5 major radio technologies that operate under CDMA, TDMA and FDMA. CDMA accounts for IMT-DS (direct speed), IMT-MC (multi carrier). TDMA holds for IMT-TC (time code), IMT-SC (single carrier). This technology is also comfortable to work with 2G technologies. The main aim of this technology is to allow much better coverage and growth with minimum investment.

 

Figure: Evolution of Mobile system from 2G to 3G

 

 

Difference between 2G and 3G Technology

·         Cost: The license fee to be paid for 3G network is much higher as compared to 2G networks. The network construction and maintenance of 3G is much costlier than 2G networks. Also from the customers point of view the expenditure for 3G network will be excessively high if they make use of the various applications of 3G. 

 

·         Data Transmission:  The main difference between 2G and 3G networks is seen by the mobile users who download data and browse the Interneton the mobile phones. They find much faster download speeds, faster access to the data and applications in 3G networks as compared to 2G networks. 2G networks are less compatible with the functions of smart phone. The speed of data transmission in 2G network is less than 50,000 bits per sec while in 3G it can be more than 4 million bits per sec.

 

·         Function: The main function of 2G technology is the transmission of information via voice signals while that of 3G technologies is data transfer via video conferencing, MMS etc.

 

·         Features: The features like mobile TV, video transfers and GPS systems are the additional features of 3G technology that are not available with 2G technologies.

 

·         Frequencies: 2G technology uses a broad range of frequencies in both upper and lower bands, under which the transmission depends on conditions such as weather. A drawback of 3G is that it is simply not available in certain regions.

 

·         Implication: 3G technology offers a high level of security as compared to 2G technology because 3G networks permit validation measures when communicating with other devices. 

 

·         Making Calls: Calls can be made easily on both 2G and 3G networks with no real noticeable differences except that in 3G network video calls can also be made. The transmission of text messages and photos is available in both the networks but 2G networks have data limit and the speed of the data transmission is also very slow as compared to 3G.

 

·         Speed:  The downloading and uploading speeds available in 2G technologies are up to 236 Kbps. While in 3G technology the downloading and uploading speeds are up to 21 Mbps and 5.7 Mbps respectively.