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

Oracle Discoverer Handbook

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Discoverer Administrator / Desktop Handbook.
-- Release Date: 03.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

Oracle Discoverer Handbook gives a complete hands on experience in Oracle Discoverer Administrator and Discoverer Desktop.

Get the book download from this link Oracle Discoverer Handbook.pdf covers all areas in discoverer desktop and discoverer administrator.

Write to us to get more updates on oracle technologies.


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