Fusion Tables and Imp Oracle Links

Fusion Tables and Imp Oracle Links

docs.oracle.com

• Table Link

https://docs.oracle.com/en/cloud/saas/procurement/22c/oedmp/index.html

Business Intelligence Publisher Secured List Views

• Secured List Link

• https://oracleclient.com/analytics -- Using for Develop custom reports workspace /analytics

• https://oracleclient.com/xmlpserver -- Using for Standard report customization /xmlpserver

FAQs

 

1. Technical Components in Oracle Cloud ERP?
   • Reports
     1. BIP
     2. OTBI
     3. FRS
     4. SmartView
   • Interfaces or Conversions (Integrations)
     1. FBDI
     2. HDL
     3. ADFdi
     4. SOAP/REST
   • Extensions
   1. Page Composers
   2. Application Composers
   3. VBCS Page/Region
   • User Interface
     1. ADF
     2. VBCS
   • Workflow
     1. BPM
     2. PCS
2. How Oracle Cloud ERP is related to Oracle E-Business Suite?
   • Both Products are ERP Applications from Oracle
   • E-Business is an On-Premise ERP
   • Oracle Cloud ERP (Fusion Application) ERP – is a SaaS Flavored ERP
   • Oracle Cloud ERP – has used many functionalities from Oracle EBS, PeopleSoft, JDEdwards and Siebel
3. What are the three Cloud Models?
   • IaaS – Infrastructure as a Service
   • PaaS- Platform as a Service
   • SaaS- Software as a Service
4. IaaS Offerings
5. PaaS Offering
   • OIC- Oracle Integration Cloud
     1. ICS- Integration Cloud Service
     2. PCS – Process Cloud Service
     3. VBCS – Visual Builder Cloud Service
   • DBCS – Database Cloud Service
   • VBS -Visual Builder Studio
6. SaaS Offerings
• Oracle Cloud ERP (Fusion Applications)
• Oracle NetSuite
• Oracle RightNow
• Oracle Taleo

Oracle EBS and Fusion Cloud
Cloud ERP		E Business Suite
Reports	BI Publisher ( SQL & PLSQL)	XML / BI Publisher – RDF Report , Data templates ( SQL & PLSQL )
	OTBI	Discover (SQL)
	Hyperion Smart View	FSG Reports
Interface & Conversion	File based data Import ( FBDI )	SQL Loder
	ADF Desktop Integration (ADF-DI)	Web ADI
	FBL File based load import, Using Spreadsheet Approach
	BI Reports – Out bound	Sql / PLSQL
	Web Services (SOAP / REST )
Forms	ADF ( Java )	Oracle Forms / OAF / JSP / Servlets (Java )
Work Flow	Oracle BPM	Oracle Workflow
	AMX	Oracle AME
Extensions	Page Composer	Forms Personalization
	Application Composer	OAF Personalization / Extension
	Process Composer	Standard Workflow Customization

Automatic Sourcing Query

 SET SERVEROUTPUT ON SIZE 30000;

Declare

p_org_id           po_requisition_headers_all.ORG_ID%TYPE     :=&&1; 
p_pr_no            po_requisition_headers_all.segment1%TYPE   :='&&2'; 

p_Requisition_header_id po_requisition_headers_all.Requisition_header_id%TYPE;
      

v_err_msg               VARCHAR2(255)   := NULL;
v_ora_err_msg_desc      VARCHAR2(255)   := NULL;
v_ora_err_msg_code      VARCHAR2(255)   := NULL;
vld_error                   EXCEPTION;

CURSOR c_pr_cursor (p_req_no Number) is
Select  SUBSTR(prh.Requisition_header_id,1,21) as Requisition_header_id      
       ,SUBSTR(prh.SEGMENT1,1,9) as  SEGMENT1                       
       ,SUBSTR(prh.AUTHORIZATION_STATUS,1,19) as AUTHORIZATION_STATUS             
       ,prh.interface_source_code            
       ,SUBSTR(prl.line_num,1,9) as line_num                         
       ,SUBSTR(msib.Segment2,1,15)   as INV_ITEM                    
       ,SUBSTR(prl.ITEM_DESCRIPTION,1.50) as ITEM_DESCRIPTION 
       ,MCB.SEGMENT2||'.'||MCB.SEGMENT4||'.'||MCB.SEGMENT3 as ITEM_CATEGORY
       ,prl.UNIT_MEAS_LOOKUP_CODE           
       ,prl.need_by_date                    
       ,SUBSTR(pv.vendor_name,1,20) as vendor_name                     
       ,pvsa.vendor_site_code               
       ,ph.segment1    as   PO_NUMBER                  
       ,pl.line_num    as   PO_LINE_NUM                     
       ,(select name 
        from apps.hr_operating_units
        where organization_id = nvl(prh.ORG_ID,-99)) as ORGANIZATION_NAME
       ,prh.org_id 
    FROM  apps.po_requisition_headers_all Prh
         ,apps.po_requisition_lines_all   Prl
         ,apps.MTL_SYSTEM_ITEMS_B         msib
         ,apps.PO_VENDORS                 pv
         ,apps.PO_VENDOR_SITES_ALL        pvsa
         ,apps.po_lines_all               pl
         ,apps.po_headers_all             ph 
         ,apps.MTL_CATEGORIES_B mcb
     Where prh.Requisition_header_id  = p_req_no 
        --and prh.org_id                = p_org_id
        and prh.Requisition_header_id = prl.Requisition_header_id
        and prh.org_id                = prl.org_id
        and prl.BLANKET_PO_HEADER_ID  = ph.po_header_id (+)
        and prl.org_id                = ph.org_id (+)
        and pl.po_header_id        (+)= ph.po_header_id 
        and pl.org_id              (+)= ph.org_id
        and Msib.INVENTORY_ITEM_ID (+) = Prl.item_id  
        and msib.ORGANIZATION_ID (+)  = prl.DESTINATION_ORGANIZATION_ID
        and pv.vendor_id (+)          = prl.vendor_id 
        and pvsa.vendor_site_id (+)   = prl.vendor_site_id
        and mcb.CATEGORY_ID = prl.CATEGORY_ID
        and prl.vendor_id is null
        and prl.BLANKET_PO_HEADER_ID is null
        and Prl.item_id is not null
        and prl.DOCUMENT_TYPE_CODE is null 
        and prh.interface_source_code is null 
    order by prh.SEGMENT1 
    ;
    
    Cursor C_history(p_req_no Number) is
    Select pah.OBJECT_TYPE_CODE
          ,pah.OBJECT_SUB_TYPE_CODE
          ,pah.ACTION_CODE
          ,pf.FULL_NAME as EMPLOYEE_NAME
   from apps.PO_ACTION_HISTORY pah
       ,apps.per_all_people_f pf
    where pah.OBJECT_ID = p_req_no 
    and pah.OBJECT_TYPE_CODE = 'REQUISITION'
    and pf.PERSON_ID = pah.employee_id
    ;
    
    Cursor C_App_SL(p_inv_item varchar2) is
    Select paa.ASL_ID           
      ,pv.vendor_name                
      ,pvs.vendor_site_code          
      ,msib.SEGMENT2                 AS INVENTORY_ITEM
      ,pad.DOCUMENT_TYPE_CODE        
      ,pha.segment1                  AS PO_NUMBER
      ,pha.start_date                
      ,pha.end_date                  
      ,pla.line_num
      ,(select organization_name 
        from apps.org_organization_definitions
        where organization_id = nvl(pasl.OWNING_ORGANIZATION_ID,-99)) as ORGANIZATION_NAME                  
    from apps.po_approved_supplier_list pasl,
         apps.po_asl_attributes paa,
         apps.PO_ASL_DOCUMENTS pad,
         apps.PO_VENDORS              pv,
         apps.PO_VENDOR_SITES_ALL     pvs,
         apps.po_headers_all pha,
         apps.po_lines_all pla,
         apps.mtl_system_items_b msib
    where pv.vendor_id        = pasl.vendor_id
    and  paa.ASL_ID = pasl.ASL_ID
    and  pad.DOCUMENT_HEADER_ID = pha.po_header_id
    and  pad.DOCUMENT_LINE_ID = pla.po_line_id
    and  paa.ASL_ID = pad.ASL_ID
    and  pasl.vendor_site_id  = pvs.vendor_site_id
    and  pasl.ITEM_ID = msib.inventory_item_id
    and  pasl.OWNING_ORGANIZATION_ID = msib.ORGANIZATION_ID
    and  msib.SEGMENT2 = p_inv_item --:v_inv_id
    ;
    
    Cursor C_SR_ASR(p_vendor varchar2
                   ,p_inv_item1 varchar2) 
                    is
    Select distinct msr.SOURCING_RULE_NAME                  
      ,msr.DESCRIPTION                                  
      ,msr.status                                       
      ,msr.PLANNING_ACTIVE                              
      ,msro.EFFECTIVE_DATE                              
      ,pv.vendor_name                                   
      ,pvs.vendor_site_code                             
      ,msso.ALLOCATION_PERCENT                          
      ,msso.RANK                                       as RANK1
      ,DECODE (msso.SOURCE_TYPE,'1','Transfer From','3','Buy From') as TYPE1
      ,MSS.ASSIGNMENT_SET_NAME                          
      ,msa.SOURCING_RULE_TYPE                           
      ,msa.SOURCING_RULE_TYPE_TEXT                      
      ,msa.ASSIGNMENT_TYPE                              
      ,msib.segment2                                    as INV_ITEM
      ,DECODE (msa.ASSIGNMENT_TYPE,'6','Item Organization','3','Item','4','Organization','1','Global') as ASSIGNED_TO
    from apps.MRP_SOURCING_RULES msr,
         apps.MRP_SR_RECEIPT_ORG msro,
         apps.MRP_SR_SOURCE_ORG msso,
         apps.MRP_ASSIGNMENT_SETS MSS,
         apps.MRP_SR_ASSIGNMENTS_v MSA,
         apps.PO_VENDORS PV,
         apps.PO_VENDOR_SITES_ALL     pvs,
         apps.mtl_system_items_b msib
    where msr.SOURCING_RULE_ID = msro.SOURCING_RULE_ID
    and msro.SR_RECEIPT_ID = msso.SR_RECEIPT_ID
    and msso.VENDOR_ID = pv.VENDOR_ID 
    and mss.ASSIGNMENT_SET_ID = msa.ASSIGNMENT_SET_ID
    and MSA.SOURCING_RULE_ID = msr.SOURCING_RULE_ID
    and msso.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
    and msib.INVENTORY_ITEM_ID = msA.INVENTORY_ITEM_ID
    and  msib.SEGMENT2 = p_inv_item1
    and pv.vendor_name = p_vendor 
    ;
    
    Cursor C_BPA(p_po_number varchar2
                   ,p_line_num NUMBER
                   ) 
                    is
    Select Distinct pha.SEGMENT1             as   PO_NUMBER
      ,pv.vendor_name                       
      ,pvs.vendor_site_code                 
      ,pha.START_DATE                       
      ,pha.END_DATE                         
      ,(SELECT nvl(SUM((quantity-quantity_cancelled) * price_override), 0)
       FROM   po_line_locations_all
       WHERE  po_header_id = pha.po_header_id ) as BLANKET_RELEASE_TOTAL
      ,pha.AMOUNT_LIMIT                     
      ,pla.LINE_NUM                         
      ,msib.segment2                     as INV_ITEM
      ,SUBSTR(pla.ITEM_DESCRIPTION,1,50) as ITEM_DESCRIPTION                 
      ,pla.UNIT_MEAS_LOOKUP_CODE            
      ,pla.UNIT_PRICE                       
      ,MCB.SEGMENT2||'.'||MCB.SEGMENT4||'.'||MCB.SEGMENT3 as ITEM_CATEGORY
    from apps.PO_HEADERS_ALL PHA,
         apps.PO_LINES_ALL   PLA,
         apps.PO_VENDORS     PV,
         apps.PO_VENDOR_SITES_ALL     pvs,
         apps.mtl_system_items_b msib,
         apps.MTL_CATEGORIES_B mcb
    WHERE pha.SEGMENT1  = p_po_number
      and pla.line_num  = p_line_num
      and pha.org_id    = p_org_id
      and PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
      and pla.org_id = pha.org_id(+)
      and Msib.INVENTORY_ITEM_ID (+) = Pla.item_id  
      and pv.vendor_id (+) = pha.vendor_id 
      and pvs.vendor_site_id (+) = pha.vendor_site_id
      and mcb.CATEGORY_ID = pla.CATEGORY_ID
    and pha.TYPE_LOOKUP_CODE = 'BLANKET'
      ;
    
   Begin
      
            DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_org_id);
   
       BEGIN
         
         Select nvl(prh.Requisition_header_id,0)
           INTO p_Requisition_header_id
           From apps.po_requisition_headers_all Prh
           where prh.SEGMENT1              = p_pr_no 
             and prh.org_id                = p_org_id
           ;
        
     
        
      IF p_Requisition_header_id !=0 
      THEN  
            dbms_output.put_line('!!!-------------------------------------------------------------------------');
            dbms_output.put_line('!!! PO Requisition details ');
            dbms_output.put_line('!!!-------------------------------------------------------------------------');
            
    FOR  c_rec in c_pr_cursor(p_Requisition_header_id)
         
         loop
         
         dbms_output.put_line('Requisition header id'||'!'||'Segment1'||'!'||'Authorization Status'||'!'||'Interface Source Code'||'!'||'Line Num'||'!'||'Inventory Item'||'!'||'Item Description'||'!'||'Item Category'||'!'||'Unit Meas Lookup Code'||'!'||'Need By Date'||'!'||'Vendor Name'||'!'||'Vendor Site Code'||'!'||'Po Number'||'!'|| 'Po Line Num'||'!'||'Organization Name'||'!'|| 'Org Id');
                  
         dbms_output.put_line(c_rec.Requisition_header_id
                         ||'!'||c_rec.SEGMENT1
                         ||'!'||c_rec.AUTHORIZATION_STATUS
                         ||'!'||c_rec.interface_source_code
                         ||'!'||c_rec.line_num
                         ||'!'||c_rec.INV_ITEM
                         ||'!'||c_rec.ITEM_DESCRIPTION
                         ||'!'||c_rec.ITEM_CATEGORY
                         ||'!'||c_rec.UNIT_MEAS_LOOKUP_CODE
                         ||'!'||c_rec.need_by_date
                         ||'!'||c_rec.vendor_name
                         ||'!'||c_rec.vendor_site_code
                         ||'!'||c_rec.PO_NUMBER
                         ||'!'||c_rec.PO_LINE_NUM
                         ||'!'||c_rec.ORGANIZATION_NAME
                         ||'!'||c_rec.Org_Id);
             
             dbms_output.put_line('!!------------------------------------');
             dbms_output.put_line('!!Action History Details');
             dbms_output.put_line('!!------------------------------------');
             dbms_output.put_line('Object Type Code'||'!'||'Object Sub Type Code'||'!'||'Action Code'||'!'||'Employee Name');
             
             FOR F_History in C_History (c_rec.Requisition_header_id)
             Loop
             dbms_output.put_line(F_History.OBJECT_TYPE_CODE
                            ||'!'|| F_History.OBJECT_SUB_TYPE_CODE  
                            ||'!'|| F_History.ACTION_CODE 
                            ||'!'|| F_History.EMPLOYEE_NAME );
             
            end loop;
            
            dbms_output.put_line('!!------------------------------------');
            dbms_output.put_line('!!Approval Supplier List');
            dbms_output.put_line('!!------------------------------------');
               
            dbms_output.put_line('Asl ID'||'!'||'Vendor Name'||'!'||'Vendor Site Code'
                            ||'!'||'Inventory Item'||'!'||'Document Type Code'||'!'||'PO Number'
                            ||'!'||'Start Date'||'!'||'End Date'||'!'||'Line Num');
      
             
            FOR F_App_SL in C_App_SL (c_rec.INV_ITEM)
            LOOP
                
            dbms_output.put_line(F_App_SL.ASL_ID
                            ||'!'||F_App_SL.VENDOR_NAME
                            ||'!'||F_App_SL.VENDOR_SITE_CODE 
                            ||'!'||F_App_SL.INVENTORY_ITEM
                            ||'!'||F_App_SL.DOCUMENT_TYPE_CODE
                            ||'!'||F_App_SL.PO_NUMBER
                            ||'!'||F_App_SL.START_DATE
                            ||'!'||F_App_SL.END_DATE
                            ||'!'||F_App_SL.LINE_NUM);
            
            dbms_output.put_line('!!!-----------------------------------------------------------'); 
            dbms_output.put_line('!!!Sourcing Rules and Assign Sourcing Rules');
            dbms_output.put_line('!!!-----------------------------------------------------------');
               
            dbms_output.put_line('Sourcing Rule Name'||'!'||'Description'||'!'||'Status'||'!'||'Planning Active'
                             ||'!'||'Effective Date'||'!'||'Vendor Name'||'!'||'Vendor Site Code'
                             ||'!'||'Allocation Percent'||'!'||'Rank1'||'!'||'Assignment Set Name'
                             ||'!'||'Sourcing Rule Type'||'!'||'Sourcing Rule Type Text'||'!'||'Assignment Type'||'!'|| 'Inventory Item'
                             ||'!'||'Assigned To');
                             
                FOR F_SR_ASR in C_SR_ASR (F_App_SL.VENDOR_NAME
                                         ,c_rec.INV_ITEM)
                LOOP
                
                    dbms_output.put_line(F_SR_ASR.SOURCING_RULE_NAME 
                                   ||'!'|| F_SR_ASR.DESCRIPTION 
                                   ||'!'|| F_SR_ASR.STATUS 
                                   ||'!'|| F_SR_ASR.PLANNING_ACTIVE
                                   ||'!'|| F_SR_ASR.EFFECTIVE_DATE
                                   ||'!'|| F_SR_ASR.VENDOR_NAME
                                   ||'!'|| F_SR_ASR.VENDOR_SITE_CODE
                                   ||'!'|| F_SR_ASR.ALLOCATION_PERCENT
                                   ||'!'|| F_SR_ASR.RANK1
                                   ||'!'|| F_SR_ASR.ASSIGNMENT_SET_NAME
                                   ||'!'|| F_SR_ASR.SOURCING_RULE_TYPE
                                   ||'!'|| F_SR_ASR.SOURCING_RULE_TYPE_TEXT
                                   ||'!'|| F_SR_ASR.ASSIGNMENT_TYPE
                                   ||'!'|| F_SR_ASR.INV_ITEM
                                   ||'!'|| F_SR_ASR.ASSIGNED_TO
                                        );
              
                 
                END LOOP;
                
                dbms_output.put_line('!!!---------------------------------------------');
                dbms_output.put_line('!!!PO Header and Line Details');
                dbms_output.put_line('!!!---------------------------------------------');
                   
               dbms_output.put_line('Po Number'||'!'||'Vendor Name'||'!'||'Vendor Site Code'||'!'||'Start Date'
                                 ||'!'||'End Date'||'!'||'Blanket Release Total'||'!'||'Amount Limit'
                                 ||'!'||'Line Num'||'!'||'Inventory Item'||'!'||'Item Description'||'!'||'Unit Meas Lookup Code'
                                 ||'!'||'Unit Price'||'!'||'Item Category'); 
                

               FOR F_BPA in C_BPA(F_App_SL.PO_NUMBER
                                  ,F_App_SL.LINE_NUM)
                LOOP
                
                      dbms_output.put_line(F_BPA.PO_NUMBER 
                                   ||'!'||F_BPA.VENDOR_NAME 
                                   ||'!'|| F_BPA.VENDOR_SITE_CODE 
                                   ||'!'|| F_BPA.START_DATE
                                   ||'!'|| F_BPA.END_DATE
                                   ||'!'|| F_BPA.BLANKET_RELEASE_TOTAL
                                   ||'!'|| F_BPA.AMOUNT_LIMIT
                                   ||'!'|| F_BPA.LINE_NUM
                                   ||'!'|| F_BPA.INV_ITEM
                                   ||'!'|| F_BPA.ITEM_DESCRIPTION
                                   ||'!'|| F_BPA.UNIT_MEAS_LOOKUP_CODE
                                   ||'!'|| F_BPA.UNIT_PRICE
                                   ||'!'|| F_BPA.ITEM_CATEGORY
                                );
                            
                
               END LOOP;
                           
            END LOOP;                

         end loop;
        
         Else
         
         Null;
     
      END IF;
     
        Exception 
       
       WHEN NO_DATA_FOUND THEN
             v_ora_err_msg_desc := sqlerrm;
             v_ora_err_msg_code := to_char(sqlcode); 
             v_err_msg := 'XXSRIERR: No Data Found  ';
              
             DBMS_OUTPUT.PUT_LINE(v_err_msg);
             DBMS_OUTPUT.PUT_LINE('XXSRIERR: Oracle error is: ' || v_ora_err_msg_code);
             DBMS_OUTPUT.PUT_LINE('v_ora_err_msg_desc : ' ||v_ora_err_msg_desc);
             --RAISE vld_error;
   
       WHEN OTHERS THEN
             v_ora_err_msg_desc := sqlerrm;
             v_ora_err_msg_code := to_char(sqlcode); 
             v_err_msg := 'XXSRIERR: SQL error while selecting PR Information.';
         
             DBMS_OUTPUT.PUT_LINE(v_err_msg);
             DBMS_OUTPUT.PUT_LINE('XXSRIERR: Oracle error is: ' || v_ora_err_msg_code);
             DBMS_OUTPUT.PUT_LINE(v_ora_err_msg_desc);
             RAISE vld_error;
     END;   
       
    END;
/

spool off;

Purchasing

Automatic Sourcing

Automatic Sourcing Query

Automatic Sourcing

Automatic Sourcing

Set Up Automatic Sourcing (Optional)

 You can set up Purchasing to automatically default source document information from a blanket purchase agreement or catalog quotation onto a requisition or purchase order line for a particular item and supplier. Or you can default just a supplier and optionally a supplier site.

You can set up automatic sourcing now or at any time on an ongoing basis.

 Profile Option Settings 

MRP: Default Sourcing Assignment Set

        This profile option indicates which sourcing rules assignment set will be used in Purchasing and Supplier Scheduling.             Planning allows you to use multiple Assignment Sets, but Purchasing looks at only a single Assignment Set—the one             that is specified in this profile option. Therefore, the Assignment Set name you choose for this profile option is the                 Assignment Set name you should specify when you assign sourcing rules in the Sourcing Rule/Bill of Distribution                 Assignments window. This profile option must be set if you are using sourcing rules in Purchasing.

 MRP: Sourcing Rule Category Set

        If you are using sourcing rules in Purchasing, this profile needs to be set to the name of the default category set used by         Purchasing as indicated in the Default Category Sets window. Otherwise, you will not be able to display Category and            Category–Organization assignments in the Sourcing Rule / Bill of Distribution Assignments window.

Profile Options:

PO: Allow Auto-generate Sourcing Rules => CREATE AND UPDATE

PO: Allow Autocreation of Oracle Sourcing Documents => Yes

MRP: Default Assignment Set => Define with desired Assignment Set

Sql Statements
 

Select * from po_headers_all
WHERE segment1 = '41017'
 
Select * from po_lines_all
WHERE PO_HEADER_ID = 334170
 
Select * from po_approved_supplier_list
where ASL_ID = 23568
order by creation_date desc
 
Select * from po_asl_attributes
where ASL_ID = 23568
order by creation_date desc
 
Select * from PO_ASL_DOCUMENTS
where ASL_ID = 23568
and DOCUMENT_HEADER_ID = 334170
 
Select * from MRP_SOURCING_RULES
where sourcing_rule_id = 8186
order by creation_date desc
 
Select * from MRP_SR_RECEIPT_ORG
where sourcing_rule_id = 8186
order by creation_date desc
 
Select * from MRP_SR_SOURCE_ORG
where sr_receipt_id = 7066
order by creation_date desc
 
Select * from MRP_ASSIGNMENT_SETS
 
Select * from MRP_SR_ASSIGNMENTS
where sourcing_rule_id = 8186

order by creation_date desc

XML BLOB Examples

 BLOB Examples

Can’t generate xml data for CLOB more than 40 kb in report builder. 
BLOB Images are supported in XML Publisher 5.6.2 or later. 
Encode BLOB to CLOB use this following function:

CREATE FUNCTION getbase64( p_source BLOB )

RETURN CLOB

IS

v_result CLOB;

BEGIN

DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);

Wf_Mail_Util.EncodeBLOB ( p_source, v_result);

RETURN ( v_result );

END getbase64; 

To show BLOB Images use the following code:
<fo:instream-foreign-object content-type=”image/jpg”><xsl:value-of select=”.//IMAGE_”/></fo:instream-foreign-object> 

<fo:instream-foreign-object content-type="image/jpg" xdofo:alt="An Image" ><xsl:value-of select=".//AD_COMPOSITE"/></fo:instream-foreign-object>

XML Publisher supports in general right now only JPG, GIF and PNG. 

CREATE or replace FUNCTION PFC_SHOW_IMAGE(P_PERSON_ID NUMBER)

RETURN CLOB
IS
v_result CLOB;
l_blob blob;
sizeb  PLS_INTEGER := 4080;
buffer RAW(4080);
offset PLS_INTEGER DEFAULT 1;
begin
BEGIN
SELECT  PerImageEO.IMAGE 
into l_blob
  FROM   PER_IMAGES PerImageEO
 WHERE   PerImageEO.PARENT_ID = P_PERSON_ID
 and PerImageEO.TABLE_NAME = 'PER_PEOPLE_F';
exception when others then 
null;
end;
DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => dbms_lob.CALL);
--Wf_Mail_Util.EncodeBLOB ( l_blob, v_result);

LOOP
      BEGIN
        dbms_lob.READ(l_blob, sizeb, offset, buffer);
      EXCEPTION
        WHEN no_data_found THEN
          EXIT;
      END;
      offset := offset + sizeb;
      dbms_lob.append(v_result,
                      to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer))));
    END LOOP;
RETURN ( v_result );

END PFC_SHOW_IMAGE;

GL Chart Of Account Query

 GL Chart Of Account Query

select  ffv.flex_value FLEX_VALUES
  ,ffvv.flex_value_meaning TRANSLATED_VALUE
,ffvt.description DESCRIPTION
,decode(ffv.summary_flag,'N',null,'*') PARENT
,ffhv.hierarchy_code GROUPS
,ffv.hierarchy_level LEVELS
   ,(decode(substr(to_char(ffv.compiled_value_attributes),1,1),'N','No','Yes')
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),3,1),'N','No','Yes') 
||'.'||(select acct_type_desc2
from gl_acct_typ gat
   where gat.acct_type_code = substr(to_char(ffv.compiled_value_attributes),5,1) ) 
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),7,1),'N','No','Yes') 
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),9,1),'N','No','Yes')) QUALIFIERS
--,fdf.descriptive_flex_context_name
,(ffv.attribute10
||'.'||ffv.attribute3
||'.'||(SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE LOCATION_ID IS NOT NULL and ORGANIZATION_ID = ffv.attribute4)
||'.'||(Select fdf.descriptive_flex_context_name from fnd_descr_flex_contexts_vl fdf where ffv.value_category = fdf.descriptive_flex_context_code)
||'.'||ffv.attribute5) DFF_VALUES
from fnd_flex_values ffv
  ,fnd_flex_values_vl ffvv
,fnd_flex_values_tl ffvt
,fnd_flex_value_sets ffvs
,fnd_id_flex_segments fifs
,fnd_id_flex_structures_vl fifst
,fnd_flex_hierarchies_vl ffhv
where 1=1
AND fifst.application_id = 101
AND fifs.application_id = fifst.application_id
AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.id_flex_code = fifst.id_flex_code
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_id = ffvv.flex_value_id
and ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffvs.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
and ffv.flex_value_set_id = ffvv.flex_value_set_id
--and ffvs.flex_value_set_id = 1014899
AND FIFS.FLEX_VALUE_SET_ID = NVL(:P_FLEX_VALUE_ID,FIFS.FLEX_VALUE_SET_ID)
and fifs.id_flex_code = 'GL#'
and ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)

order by ffv.flex_value

SELECT ffvv.flex_value,ffvs.flex_value_set_name,ffvv.DESCRIPTION

     INTO v_type_val

     FROM apps.fnd_flex_values_vl ffvv, apps.fnd_flex_value_sets ffvs

    WHERE 1=1 

  AND ffvv.flex_value_set_id = ffvs.flex_value_set_id;

      --AND ffvs.flex_value_set_name = <Value Set Name>;

  

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

SELECT   ffvv.flex_value,

ffvv.DESCRIPTION,

fifst.STRUCTURE_VIEW_NAME,

         fifst.CONCATENATED_SEGMENT_DELIMITER,

         fifs.application_column_name,

         fifs.segment_name,

         fifs.segment_num,

         ffs.flex_value_set_name

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs,

         fnd_flex_value_sets ffs,

apps.fnd_flex_values_vl ffvv

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

AND ffvv.flex_value_set_id = ffs.flex_value_set_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and FFS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID

         and fifst.ID_FLEX_CODE = 'GL#';

 

 

 

SELECT   vsvls.flex_value , vsvls.description ,vsvls.ENABLED_FLAG , vsvls.SUMMARY_FLAG , 

vsvls.COMPILED_VALUE_ATTRIBUTES

 FROM  FND_ID_FLEX_SEGMENTS_VL SGMNT , FND_FLEX_VALUES_VL  vsvls

 WHERE SGMNT.ID_FLEX_CODE =  'GL#' 

AND  SGMNT.ID_FLEX_NUM = 503

and  SGMNT.FLEX_VALUE_SET_ID = vsvls.flex_value_set_id

and  SGMNT.SEGMENT_NUM = 4

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

SELECT   ffvv.flex_value,

ffvv.DESCRIPTION,

fifst.STRUCTURE_VIEW_NAME,

         fifs.segment_name,

         ffs.flex_value_set_name,

    ffvv.ENABLED_FLAG, 

ffvv.SUMMARY_FLAG

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs,

         fnd_flex_value_sets ffs,

apps.fnd_flex_values_vl ffvv

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

AND ffvv.flex_value_set_id = ffs.flex_value_set_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and FFS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID

AND FIFS.FLEX_VALUE_SET_ID = NVL(,FIFS.FLEX_VALUE_SET_ID)

         and fifst.ID_FLEX_CODE = 'GL#';

 

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

SELECT   fifs.segment_name,

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and fifst.ID_FLEX_CODE = 'GL#';

 

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

SELECT   fifs.segment_name, FLEX_VALUE_SET_ID

  FROM   fnd_id_flex_structures_vl fifst, fnd_id_flex_segments fifs

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and fifst.ID_FLEX_CODE = 'GL#';