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'
)
)

AOL SYLLABUS:

PREREQUISITE

SQL
PL/SQL
D2K

GENERAL INTRODUCTION

KNOW HOW OF ERP
Version of Oracle Apps 11i
Comparison of 10.7/11.0.3/11i Version
Modules of Oracle applications
Cycle of Oracle Financials
Profiles of ERP Consultants
Front End and Back End of Apps 11i
Introduction to Tnsnames.ora files.

AOL:

1. User Creation
·         Navigation Path
·         How to Create User in ERP
·         How to assign responsibility to the User
·         How to attached Buyer to the User
·         How to disable User
·         How to add and remove responsibility assign to User
·         Name of  table in which User Stored

2. Responsibility
·         Navigation Path
·         What is responsibility
·         What are the three Main Component of Responsibility
·         How to Exclude Menu and Function from responsibility
·         How to disable responsibility
·         Name of  table in which Responsibility Stored
·         Difference between FND_RESPONSIBILITY and FND_RESPONSIBILITY_TL

3. Data Group
·         Navigation Path
·         Overview of Data Group
·         Importance of Data Group
·         How to attach custom Application with Data Group
·         How to Create Custom data group
·         How to register new Schema.

4.WHO COLUMN
5.Applications and table Name
6.Schema Registrations
7.Responsibility
8.Menu Customizations
9.Request Group
10.How to Register Custom Application
11.Request Set (How to Create Request Set).
12.How to Share Paramater in Request Set
13.Stages of Request Set
14.SRS WINDOW
15.Value Set
16.Lookups
17.How to Get Name of table From Front End
18.How to Register Custom tables in Oracle Applications
19.Directory Structure of Oracle Apps
20.How to Attach Reports in Apps
21.Default Type in Reports
22. Token in Reports
A. Default Type in Reports
B. Token in Reports
c. How to call reports through APIs
23.How to Attach Forms in Apps
24.Executable
25.Concurrent Programs
26.Incompatibility in Concurrent Programs
27.Flex fields (Key and Descriptive Flex Fields)
28.How to Enable DFF in the Forms
29.Profile options
30.User Exit
31.Importance of P_CONC_REQUEST_ID Parameter in reports
32.Difference between D2k Reports and APPS reports
33.How to Register PL/SQL Procedure in apps
34.How to Register SQL*LOADER in Oracle Applications.
35.Telnet
36 VI Editor
   FTP
37.Concurrent Manager
38. Unix Command
39. AIM Standard for Documentations (MD50 , MD70)

AOL SYLLABUS:

AOL SYLLABUS:

PREREQUISITE

SQL
PL/SQL
D2K

GENERAL INTRODUCTION

KNOW HOW OF ERP
Version of Oracle Apps 11i
Comparison of 10.7/11.0.3/11i Version
Modules of Oracle applications
Cycle of Oracle Financials
Profiles of ERP Consultants
Front End and Back End of Apps 11i
Introduction to Tnsnames.ora files.

AOL:

1. User Creation
·         Navigation Path
·         How to Create User in ERP
·         How to assign responsibility to the User
·         How to attached Buyer to the User
·         How to disable User
·         How to add and remove responsibility assign to User
·         Name of  table in which User Stored

2. Responsibility
·         Navigation Path
·         What is responsibility
·         What are the three Main Component of Responsibility
·         How to Exclude Menu and Function from responsibility
·         How to disable responsibility
·         Name of  table in which Responsibility Stored
·         Difference between FND_RESPONSIBILITY and FND_RESPONSIBILITY_TL

3. Data Group
·         Navigation Path
·         Overview of Data Group
·         Importance of Data Group
·         How to attach custom Application with Data Group
·         How to Create Custom data group
·         How to register new Schema.

4.WHO COLUMN
5.Applications and table Name
6.Schema Registrations
7.Responsibility
8.Menu Customizations
9.Request Group
10.How to Register Custom Application
11.Request Set (How to Create Request Set).
12.How to Share Paramater in Request Set
13.Stages of Request Set
14.SRS WINDOW
15.Value Set
16.Lookups
17.How to Get Name of table From Front End
18.How to Register Custom tables in Oracle Applications
19.Directory Structure of Oracle Apps
20.How to Attach Reports in Apps
21.Default Type in Reports
22. Token in Reports
A. Default Type in Reports
B. Token in Reports
c. How to call reports through APIs
23.How to Attach Forms in Apps
24.Executable
25.Concurrent Programs
26.Incompatibility in Concurrent Programs
27.Flex fields (Key and Descriptive Flex Fields)
28.How to Enable DFF in the Forms
29.Profile options
30.User Exit
31.Importance of P_CONC_REQUEST_ID Parameter in reports
32.Difference between D2k Reports and APPS reports
33.How to Register PL/SQL Procedure in apps
34.How to Register SQL*LOADER in Oracle Applications.
35.Telnet
36 VI Editor
   FTP
37.Concurrent Manager
38. Unix Command
39. AIM Standard for Documentations (MD50 , MD70)

Oracle apps interview Question

Oracle apps/ Oracle Reports Interview question

                

1. What is the Diff between APPS Schema and other Schemas?

Apps schema contains only Synonyms we can't create tables in apps schema, where as other schemas contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will connect to apps schema only.

            

2. What is meant by Custom Top and what is the Purpose?

Custom Top is nothing but Customer Top, which is created for customer only. We can have multiple custom

Tops based on client requirement. It is used to store developed & customized components. Whenever oracle corp applying patches it will over ride on all the modules except custom top. That’s why we will use custom top.

             

3. What is the Significancy of US Folder?

It is nothing but language specification by default it is in American language. We can have multiple languages folders based on installed languages. From backend we can get it from

FND_LANGUAGES -- COL --INSTALLED_FLAG I,B,D

              I--INSTALLED,

              B--BASE,

              D--DISABLE

              select language_code,nls_language from fnd_languages where installed_flag like 'B'

4. Where did U find the Applcation short name and basepath names?

select basepath,application_short_name from fnd_application from the backend. From the from end we can get it Navigation Application Developer.-----> Application---->Register The application name we will get from FND_APPLICATION_TL

             

5. Where can U find the release version from backend?

SELECT release_name from FND_PRODUCT_GROUPS; ---11.5.10.2             .

6. What are the Folders we will find below the 11.5.0 Folder?

Reports,forms,sql,lib,log,out,bin,admin,html,xml,msg,def, etc             

7. Can we create Tables in the Apps Schema?

No.             

8. Can we have custom schema when it it required?

Yes, we can have custom schema, when we want to create a new table we required custom schema.               

9. What is meant by concurrent Program?

It is nothing but Instance of the execution along with parameters & Incompatibles. Here Incompatibles nothing but if we are submitting cc programs if anyone can be execute in those program , which programs are not imp yet this time we will  mention those programs in incompatibles tab.              

10. What are the steps we will follow to register Reports as Concurrent Program?

 First develop the report & save it in local machine. upload into custom_top/11.5.0/reports/us/ go to system  administrator  open executable form create executable by mentioning executable method as reports ,executable  as report name which  was created. Go to cc program form create program by attach executable name in executable section. Then attach this CC program to request group, Request group to Responsibility. Responsibility to User.                   

11. What is meant by Request group?

It is nothing but collection of cc programs.         

                 

12. What is Application Top? What are the types and Purpose?

                 A) When we connect to the server we will find the top called application top. Under application top we have Product top.

 Custom top

                 Product top is the default top built by the manufacturer. Custom top is used to select the Client for his business purposes. Customizations are done with the Custom top.

13. What is US folder in the Custom Top?

             It is a language specific folder used to store the G.U.I like reports and forms.

14. What are mandatory parameters of Procedures and what the use of those?

 Errorbuf: It is used to returns the error messages and sent it to the log file.

 Retcode: It is used to show the status of the Procedure with 0, 1, and 2 0 for Completed Normal

                 1 for Completed Warning

2 for Completed Error

15 What is Apps Schema and Schema?

 Schema: Schema is the location in database contains database objects like views, tables, and synonyms.

                 Apps Schema: It is used to connect the all schemas to get the information from The database.

16. What is Token?

            a) Use to transfer values to report builder and it is not case sensitive.

17. Difference between FORM, Function and Menu?

                a) A menu is a hierarchical arrangement of functions and menus. Each responsibility has a menu assigned to it. A function is a part of an application that is registered under a unique name for the purpose of assigning it to be including it from a menu.

18.Tell me something about SQL-LOADER.

               

Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.

Sql* loader supports various load formats, selective loading, and multi-tables loads.

1) Conventional --The conventional path loader essentially loads the data by using standard ‘insert’ statement.

2) Direct -- The direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.

EX:- My data.csv file

1001, “scott tiger”,1000,40

1002,”gvreddy”,2345,50

Load data

Infile ‘c:\data\mydata.csv’

insert Into table emp Fields terminated by “,” optionally enclosed by‘”’

(empno, empname,sal,deptno)

>sqlldr scott/tiger@vis control=loader.ctl log= gvlog.log bad=gvbad.bad discard=gvdis.dsc .

19. What is SET-OF-BOOKS?

               

                Collection of Chart of Accounts and Currency and Calendars is called SOB

20. Tell me what r the Base tables in the AR?    

               

hz_parties (party_id) (store info about org, groups and people)

HZ_PARTIES stores information about parties such as organizations,

people, and groups, including the identifying address information for the party.

hz_cust_accounts (cust_account_id)

HZ_CUST_ACCOUNTS stores information about customer relationships. If a

party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.

hz_cust_acct_sites_all (cust_acct_site_id)

HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One

customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.

hz_cust_site_uses_all (site_use_id)

HZ_CUST_SITE_USES_ALL stores information about site uses or business

purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.

hz_party_sites (party_site_id)

HZ_PARTY_SITES stores information about the relationship between Parties

and Locations. The same party can have multiple party sites. Physical addresses are stored in HZ_LOCATIONS.

hz_locations (location_id)

HZ_LOCATIONS stores information about physical locations.

hz_Person_Profiles (person_profile_id)

HZ_PERSON_PROFILES stores detail information about people.

hz_Organization_Profiles (organization_profile_id)

HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics,

socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.

21. FND USER EXITS:-     

               

FND SRWINIT sets your profile option values, multiple organizations and allows

Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.

FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.

FND FLEXIDVAL are used to display flex field information like prompt, value etc

FND FLEXSQL these user exits allow you to use flex fields in your reports

FND FORMAT_CURRENCY is used to print currency in various formats by using formula column

22. What is Value Set?

The value set is a collection (or) container of values.

Whenever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.

If the list of values needed to be dynamic and ever changing and define a table based values set.

12) What are the validation types?

1) None -------- validation is minimal.

2) Independent ------input must exist on previously defined list of values

3) Dependent ------input is checked against a subset of values based on a

Prior value.

3) Table ----- input is checked against values in an application table

4) Special ------values set uses a flex field itself.

5) Pair ------ two flex fields together specify a range of valid values.

6) Translatable independent ----- input must exist on previously defined list of values; translated values can be used.

7) Translatable dependent ------- input is checked against a subset of values based on a prior values; translated value can be used.

23. Form development process?             

                a) Open template form

b) Save as <your form>.fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas (subclass info)

h) Assign canvas property class to the canvas

I) assign the window to the canvas and canvas to the window

j) Create a data block

k) Modify the form level properties. (sub class item Text item)

l) Modify the app_custom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties ((console window, First navigation

p) Save and compile the form.

Place the .fmx in the server directory.

24. How does u customize the Reports?

 a. Identify the Short name of the standard report in which module we have to customize

Ex: - if u wants to customize in the AR module path is

Appl top\ar\11.5.0\reports\US\ .rdf

b. Open the .rdf file in Report builder and change the name of the module.

c. Open the data module and modify the query (what is client requirements) assign the columns to the attributes.

d. Go to report wizard and select, what r the newly created columns.

e. Then Compile it. Then u will get a .rep file in the specified module. If it is not in the specified directory then we have to put in the server directory.

f. Then Register in the AOL Concurrent Executable and

Concurrent Program.

g. Go to system administrator Security responsibility request.

h. Add and assign a concurrent program to a request group

25. FLEX FIELDS?              

                Used to capture the additional business information.

DFF

KFF

 Additional          Unique Info, Mandatory

 Captured in attribute prefixed columns                Segment prefixed

 Not reported on standard reports           Is reported on standard reports

 To provide expansion space on your form With the help of [].

 [] Represents descriptive Flex field.

 FLEX FILED : DESCRIPTIVE : REGISTER      Used for entering and displaying key information

For example Oracle General uses a key Flex field called Accounting Flex field to uniquely identify a general account.

FLEX FILED : KEY : REGISTER

26. Difference between Bind and Lexical parameters? 

               

BIND VARIABLE:

are used to replace a single value in sql, pl/sql

bind variable may be used to replace expressions in select, where, group, order

by, having, connect by, start with cause of queries.

bind reference may not be referenced in FROM clause (or) in place of

reserved words or clauses.

LEXICAL REFERENCE:

You can use lexical reference to replace the clauses appearing AFTER select,

from, group by, having, connect by, start with.

You can’t make lexical reference in pl/sql statements.

27. what is Flex mode and Confine mode?

 Confine mode:

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders "stretch" when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against them.

28. What is Place holder Columns?          

 A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object.

You can set the value of a placeholder column is in a Before Report trigger.

Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

29. What is Formula Column?

 A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

30. What is Summary columns?

               

 A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional

Summaries: first, last, standard deviation, variance.

31. What is TCA (Trading Community Architecture)?

 Ans. Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

32. Difference between Application Developer and System Administrator?

 Ans.

Role of Technical Consultant:

a. Designing New Forms, Programs and Reports

b. Forms and Reports customization

c. Developing Interfaces

d. Developing PL/SQL stored procedures

e. Workflow automations

Role of System Administrator:

a. Define Logon Users

b. Define New/Custom Responsibility

c. Define Data Groups

d. Define Concurrent Managers

e. Define Printers

f. Test Network Preferences

g. Define/Add new Modules

Role of an Apps DBA:

a. Installing of Application

b. up gradation

c. Migration

d. Patches

e. Routing maintenance of QA

f. Cloning of OA

33. What are Flex fields?            

Ans. A Flex field is a customizable field that opens in a window from a regular Oracle Applications window. Defining flex fields enables you to tailor Oracle Applications to your own business needs. By using flex fields, you can:

(a) Structure certain identifiers required by oracle applications according to your own business environment.

(b) Collect and display additional information for your business as needed.

Key Flex fields: You use key flex fields to define your own structure for many of the identifiers required by Oracle Applications. Profile – ‘Flexfields:Open Key Window’ (FND_ID_FLEXS)

Descriptive Flex field: You use descriptive flex fields to gather additional information about your business entities beyond the information required by Oracle Applications. Profile – Flex fields: Open Descr Window’ (FND_DESCRIPTIVE_FLEXS)

34. Report registration process?

 1. Create the report using the report builder.

2. Place the report definition file in the module specific reports directory.

3. Create an executable for the report definition file.

4. Create a concurrent program to that executable.

5. Associate the concurrent program to a request group.

35. Define Request Group?

 A request security group is the collection of requests, request sets, and concurrent programs that a user, operating under a given responsibility, can select from the Submit Requests window.

36. Value Sets?

 Oracle Application Object Library uses values, value sets and validation tables as important components of key flex fields, descriptive flex fields, Flex Builder, and Standard Request Submission.

When you first define your flex fields, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values.

You define your value sets first, either before or while you define your flex field

segment structures. You typically define your individual values only after your flex field has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your flex field.

You can share value sets among segments in different flex fields, segments in

different structures of the same flex field, and even segments within the same flex field structure. You can share value sets across key and descriptive flex fields. You can also use value sets for report parameters for your reports that use the Standard Report Submission feature.

Navigation Path:

Login – Application Developer -> Application -> Validation -> Set

37. Value Validation Types?

 1. Dependent

2. Independent

3. None

4. Pair

5. Special

6. Table

7. Translate Independent

8. Translate Dependent

38. Incompatibility in report registration and Run Alone?

 Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution. You can specify your program as being incompatible with itself.

Application: Although the default for this field is the application of your concurrent program, you can enter any valid application name.

Name: The program name and application you specify must uniquely identify a

concurrent program. Your list displays the user-friendly name of the program, the short name, and the description of the program.

Scope: Enter Set or Program Only to specify whether your concurrent program is zincompatible with this program and all its child requests (Set) or only with this program (Program Only).

Run Alone: Indicate whether your program should run alone relative to all other programs in the same logical database. If the execution of your program interferes with the execution of all other programs in the same logical database (in other words, if your program is incompatible with all programs in its logical database, including itself), it should run alone.

                Pl/Sql interview questions

                 

1. What are the various types of Exceptions?

 User defined and Predefined Exceptions.

2. Can we define exceptions twice in same block?

No.           

3. What is the difference between a procedure and a function?

Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

         

4. Can you have two functions with the same name in a PL/SQL block?

Yes.              

5. Can you have two stored functions with the same name?

  Yes.

              

6. Can you call a stored function in the constraint of a table?

No.

             

7. What are the various types of parameter modes in a procedure?

IN, OUT AND INOUT.

              

8. What is Over Loading and what are its restrictions?

Overloading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

             

9. Can functions be overloaded?

Yes.

10. Can 2 functions have same name & input parameters but differ only by return datatype

No.