GL Code combination query in oracle fusion

 select 
gcc.Code_Combination_id Account_Code_Combination_Id,
CONCATENATED_SEGMENTS Account_Code_Combination, 
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7 ,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
(
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment1
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT1'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment1_desc,
 (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment2
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT2'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment2_desc,
  (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment3
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT3'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment3_desc,
 (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment4
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT4'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment4_desc,
  (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment5
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT5'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment5_desc,
 (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment6
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT6'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment6_desc,
 (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment7
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT7'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment7_desc,
  (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment8
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT8'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment8_desc,
  (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment9
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT9'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment9_desc,
   (
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment10
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT10'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment10_desc,
 (
 select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment11
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT11'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment11_desc,
 (
 select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment12
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT12'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment12_desc,
(
select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment13
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT13'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment13_desc ,
 (
 select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment14
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT14'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment14_desc,
 (
 select DESCRIPTION  from fnd_flex_values_vl 
where flex_value  = gcc.segment15
AND FLEX_VALUE_SET_ID IN
 (
    SELECT FLEX_VALUE_SET_ID FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE  application_id           = 101
    AND id_flex_code           = 'GL#'
    AND enabled_flag           = 'Y'
    AND application_column_name='SEGMENT15'
and id_flex_num=gcc.chart_of_Accounts_id
  )
 ) segment15_desc,
gcc.START_DATE_ACTIVE GL_Effective_From,
gcc.END_DATE_ACTIVE GL_effective_To,
to_Char(gcc.LAST_UPDATE_DATE,'yyyy-MM-dd HH:mm:ss')  LAST_UPDATE_DATE
from gl_Code_Combinations gcc 
where trunc(gcc.last_update_date) between NVL(:P_FROM_DATE,trunc(gcc.last_update_date)) AND NVL(:P_END_DATE, trunc(gcc.last_update_date))
--and gcc.ENABLED_FLAG = 'Y'
--and nvl(END_DATE_ACTIVE,sysdate+1) >= sysdate
--and gcc.DETAIL_POSTING_ALLOWED_FLAG = 'Y'
and gcc.account_type in ('E','L','A')

Query to get oracle Project expenditures in oracle fusion

 select 
pet.EXPENDITURE_TYPE_ID Expenditure_Type_Id, 
pett.EXPENDITURE_TYPE_NAME Expenditure_Type,
pect.EXPENDITURE_CATEGORY_NAME Expenditure_Category,
pet.REVENUE_CATEGORY_CODE Revenue_Category_Code, 
pet.UNIT_OF_MEASURE UOM,
pett.DESCRIPTION Description,
case when END_DATE_ACTIVE is null then 'Y' else 'N' end 
Active_Flag 
from PJF_EXP_TYPES_B pet, PJF_EXP_TYPES_TL pett, PJF_EXP_CATEGORIES_TL PECT
where pet.EXPENDITURE_TYPE_ID = pett.EXPENDITURE_TYPE_ID 
and pett.language =  USERENV('LANG')
AND pet.EXPENDITURE_CATEGORY_ID = PECT.EXPENDITURE_CATEGORY_ID
and PECT.language =  USERENV('LANG')
AND trunc(PET.LAST_UPDATE_DATE) BETWEEN NVL(:P_FROM_DATE,trunc(PET.LAST_UPDATE_DATE)) AND NVL(:P_END_DATE,trunc(PET.LAST_UPDATE_DATE))

Query to get oracle projects in oracle fusion

 select  
(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hou.name||'-'||ppa.project_id||'-'||ptv.PROJ_ELEMENT_ID AP_External_Key__c,  
(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1) Source_System,
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'|| to_char(sysdate,'yyyymmddhhmmss') Batch_Id,
(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hou.name Company_Code_Unique_Key,
ppa.project_id Project_Id,
ppat.name Project_Name, 
ppa.segment1 Project_Number ,
ptv.PROJ_ELEMENT_ID  Task_Id,
PPETL.NAME Task_Name,
ptv.ELEMENT_NUMBER  Task_Number,
hou.name Expenditure_Orgranization,
ppa.ENABLED_FLAG Active_Status,
to_Char(ppa.LAST_UPDATE_DATE,'yyyy-MM-dd HH:mm:ss')  LAST_UPDATE_DATE,
ppa.attribute1 Temp_1,
ppa.attribute2 Temp_2,
ppa.attribute3 Temp_3,
ppa.attribute4 Temp_4,
ppa.attribute5 Temp_5,
ppa.attribute6 Temp_6,
ppa.attribute7 Temp_7,
ppa.attribute8 Temp_8,
ppa.attribute9 Temp_9,
ppa.attribute10 Temp_10
from PJF_PROJECTS_ALL_B ppa, 
PJF_PROJECTS_ALL_TL ppat, 
hr_organization_units_f_tl hou,
pjf_project_types_tl pptt,
PJF_PROJ_ELEMENTS_B  ptv,
PJF_PROJ_ELEMENTS_TL  PPETL
where ppa.project_id=ppat.project_id
and ppat.language =  userenv('LANG')
and ppa.org_id = hou.organization_id
and ppat.language= hou.language 
and ppa.project_type_id =  pptt.project_type_id
and hou.language = pptt.language
and ppa.project_id= ptv.project_id
and ptv.PROJ_ELEMENT_ID = PPETL.PROJ_ELEMENT_ID 
AND PPETL.LANGUAGE = USERENV('LANG') 
AND ptv.ELEMENT_TYPE = 'FINANCIAL' 
AND ptv.OBJECT_TYPE = 'PJF_TASKS'
and exists 
(
select 
1
from PJF_PROJECT_STATUSES_B pps, PJF_PROJECT_STATUSES_tl ppbt
where pps.PROJECT_STATUS_CODE =  ppa.PROJECT_STATUS_CODE 
and pps.PROJECT_STATUS_CODE = ppbt.PROJECT_STATUS_CODE
and ppbt.LANGUAGE = USERENV('LANG')
and PROJECT_STATUS_NAME not in 
(
'Canceled','Terminated','Suspended','Draft','Closed','Rejected'
)
)
and trunc(ppa.LAST_UPDATE_DATE) between nvl(:P_FROM_DATE,trunc(ppa.LAST_UPDATE_DATE)) and nvl(:P_END_DATE,trunc(ppa.LAST_UPDATE_DATE))

Query to get ship to location in oracle fusion

 select 
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1)||'-'|| hut.bu_name||'-'||loc.location_id AP_External_Key__c,
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1) Source_System,
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'|| to_char(sysdate,'yyyymmddhhmmss') Batch_Id,
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1)||'-'|| hut.bu_name Company_Code_Unique_Key, 
hut.bu_name Company_Code,
lot.location_code Ship_to_Location_Code,
lot.description Description,
decode(loc.ACTIVE_STATUS,'A','Y','I','N','Y') Active_Status,
to_char(loc.last_update_Date,'yyyy-MM-dd HH:mm:ss') last_update_Date,
null Temp_1,
null Temp_2,
null Temp_3,
null Temp_4,
null Temp_5,
null Temp_6,
null Temp_7,
null Temp_8,
null Temp_9,
null Temp_10 
from per_location_details_f loc,
     per_location_details_f_tl lot, 
     FUN_ALL_BUSINESS_UNITS_V  hut
where loc.location_details_id = lot.location_details_id
AND lot.language = userenv('LANG')
AND loc.effective_start_date = lot.effective_Start_date
AND loc.effective_end_date = lot.effective_end_date
AND trunc(sysdate) BETWEEN loc.effective_start_date and loc.effective_end_date
and loc.location_id=hut.location_id
and loc.ship_to_Site_flag ='Y'
AND trunc(LOC.LAST_UPDATE_DATE) BETWEEN NVL(:P_FROM_DATE,trunc(LOC.LAST_UPDATE_DATE)) AND NVL(:P_END_DATE,trunc(LOC.LAST_UPDATE_DATE))
union all
select 
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1)||'-'|| hut.name||'-'||loc.location_id AP_External_Key__c,
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1) Source_System,
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'|| to_char(sysdate,'yyyymmddhhmmss') Batch_Id,
(select 'Oracle-'||INSTANCE_NAME from gv$instance  where INSTANCE_NUMBER =1)||'-'|| hut.name Company_Code_Unique_Key, 
hut.name Company_Code,
lot.location_code Ship_to_Location_Code,
lot.description Description,
decode(loc.ACTIVE_STATUS,'A','Y','I','N','Y') Active_Status,
to_char(loc.last_update_Date,'yyyy-MM-dd HH:mm:ss') last_update_Date,
null Temp_1,
null Temp_2,
null Temp_3,
null Temp_4,
null Temp_5,
null Temp_6,
null Temp_7,
null Temp_8,
null Temp_9,
null Temp_10 
from per_location_details_f loc,
     per_location_details_f_tl lot, 
     inv_org_parameters iop, 
     hr_organization_units_f_tl hut
where 1=1
and loc.location_details_id = lot.location_details_id
AND lot.language = userenv('LANG')
AND loc.effective_start_date = lot.effective_Start_date
AND loc.effective_end_date = lot.effective_end_date
AND trunc(sysdate) BETWEEN loc.effective_start_date and loc.effective_end_date
and loc.INVENTORY_ORGANIZATION_ID =  iop.ORGANIZATION_ID
and iop.BUSINESS_UNIT_ID= hut.ORGANIZATION_ID
and loc.ship_to_Site_flag ='Y'
and hut.LANGUAGE = USERENV('LANG')
AND trunc(LOC.LAST_UPDATE_DATE) BETWEEN NVL(:P_FROM_DATE,trunc(LOC.LAST_UPDATE_DATE)) AND NVL(:P_END_DATE,trunc(LOC.LAST_UPDATE_DATE))

Query to get Tax setup in oracle fusion

 select 
(select 'Oracle-'||instance_name from v$instance) ||'-'||TAX_RATE_ID AP_External_Key__c,
(select 'Oracle-'||instance_name from v$instance)  Source_System,
(select 'Oracle-'||instance_name||'-'||to_Char(sysdate,'yyyymmddhhmmss') from v$instance) Batch_Id,
NULL Company_Code,
NULL Company_Code_Unique_Key,
TAX_RATE_ID ,
TAX_RATE_CODE Tax_Code,
PERCENTAGE_RATE Tax_Rate,
DESCRIPTION Tax_Description,
CASE WHEN NVL(EFFECTIVE_TO, SYSDATE+1) > SYSDATE 
THEN 'Y'
ELSE
'N'
END Tax_Status,
to_Char(LAST_UPDATE_DATE,'yyyy-MM-dd HH:mm:ss')  LAST_UPDATE_DATE,
attribute1 Temp_1,
attribute2 Temp_2,
attribute3 Temp_3,
attribute4 Temp_4,
attribute5 Temp_5,
attribute6 Temp_6,
attribute7 Temp_7,
attribute8 Temp_8,
attribute9 Temp_9,
attribute10 Temp_10
from ZX_RATES_B

Withholding tax setup query in oracle fusion

 select 
zht.GROUP_ID WHT_Inv_Group_Id,
zht.NAME WHT_Inv_Group_Name,
zht.DESCRIPTION Description ,
zrb.TAX WHT_Inv_Tax_Name,
zrb.PERCENTAGE_RATE WHT_Inv_Tax_Rate, 
zrb.tax_Rate_id WHT_Inv_Tax_Rate_Id,
ztb.TAX_AUTH_INV_CREATION_POINT WHT_Type, 
zrb.active_flag Active_flag,
to_char(zht.last_update_date,'yyyy-MM-dd HH:mm:ss') last_update_date,
zrb.attribute1 Temp_1,
zrb.attribute2 Temp_2,
zrb.attribute3 Temp_3,
zrb.attribute4 Temp_4,
zrb.attribute5 Temp_5,
zrb.attribute6 Temp_6,
zrb.attribute7 Temp_7,
zrb.attribute8 Temp_8,
zrb.attribute9 Temp_9,
zrb.attribute10 Temp_10
from 
ZX_WHT_TAX_CLASSIFICATION_V zht, 
zx_rules_b zr,
zx_party_tax_profile zpt,
HR_ORGANIZATION_UNITS_F_TL fab, 
zx_Rates_b zrb, 
zx_taxes_b ztb
where zht.name =  zr.TAX_RULE_CODE
and zpt.PARTY_TAX_PROFILE_ID = zr.CONTENT_OWNER_ID
and zpt.PARTY_ID = fab.ORGANIZATION_ID
and zr.tax=zrb.tax_Rate_Code
and zr.tax=ztb.tax
--and zr.TAX_RULE_CODE ='US WHT GROUP'
and fab.LANGUAGE = USERENV('LANG')

Query to get Purchase order types in oracle fusion

 select hut.NAME Company_Code, Pdt.DOCUMENT_SUBTYPE PO_Type,pdt.CO_LAYOUT_TEMPLATE  Description ,
pdt.last_update_date
from PO_DOCUMENT_TYPES_ALL_B pdt, HR_ORGANIZATION_UNITS_F_TL hut
where trunc(pdt.last_update_date) between nvl(:P_FROM_DATE,trunc(pdt.last_update_date)) and nvl(:P_END_DATE,trunc(pdt.last_update_date))
   and  pdt.prc_bu_id=hut.ORGANIZATION_id
   and hut.LANGUAGE = USERENV('LANG')
   order by hut.NAME