Oracle ERP HandBook by Chaitanya

----------------------------------------------------------------------------------------
-- Purpose : Oracle ERP HandBook
-- Release Date: 03.Jun.2011
-- Created by : Siri
---------------------------------------------------------------------------------------

Oracle APPS Technical Handbook gives a complete hands on experience for freshers.

The link is now disabled Oracle ERP Handbook.pdf, Contact kchaitanya.v@gmail.com for the same.

Contents of the Oracle ERP Handbook.pdf is mentioned below.

Write to us to get more updates on oracle technologies.

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

Watch this Space to get more updates on Fusion HCM

Thanks,

Chaitanya

Fixed Asset details - R12

-- ----------------------------------------------------------------------------------------------------
-- Purpose : R12 Fixed Asset details
-- Release Date: 13.Jul.2012
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select fab.asset_id     asset_id,
fab.asset_number     asset_number,
fal.description     asset_description,
fab.current_units     units,
(select sum(fixed_assets_cost)
from apps.fa_invoice_details_v
where asset_id = fab.asset_id)   cost,
fab.serial_number     serial_number,
fab.model_number     model_number,
fab.tag_number     tag_number,
(select ASSIGNMENT_NUMBER
from apps.per_all_assignments_f
where person_id = fdh.assigned_to
and trunc(sysdate) between effective_start_date and effective_end_date
)     assigned_to
from apps.fa_additions_b fab,
apps.fa_distribution_history fdh,
apps.fa_additions_tl fal
where 1=1
and fab.asset_id = fdh.asset_id
and fal.asset_id = fab.asset_id
and trunc(sysdate) between fdh.date_effective and nvl(fdh.date_ineffective,sysdate+1);


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

To get the Internal BankAccount / Bank Branch Details - R12

-- ----------------------------------------------------------------------------------------------------
-- Purpose : R12 To get the Internal BankAccount / Bank Branch Details
-- Release Date: 13.Jul.2012
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select ieba.ext_bank_account_id,
ieba.bank_account_num,
ieba.bank_account_name,
ieba.bank_id,
ieba.branch_id,
ieb.bank_name,
iebb.bank_branch_name,
iebb.branch_number,
iebb.eft_swift_code,
iebb.address_line1,
iebb.address_line2,
iebb.address_line3,
iebb.address_line4,
iebb.city,
iebb.state,
iebb.country,
iebb.zip,
ieba.creation_date,
(select user_name from fnd_user where user_id = ieba.created_by) created_by
from
apps.iby_ext_bank_branches_v iebb,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_accounts ieba
where iebb.bank_party_id = ieba.bank_id
and iebb.branch_party_id = ieba.branch_id
and ieb.bank_party_id = iebb.bank_party_id
and ieb.bank_party_id = ieba.bank_id

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

To get the selected invoices for the payment batch

-- ----------------------------------------------------------------------------------------------------
-- Purpose : 11i To get the selected invoices for the payment batch, After invoice selection Screen
-- Release Date: 22.Sep.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select ai.INVOICE_NUM,
ai.INVOICE_AMOUNT,
ai.AMOUNT_PAID,
asi.CHECKRUN_NAME,
asi.CHECK_NUMBER,
asi.AMOUNT_REMAINING,
abb.BANK_NAME,
abb.BANK_BRANCH_NAME,
asi.BANK_ACCOUNT_NUM,
asi.BANK_ACCOUNT_TYPE
from ap_selected_invoices_all asi,
AP_BANK_ACCOUNTS_all aba,
AP_BANK_BRANCHES abb,
ap_invoices_all ai
where 1=1
and asi.checkrun_name = :Payment_Batch_name
and asi.INVOICE_ID = ai.INVOICE_ID
and aba.BANK_ACCOUNT_NUM = asi.BANK_ACCOUNT_NUM
and aba.BANK_ACCOUNT_TYPE = asi.BANK_ACCOUNT_TYPE
and aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
order by 4,1;

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

Query to get all the period statuses for all the applications

-- ----------------------------------------------------------------------------------------------------
-- Purpose : To get current period statuses for all the application modules at a single query
-- Release Date: 03.Aug.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select gsob.NAME sob_name,
fav.APPLICATION_NAME,
gps.PERIOD_NAME,
gps.start_date,
gps.END_DATE,
(select meaning from fnd_lookups
where lookup_code = gps.CLOSING_STATUS
and lookup_type = 'IGC_CC_PERIOD_STATUS') Period_status
from GL_PERIOD_STATUSES gps,
fnd_application_vl fav,
gl_sets_of_books gsob
where gps.APPLICATION_ID = fav.APPLICATION_ID
and gsob.set_of_books_id = gps.SET_OF_BOOKS_ID
and fav.APPLICATION_SHORT_NAME = nvl(:appl_short_name,fav.APPLICATION_SHORT_NAME)
and gps.PERIOD_NAME = nvl(:p_period,to_char(sysdate,'Mon-YY'))
order by 1,2

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

Query to get the list of all profiles defined at different levels

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the List of all profiles defined at different levels
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select fpo.profile_option_name Profile_name
, fpot.user_profile_option_name User_Profile_Name
, fpot.description Description
, fpo.start_date_active Start_date
, fpo.end_date_active End_Date
, fpo.creation_date Creation_Date
, fu.user_name created_by
, fpov.LAST_UPDATE_DATE Updated_Date
, (select user_name from fnd_user fu1
where fpov.LAST_UPDATEd_by = fu1.user_id) Updated_by
, decode(fpov.level_id ,10001, 'Site',10002, 'Application',10003, 'Responsibility',10004, 'User') Profile_Level
, fpov.profile_option_value Profile_option_value
from FND_PROFILE_OPTIONS_vl fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
order by fpov.level_id;

Query to get the account details for a particular payable bank

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the account details for a particular payable bank
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select abb.bank_name,
aba.BANK_ACCOUNT_NAME,
aba.BANK_ACCOUNT_NUM,
aba.DESCRIPTION,
aba.CURRENCY_CODE curr_code
from AP_BANK_branches abb,
AP_BANK_ACCOUNTS_ALL aba
where abb.BANK_BRANCH_ID = aba.BANK_BRANCH_ID
and abb.bank_name = :Bank_name