Structure of BCF File

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
    <xapi:request select="/XXCOMREPT/LIST_Q_MAIN_HDR/Q_GROUP_BY">
        <xapi:delivery>
        <xapi:email id="123" server="smtp.company.local" port="25" from="donotreply@yourcompanydomain.com"  reply-to ="donotreply@yourcompanydomain.com">
        <xapi:message id="123"
                      to="${EMAIL_TO}"
                      bcc="${EMAIL_BCC}"
                      attachment="true"
                      content-type="text/html"
                      subject="${EMAIL_SUBJ}"><![CDATA[${EMAIL_BODY_1}]]>
        </xapi:message>
        </xapi:email>
        </xapi:delivery>
      <xapi:document output="TYPE1${GROUP_BY_COL}" output-type="pdf" delivery="123" >
        <xapi:template type="rtf" location="xdo://XXSFNT.TEMPLATE_NAME1.en.US/?getSource=true" filter=".//Q_MAIN[TYPE='TYPE1']"></xapi:template>
   </xapi:document>
  <xapi:document output="TYPE2${HEADER_ID}" output-type="pdf" delivery="123" >
        <xapi:template type="rtf" location="xdo://XXSFNT.TEMPLATE_NAME2.en.US/?getSource=true" filter=".//Q_MAIN[TYPE='TYPE2']"></xapi:template>
   </xapi:document>
</xapi:request>
</xapi:requestset>

Logging in reports rdf

   srw.MESSAGE (100, 'XXCOM: ENTER _TEXT');

Order management tables and its details

Order Management Tables. Entered

oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records

oe_price_adjustments When discount gets applied

oe_order_price_attribs If line has price attributes then populated

oe_order_holds_all If any hold applied for order like credit check etc.

Booked

oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release

Pick Released 

wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)

wsh_picking_batches After batch is created for pick release. 

mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction

mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers

mtl_txn_request_lines

wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned

Pick Confirmed

wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock

Ship Confirmed

wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.

oe_order_headers_all If all the lines get shipped then only flag N

Autoinvoice

wsh_delivery_details Released_status=I Need to run workflow background process.

ra_interface_lines_all Data will be populated after wkfw process.

ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated

Price Detailsqp_list_headers_b To Get Item Price Details.

qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes

End User Details 
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits

Attaching Documents 
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Ordersoe_order_lines_all Cancel Order Details.

Courtesy: http://www.oracleerpappsguide.com/2012/01/order-management-tables-and-its-details.html

Calling Remote Procedure in PLSQL

1. Create the database link to the remote server (replace service name with your remote service name):

DROP DATABASE LINK db1;

CREATE DATABASE LINK db 1 CONNECT TO scott IDENTIFIED BY tiger USING '<service name>';

2. Create the remote function

CREATE OR REPLACE FUNCTION f_num(id number) return number is

result number;

BEGIN

SELEC T deptno INTO result FROM emp WHERE empno = id;

RETURN result;

END;

/

3. Create the call to the remote function:

DECLARE

v_num n umber;

BEGIN

v_num := f_num@db1(7900);

END;

/

4. Select from v$dblink to find out if the database link is currently in a transaction

select db_link, in_transaction from v$dblink where db_link like '%DB1%';

5. Issue the rollback or commit to end the transaction. After this you can then recheck v$dblink.

rollback;

6. Close the database link

exec dbms_session.close_database_link('db1');

How to compile a library or report in Oracle E-Business Suite R12.2.x?

How to compile a library or report in Oracle E-Business Suite R12.2.x?

Reference:
http://www.aclnz.com/interests/blogs/how-to-compile-a-library-or-report-in-oracle-e-business-suite-r12-2-x

Compiling Reports

Example

/u01/oracle/VIS/fs1/EBSapps/10.1.2/bin/rwconverter source=/u01/oracle/VIS/fs1/EBSapps/appl/xla/12.0.0/reports/US/XLAALIMP.rdf dest=/u01/oracle/VIS/fs1/EBSapps/appl/admin/VIS_patch/out/tmp002.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes

Using LIMIT with BULK COLLECT

Using LIMIT with BULK COLLECT

Oracle provides a LIMIT clause for BULK COLLECT that allows you to limit the number of rows fetched from the database. The syntax is:

FETCH cursor BULK COLLECT INTO ... [LIMIT rows];

where rows can be any literal, variable, or expression that evaluates to an integer (otherwise, the database will raise a VALUE_ERROR exception).

LIMIT is very useful with BULK COLLECT, 

because it helps you manage how much memory your program will used to process data. 

Suppose, for example, that you need to query and process 10,000 rows of data. 

You could use BULK COLLECT to retrieve all those rows and populate a rather large collection. However, this approach will consume lots of memory in the PGA for that session. If this code is run by many separate Oracle schemas, your application performance may degrade because of PGA swapping.

The following block of code uses the LIMIT clause in a FETCH that is inside a simple loop.

DECLARE

l_limit PLS_INTEGER := 100;

CURSOR allrows_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF allrows_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employees employees_aat;

BEGIN
OPEN allrows_cur;
LOOP
FETCH allrows_cur BULK COLLECT INTO l_employees LIMIT l_limit;

/* Process the data by scanning through the collection. */ FOR l_row IN 1 .. l_employees.COUNT
LOOP
upgrade_employee_status (l_employees(l_row).employee_id);
END LOOP;

EXIT WHEN allrows_cur%NOTFOUND;
END LOOP;

CLOSE allrows_cur;
END;

Notice that I terminate the loop by checking the value of allrows_cur%NOTFOUND at the bottom of the loop. When querying data one row at a time, we usually put this code immediately after the FETCH statement. You should not do that when using BULK COLLECT, because when the fetch retrieves the last set of rows, the cursor will be exhausted (and %NOTFOUND will return TRUE) but you will still have some elements in the collection to process.

So either check the %NOTFOUND attribute at the bottom of your loop, or check the contents of the collection immediately after the fetch:

LOOP
FETCH allrows_cur BULK COLLECT INTO l_employees LIMIT l_limit;
EXIT WHEN l_employees.COUNT = 0;

The disadvantage of this second approach is that you will perform an extra fetch that returns no rows, compared to checking %NOTFOUND at the bottom of the loop body. The performance impact of this extra fetch should, however, be minimal.

Source: https://www.toadworld.com/platforms/oracle/b/weblog/archive/2011/07/13/quicktips-using-bulk-collect-with-queries

VM Issues

I was having below issues

not able to connect to 8000 port

You are running adapcctl.sh version 120.0.12020000.6
Stopping OPMN managed Oracle HTTP Server (OHS) instance ...
adapcctl.sh: exiting with status 2
adapcctl.sh: check the logfile /u01/install/VISION/fs1/inst/apps/EBSDB_ebs/logs/appl/admin/log/adapcctl.txt for more information ...
.end std out.
.end err out.
--------------

07/21/16-01:48:51 :: adapcctl.sh version 120.0.12020000.6
07/21/16-01:48:53 :: adapcctl.sh: stopping OPMN managed OHS instance

opmnctl stopproc: stopping opmn managed processes...
RCV: No such file or directory
Communication error with the OPMN server local port.
Check the OPMN log files
07/21/16-01:49:06 :: adapcctl.sh: exiting with status 2 

----------------------
07/21/16-01:50:47 :: admanagedsrvctl.sh version 120.14.12020000.9 
Validated the passed arguments for the option ebs-get-serverstatus
forms-c4ws_server1 is in RUNNING mode.
Validated the passed arguments for the option ebs-nmstop-managedsrv
Shutting down managed server forms-c4ws_server1
Connecting to t3://ebs.example.com:7801 with userid weblogic ...
The CompatabilityMBeanServer is not initialized properly. 
This might happen if the CompatabilityMBeanServer is 
disabled via the JMXMBean.

To view the root cause exception use dumpStack()
WLST detected that the DomainRuntimeMBeanServer is not enabled 
or not initialized properly. This might happen if the 
DomainRuntimeMBeanServer is disabled via the JMXMBean. Please 
ensure that this MBeanServer is enabled. Online WLST cannot 
function without this MBeanServer.


Cannot connect to t3://ebs.example.com:7801
ERROR: Unable to shutdown the managed server forms-c4ws_server1
07/21/16-02:04:32 :: admanagedsrvctl.sh: exiting with status 0 
================================================================================
07/21/16-02:32:55 :: admanagedsrvctl.sh version 120.14.12020000.9 
Validated the passed arguments for the option ebs-get-serverstatus
forms-c4ws_server1 is in RUNNING mode.
07/21/16-02:33:22 :: admanagedsrvctl.sh: exiting with status 2 
================================================================================
issue with form server
07/21/16-01:50:47 :: admanagedsrvctl.sh version 120.14.12020000.9 
Validated the passed arguments for the option ebs-get-serverstatus
forms-c4ws_server1 is in RUNNING mode.
Validated the passed arguments for the option ebs-nmstop-managedsrv
Shutting down managed server forms-c4ws_server1
Connecting to t3://ebs.example.com:7801 with userid weblogic ...
The CompatabilityMBeanServer is not initialized properly. 
This might happen if the CompatabilityMBeanServer is 
disabled via the JMXMBean.

To view the root cause exception use dumpStack()

WLST detected that the DomainRuntimeMBeanServer is not enabled 
or not initialized properly. This might happen if the 
DomainRuntimeMBeanServer is disabled via the JMXMBean. Please 
ensure that this MBeanServer is enabled. Online WLST cannot 
function without this MBeanServer.


Cannot connect to t3://ebs.example.com:7801
ERROR: Unable to shutdown the managed server forms-c4ws_server1

07/21/16-02:04:32 :: admanagedsrvctl.sh: exiting with status 0 

================================================================================


07/21/16-02:32:55 :: admanagedsrvctl.sh version 120.14.12020000.9 


Validated the passed arguments for the option ebs-get-serverstatus
forms-c4ws_server1 is in RUNNING mode.

07/21/16-02:33:22 :: admanagedsrvctl.sh: exiting with status 2 

================================================================================

Solved by running below:

Stop the application and run autoconfig
cd $ADMIN_SCRIPTS_HOME
sh adstpall.sh apps/apps
sh adautocfg.sh
sh adstrtal.sh apps/apps