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

Query to get supplier payment method in oracle fusion

 select*
from 
IBY_PAYMENT_METHODS_vl

Query to get GRN Lines detail in oracle fusion

 SELECT DISTINCT 
  hout.name Company_Code,  
  rt.Transaction_Id Transaction_Id,
  rt.PARENT_TRANSACTION_ID Parent_Transaction_Id,
  pha.segment1 PO_Number,
  (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hout.name||'-'||PHA.po_header_id||DECODE(XX.SEGMENT1,NULL,NULL,'-'||XX.SEGMENT1) Purchase_Order_Number_Unique_Key,
  rsh.comments Delivery_Note,
   to_char(rsh.gl_date,'yyyy-MM-dd') GRN_Posting_Date,
  PLA.LINE_NUM PO_Line_Number,
 (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hout.name||'-'||PHA.segment1||'-'||PLA.LINE_NUM||'-'||PLA.po_line_id||XX.SEGMENT1||'-'||plla.SHIPMENT_NUM PO_Line_Unique_Key, 
  rt.CURRENCY_CODE Currency,
  rsl.line_num GRN_Line_Number,
  (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) ||'-'||hout.name||'-'||pha.segment1||'-'||rsh.shipment_header_id GRN_Unique_Key, 
  rsh.receipt_num GRN_Number, 
  rt.TRANSACTION_TYPE Movement_Type,
  rt.QUANTITY*rt.PO_UNIT_PRICE Net_Amount,
  rt.QUANTITY Net_Quantity,
  rsl.ITEM_DESCRIPTION Product_Service_Desc,
  nvl((select ITEM_NUMBER from egp_system_items_b where inventory_item_id= pla.item_id and rownum=1),rsl.ITEM_DESCRIPTION) Product_Service_No,
  rt.PO_UNIT_PRICE Rate_unit_price,
  (select location_name from hr_locations hl
where hl.location_id  = rsl.DELIVER_TO_LOCATION_ID
) Storage_Location,
  /*(select LOCATOR_NAME  from INV_ITEM_LOCATIONS
where INVENTORY_LOCATION_ID  = rsl.locator_id
and SUBINVENTORY_CODE = rsl.TO_SUBINVENTORY
   )  Storage_Location,  */
  rt.UOM_CODE,
  PLA.QUANTITY-rt.QUANTITY Remaining_Qty,
  (PLA.QUANTITY-rt.QUANTITY) * rt.PO_UNIT_PRICE Remaining_Amount,
  to_Char(rsL.last_update_date,'yyyy-MM-dd HH:mm:ss') last_update_date,
    rsl.attribute1 Temp_1,
rsl.attribute2 Temp_2,
rsl.attribute3 Temp_3,
rsl.attribute4 Temp_4,
rsl.attribute5 Temp_5,
rsl.attribute6 Temp_6,
rsl.attribute7 Temp_7,
rsl.attribute8 Temp_8,
rsl.attribute9 Temp_9,
rsl.attribute10 Temp_10   
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
-- egp_system_items esib,
RCV_TRANSACTIONS  rt,
po_headers_All PHA,
po_lines_All PLA,  
poz_suppliers ps, 
hz_parties hp, 
poz_supplier_sites_All_m pssa ,
hr_organization_units_f_tl hout,
XLE_ENTITY_PROFILES xep
,(
select distinct '-'||poh.segment1 segment1, pol.from_header_id, pol.from_line_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx,
po_line_locations_All plla
where rsh.shipment_header_id=rsl.shipment_header_id
and  rsl.po_header_id=PHA.po_header_id
and rsl.po_line_id=PLA.po_line_id
--and rsl.ITEM_DESCRIPTION = esib.DESCRIPTION
--and rsl.TO_ORGANIZATION_ID =  esib.ORGANIZATION_ID
and rsh.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and pha.po_header_id=pla.po_header_id
and pha.PRC_BU_ID=pla.PRC_BU_ID
and rsh.vendor_id= ps.vendor_id
and  ps.party_id= hp.party_id
and rsh.vendor_Site_id=pssa.vendor_Site_id
and ps.vendor_id=pssa.vendor_id
and pha.prc_bu_id=hout.organization_id
and PHA.DOCUMENT_STATUS not in ('CANCELED','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING'
)
and hout.LANGUAGE = USERENV('LANG')
and xep.LEGAL_ENTITY_ID =PHA.soldto_le_id
and hout.name = NVL(:P_BU_NAME,hout.name)
and trunc(rsL.last_update_date) between nvl(:P_FROM_DATE,trunc(rsL.last_update_date)) and nvl(:P_END_DATE,trunc(rsL.last_update_date))
and pha.po_header_id= xx.from_header_id(+)
and PLA.PO_Line_Id=xx.from_line_id(+)
and RT.PO_LINE_LOCATION_ID=plla.LINE_LOCATION_ID(+)

Query to get GRN Header detail in Oracle fusion

 SELECT DISTINCT  
   hout.name Company_Code,
   (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) ||'-'||hout.name Company_Code_Unique_Key,   
   pha.segment1 PO_Number,
   rsh.shipment_header_id GRN_Id,
rsh.receipt_num GRN_Number,
to_char(rsh.CREATION_DATE,'yyyy-MM-dd') GRN_Date,
to_char(rsh.expected_receipt_date,'yyyy-MM-dd') Expected_Receipt_Date,
to_char(rsh.last_update_date,'yyyy-MM-dd HH:mm:ss') last_update_date,
rsh.attribute1 Temp_1,
rsh.attribute1 Temp_2,
rsh.attribute1 Temp_3,
rsh.attribute1 Temp_4,
rsh.attribute1 Temp_5,
rsh.attribute1 Temp_6,
rsh.attribute1 Temp_7,
rsh.attribute1 Temp_8,
rsh.attribute1 Temp_9,
rsh.attribute1 Temp_10
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_headers_All PHA,
poz_suppliers ps, 
hz_parties hp, 
poz_supplier_sites_All_m pssa ,
hr_organization_units_f_tl hout,
XLE_ENTITY_PROFILES xep ,
(
select poh.segment1 , pol.from_header_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx
where rsh.shipment_header_id=rsl.shipment_header_id
and  rsl.po_header_id=PHA.po_header_id
and rsh.vendor_id= ps.vendor_id
and  ps.party_id= hp.party_id
and rsh.vendor_Site_id=pssa.vendor_Site_id
and ps.vendor_id=pssa.vendor_id
and pha.prc_bu_id=hout.organization_id
and hout.LANGUAGE = USERENV('LANG')
and PHA.DOCUMENT_STATUS not in ('CANCELED','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING'
)
and PHA.po_header_id= xx.from_header_id(+)
and xep.LEGAL_ENTITY_ID =PHA.soldto_le_id
and hout.name = NVL(:P_BU_NAME,hout.name)
and trunc(rsh.last_update_date) between nvl(:P_FROM_DATE,trunc(rsh.last_update_date)) and nvl(:P_END_DATE,trunc(rsh.last_update_date))

Query to get purchase order(PO) line detail in oracle fusion

 select
hou.name Company_Code,
poh.segment1 PO_Number,
pol.LINE_NUM PO_Line_No,
pol.po_line_id  PO_Line_Id,
null Shipment_Id,
pol.LINE_STATUS PO_Line_Status,
plla.SCHEDULE_STATUS Schedule_Status,
plla.SHIPMENT_NUM Schedule_Number,
plla.QUANTITY_RECEIVED Shipment_Quantity_Received,
plla.QUANTITY_ACCEPTED Shipment_Quantity_Accepted,
plla.QUANTITY_BILLED Shipment_Quantity_Billed,
plla.QUANTITY_CANCELLED Shipment_Quantity_Cancelled,
plla.QUANTITY  Shipment_Quantity_Ordered,
plla.CANCEL_FLAG Shipment_Cancel_Flag,
replace(XX.SEGMENT1,'-',null) Release_Number,
poh.DOCUMENT_STATUS Authorization_Status, 
CASE
when plla.RECEIPT_REQUIRED_FLAG = 'N'
then '2-Way'
when  plla.RECEIPT_REQUIRED_FLAG = 'Y' AND plla.INSPECTION_REQUIRED_FLAG ='N'
THEN '3-Way'
when  plla.RECEIPT_REQUIRED_FLAG = 'Y' AND plla.INSPECTION_REQUIRED_FLAG ='Y'
THEN '4-Way'
end
Match_Approval_Level,
pol.QUANTITY PO_Line_Quantity,
pol.QUANTITY * pol.UNIT_PRICE PO_Line_Amount,
null Schedule_Qty, 
null Schedule_Amount, 
pol.QUANTITY - plla.QUANTITY_RECEIVED Shipment_Remaining_Qty,
(pol.QUANTITY - plla.QUANTITY_RECEIVED)*pol.UNIT_PRICE  Shipment_Remaining_Amount,
(select ITEM_NUMBER from egp_system_items_b where inventory_item_id= pol.item_id and rownum=1) Material_No ,
to_char(plla.NEED_BY_DATE ,'yyyy-MM-dd') Need_By_Date,
pol.ITEM_DESCRIPTION Product_Description, 
to_char(plla.PROMISED_DATE ,'yyyy-MM-dd') Promised_Date,
pol.UNIT_PRICE Unit_Price,
pol.LIST_PRICE_PER_UNIT Price_Unit,
pol.UOM_CODE UOM,
pol.tax_code_id Tax_Code_Unique_Key,
null UNSPSC_Code,
pol.last_update_Date,
pol.attribute1 Temp_1,
pol.attribute2 Temp_2,
pol.attribute3 Temp_3,
pol.attribute4 Temp_4,
pol.attribute5 Temp_5,
pol.attribute6 Temp_6,
pol.attribute7 Temp_7,
pol.attribute8 Temp_8,
pol.attribute9 Temp_9,
pol.attribute10 Temp_10   
from po_headers_all poh, 
po_lines_all pol, 
po_line_locations_All plla,
--PO_DISTRIBUTIONS_ALL pda,
HR_ORGANIZATION_UNITS_F_TL hou, 
hr_locations hl,
PO_LINE_TYPES_TL pltt,
(
select distinct '-'||poh.segment1 segment1, pol.from_header_id, pol.from_line_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx
where poh.po_header_id=pol.po_header_id(+)
and pol.po_line_id=plla.po_line_id(+)
--and pol.po_header_id=pda.po_header_id(+)
--and pol.po_line_id=pda.po_line_id(+)
--and plla.line_location_id=pda.line_location_id(+)
and poh.PRC_BU_ID=hou.ORGANIZATION_id
and hou.LANGUAGE = USERENV('LANG')
and plla.SHIP_TO_LOCATION_ID= hl.location_id(+)
and pol.LINE_TYPE_ID =  pltt.LINE_TYPE_ID(+)
and hou.LANGUAGE=  pltt.LANGUAGE
--and poh.segment1='80306'
--and nvl(poh.LINE_STATUS,'XX')  not in ('CANCELED','CLOSED', 'REJECTED','CLOSED FOR INVOICING','FINALLY CLOSED','CLOSED FOR RECEIVING')
and poh.DOCUMENT_STATUS not in ('CANCELED','INCOMPLETE','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING')
and poh.po_header_id= xx.from_header_id(+)
and pol.PO_Line_Id=xx.from_line_id(+)
and hou.name= nvl(:P_bu_name,hou.name)
and trunc(pol.last_update_Date) between nvl(:P_FROM_DATE,trunc(pol.Last_update_Date)) and nvl(:P_END_DATE,trunc(pol.Last_update_Date))

Query to get Purchase order(PO) header detail in oracle fusion

 select distinct 
hou.name Company_Code,
poh.segment1 Purchase_Order_Number,
poh.CURRENCY_CODE  Currency,
XX.SEGMENT1 Release_Number,
Null E_Invoic_Flag,
(
select username from per_users where PERSON_ID= poh.agent_id and  
TRUNC(SYSDATE) BETWEEN (START_DATE) AND  NVL(END_DATE,SYSDATE+1)
and rownum=1
) GR_Responsible_SSO,
(select full_name from Per_person_names_f ppf  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND  NVL(PPF.EFFECTIVE_END_DATE,SYSDATE+1)
and rownum=1
) GR_Responsible_Name, 
(select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES pea  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
and rownum=1
) GR_Responsible_Email ,
(
select username from per_users where PERSON_ID= poh.agent_id and  
TRUNC(SYSDATE) BETWEEN (START_DATE) AND  NVL(END_DATE,SYSDATE+1)
and rownum=1
) PO_Buyer_SSO,
(
select full_name from Per_person_names_f ppf  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND  NVL(PPF.EFFECTIVE_END_DATE,SYSDATE+1)
and rownum=1
) PO_Buyer_Name, 
(
select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES pea  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
and rownum=1
) PO_Buyer_Email ,
(
select PU.username from Per_person_names_f ppf, per_users pu  
where ppf.PERSON_ID =  pu.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pu.START_DATE) AND  NVL(pu.END_DATE,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name)= poh.created_by
and rownum=1
) PO_Creator_SSO,
poh.created_by PO_Creator_Name, 
(
select EMAIL_ADDRESS from Per_person_names_f ppf, PER_EMAIL_ADDRESSES pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name)= poh.created_by
and rownum=1
) PO_Creator_Email_Address,
(
select pea.username from Per_person_names_f ppf, per_users pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.start_date) AND  NVL(pea.end_Date,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name) in 
(
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
)
)
and rownum=1
) PO_Requestor_SSO, 
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
) PO_Requestor_Name,
(
select EMAIL_ADDRESS from Per_person_names_f ppf, PER_EMAIL_ADDRESSES pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name) in 
(
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
)
)
and rownum=1
) PO_Requestor_Email,
nvl((select sum(nvl(pol.unit_price,0) * nvl(pol.quantity,0)) from po_lines_All pol where po_header_id=poh.po_header_id),0) Net_Amount,
nvl((
select sum(nvl(pol.unit_price,0) * nvl(pol.quantity,0))+ sum(nvl(RECOVERABLE_TAX,0)+nvl(NONRECOVERABLE_TAX,0)) from po_lines_All pol , po_distributions_All pda
where pol.po_header_id=poh.po_header_id
and pol.PO_LINE_ID=pda.PO_LINE_ID
),0) Total_Amount,
to_char(poh.creation_Date,'yyyy-MM-dd') PO_Date,
 poh.DOCUMENT_STATUS PO_Status ,
 poh.REVISION_NUM PO_Version,
 poh.TYPE_LOOKUP_CODE  Purchase_Order_Type, 
 decode(poh.ship_to_location_id,null,null,
 (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hou.name||'-'||(SELECT location_id FROM hr_locations
WHERE location_id = poh.ship_to_location_id
)) Ship_to_Location_Code_Unique_Key,
 xep.NAME Legal_Entity_Name,
 poh.Last_update_Date,
 poh.attribute1  Temp_1,
 poh.attribute2  Temp_2,
 poh.attribute3  Temp_3,
 poh.attribute4  Temp_4,
 poh.attribute5  Temp_5,
 poh.attribute6  Temp_6,
 poh.attribute7  Temp_7,
 poh.attribute8  Temp_8,
 poh.attribute9  Temp_9,
 poh.attribute10  Temp_10
from po_headers_All poh, HR_ORGANIZATION_UNITS_F_TL hou, poz_suppliers ps, poz_supplier_sites_All_m possa,
ap_terms att, xle_entity_profiles xep,
(
select poh.segment1 , pol.from_header_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx
where poh.prc_bu_id =  hou.ORGANIZATION_id
and hoU.LANGUAGE = USERENV('LANG')
and poh.vendor_id=ps.vendor_id(+)
and poh.vendor_id=possa.vendor_id(+)
and poh.Vendor_Site_id=possa.Vendor_Site_id(+)
and poh.prc_bu_id=possa.prc_bu_id
and poh.terms_id = att.term_id
and poh.SOLDTO_LE_ID =  xep.legal_entity_id(+)
and poh.approved_flag ='Y'
and poh.TYPE_LOOKUP_CODE not in ('BLANKET','CONTRACT')
and poh.DOCUMENT_STATUS not in ('CANCELED','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING','INCOMPLETE')
and poh.po_header_id= xx.from_header_id(+)
and hou.name = nvl(:P_BU_NAME,hou.name)
and trunc(poh.Last_update_Date) between NVL(:P_FROM_DATE,trunc(poh.Last_update_Date)) and NVL(:P_END_DATE,trunc(poh.Last_update_Date))

Query to get supplier bank master data in oracle fusion

 select 
    PS.segment1    Supplier_Number,
pvs.vendor_Site_id Supplier_Site_Id,
    pvs.vendor_site_code Supplier_Site_Code,   
    IEBA.EXT_BANK_ACCOUNT_ID Bank_Account_Id,
    ieba.BANK_ACCOUNT_NUM Bank_Account_No,
  TO_cHAR(ieba.START_DATE,'yyyy-MM-dd') Bank_Account_Start_Date,
TO_cHAR(ieba.end_DATE,'yyyy-MM-dd') Bank_Account_end_Date,
decode(hp_bank.STATus,'A','Y','I','N',hp_bank.STATus) Bank_Active_Status,
hp_branch.Party_Name Bank_Branch_Name,
        hp_branch.CITY Bank_City,  
    hp_branch.COUNTRY Bank_Country_Code ,
    hp_bank.PArty_Name Bank_Name,
hp_branch.ADDRESS1 Bank_Street1,
    hp_branch.ADDRESS2 Bank_Street2,
    hp_branch.ADDRESS3 Bank_Street3,
    ieba.BANK_ACCOUNT_NAME Bank_Account_Name, 
    hp_branch.ADDRESS4 Bank_Name_Alt ,
    ieba.CURRENCY_CODE Currency, 
    ieba.iban Iban_No,
    hp_branch.CITY Local_Bank_City,
    hp_bank.PArty_Name Local_Bank_Name,
    null Order_of_Preference, 
    null Partner_Bank_Type,
    ieba.EFT_SWIFT_CODE Swift_Code,
ieba.last_update_date,
ieba.attribute1  Temp_1,
ieba.attribute2  Temp_2,
ieba.attribute3  Temp_3,
ieba.attribute4  Temp_4,
ieba.attribute5  Temp_5,
ieba.attribute6  Temp_6,
ieba.attribute7  Temp_7,
ieba.attribute8  Temp_8,
ieba.attribute9  Temp_9,
ieba.attribute10  Temp_10   
from 
poz_suppliers PS,  
poz_supplier_sites_all_m pvs,
hz_parties hp,
hr_organization_units_f_tl hout,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
iby_ext_bank_accounts ieba,
--hz_party_sites bank_address,
--hz_locations hl,
hz_parties hp_bank ,
hz_parties hp_branch 
where ps.vendor_id = pvs.vendor_id
--AND ps.enabled_flag ='Y'
--AND nvl(ps.end_Date_Active, sysdate+1) >= sysdate
--AND nvl(pvs.EFFECTIVE_END_DATE,sysdate+1) >= sysdate
and ps.party_id=hp.party_id
and pvs.prc_bu_id=hout.organization_id 
and hout.LANGUAGE = USERENV('LANG')
and iepa.payee_party_id = PS.party_id
--and trunc(IEPA.end_Date) >= trunc(sysdate)
and pvs.party_site_id=iepa.party_site_id
and iepa.supplier_Site_id= pvs.vendor_Site_id
and ipiua.ext_pmt_party_id = IEPA.ext_payee_id
--and trunc(ipiua.end_Date) >= trunc(sysdate)
and ieba.ext_bank_account_id = ipiua.instrument_id
--and pvs.party_site_id = bank_address.party_site_id
--and bank_address.location_id=hl.location_id
and ieba.bank_id = hp_bank.party_id
and ieba.branch_id = hp_branch.party_id
and hout.name = NVL(:P_BU_NAME,hout.name) 
--and pvs.vendor_Site_id=300000185706839 
and ieba.OBJECT_VERSION_NUMBER
IN(
select max(OBJECT_VERSION_NUMBER) from iby_ext_bank_accounts ieba1
where ieba1.ext_bank_account_id = ieba.ext_bank_account_id
and ieba1.bank_id = ieba.bank_id
)
and EXISTS
   (
   select 1 from POZ_SITE_ASSIGNMENTS_ALL_M psaa 
   where psaa.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
   and psaa.bu_id= pvs.PRC_BU_ID
--   and nvl(psaa.EFFECTIVE_END_DATE,sysdate+1) >= sysdate
   )
and trunc(ieba.last_update_date) between nvl(:P_FROM_DATE,trunc(ieba.last_update_date)) and nvl(:P_END_DATE,trunc(ieba.last_update_date))
UNION ALL
select 
(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||PS.segment1||'-'||IEBA.EXT_BANK_ACCOUNT_ID AP_External_Key__c,  
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) Source_System, 
(select 'Oracle-'||instance_name||'-'||to_Char(sysdate,'yyyymmddhhmmss') from gv$instance  where INSTANCE_NUMBER =1)  Batch_Id,
NULL Company_Code_Unique_Key, 
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) ||'-'||PS.segment1 Supplier_Unique_Key,
    PS.segment1    Supplier_Number,
NULL Supplier_Site_Id,
NULL  Supplier_Site_Code,   
    IEBA.EXT_BANK_ACCOUNT_ID Bank_Account_Id,
    ieba.BANK_ACCOUNT_NUM Bank_Account_No,
  TO_cHAR(ieba.START_DATE,'yyyy-MM-dd') Bank_Account_Start_Date,
TO_cHAR(ieba.end_DATE,'yyyy-MM-dd') Bank_Account_end_Date,
decode(hp_bank.STATus,'A','Y','I','N',hp_bank.STATus) Bank_Active_Status,
hp_branch.Party_Name Bank_Branch_Name,
        hp_branch.CITY Bank_City,  
    hp_branch.COUNTRY Bank_Country_Code ,
    hp_bank.PArty_Name Bank_Name,
hp_branch.ADDRESS1 Bank_Street1,
    hp_branch.ADDRESS2 Bank_Street2,
    hp_branch.ADDRESS3 Bank_Street3,
    ieba.BANK_ACCOUNT_NAME Bank_Account_Name, 
    hp_branch.ADDRESS4 Bank_Name_Alt ,
    ieba.CURRENCY_CODE Currency, 
    ieba.iban Iban_No,
    hp_branch.CITY Local_Bank_City,
    hp_bank.PArty_Name Local_Bank_Name,
    null Order_of_Preference, 
    null Partner_Bank_Type,
    ieba.EFT_SWIFT_CODE Swift_Code,
ieba.last_update_date,
ieba.attribute1  Temp_1,
ieba.attribute2  Temp_2,
ieba.attribute3  Temp_3,
ieba.attribute4  Temp_4,
ieba.attribute5  Temp_5,
ieba.attribute6  Temp_6,
ieba.attribute7  Temp_7,
ieba.attribute8  Temp_8,
ieba.attribute9  Temp_9,
ieba.attribute10  Temp_10   
from 
poz_suppliers PS,  
--poz_supplier_sites_all_m pvs,
hz_parties hp,
--hr_organization_units_f_tl hout,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
iby_ext_bank_accounts ieba,
--hz_party_sites bank_address,
--hz_locations hl,
hz_parties hp_bank ,
hz_parties hp_branch 
where 1=1
and ps.party_id=hp.party_id
and iepa.payee_party_id = PS.party_id
--and trunc(IEPA.end_Date) >= trunc(sysdate)
and iepa.party_site_id is null
and ipiua.ext_pmt_party_id = IEPA.ext_payee_id
--and trunc(ipiua.end_Date) >= trunc(sysdate)
and ieba.ext_bank_account_id = ipiua.instrument_id
--and pvs.party_site_id = bank_address.party_site_id
--and bank_address.location_id=hl.location_id
and ieba.bank_id = hp_bank.party_id
and ieba.branch_id = hp_branch.party_id
and ieba.OBJECT_VERSION_NUMBER
IN(
select max(OBJECT_VERSION_NUMBER) from iby_ext_bank_accounts ieba1
where ieba1.ext_bank_account_id = ieba.ext_bank_account_id
and ieba1.bank_id = ieba.bank_id
)
and trunc(ieba.last_update_date) between nvl(:P_FROM_DATE,trunc(ieba.last_update_date)) and nvl(:P_END_DATE,trunc(ieba.last_update_date))

Query to get supplier master data in oracle fusion

 SELECT 
   PS.segment1  Supplier_Number,
   PS.vendor_id Supplier_Id,
   pvs.vendor_Site_id Supplier_Site_Id,
   pvs.vendor_site_code Supplier_Site_Code,
   hp.party_name Supplier_Name,
       Tax.REGISTRATION_NUMBER Supplier_Tax_No,
   ps.Vendor_Type_Lookup_Code Supplier_Type,
   ps.AWT_GROUP_ID WHT_Inv_Group_Id, 
   Pay_method.payment_method_code Payment_Method, 
   Pay_method.description Payment_Method_Desc,
   hzl.city                                    Local_City,
   hzl.country                                 Local_Country,
   hzl.state                                   Local_State,
   hzl.ADDRESS1                                Local_Supplier_Address1,
       hzl.address2                                Local_Supplier_Address2,
       hzl.address3                                Local_Supplier_Address3,
   hzl.postal_code                             Local_Postal_Code,
   hzl.address_lines_phonetic                  Alternate_Address,
   pvs.email_address Primary_Email,
   Pay_method.remit_advice_email Remitt_to_email,
   pvs.phone Supplier_Phone,    
   case when nvl(pvs.inactive_date,sysdate+1) < sysdate THEN 'N'
   else 'Y'  end  Active_Status, 
   decode(att.term_id, null,null,(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'|| ATT.TERM_ID ) Payment_Term_Unique_Key , 
   null E_Invoice,
   pvs.pay_group_lookup_code Supplier_Group,
   pvs.pay_site_flag,
   hzl.ADDRESS1                                Address1,
       hzl.address2                                Address2,
       hzl.address3                                Address3,
   hzl.address4                                Address4,
   NULL                                   Address5,
   hzl.city                                    city,
   hzl.postal_code                             Postal_Code,
   hzl.state                                   state,
   hzl.country                                 country,
   null MSME ,
   TO_cHAR(ps.creation_date,'yyyy-MM-dd HH:mm:ss') Supplier_Creation_Date,
   Pay_method.EXCLUSIVE_PAYMENT_FLAG Pay_Alone_Flag,
   NULL Direct_Debit_Supplier_Flag,
   pvs.ALLOW_UNORDERED_RECEIPTS_FLAG NPO_Supplier_Flag,
   CASE when pvs.PAY_SITE_FLAG ='Y' and pvs.PURCHASING_SITE_FLAG ='Y' 
   then 
    'Pay_Site'
when pvs.PAY_SITE_FLAG ='N' AND pvs.PURCHASING_SITE_FLAG ='Y' 
then 'Purchasing_Site'
WHEN pvs.PAY_SITE_FLAG ='Y' AND pvs.PURCHASING_SITE_FLAG ='N' 
then 
    'Pay_Site'
END   
   Supplier_Site_Type,
   NULL Tax_Rate,
   PVS.invoice_currency_code Currency,
   hzl.county County,
   to_char(ps.last_update_Date,'yyyy-MM-dd HH:mm:ss') last_update_Date,
   ps.attribute1  Temp_1,
   ps.attribute2  Temp_2,
   ps.attribute3  Temp_3,
   ps.attribute4  Temp_4,
   ps.attribute5  Temp_5,
   ps.attribute6  Temp_6,
   ps.attribute7  Temp_7,
   ps.attribute8  Temp_8,
   ps.attribute9  Temp_9,
   ps.attribute10 Temp_10    
FROM   poz_suppliers PS,  
   POZ_SUPPLIER_SITES_ALL_M pvs,
       hz_locations hzl,
       hz_party_sites hps,       
   hz_parties hp,
   hr_organization_units_f_tl hout,
   ap_terms att    ,
   (
   SELECT ppm.payment_method_code,               
               iep.remit_advice_email,
               iep.EXCLUSIVE_PAYMENT_FLAG,  
   iep.supplier_Site_id,
   ppmt.description,
               MAX (ppm.object_version_number)
          FROM IBY_EXTERNAL_PAYEES_ALL  iep,
               IBY_EXT_PARTY_PMT_MTHDS  ppm,
   iby_payment_methods_tl ppmt
         WHERE iep.ext_payee_id = ppm.ext_pmt_party_id
           AND ppm.primary_flag = 'Y'
           AND NVL (ppm.inactive_date, SYSDATE+1) >= SYSDATE
           AND NVL (iep.inactive_date, SYSDATE+1) >= SYSDATE
   AND ppm.payment_method_code=ppmt.payment_method_code
   AND ppmt.LANGUAGE = USERENV('LANG')
         GROUP BY ppm.payment_method_code,               
               iep.remit_advice_email,
               iep.EXCLUSIVE_PAYMENT_FLAG,
   iep.supplier_Site_id ,ppmt.description
   ) Pay_method,
   (
   select distinct zpt.party_id, zr.REGISTRATION_NUMBER  from ZX_REGISTRATIONS zr, ZX_PARTY_TAX_PROFILE  zpt
   where zr.PARTY_TAX_PROFILE_ID=zpt.PARTY_TAX_PROFILE_ID
   and rownum=1
) Tax
WHERE  ps.vendor_id = pvs.vendor_id
   AND ps.enabled_flag ='Y'
--  AND nvl(ps.end_Date_Active, sysdate+1) >= sysdate
   AND pvs.location_id = hzl.location_id(+)  
       AND pvs.party_site_id =hps.party_site_id 
   AND PS.PARTY_ID=HP.PARTY_ID
   --AND nvl(pvs.INACTIVE_DATE,sysdate+1) >= sysdate
   and pvs.prc_bu_id=hout.organization_id 
   and hout.LANGUAGE = USERENV('LANG')
   and pvs.terms_id=att.term_id(+)
   and pvs.VENDOR_SITE_ID=Pay_method.supplier_Site_id(+)
   and hp.party_id=Tax.party_id(+)
   and EXISTS
   (
   select 1 from POZ_SITE_ASSIGNMENTS_ALL_M psaa 
   where psaa.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
   and psaa.bu_id= pvs.PRC_BU_ID
  --and nvl(psaa.INACTIVE_DATE,sysdate+1) >= sysdate
   )    
   and trunc(pvs.last_update_Date) between nvl(:P_FROM_DATE,trunc(pvs.last_update_Date)) and nvl(:P_END_DATE,trunc(pvs.last_update_Date))

Currency Master Data Query in oracle Fusion

 select fcb.CURRENCY_CODE, fct.DESCRIPTION,fcb.PRECISION ,
to_Char(fcb.LAST_UPDATE_DATE,'yyyy-MM-dd HH:mm:ss')  LAST_UPDATE_DATE
from FND_CURRENCIES_B fcb , FND_CURRENCIES_tl fct
where fcb.CURRENCY_CODE= fct.CURRENCY_CODE
and fct.LANGUAGE = USERENV('LANG')

Query to get Business Unit in Oracle Fusion

 select 
fab.SHORT_CODE Operating_Unit_Code,
fab.BU_NAME Operating_Unit_Name,GLB.currency_code Currency_Code,
hl.MAINPHONE_COUNTRY_CODE1 Country_Code,
hl.COUNTRY Country,
xr.REGISTRATION_NUMBER Company_VAT_ID, 
hl.ADDRESS_LINE_1 Address_1, hl.ADDRESS_LINE_2 Address_2, hl.ADDRESS_LINE_3 Address_3,
hl.TOWN_OR_CITY City, hl.region_1 State, hl.Postal_Code,
fab.last_update_Date
from FUN_ALL_BUSINESS_UNITS_V fab, gl_ledgers  GLB, hr_locations hl, XLE_REGISTRATIONS XR
where FAB.PRIMARY_LEDGER_ID= GLB.LEDGER_ID(+)
and fab.location_id=hl.LOCATION_ID(+)
and fab.BU_ID=xr.source_id(+)
--and fab.bu_name like 'ABC BUSINESS UNIT'
and EXISTS
(
select 1 from FUN_BU_USAGES_V fbu
where fbu.SHORT_CODE = fab.SHORT_CODE
and MODULE_KEY in 
(
'PAYABLES_INVOICING_BF',
'PROJECT_ACCOUNTING_BF',
'PAYABLES_PAYMENT_BF',
'REQUISITIONING_BF',
'RECEIVING_BF',
'EXPENSE_MANAGEMENT_BF',
'PROCUREMENT_BF',
'PRC_CONTRACT_MANAGEMENT_BF'
)
)