Required Profile Options for Form Personalization to work

Following profile options should be set to the user making the Form Personalization:

o ‘FND: Diagnostics’ set to ‘Yes’

o ‘Hide Diagnostics menu entry’ set to ‘No’

o ‘Utilities:Diagnostics’ set to ‘Yes’

Order Management : Calling Release Hold API

SET SERVEROUTPUT ON;

DECLARE

   v_msg_data        VARCHAR2 (4000);

   p_header_id       OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE := 40750464;

   p_line_id         OE_ORDER_LINES_ALL.LINE_ID%TYPE := 48876396;

   l_cnt             NUMBER (10) := 0;

   p_user_id         FND_USER.USER_ID%TYPE := 66982;        --127903; --66982;

   p_resp_id         FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE := 52434; --52434; --50609;  --52434;

   p_resp_appl_id    FND_RESPONSIBILITY.APPLICATION_ID%TYPE := 660;

   p_org_id          oe_order_lines_all.ORG_ID%TYPE := 149;

   p_hold_id         OE_HOLD_DEFINITIONS.hold_id%TYPE := 3026; --:= getHoldId;

   p_error_msg       VARCHAR2 (1000);

   p_status          VARCHAR2 (1);

   l_act_status      BOOLEAN := FALSE;

   l_order_tbl       OE_HOLDS_PVT.order_tbl_type;

   l_return_status   VARCHAR2 (100);

   l_msg_data        VARCHAR2 (4000);

   l_msg_count       NUMBER;

   X_DEBUG_FILE      VARCHAR2 (1000);

   l_msg_index_out   NUMBER (10);

BEGIN

   DBMS_OUTPUT.enable (10000);

   /* Setting the org context for the particular session */

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-1' || MO_GLOBAL.get_current_org_id);

   fnd_global.apps_initialize (user_id        => p_user_id,

                               resp_id        => p_resp_id,

                               resp_appl_id   => p_resp_appl_id);

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-2' || MO_GLOBAL.get_current_org_id);

   mo_global.init ('ONT');

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-3' || MO_GLOBAL.get_current_org_id);

   mo_global.set_policy_context ('S', p_org_id);

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-4' || MO_GLOBAL.get_current_org_id);

   --oe_debug_pub.setdebuglevel (5);

   oe_msg_pub.initialize;

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-5' || MO_GLOBAL.get_current_org_id);

   l_order_tbl (1).header_id := p_header_id;

   l_order_tbl (1).line_id := p_line_id;

   OE_Holds_PUB.Release_Holds (

      p_api_version           => 1.0,

      p_order_tbl             => l_order_tbl,

      p_hold_id               => p_hold_id,

      p_release_reason_code   => 'VALID_REASON_CODE',

      p_release_comment       => 'Released by anonymous block',

      x_return_status         => l_return_status,

      x_msg_count             => l_msg_count,

      x_msg_data              => l_msg_data);

   -- Check the return status

   IF l_return_status = FND_API.G_RET_STS_SUCCESS

   THEN

      p_status := 'S';

      COMMIT;

      DBMS_OUTPUT.PUT_LINE ('successfully completed');

   ELSE

      DBMS_OUTPUT.PUT_LINE ('failure:' || l_msg_count);

      DBMS_OUTPUT.PUT_LINE ('failure:' || l_return_status);

      IF (fnd_msg_pub.count_msg > 0)

      THEN

         FOR i IN 1 .. fnd_msg_pub.count_msg

         LOOP

            fnd_msg_pub.get (p_msg_index       => i,

                             p_encoded         => 'F',

                             p_data            => L_msg_data,

                             p_msg_index_out   => L_msg_count);

            DBMS_OUTPUT.PUT_LINE ('API ERROR: ' || L_msg_data);

         END LOOP;

         DBMS_OUTPUT.put_line (                             --   x_jtf_note_id

                               L_msg_count || '--' || L_msg_data);

      END IF;

      ROLLBACK;

   END IF;

--DBMS_OUTPUT.PUT_LINE ('err1:' || l_msg_data);

EXCEPTION

   WHEN OTHERS

   THEN

      --p_error_msg:=SUBSTR (SQLERRM, 1, 300);

      DBMS_OUTPUT.PUT_LINE ('err2:' || SQLERRM);

END;

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