Query to get Product details using ingredient in ASCP

SELECT
mtp1.organization_code
,(
    SELECT
      item_name
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
    and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
  )
  product
,(
    SELECT
      description
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
      and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
      )
  product_description
,(
    SELECT
      uom_code
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
     and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
  )
product_uom
, md.USING_REQUIREMENT_QUANTITY fg_forecast
,md.USING_ASSEMBLY_DEMAND_DATE fg_forecast_due_date
,msib.item_name ingredient
,msib.description ingredient_desc
,msib.uom_code  ingredient_uom
,mbc.USAGE_QUANTITY
,(md.USING_REQUIREMENT_QUANTITY*mbc.USAGE_QUANTITY ) total_ingredient_requirement
FROM
  MSC_BOM_COMPONENTS mbc
, msc_system_items msib
, msc_trading_partners mtp
,MSC_DEMANDS md
, msc_trading_partners mtp1
WHERE
  mbc.inventory_item_id   =:p_item_id
AND mbc.plan_id           =msib.plan_id
AND msib.plan_id          =:p_plan_id
AND mtp.sr_tp_id          =msib.organization_id
AND mbc.organization_id   =msib.organization_id
AND mbc.inventory_item_id = msib.inventory_item_id
AND mbc.organization_id   =3095
AND md.ORGANIZATION_ID    =75
AND md.Plan_id            =:p_plan_id
AND md.ORIGINATION_TYPE   =29
AND mbc.using_assembly_id =md.INVENTORY_ITEM_ID
AND mtp1.sr_tp_id          =md.organization_id
order by 6 asc,2 asc
 ;

RDF report to XML Publisher Report Conversion Process

How to convert Oracle reports to XML Publisher Report.

1> During the conversion process, the source reports must eventually be in Oracle Reports XML format. Oracle Reports XML format is supported in Oracle Reports 9i and above.

Open   the .rdf file and convert it into report xml file

The process of conversion is given with screenshots:-

 

c> Source : Browse the .rdf file to be converted. and click on OK.

    A file with name .xml will be created.

2>  Move the .xml file to unix box home directory.

3>  Run the following command :

java  oracle.apps.xdo.rdfparser.BIPBatchConversion  .xml  -source 

report.xml file> -target  –debug

e.g

java  oracle.apps.xdo.rdfparser.BIPBatchConversion .xml -source /home/Bomma600 -target /home/Bomma600 –debug

Note : You have to check whether BIPBatchConversion.class file is present at location cd JAVA_TOP/oracle/apps/xdo/rdfparser/ BIPBatchConversion

Output Files

The conversion utility will generate the following output files for each report:

·         Report definition file that includes the Data Model (for example: pfoa181p.xdo)

(Note: This file is not needed for E-Business Suite users; see the following note under DataTemplate.)

·         DataTemplate (for example: pfoa181p_template.xml)

(Note: this is not required for Oracle BI Publisher Enterprise users because the data template is embedded in pfoa181p.xdo.)

·         Default PL/SQL package specification (for example: pfoa181pS.pls)

·         Default PL/SQL package body (for example: pfoa181pB.pls)

·         RTF Layout Template (for example: pfoa181p.rtf)

·         Log file (for example: pfoa181p.log)

Most converted reports will run exactly as expected without further modification. More complex reports may require some additional modification to work as desired. Following are some common issues with converted reports.

Data Template and PL/SQL Files

Occasionally when converting a more complex Oracle Reports report, the Data Template or PL/SQL may contain minor errors and require manual correction.
The conversion utility will move all formula columns to the select clause of the SQL query in the data model. In most cases this will not cause a problem. However, if any of the arguments to the formula is a summary column, this will not work because the summary column will not be calculated at the time the query is executed.
To correct this problem you will need to remove this formula from the select clause and implement the formula as XSL in your layout template. Most of these formulas are used either for simple addition or summation or currency conversion, formatting, and rounding.

RTF Layout Template File

The majority of Oracle Reports reports use simple 'if' formatting logic that is automatically converted to equivalent XSL-FO and inserted into form fields in the RTF layout template. However, there is no support for PL/SQL in RTF layout templates. The conversion utility does not convert any PL/SQL format trigger logic present in the report. Instead the conversion utility writes all the format trigger code to a log file. You will need to implement any corresponding PL/SQL logic as XSL code.
To aid in this process, the resulting RTF template will contain form fields that hold the format trigger names that are called; these fields will be highlighted in red. You can then refer to the log to find the actual PL/SQL code used in the original Oracle Report. You will need to rewrite these PL/SQL triggers as XSL-FO.

Migrating Web ADI template from one Instance to another

Following are the components that are associated to the Web ADI that can be migrated from one instance to another:

• Integrators
• Layouts
• Mappings
• Contents

Integrators:

Identify the Integrator to migrate as below,

select integrator_code, application_short_name
  from apps.bne_integrators_vl  bnv
      ,apps.fnd_application_vl  fav
 where bnv.user_name = &integrator_name
   and fav.application_id = bnv.application_id;

For the Integrators, you can

Download the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN="appl_short_name" INTEGRATOR_CODE="integrator_name"

Upload the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt

Layouts:

Identify the Layout to migrate as below,

select layout_code
  from apps.bne_layouts_vl
 where user_name = &integrator_name;

For the Above Layouts, you can

Download the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct LAYOUT_LDT.ldt BNE_LAYOUTS LAYOUT_ASN="appl_short_name" LAYOUT_CODE="Layout_code"

Upload the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct LAYOUT_LDT.ldt

Mappings:

Identify the Mapping to migrate as below,


select mapping_code
  from apps.bne_mappings_vl
 where integrator_code = &integrator_code

For the Above Mappings, you can,

Download the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct MAPPING_LDT.ldt BNE_MAPPINGS MAPPING_ASN="appl_short_name" MAPPING_CODE="Mapping_code"

Upload the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct MAPPING_LDT.ldt

Contents:

Identify the Contents to migrate as below,

select content_code
   from apps.bne_contents_vl
  where integrator_code = &integrator_code

For the Above Contents, you can

Download the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct CONTENT_LDT.ldt BNE_CONTENTS CONTENT_ASN="appl_short_name" CONTENT_CODE="Content_code"

Upload the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct CONTENT_LDT.ldt

Oracle Web Applications Desktop Integrator Receiving Error BNE_UIX_PHYSICAL_DIRECTORY is not configured

1. Go into the responsibility: System Administrator

2. Navigate to Profile -> System -> Profile: BNE%Directory%

3. Remove any value in the profile BNE_UIX_PHYSICAL_DIRECTORY

4. Bounce the web server and retest the issue.

XXX is not a valid responsibility for the current user. Please contact your System Administrator.

How to Resolved XXX  is not a valid responsibility for the current user. Please contact your System Administrator.

Some time it happens that we assign a new web based responsibility like isupplier or iprocurement but when opening it display following screenshot.

This is happening because middle tiers has yet to pick the change in assigned responsibility. To resolve this issue we need to clear the middle tier cache.

Navigate to Functional administrator>Core Service >Caching Framework > Global Configuration

Click on Clear All Cache

A warning message will displayed. Click Yes

Confirmation will be displayed. Now if you navigate to iProcurement responsibility.

It will open without error

How to bounce (start/stop) Apache Server in Oracle EBS R12

1. Login to your EBS Instance with putty tool or any other software.
2. Switched to applmgr user (using sudo su - applmgr)
3. set Apps env (by running /u01/PROD/app/apps/apps_st/appl/APPS<$ORACLE_SID>_$HOSTNAME.env file)
4. Go to $INST_TOP/admin/scripts
5. run script adapcctl.sh for stopping Apache server first, and then run the same script to start it again.

cd $INST_TOP/admin/scripts

./adapcctl.sh stop

./adapcctl.sh start

6. You can check the status of Apache server using below command

./adapcctl.sh status

Solution: Problem with Desktop Integrator in R12

While Accessing Web ADI Responsibility some times we will get the below error message. To Fix this issue we need to disable one profile option value.

Error:
Desktop Integration is not a valid responsibility for the current user. Please contact your System Administrator.


Solution:
By default below Profile Option is either Null or Yes. Just set the below option, then you can access the screen.

Set Profile Option : 

ADI: Use Function Security => No