Anatomy of Pick Release

Pick release will create data in

mtl_txn_request_headers mtrh  ( pick wave )
mtl_txn_request_lines mtrl
wsh_picking_batches with mtrh.request_number = batch_name

mtrl.txn_source_line_detail_id = wdd.delivery_detail_id

if rule has prior reservations flag yes, then only released lines are reserved 

If rule has auto allocate flag as yes , then mtrl is allocated ( mmtt populated)

If any line is backordered, then corresponding mtrl is line is deleted

corresponding mtrl line id is removed from wdd

Convert FMB to FMT

frmcmp_batch module=$AU_TOP/forms/US/MSCDSPNM.fmb output_file=$AU_TOP/forms/US/MSCDSPNM.fmt  userid=apps/wwt_inn0vate forms_doc=yes

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;