R12 EBR : Deploying custom tables

Background

An Oracle E-Business Suite Release 12.2 installation now includes two editions (versions)of the application code and seed data. The file system contains two complete copies of the Oracle E-Business Suite and technology files.

The "RunEdition" is the code and data used by the running application. The Run Edition includes a complete application-tier file system along with all objects and data visible in the default edition of the database. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.

The "PatchEdition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching. The Patch Edition includes a complete copy of the application-tier file system and editioned database code objects.The Patch Edition is only usable when an Online Patching session is in progress. End users cannot access the Oracle E-Business Suite Patch Edition,but as a developer you may need to connect to the Patch Edition of a system when applying patches or debugging problems with Online Patch execution.

The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:

• fs1 - file system 1 (either run or patch edition)
• fs2 - file system 2 (alternate of file system 1)
• fs_ne - non-editioned file system, for data files

To find out which file system is the Run Edition you must look at thevalue of FILE_EDITION environment variable in the environment script for eachfile system:

$ cd /u01/R122_EBS 
$ grep FILE_EDITION= */EBSapps/appl/*.env

Oracle E-Business Suite Release 12.2.2 and higher includes a script to set the run or patch edition environment by name. The script is called "EBSapps.env" and is found in the root directory of an Oracle E-Business Suite application-tier installation.

$ source /u01/R122_EBS/EBSapps.env run 

SQL> select ad_zd.get_edition_type from dual; 

All ADZD* scripts are found under $AD_TOP/sql.For convenience, you can add this directory to the SQLPATH environment variableso that you can refer to the scripts by simple name.

$SQLPATH=$AD_TOP/sql; export SQLPATH

1. If you copied any custom files under the $JAVA_TOP     directory, run the adcgnjar utility to generate and sign a JAR file     containing these files. When prompted, enter the user name and     password of the APPS user. See Section 5.3: Running the adcgnjar Utility.

If your application changes will cause significant object invalidation in the development database, you may wish to call the "ad_zd.compile"procedure to recompile invalid objects in the run edition.

TABLE

exec ad_zd_table.upgrade('CUSTOM_SCHEMA', 'XYZ_CUSTOM_TABLE') 

Run in apps

This will create a editioning view XYZ_CUSTOM_TABLE# in apps, as well as a synonym XYZ_CUSTOM_TABLE in apps based on the view.

IF you have to alter a table, adding a column

exec ad_zd_table.patch('CUSTOM_SCHEMA', 'XYZ_CUSTOM_TABLE') 

For Indexes, Views, Packages you need not do anything.

Materialized View

First create a logical view with the name ending with #

create or replace view XYZ_SCHEMAS_MV#

While it is acceptable for the logical view to depend on editioned synonyms and views, it must not depend on editioned PL/SQL functions, such as those in the Oracle E-Business Suite APPS schema (built-in PL/SQL functions such as "upper" are acceptable). 

materialized views are generated from their corresponding logical views using the AD_ZD_MVIEW.UPGRADE procedure.

execad_zd_mview.upgrade('APPS', 'XYZ_SCHEMAS_MV')

Source

Average Cost Query

SELECT cql.inventory_item_id,clcd.*
FROM   CST_QUANTITY_LAYERS cql
,      mtl_system_items_b msi
,      CST_LAYER_COST_DETAILS clcd
WHERE  cql.inventory_item_id = msi.inventory_item_id
AND    cql.organization_id = msi.organization_id
AND    msi.segment4 = 'SBC/HP'
AND    cql.organization_id = 372
AND    cql.layer_id = clcd.layer_id
AND    cql.inventory_item_id = 17495806

Inventory GL

SELECT   xld.source_distribution_type,
         xld.accounting_line_code,
         xld.accounting_line_type_code,
         xld.line_definition_code,
         xld.event_class_code,
         xld.event_type_code,
         xld.rounding_class_code,
         xld.unrounded_entered_cr,
         xld.unrounded_entered_dr,
         xld.unrounded_accounted_cr,
         xld.unrounded_accounted_dr,
         ael.gl_sl_link_id,
         ael.gl_sl_link_table,
         GJB.NAME BATCH_NAME,
         gL.period_name,
         gl.accounted_cr,
         gl.accounted_dr,
         gl.entered_cr,
         gl.entered_dr,
         gh.je_source,
         gh.je_category,
         gh.posted_date,
         mta.inv_sub_ledger_id,
         mta.base_transaction_value,
         mta.currency_conversion_rate,
         mta.rate_or_amount,
         mta.currency_code,
         mmt.subinventory_code,
         TRUNC (mmt.transaction_date) transaction_date,
         mmt.transaction_quantity,
         mmt.transaction_uom,
         mmt.primary_quantity,
         mmt.actual_cost,
         mmt.source_code,
         mmt.source_line_id,
         mmt.rcv_transaction_id,
         msi.concatenated_segments item,
         gcc.concatenated_segments GL_ACCOUNT_STRING,
         ood.organization_code,
         ood.organization_name,
         mmt.transaction_id,
         ael.ae_header_id,
         ael.ae_line_num,
         gl.je_header_id,
         gl.je_line_num,
         gcc.code_combination_id,
         msi.inventory_item_id,
         msi.organization_id,
         gjb.je_batch_id,
--         GCC.SEGMENT2 GCC#50353#ACCOUNT,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT2,
--                                               'MAS_GL_COA_ACCOUNT')
--            GCC#50353#ACCOUNT#DESCR,
--         GCC.SEGMENT1 GCC#50353#COMPANY,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT1,
--                                               'MAS_GL_COA_COMPANY')
--            GCC#50353#COMPANY#DESCR,
--         GCC.SEGMENT6 GCC#50353#DEPARTMENT,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT6,
--                                               'MAS_GL_COA_DEPARTMENT')
--            GCC#50353#DEPARTMENT#DESCR,
--         GCC.SEGMENT8 GCC#50353#FUTURE,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT8,
--                                               'MAS_GL_COA_FUTURE')
--            GCC#50353#FUTURE#DESCR,
--         GCC.SEGMENT7 GCC#50353#IC,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT7,
--                                               'MAS_GL_COA_COMPANY')
--            GCC#50353#IC#DESCR,
--         GCC.SEGMENT4 GCC#50353#LOB,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT4,
--                                               'MAS_GL_COA_LOB')
--            GCC#50353#LOB#DESCR,
--         GCC.SEGMENT5 GCC#50353#LOCATIONS,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT5,
--                                               'MAS_GL_COA_LOCATION')
--            GCC#50353#LOCATIONS#DESCR,
--         GCC.SEGMENT3 GCC#50353#QUALIFIER,
--         xxeis.eis_rs_fin_utility.decode_vset (GCC.SEGMENT3,
--                                               'MAS_GL_COA_ACCT_QUAL')
--            GCC#50353#QUALIFIER#DESCR
  FROM   xla_transaction_entities_upg ent,
         xla_events e,
         xla_distribution_links xld,
         mtl_transaction_accounts mta,
         mtl_material_transactions mmt,
         xla_ae_headers ah,
         xla_ae_lines ael,
         gl_import_references gir,
         gl_je_lines gl,
         gl_code_combinations_kfv gcc,
         gl_je_headers gh,
         GL_JE_BATCHES GJB,
         mtl_system_items_kfv msi,
         org_organization_definitions ood
 WHERE       mmt.transaction_id = NVL (ent.source_id_int_1, -99)
         AND ent.entity_code = 'MTL_ACCOUNTING_EVENTS'
         AND ent.application_id = 707
         AND ent.entity_id = e.entity_id
         AND e.application_id = 707
         AND e.event_id = xld.event_id
         AND ah.application_id = 707
         AND ah.entity_id = ent.entity_id
         AND ah.event_id = e.event_id
         AND ah.ledger_id = ent.ledger_id
         AND ah.ae_header_id = ael.ae_header_id
         AND ael.application_id = 707
         AND ael.ledger_id = ah.ledger_id
         AND ael.AE_HEADER_ID = xld.AE_HEADER_ID
         AND ael.AE_LINE_NUM = xld.AE_LINE_NUM
         AND xld.application_id = 707
         AND xld.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
         AND xld.source_distribution_id_num_1 = mta.inv_sub_ledger_id
         AND mta.transaction_id = mmt.transaction_id
         AND ael.gl_sl_link_id = gir.gl_sl_link_id
         AND ael.gl_sl_link_table = gir.gl_sl_link_table
         AND gir.je_header_id = gl.je_header_id
         AND gir.je_line_num = gl.je_line_num
         AND gl.code_combination_id = gcc.code_Combination_id
         AND gl.je_header_id = gh.je_header_id
         AND GH.JE_BATCH_ID = GJB.JE_BATCH_ID
         AND mta.transaction_id = mmt.transaction_id
         AND mmt.inventory_item_id = msi.inventory_item_id
         AND mmt.organization_id = msi.organization_id
         AND msi.organization_id = ood.organization_id

FND_PROFILE in R12

FND_PROFILE.value('ORG_ID') will return value only if the 'MO: Operating Unit' is set for the responsibility used.

If you need to get the ORG_ID in a MOAC enabled instance, 
use FND_GLOBAL.org_id or MO_GLOBAL.get_current_org_id instead.

From the reference given below:

"For MOAC-enabled instances, FND_PROFILE.VALUE('ORG_ID') will no longer convey the correct org context for a given session.  The customer *has to change* all FND_PROFILE.VALUE('ORG_ID') calls to either use MO_GLOBAL.get_current_org_id() (which is highly recommended by the MO Team or FND_GLOBAL.ORG_ID().  Do NOT use FND_PROFILE.VALUE('ORG_ID') for any MOAC-enabled instances."

To get the org_id in OA Framework code:

use appModule.getOADBTransaction().getOrgId() or pageContext.getOrgId(). 

instead of pageContext.getProfile("ORG_ID").

Oracle Time and Projects

SELECT   hts.approval_status ,hts.timecard_id, hts.resource_id, hts.start_time,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id,papa.name, papa.segment1, pt.task_number, htb2.measure, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              --AND htb1.date_to = hr_general.end_of_time
              AND htb2.parent_building_block_id = htb1.time_building_block_id
              AND htb2.parent_building_block_ovn = htb1.object_version_number
              AND htb2.SCOPE = 'DETAIL'
              --AND htb2.date_to = hr_general.end_of_time
              AND htau.time_building_block_id = htb2.time_building_block_id
              AND htau.time_building_block_ovn = htb2.object_version_number
              AND htau.time_attribute_id = hta.time_attribute_id
              AND papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND  hts.start_time > sysdate - 380
              AND hts.resource_id = htb.resource_id
             AND htb.resource_id = :p_resource_id -- person_id
              --AND hts.timecard_id = :p_timecard_id
              AND hta.attribute_category = 'PROJECTS'
              AND hts.approval_status = 'APPROVED'
              AND hta.attribute2 = pt.task_id
              AND hta.attribute1 = pt.project_id
         ORDER BY htb1.start_time;

regexp_like

SELECT :expr, 'narayan is my name raykar. ('
FROM   DUAL
WHERE regexp_like (:expr,'(^|\s)raykar($|\s|\W)','i' )

(^|\s) - starts with or has space
($|\s|\W) - ends with or has space or has a nonword character

Session SQL and Lock

                    SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id, a.concurrent_program_id, d.event
                    , d.*
                    FROM apps.fnd_concurrent_requests a,
                    apps.fnd_concurrent_processes b,
                    gv$process c,
                    gv$session d
                    WHERE a.controlling_manager = b.concurrent_process_id
                    AND c.pid = b.oracle_process_id
                    --AND concurrent_program_id = 32766
                    AND b.session_id=d.audsid
                    AND c.inst_id = d.inst_id
                    AND a.request_id = 197521679
                   
                             SELECT s2.sid
                             ,      s2.lockwait
                             ,      s1.sql_text
                             ,      s1.piece
                             FROM   gv$SQLtext s1
                             ,      gv$session s2
                             WHERE  s1.address =  s2.sql_address
                             AND    s1.inst_id = s2.inst_id
                             --AND    s2.sid = 1380
                             --AND    s2.inst_id = 2
                             AND module IN ('e:XXX:cp:inv/INCOIN')
                             ORDER BY s2.sid, s1.piece


                             SELECT *
                             FROM   gv$session
                             WHERE  1=1
                             --AND    module like '%cp:%INV%'
                             AND    sid in (1380, 731)
                                                       
                            SELECT event, state, p1, p2, p3
                            FROM gv$session_wait
                            WHERE sid = 1380                            
                                       
                    SELECT description, USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_NAME
                    FROM   fnd_concurrent_programs_vl
                    WHERE  1=1
                    --AND    concurrent_program_id = 32766
                    AND    USER_CONCURRENT_PROGRAM_NAME like '%Receiving Transaction%'
                   


                        SELECT
                           l1.sid || ' is blocking ' || l2.sid blocking_sessions
                        FROM
                           gv$lock l1, gv$lock l2
                        WHERE
                           l1.block = 1 AND
                           l2.request > 0 AND
                           l1.id1 = l2.id1 AND
                           l1.id2 = l2.id2 AND
                           l1.inst_id = l2.inst_id                                    
                            

Handle LISTAGG length issue for CSV

DECLARE


                              CURSOR rsrv_cur
                              IS
                              SELECT distinct stg.so_line_id
                              FROM   apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
                              WHERE  1 = 1
                              AND    stg.source_code = 'VALIDATION_DATA'
                             --AND    stg.s_status_code = 'V'
                              AND    stg.l_source_organization_id = :g_source_organization_id
                              AND    stg.l_dest_organization_id = :g_destination_organization_id
                              AND    stg.lpn IS NULL
                              AND    stg.serial_number IS NOT NULL
                              AND    stg.serial_control_flag = 'Y'
                              GROUP BY stg.so_line_id,
                                       'SERIAL';

   l_rsrv_value  VARCHAR2(32767) ;

BEGIN

 for rsrv_rec IN rsrv_cur LOOP

    dbms_output.put_line ('SO_LINE_ID => '||rsrv_rec.so_line_id);

   BEGIN

    SELECT
    RTRIM (REGEXP_REPLACE ( (LISTAGG (stg.serial_number, ',') WITHIN GROUP (ORDER BY stg.serial_number) ),
                                            '([^,]*)(,\1)+($|,)',
                                            '\1\3'),
                                     ',') rsrv_value
    INTO l_rsrv_value
    FROM apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
    WHERE  stg.so_line_id = rsrv_rec.so_line_id ;

   EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line ('SO_LINE_ID => '||rsrv_rec.so_line_id||' '||SQLERRM);


      SELECT regexp_replace( XMLAGG(XMLELEMENT(E,stg.serial_number||',').EXTRACT('//text()') ).getclobval(),'^,|,$', '') Result
       INTO l_rsrv_value
      FROM apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
      WHERE  stg.so_line_id =  rsrv_rec.so_line_id ;

      dbms_output.put_line ('l_rsrv_value => '||l_rsrv_value);


   END ;

 END LOOP rsrv_rec;

EXCEPTION
   WHEN OTHERS THEN
    dbms_output.put_line ('Error '||SQLERRM);


END;

Parsing parent child XML structure using PL/SQL

Source


Doing some investigation about processing a XML stucture where we have a parent child type relationship where we want to get all the details for each child record and loop through them.
As part of looping through the child records we want the parent details to be available without the need to go back and read the XML again.

The following example displays each RaceLap tag and includes parent information. Including the case where there may be no child RaceLap entries.
The trick for me to understand was using the laptimes column extracted from the v_xml_example as an XMLTYPE as input into the second part of the FROM clause (laps) using the PASSING race.laptimes.


If there are any questions add a comment.


DECLARE

 v_xml_example                  SYS.xmltype := xmltype(

'<Races>
 <RaceResult>
  <Id>743845</Id>
  <PlateNo>420</PlateNo>
  <Completed_ind>N</Completed_ind>
  <Comments>DNF. No laps recorded</Comments>
 </RaceResult>
 <RaceResult>
  <Id>123145</Id>
  <PlateNo>233</PlateNo>
  <Completed_ind>Y</Completed_ind>
  <Comments>Finished after 3 laps</Comments>
  <RaceLap>
   <Lap>1</Lap>
   <Time>34.34</Time>
  </RaceLap>
  <RaceLap>
   <Lap>2</Lap>
   <Time>35.66</Time>
  </RaceLap>
  <RaceLap>
   <Lap>3</Lap>
   <Time>34.00</Time>
  </RaceLap>
 </RaceResult>
</Races>');


CURSOR c_race_laps IS
 SELECT race.id,
  race.plate_num,
  race.completed_ind,
  race.comments,
  laps.lap,
  laps.lap_time
 FROM    XMLTABLE('/Races/RaceResult'  -- XQuery string to get RaceResult tag
                PASSING v_xml_example
                COLUMNS
   id  VARCHAR2(100) PATH 'Id',
   plate_num NUMBER(10) PATH 'PlateNo',
   completed_ind VARCHAR2(1) PATH 'Completed_ind',
   comments VARCHAR2(100) PATH 'Comments',
   laptimes XMLTYPE  PATH 'RaceLap') race
  LEFT OUTER JOIN -- want parent with child nodes
  XMLTABLE('/RaceLap'  -- XQuery string to get RaceLap tag
                PASSING race.laptimes   -- the laptimes XMLTYPE output from the first xmltable containing the laptimes
                COLUMNS
                        lap  NUMBER  PATH 'Lap',
   lap_time NUMBER  PATH 'Time') laps
  ON (1 = 1); -- left outer join always join

BEGIN


 FOR v_race_laps_rec IN c_race_laps LOOP


  dbms_output.put_line('atr id:' || v_race_laps_rec.id ||
    ' plate_num:' || v_race_laps_rec.plate_num ||
    ' completed_ind:' || v_race_laps_rec.completed_ind ||
    ' Comments:' || v_race_laps_rec.comments ||
    ' Lap Number:' || v_race_laps_rec.lap ||
    ' Lap Time:' || v_race_laps_rec.lap_time);

 END LOOP;


END;