Query for Which User has assigned which responsibility and report

select  frv.RESPONSIBILITY_NAME,frv.RESPONSIBILITY_ID,fu.USER_NAME, frg.REQUEST_GROUP_NAME,frg.REQUEST_GROUP_CODE,frgu.REQUEST_GROUP_ID,
        decode(frgu.APPLICATION_ID,401,'INV') APPLICATION, frgu.REQUEST_UNIT_ID, a.USER_CONCURRENT_PROGRAM_NAME
from    FND_CONCURRENT_PROGRAMS_VL a,
        FND_REQUEST_GROUP_UNITS frgu,
        FND_REQUEST_GROUPS frg,
        FND_RESPONSIBILITY_VL frv,
        FND_USER_RESP_GROUPS_DIRECT fcr,
        fnd_user fu 
where   frgu.REQUEST_UNIT_ID    =   a.CONCURRENT_PROGRAM_ID
and     frgu.APPLICATION_ID     =   frg.APPLICATION_ID
and     frgu.REQUEST_GROUP_ID   =   frg.REQUEST_GROUP_ID
and     frgu.REQUEST_GROUP_ID   =   frv.REQUEST_GROUP_ID
and     frgu.APPLICATION_ID     =   frv.APPLICATION_ID
and     frv.RESPONSIBILITY_ID   =   fcr.RESPONSIBILITY_ID(+)
and     fcr.USER_ID             =   fu.USER_ID(+)
--and     a.CONCURRENT_PROGRAM_ID =   32427
and     frg.APPLICATION_ID      =   401
--and     frgu.REQUEST_GROUP_ID   =   58

Replace the Default Oracle Logo with a Customized Logo

1. Change the FNDSSCORP.gif file.

A. Make a backup copy of the FNDSSCORP.gif file.  Name it FNDSSCORP.bak
B. Make the name of the custom logo FNDSSCORP.gif
C. Upload the new FNDSSCORP.gif file to the $OA_MEDIA directory
D. Log out and log back into the application

2. Leave the FNDSSCORP.gif file alone and upload a new image (test.jpg, for example) $OA_MEDIA directory.

A. Log into (N) System Administrator > Profile > System
B. Query the "Corporate Branding Image for Oracle Applications" profile
C. Enter the filename of the image under the Site Column
D. Save
E. Log out and log back into the application

How To Upload Item Cross Reference Using mtl_cross_references_interface

INSERT INTO mtl_ci_interface
(customer_id,
customer_item_number,
commodity_code,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date,ATTRIBUTE5,ATTRIBUTE6
)
VALUES
(2045,
'Test_upload',
'Finished Goods',
1, 1, 1,
'2', 'CREATE', 'N',
1212, 1212, SYSDATE,
SYSDATE,'E-0001','E-0002'
);


INSERT INTO mtl_ci_xrefs_interface
(customer_id,
customer_item_number,
preference_number,
inventory_item,
master_organization_id,
item_definition_level, process_flag, process_mode,
inactive_flag, transaction_type, lock_flag,
last_updated_by, created_by, last_update_date,
creation_date
)
VALUES (2045,
'Test_upload',
1,
'00-0001-0004-2-0000-0000',
313,
1, 1, 1,
'2', 'CREATE', 'N',1212,1212, SYSDATE,
SYSDATE
);

Modify the hostname on EBS R12

1. DB Tier: Deregister the current database server

    As the database hostname and domain will be changed, the current database server node needs to be de-registered.

    select NAME, SERVER_TYPE
    from FND_APP_SERVERS, FND_NODES
    where FND_APP_SERVERS.NODE_ID = FND_NODES.NODE_ID
    and SERVER_TYPE='DB'
    and FND_NODES.NODE_NAME='old_hostname';
    

    NAME SERVER_TYPE
    --------------- -----------
    oldhost_<SID>_DB DB

    Executing following commands to deregister the current database server:

        cd $ORACLE_HOME/appsutil
        perl ./bin/adgentns.pl appspass=<APPSpwd> contextfile=./<SID>_oldhost.xml -removeserver
        
        select NAME, SERVER_TYPE
        from FND_APP_SERVERS, FND_NODES
        where FND_APP_SERVERS.NODE_ID = FND_NODES.NODE_ID
        and SERVER_TYPE='DB'
        and FND_NODES.NODE_NAME='old_hostname';
        

        no rows selected.


2. DB Tier: Create a new Context file

    You can create the new context file using whichever of the following methods:

    a. Manual Method:

        cd $ORACLE_HOME/appsutil
        cp <SID>_oldhost.xml <SID>_newhost.xml
        
        Edit <SID>_newhost.xml manually:
        Replace all oldhost with newhost

    b. Script Method:(The command below will create a new Context file of the format <SID>_newhost.xml in the current working directory.)

        cd $ORACLE_HOME/appsutil
        perl ./clone/bin/adclonectx.pl contextfile=./<SID>_oldhost.xml
        

    **************************************************************************************************************************

    Enter the APPS password :  <Password>
    Log file located at <DB - $ORACLE_HOME>/appsutil/CloneContext_1223165155.log
    Provide the values required for creation of the new Database Context file.
    Target System Hostname (virtual or normal) [nascapp6] :
    It is recommended that your inputs are validated by the program.
    However you might choose not to validate them under following circumstances:
    -If cloning a context on source system for a remote system.
    -If cloning a context on a machine where the ports are taken and
    you do not want to shutdown the services at this point.
    -If cloning a context but the database it needs to connect is not available.
    Do you want the inputs to be validated (y/n) [n] ? :
    Target Instance is RAC (y/n) [n] :
    Target System Database SID : <SID>
    Target System Base Directory : <EBS Base Directory>
    Oracle OS User [oravis] :
    Oracle OS Group [dba] :
    Target System utl_file_dir Directory List :/usr/tmp
    Number of DATA_TOP's on the Target System [4] : 1
    Target System DATA_TOP Directory 1 : /d01/oracle/PROD/db/apps_st/data <------- input the path of your data files
    Target System RDBMS ORACLE_HOME Directory [/d01/oracle/PROD/db/tech_st/11.1.0] :
    Do you want to preserve the Display [null] (y/n) ? : n
    Target System Display [newhost:0.0] :
    Do you want the the target system to have the same port values as the source system (y/n) [y]?:
    Complete port information available at <DB - $ORACLE_HOME>/appsutil/out/<SID>_nascapp6/portpool.lst
    New context path and file name [<DB - $ORACLE_HOME>/appsutil/PROD_nascapp6.xml] :
    Creating the new Database Context file from :
    <DB - $ORACLE_HOME>/appsutil/template/adxdbctx.tmp
    The new database context file has been created :
    <DB - $ORACLE_HOME>/appsutil/PROD_nascapp6.xml
    Log file located at <DB - $ORACLE_HOME>/appsutil/CloneContext_1223170424.log
    contextfile=<DB - $ORACLE_HOME>/appsutil/PROD_nascapp6.xml
    Check Clone Context logfile <DB - $ORACLE_HOME>/appsutil/CloneContext_1223170424.log for details.
    **************************************************************************************************************************

3. APPS Tier: Deregister the current Applications server

    As the Applications hostname and domain will be changed, the current Applications server node needs to be de-registered.

    select NAME, SERVER_TYPE
    from FND_APP_SERVERS, FND_NODES
    where FND_APP_SERVERS.NODE_ID = FND_NODES.NODE_ID
    and SERVER_TYPE='APPS'
    and FND_NODES.NODE_NAME='old_hostname';
    

    NAME SERVER_TYP
    ----------------- ----------
    oldhost_<SID>_APPS APPS

    Executing following commands to deregister the current Applications server:

        cd $APPL_TOP/admin
        perl $AD_TOP/bin/adgentns.pl appspass=<APPSpwd> contextfile=./<SID>_oldhost.xml -removeserver
        
        select NAME, SERVER_TYPE
        from FND_APP_SERVERS, FND_NODES
        where FND_APP_SERVERS.NODE_ID = FND_NODES.NODE_ID
        and SERVER_TYPE='APPS'
        and FND_NODES.NODE_NAME='old_hostname';
        

        no rows selected.

 4. APPS Tier: Create a new Context file

    You can create the new context file using whichever of the following methods:

    a. Manual Method:

        cd $APPL_TOP/admin
        cp <SID>_oldhost.xml <SID>_newhost.xml
        
        Edit <SID>_newhost.xml manually:
        Replace all oldhost with newhost

    b. Script Method:(The command below will create a new Context file of <SID>_newhost.xml in new <SID>_newhost directory.)

        cd $INST_TOP/appl/admin
        perl $COMMON_TOP/clone/bin/adclonectx.pl contextfile=./<SID>_oldhost.xml
        


    **************************************************************************************************************************

    Enter the APPS password : apps
    Log file located at $INST_TOP/appl/admin/CloneContext_1223173053.log
    Provide the values required for creation of the new APPL_TOP Context file.
    Target System Hostname (virtual or normal) [nascapp6] :
    It is recommended that your inputs are validated by the program.
    However you might choose not to validate them under following circumstances:
    -If cloning a context on source system for a remote system.
    -If cloning a context on a machine where the ports are taken and
    you do not want to shutdown the services at this point.
    -If cloning a context but the database it needs to connect is not available.
    Do you want the inputs to be validated (y/n) [n] ? :
    Target System Database SID : <SID>
    Target System Database Server Node [nascapp6] :
    Target System Base Directory : <EBS Base Directory>
    Target System Tools ORACLE_HOME Directory [<10.1.2 $ORACLE_HOME>] :
    Target System Web ORACLE_HOME Directory [<10.1.3 $ORACLE_HOME>] :
    Target System APPL_TOP Directory [<$APPL_TOP>] :
    Target System COMMON_TOP Directory [<$COMMON_TOP>] :
    Target System Instance Home Directory [<$INST_TOP>] :
    Username for the Applications File System Owner [applvis] :
    Group for the Applications File System Owner [dba] :
    Target System Root Service [enabled] :
    Target System Web Entry Point Services [enabled] :
    Target System Web Application Services [enabled] :
    Target System Batch Processing Services [enabled] :
    Target System Other Services [disabled] :
    Do you want to preserve the Display [oldhost:0.0] (y/n) ? : newhost:0.0
    Target System Display [newhost:0.0] :
    New context path and file name [$INST_TOP/appl/admin/<SID>_newhost.xml] :
    **************************************************************************************************************************


5. APPS Tier: Shutdown the Application Tier Services

    cd $INST_TOP/admin/scripts
    ./adstpall.sh apps/<appspasswd>
    

6. Change the server machine hostname.

    Please contact your IT administrator to change the machine hostname.

7. DB Tier: Reseed the Net Services Topology Model

    cd $ORACLE_HOME/appsutil
    ./bin/adconfig.sh contextfile=./<SID>_newhost.xml
    

8. APPS Tier: Reseed the Net Services Topology Model

    The Net Services Topology Model is automatically updated by running AutoConfig.
    $AD_TOP/bin/adconfig.sh contextfile= $INST_TOP/../<SID>_newhost//appl/admin/<SID>_newhost.xml appspass=<appspasswd>
    

9. DB Tier: Shutdown the database and listener

    $ORACLE_HOME/appsutil/scripts/<SID>_oldhost/addbctl.sh stop
    $ORACLE_HOME/appsutil/scripts/<SID>_oldhost/addlnctl.sh stop <SID>
    

10. DB Tier: Start the listener and database

    $ORACLE_HOME/appsutil/scripts/<SID>_newhost/addlnctl.sh start <SID>
    $ORACLE_HOME/appsutil/scripts/<SID>_newhost/addbctl.sh start
    

11. APPS Tier: Start the Application Tier Services

    $INST_TOP/admin/scripts/<SID>_newhost/adstrtal.sh apps/<appspasswd>

Autoconfig on APPS and DB Tier

APPS Tier

1. Run Environment file
1. . /opt/oracle/PROD/apps/apps_st/appl/APPSPROD_prodapps01.en
2. Run ADautocfg.sh
1. . /opt/oracle/PROD/inst/apps/PROD_prodapps01/admin/scripts/adautocfg.sh
3.  Log will be located at 
1. /opt/oracle/PROD/inst/apps/PROD_prodapps01/admin/log/
4. Create appsutil.zip file for this run
1. /opt/oracle/PROD/apps/apps_st/appl/ad/12.0.0/bin/
2. perl admkappsutil.pl
5. This will create apputil.zip in
1. /d01/oracle/PROD/inst/apps/PROD_prodapps01/admin/out/appsutil.zip
6. Log will be located at 
7. /d01/oracle/PROD/inst/apps/PROD_prodapps01/admin/log/MakeAppsUtil_06161445.log
   DB Tier
8. Copy appsutil.zip on 
1. /opt/oracle/PROD/db/tech_st/10.2.0/
9. unzip -o appsutil.zip
10. . /d01/oracle/PROD/db/tech_st/10.2.0/PROD_proddb01.env
11. cd /d01/oracle/PROD/db/tech_st/10.2.0/appsutil
1. perl bin/adconfig.pl contextfile=PROD_proddb01.xml appspass=apps
12. Log will be located at /d01/oracle/PROD/db/tech_st/10.2.0/appsutil/log/PROD_proddb01/[mmddhhmm]0616112/ 

Data Uploading on Oracle EBS using interface Tables

1. Create csv file and map it with interface table.
2. Check there are no commas in csv file, if any then replace comma with null.
3. Create a customized table as
1. Create SYSTEM_ITEMS_INTERFACE as Select * from MTL_SYSTEM_ITEMS_INTERFACE
4. Make sure that control file contain customized table name.
5. Open WinSCP or any FTP program.
6. Copy csv and control file in prod or test server in new directory.
7. Open Putty. 
8. Set environment of serve.
9. Now go to control file cd /home/oracle/upload/DIR
10. Now run the control file
1. Sqlldr apps/password(toad password) item_master.ctl 
11. Data will be uploaded in customized table
12. If there are some errors during data uploading, check the log file from WinSCP and identify the error
13. Otherwise upload the data in the interface table.
1. insert into MTL_SYSTEM_ITEMS_INTERFACE select * from  SYSTEM_ITEMS_INTERFACE
14. Then run the request from Application "Import Item" with Create parameter

AP Accrual Queries

Cost Management Source

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,xl.accounted_dr

      ,xl.accounted_cr

      ,nvl(xl.accounted_dr,0) - nvl(xl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT     

      ,mtt.TRANSACTION_TYPE_NAME

      ,Case when mmt.transaction_type_id in (18,36) then

            (select segment1 from po_headers_all pha where pha.po_header_id=mmt.TRANSACTION_SOURCE_ID)

       end PO_Number   

      ,to_char(xte.SOURCE_ID_INT_1)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,mmt.TRANSACTION_QUANTITY

--      ,xte.SECURITY_ID_INT_1 Inventory_Org

--      ,xte.SECURITY_ID_INT_2 Operating_Unit

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,xte.ENTITY_CODE

--      ,decode(mtt.TRANSACTION_TYPE_NAME,'PO Receipt',pha.segment1,null) PO_Number

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,xla_ae_lines xl

     ,xla_ae_headers xh

     ,xla.xla_transaction_entities xte

     ,org_organization_definitions ood

     ,MTL_MATERIAL_TRANSACTIONS mmt

     ,mtl_transaction_types mtt

--     ,po_headers_all pha

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 518282

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and xl.ae_header_id     = xh.ae_header_id

and xl.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

and xl.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE

and gir.GL_SL_LINK_TABLE = 'XLAJEL'

AND xte.entity_id = xh.entity_id

and ood.organization_id = xte.SECURITY_ID_INT_1

--and gh.period_name      = :P_PERIOD --'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and xte.SOURCE_ID_INT_1 = mmt.transaction_id

and mtt.transaction_type_id = mmt.transaction_type_id

--and mmt.TRANSACTION_SOURCE_ID = pha.po_header_id (+)

and msi.inventory_item_id = mmt.inventory_item_id 

and msi.organization_id = 87

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and xte.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'

UNION ALL

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,xl.accounted_dr

      ,xl.accounted_cr

      ,nvl(xl.accounted_dr,0) - nvl(xl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,rt.SOURCE_DOCUMENT_CODE||'-'||rt.TRANSACTION_TYPE TRANSACTION_TYPE_NAME

      ,pha.segment1 PO_Number

      ,to_char(xte.SOURCE_ID_INT_1)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,rt.quantity

--      ,xte.SECURITY_ID_INT_1 Inventory_Org

--      ,xte.SECURITY_ID_INT_2 Operating_Unit

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID   

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,xte.ENTITY_CODE

--      ,xte.* 

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,xla_ae_lines xl

     ,xla_ae_headers xh

     ,xla.xla_transaction_entities xte

     ,org_organization_definitions ood

     ,rcv_transactions rt

     ,po_headers_all pha

     ,po_lines_all pla

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2212350

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and xl.ae_header_id     = xh.ae_header_id

and xl.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

and xl.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE

and gir.GL_SL_LINK_TABLE = 'XLAJEL'

AND xte.entity_id = xh.entity_id

and ood.organization_id = xte.SECURITY_ID_INT_1

--and gh.period_name      = :P_PERIOD --'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and rt.transaction_id = xte.SOURCE_ID_INT_1

and rt.organization_id   = xte.SECURITY_ID_INT_1

and rt.po_header_id = pha.po_header_id

and pla.po_header_id = pha.po_header_id

and rt.po_line_id = pla.po_line_id

and xte.ENTITY_CODE ='RCV_ACCOUNTING_EVENTS'

and pla.item_id = msi.inventory_item_id

and msi.organization_id = 87

 Payables Source

SELECT gh.je_source

           ,gh.period_name

           ,gh.je_category

           ,gh.NAME

           ,gc.segment4

           ,gc.segment1

           ,gc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

         ,xdl.UNROUNDED_ACCOUNTED_DR

         ,xdl.UNROUNDED_ACCOUNTED_CR

         ,nvl(xdl.UNROUNDED_ACCOUNTED_DR,0) - nvl(xdl.UNROUNDED_ACCOUNTED_CR,0) NET

         ,(select ood.ORGANIZATION_CODE from  org_organization_definitions ood where ood.organization_id = rt.organization_id) ORGANIZATION_CODE

         ,aia.org_id

         ,xl.accounting_class_code

         ,(select segment1 from po_headers_all pha where pha.po_header_id = rt.po_header_id) PO_Number

         ,(select rvh.RECEIPT_NUM from RCV_VRC_HDS_V rvh where rvh.SHIPMENT_HEADER_ID= rt.SHIPMENT_HEADER_ID) GRN

         ,to_char(aia.invoice_num)

         ,(select msi.SEGMENT1 from po_lines_all pla, mtl_system_items_kfv msi where msi.organization_id = 87 and msi.inventory_item_id = pla.item_id  and pla.PO_LINE_ID = rt.PO_LINE_ID) PREFIX

         ,(select CONCATENATED_SEGMENTS from po_lines_all pla, mtl_system_items_kfv msi where msi.organization_id = 87 and msi.inventory_item_id = pla.item_id  and pla.PO_LINE_ID = rt.PO_LINE_ID) Item_Code

         ,rt.QUANTITY_BILLED

          ,pv.vendor_name

    FROM gl_je_lines gl, gl_je_headers gh, gl_code_combinations_kfv gc

        ,xla_ae_lines xl

        ,xla_ae_headers xh

        ,xla.xla_transaction_entities xte 

        ,xla.xla_distribution_links xdl

        ,ap_invoice_distributions_all aida

        ,rcv_transactions rt

        ,ap_invoices_all aia

        ,po_vendors pv

WHERE   gh.je_header_id = gl.je_header_id

     and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

     and gc.code_combination_id = gl.code_combination_id

     and xh.ae_header_id = xl.ae_header_id

     and xte.entity_id = xh.entity_id

     and xte.entity_code = 'AP_INVOICES'

     and xte.source_id_int_1 = aia.invoice_id

     and aia.vendor_id = pv.vendor_id

     and gl.GL_SL_LINK_ID = xl.GL_SL_LINK_ID --(+)

     and gh.status = 'P'

     and xl.AE_HEADER_ID = xdl.AE_HEADER_ID

     and xl.AE_LINE_NUM  = xdl.AE_LINE_NUM

     and aida.invoice_id = aia.invoice_id

     and aida.INVOICE_DISTRIBUTION_ID = xdl.SOURCE_DISTRIBUTION_ID_NUM_1

     and aida.RCV_TRANSACTION_ID = rt.TRANSACTION_ID(+)

    AND TRUNC (gl.effective_date) BETWEEN :P_date_from and :P_date_to

     and gh.actual_flag = 'A'

     and gc.segment4 =  '1340101001'

     and gc.segment1 =  '20'

     and gc.segment4 =  '23'

     and gh.je_source = 'Payables'

 Periodic Source

select gh.je_source

      ,gh.period_name

      ,gcc.segment4 Natural_Code

      ,gcc.segment1 Division_Code

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,gl.accounted_dr

      ,gl.accounted_cr

      ,nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,mtt.TRANSACTION_TYPE_NAME

      ,Case when mmt.transaction_type_id in (18,36) then

            (select segment1 from po_headers_all pha where pha.po_header_id=mmt.TRANSACTION_SOURCE_ID)

       end PO_Number   

      ,to_char(mmt.transaction_id)

      ,msi.SEGMENT1  PREFIX

      ,msi.CONCATENATED_SEGMENTS 

      ,mmt.TRANSACTION_QUANTITY

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID     

--      ,cal.*

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,cst_ae_lines cal

     ,cst_ae_headers cah

     ,mtl_material_transactions mmt

     ,mtl_transaction_types mtt

     ,mtl_system_items_kfv msi

     ,org_organization_definitions ood

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2296647 --2296591

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and gir.GL_SL_LINK_TABLE = 'CSTECL'

and JE_CATEGORY = 'MTL'

and cal.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

AND cah.ae_header_id = cal.ae_header_id

--and gh.period_name      = 'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

--and gcc.segment4 = :P_Segment4

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

AND (cah.accounting_event_id = mmt.transaction_id)

and mtt.transaction_type_id = mmt.transaction_type_id

and msi.inventory_item_id = mmt.inventory_item_id 

and msi.organization_id = 87

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and mmt.organization_id = ood.organization_id

UNION ALL

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,gl.accounted_dr

      ,gl.accounted_cr

      ,nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,rt.SOURCE_DOCUMENT_CODE||'-'||rt.TRANSACTION_TYPE TRANSACTION_TYPE_NAME

      ,pha.segment1 PO_Number

      ,to_char(rt.transaction_id)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,rt.quantity     

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,cah.ACCT_EVENT_SOURCE_TABLE

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,cst_ae_lines cal

     ,cst_ae_headers cah

     ,org_organization_definitions ood

     ,rcv_transactions rt

     ,po_headers_all pha

     ,po_lines_all pla

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2296647 --2296591

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and gir.GL_SL_LINK_TABLE = 'CSTECL'

and JE_CATEGORY = 'Receiving'

and cal.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

AND cah.ae_header_id = cal.ae_header_id

--and gh.period_name      = 'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and cah.organization_id = rt.organization_id 

AND (cah.accounting_event_id = rt.transaction_id)

and rt.organization_id   = ood.organization_id

and rt.po_header_id = pha.po_header_id

and pla.po_header_id = pha.po_header_id

and rt.po_line_id = pla.po_line_id

and pla.item_id = msi.inventory_item_id

and msi.organization_id = 87