How to compile Oracle forms in R12

1) Login to application server.
2) Navigate to the path $AU_TOP/forms/US
3) transfer the .fmb file in binary mode
4) run the below command, .fmx file will be generated if there are no errors.
frmcmp_batch userid=apps/$PASSWORD module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=yes
5) Transfer the .fmx file to the respective directory.

Update Sales order line details using OE_ORDER_PUB.PROCESS_ORDER API

In the below shown example I am updating the request_date to sysdate, also I am considering only the first line of the sales Order.

Logic can be modified accordingly for updating any number of lines in the sales Order

v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;

BEGIN
-- Line Record --
v_line_tbl(1)                           := OE_ORDER_PUB.G_MISS_LINE_REC;--oe_order_pub.G_MISS_LINE_TBL;
v_line_tbl(1).last_update_date       := sysdate;
v_line_tbl(1).last_updated_by        := FND_GLOBAL.USER_ID;
v_line_tbl(1).last_update_login      := FND_GLOBAL.LOGIN_ID;
v_line_tbl(1).header_id              := l_header_id; --fetch the value
v_line_tbl(1).line_id                := l_line_id; --fetch the value
v_line_tbl(1).operation              := oe_globals.G_OPR_UPDATE;
v_line_tbl(1).change_reason          := 'MANUAL';
v_line_tbl(1).request_date           := sysdate;


OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

END;

How to Populate charges(Header level) field in Sales Order form.

Here the user wants a menu, once he clicks on the menu then charges field should be populated.

Below things need to developed for the requirement.
1) setup for discounts at header level.
2) Database procedure for calling the API to populate the charges in OE_PRICE_ADJUSTMENTS table.
3) Personalization for creating menu, call the procedure by using the menu.

Solution:
1) Login to Order Management Super user, and navigate to Modifiers.
Pricing--> Modifiers
 Setup the Modifier Type,Name and Number in header section, and in the line I have provided level as Order since my requirement is to populate at header

level.

Once setup is done the values can be queried in the tables QPFV_MODIFIER_HEADERS and QPFV_MODIFIER_LINES.


2) Create the database procedure to call the API OE_ORDER_PUB.PROCESS_ORDER for populating the charges.
In my requirement Sales Order is already present, which was created by user manually. Now I need to update the charges, in the manual process for populating

the charges user has to click on the Actions Button and then enter the charge Name, Amount and rest details, and there by the charges field will be populated

with the value.

In the automated process, now I am populated the charges lines by using the process_order API.

For this procedure, provide the Order Number as the parameter.

create or replace procedure XXAJ_CREATESALESORDER(p_so_num VARCHAR2) IS
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);

-- IN Variables --
v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
v_hdr_adj_tbl               oe_order_pub.Header_Adj_Tbl_Type;

   v_list_header_id number;
  v_list_line_id   number;
  v_operand        varchar2(10); 
  v_ln_type_code   varchar2(10);
  v_operator       varchar2(10);
  v_phase_id       number;
  v_mod_level_code varchar2(10);
  t_line_id number;
  t_header_id number;
  t_org_id number;

  l_pricing_quantity NUMBER;
  l_unit_selling_price NUMBER;
  l_attribute1 NUMBER;
  l_header_id NUMBER;
  l_unit_price NUMBER;
  l_operand NUMBER;


-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;

v_msg_index                    NUMBER;
v_data                         VARCHAR2 (2000);
v_loop_count                   NUMBER;
v_debug_file                   VARCHAR2 (200);
b_return_status                VARCHAR2 (200);
b_msg_count                    NUMBER;
b_msg_data                     VARCHAR2 (2000);

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');


SELECT hdr.list_header_id,
             lin.list_line_id
    into v_list_header_id,
             v_list_line_id
             FROM QPFV_MODIFIER_HEADERS hdr,
             QPFV_MODIFIER_LINES lin
     WHERE 1=1
        and lin.list_header_id = hdr.list_header_id
        AND lin.list_line_type_code ='FREIGHT_CHARGE'
        AND lin.modifier_level_code = 'ORDER'
        AND hdr.name = 'Transport Charge -3';

select l.pricing_quantity,l.unit_selling_price,l.attribute1,h.header_id
INTO   l_pricing_quantity ,
  l_unit_selling_price ,
  l_attribute1 ,
  l_header_id
from OE_ORDER_HEADERS_ALL h,OE_ORDER_lines_ALL l
where l.header_id  = h.header_id 
AND h.order_number = p_so_num;

select l.unit_price INTO  l_unit_price from po_headers_all h, po_lines_all l
where segment1 = l_attribute1
AND h.po_header_id = l.po_header_id;

-- Adjustment Variables
v_hdr_adj_tbl                      :=oe_order_pub.G_MISS_HEADER_ADJ_TBL;
v_hdr_adj_tbl(1).header_id            := l_header_id;--3778;
v_hdr_adj_tbl(1).automatic_flag       := 'N';
v_hdr_adj_tbl(1).list_line_type_code  := 'FREIGHT_CHARGE';
v_hdr_adj_tbl(1).change_reason_code   := 'MANUAL';
v_hdr_adj_tbl(1).operand              := 5;  -- The value which appears in charges field at header level
v_hdr_adj_tbl(1).charge_type_code     := 'FREIGHT';
v_hdr_adj_tbl(1).change_reason_text     := 'Manually applied adjustments';
v_hdr_adj_tbl(1).modifier_level_code     := 'ORDER';
v_hdr_adj_tbl(1).updated_flag     := 'Y';
v_hdr_adj_tbl(1).applied_flag      := 'Y';
v_hdr_adj_tbl(1).arithmetic_operator     := 'LUMPSUMP';
v_hdr_adj_tbl(1).adjusted_amount     := '5';
v_hdr_adj_tbl(1).price_adjustment_id     := oe_price_adjustments_s.nextval;
v_hdr_adj_tbl(1).creation_date     := sysdate;
v_hdr_adj_tbl(1).created_by     := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_date     := sysdate;
v_hdr_adj_tbl(1).last_updated_by      := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_login      := FND_GLOBAL.LOGIN_ID;
v_hdr_adj_tbl(1).list_header_id      := v_list_header_id;
v_hdr_adj_tbl(1).list_line_id      := v_list_line_id;
v_hdr_adj_tbl(1).arithmetic_operator      := 'LUMPSUM';
v_hdr_adj_tbl(1).orig_sys_discount_ref     := 'OE_PRICE_ADJUSTMENTS'||v_hdr_adj_tbl(1).price_adjustment_id;

v_hdr_adj_tbl(1).operation     := oe_globals.g_opr_create;

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;


DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to update the header details of an existing Order --

OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_Header_Adj_tbl        => v_hdr_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

DBMS_OUTPUT.PUT_LINE('Completion of API');


IF v_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Order Header Updation Success : '||v_header_rec_out.header_id);
ELSE
    DBMS_OUTPUT.put_line ('Order Header Updation failed:'||v_msg_data);
    ROLLBACK;
    FOR i IN 1 .. v_msg_count
    LOOP
      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
    END LOOP;
END IF;
END xxaj_createsalesorder;


3) Now the final part is to call the database procedure from the forms personalization, here I have two steps.
i) Create a new Menu
When new form instance, go to actions and provide the Menu label for any Special Trigger.
ii) Now In condition the trigger event will be special event, go to actions click on built in , builtin type is execute procedure. Call the procedure with

the  Order Number as parameter.
Also add another action execute_query to refersh the form, so that charges will appear immediately.

Now the requirement is accomplished.

Let me know your comments... this has worked perfectly for me.

Why we cannot use commit in a trigger.

1) Trigger is a part of larger transaction, mean it is called by a parent transaction.
2) For instance we used a commit in trigger (child transaction), and processed a transaction,but parent transaction got into error for some reason still the data will be commited in child because of the commit statement.
3) This may mislead the expectation, So it is not suggested to use the commit in triggers because of the parent and child transaction relationship.
4) But if you are OK with the result then by using AUTONOMOUS_TRANSATION, commit can be used.
5) The usage may hurt if properly not used.

How to compile CUSTOM.pll in R12, below is the command.

How to compile CUSTOM.pll in R12, below is the command.
---------------------------------------------------------
frmcmp_batch module=CUSTOM.pll userid=apps/<pwd> output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Path for CUSTOM.pll:
-------------------------
$AU_TOP/resource

Background
----------------
The CUSTOM library allows extension of Oracle E-Business Suite without modification of Oracle E-Business Suite code.

Comparison between CUSTOM.pll and Form personalization s
-----------------------------------------------------------
http://pboracletechnical.blogspot.com/2013/03/forms-personalization-and-custompll.html


For more information on CUSTOM.pll check the below link.
http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T458265.htm

How to change Item price in Sales Order form by some formula in Sales Order Form

Here we will learn how to change the price of the item by formula.The formula here would be some calculation based on the Client requirement.
Like adding some tax to the actual Item price.

For instance if Item A price is 10$, the requirement is like make it 12$ by adding the Tax.

This can be accomplished by doing the following steps.

1) There is a package QP_CUSTOM, which is a Oracle standard package, modify it and add the formula there for calculating the New price.
2) return the price as the parameter.
3) Navigate to Order Management Super User and create a new Pricing Formula and the Component Should be "Get Custom Price"
4) Create a new price list and attach the New Formula to the Price List, also check the Item where the price list need to be affected.

Forms Personalization and CUSTOM.pll introduction

Some Introduction on Forms personalization and CUSTOM.pll

Forms personalization: is a new feature provided by Oracle starting from 11.5.10 version. This is a user interface for altering/customizing the oracle apps behavior.
Below is the navigation for Forms Personalization.

Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.

The personalization form should be used to implement the custom rules on a specific form. The
specific form refers to the desired form on which you want to apply the custom business logic or
modify the form behavior.

CUSTOM.pll: is a pl/sql library which is present in UNIX Box in $AU_TOP/resource. It is a single library which is used for altering/Customizing the Oracle apps behavior.

Below tasks can be performed by both Forms personalization and CUSTOM.pll

1) Disabling the Button/field or any item on the form
2) Make the fields/DFF read only.
3) Pop some warning/error messages/ interactive messages.
4) populate other fields based on the other input field.
5) create some LOV on fly
6) Change the navigation/block in the form.


Advantages of Forms personalization

i) Forms personalization can be applied on any form easily with simple navigation from the form.
Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.
ii) Multiple users can work on Forms personalization
iii) Can be easily migrated from instance to instance by using FNDLOAD
iv) The forms personalization rules are stored in the FND tables

Advantages of CUSTOM.pll
a) Not all triggers can be performed by Forms personalization, like using 'WHEN-NEW-ITEM-INSTANCE'
b) User interactive messages and actions can be added.

How to get Request Id for the program run

Below SQL gives the Concurrent Request information,  parameter is concurrent program name.

select fcr.request_id request_id,
fcp.user_concurrent_program_name,substr(resp.responsibility_name ,1,50) responsibility_name,
to_char(fcr.ACTUAL_START_DATE, 'DD-MON-YYYY HH24:MI:SS') Start_date,
to_char(fcr.ACTUAL_COMPLETION_DATE, 'DD-MON-YYYY HH24:MI:SS') completion_date,
round(((fcr.ACTUAL_COMPLETION_DATE-fcr.ACTUAL_START_DATE)*24*60),2) Time_taken,
substr(users.description,1,30) user_description,
substr(fcp.user_concurrent_program_name,1,55) program_name,
fcr.phase_code,fcr.status_code,users.user_name,fcr.argument_text
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_user users,
apps.fnd_responsibility_tl resp
where 1 = 1
and fcr.responsibility_id = resp.responsibility_id
and fcr.requested_by = users.user_id
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.request_date > (sysdate - 200)
and fcp.user_concurrent_program_name like &User_Conc_prog_name
order by fcr.request_date desc ;

How to get object definition from database

Below statement gets the database object definition from database.

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','SCHEMA') from dual;

Object_type,Object_name and Schema should be in Caps.

How to set Org Context in R12

Set org context in R12


The SQL command to set the ORG_ID prior to running a script is:


SQL> exec mo_global.init('AR');
exec mo_global.set_policy_context('S','&org_id');
Enter the org_id when prompted.


The procedure - mo_global.set_policy_context has two parameters
p_access_mode & p_org_id


p_access_mode          Description
S In case you want your current session to work against Single ORG_ID
M In case you want your current session to work against multiple ORG_IDs


p_org_id: Only applicable if p_access_mode is passed value of "S"


If using Toad
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;


Set org context in 11i:


The SQL command to set the ORG_ID prior to running a script is:
SQL> execute dbms_application_info.set_client_info(&org_id);
Enter the org_id when prompted.
If using Toad
Begin
fnd_client_info.set_org_context(&org_id);
End;