Query to find Credit Card Type in EBS R12

To Check the Valid Credit Card Type in R12. Execute the query below.


SELECT lookup_code,
      meaning,
      enabled_flag,
      start_date_active,
      end_date_active
FROM oe_lookups
WHERE lookup_type = 'CREDIT_CARD';

Read More

Example : get_service_duration API in Oracle R12 Order Management

--It is always a good practice to initialize apps and set global policy context

PROCEDURE get_service_duration (
   p_serv_period          IN              VARCHAR2,
   p_end_date             IN              DATE,
   p_start_date           IN              DATE,
   p_serv_duration        IN              NUMBER,
   p_out_serv_duration    OUT NOCOPY      NUMBER,
   p_out_service_period   OUT NOCOPY      VARCHAR2,
   p_out_end_date         OUT NOCOPY      VARCHAR2,
   p_out_dur_return_sts   OUT NOCOPY      VARCHAR2
)
IS
   v_ou_dur_return_sts   VARCHAR2 (1)            := fnd_api.g_ret_sts_success;
   v_dur_line_rec        oe_oe_form_line.line_rec_type;
   v_dur_null_line_rec   oe_oe_form_line.line_rec_type;
   v_dur_line_rec        oe_oe_form_line.line_rec_type;
BEGIN
   v_dur_line_rec := v_dur_null_line_rec;
   v_dur_line_rec.service_duration := p_serv_duration;
   v_dur_line_rec.service_start_date := p_start_date;
   v_dur_line_rec.service_period := p_serv_period;
   v_dur_line_rec.service_end_date := p_end_date;
   oe_service_util.get_service_duration (x_return_status      => v_dur_return_sts,
                                         p_line_rec           => v_dur_line_rec,
                                         x_line_rec           => v_dur_line_rec
                                        );
   p_out_service_period := v_dur_line_rec.service_period;
   p_out_serv_duration := v_dur_line_rec.service_duration;
   p_out_dur_return_sts := v_dur_return_sts;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END get_service_duration;

Read More

API to Reprice Order Total in Oracle R12.

 -- Make Sure you initialize apps and set mo_global.set_policy_context   


PROCEDURE reprice_order (p_in_header_id IN NUMBER)
   IS
      v_header_count    NUMBER;
      v_header_list     VARCHAR2 (32000);
      v_line_count      NUMBER;
      v_line_list       VARCHAR2 (32000);
      v_price_level     VARCHAR2 (32000);
      v_return_status   VARCHAR2 (32000);
      v_msg_count       NUMBER;
      v_msg_data        VARCHAR2 (32000);
   BEGIN

      v_header_count := 1;
      v_header_list := TO_CHAR (p_in_header_id);
      v_price_level := 'ORDER';

      BEGIN
         oe_order_adj_pvt.price_action (p_header_count       => v_header_count,
                                        p_header_list        => v_header_list,
                                        p_line_count         => v_line_count,
                                        p_line_list          => v_line_list,
                                        p_price_level        => v_price_level,
                                        x_return_status      => v_return_status,
                                        x_msg_count          => v_msg_count,
                                        x_msg_data           => v_msg_data
                                       );
         COMMIT;

         IF (v_return_status != fnd_api.g_ret_sts_success)
         THEN
            dbms_output.put_line(v_msg_data);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            dbms_output.put_line(sqlerrm);
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
        dbms_output.put_line(sqlerrm);
   END reprice_order;

Read More

Creating a Price Adjustment to an Sales Order in Oracle R12

------------------------------------------------------------------------------------------------
--Query to Fetch ID's


select * from fnd_user where user_name like 'ANU%'; --16705
select * from fnd_responsibility_vl where responsibility_name=' XX OM ORDER ENTRY'; --51546  
select * from fnd_application_vl where application_name like '%Order%'; --660
select header_id from oe_order_headers_all;
select line_id from oe_order_lines_all;
select list_header_id from qp_list_headers_tl;
select list_line_id from qp_list_lines;

------------------------------------------------------------------------------------------------
--Call to MAIN API

SET serveroutput on;

DECLARE
   v_api_version_number          NUMBER                                   := 1.0;
   v_init_msg_list               VARCHAR2 (10)                            := fnd_api.g_false;
   v_return_values               VARCHAR2 (10)                            := fnd_api.g_false;
   v_action_commit               VARCHAR2 (10)                            := fnd_api.g_false;
   x_ou_header_val_rec           oe_order_pub.header_val_rec_type;
   x_ou_header_adj_tbl           oe_order_pub.header_adj_tbl_type;
   x_ou_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;
   x_ou_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;
   x_ou_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;
   x_ou_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;
   x_ou_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;
   x_ou_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;
   x_ou_line_val_tbl             oe_order_pub.line_val_tbl_type;
   x_ou_line_adj_tbl             oe_order_pub.line_adj_tbl_type;
   x_ou_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;
   x_ou_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;
   x_ou_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;
   x_ou_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;
   x_ou_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;
   x_ou_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;
   x_ou_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;
   x_ou_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;
   x_ou_action_request_tbl       oe_order_pub.request_tbl_type;
   x_debug_file                  VARCHAR2 (100);
   v_return_status               VARCHAR2 (1);
   v_msg_count                   NUMBER;
   v_msg_data                    VARCHAR2 (100);
   v_line_tbl_index              NUMBER;
   v_header_rec                  oe_order_pub.header_rec_type;
   v_line_tbl                    oe_order_pub.line_tbl_type;
   v_line_adj_tbl                oe_order_pub.line_adj_tbl_type;
   v_action_request_tbl          oe_order_pub.request_tbl_type;
   v_msg_index_out               NUMBER (10);
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
   mo_global.set_policy_context ('S', 103);
   fnd_global.apps_initialize (16705
                              ,21623
                              ,660
                              );   -- pass in user_id, responsibility_id, and application_id
   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
--   x_debug_file := oe_debug_pub.set_debug_mode ('FILE');
  -- oe_debug_pub.setdebuglevel (5);   -- Use 5 for the most debugging output, I warn  you its a lot of data
   DBMS_OUTPUT.put_line ('START OF NEW DEBUG');
--This is to UPDATE order line
   v_line_tbl_index := 1;
-- Changed attributes
   v_header_rec := oe_order_pub.g_miss_header_rec;
   v_header_rec.header_id := 728232;
   v_header_rec.operation := oe_globals.g_opr_update;
 
   v_line_tbl (v_line_tbl_index) := oe_order_pub.g_miss_line_rec;
   v_line_tbl (v_line_tbl_index).header_id := 728232;
   v_line_tbl (v_line_tbl_index).line_id := 1813117;
   v_line_tbl (v_line_tbl_index).operation := oe_globals.g_opr_update;
 
   v_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
   v_line_adj_tbl (1).operation := oe_globals.g_opr_create;
   v_line_adj_tbl (1).header_id := 728232;   --header_id of the sales order
   v_line_adj_tbl (1).line_id := 1813117;   --line_id of the sales order line
--v_Line_Adj_Tbl(1).price_adjustment_id := 3419268;
--price_adjustment_id is required for update/delete
   v_line_adj_tbl (1).line_index := 1;
   v_line_adj_tbl (1).automatic_flag := 'N';
   v_line_adj_tbl (1).applied_flag := 'Y';
   v_line_adj_tbl (1).updated_flag := 'Y';   --Optional, this is the fixed flag.
   v_line_adj_tbl (1).list_header_id := 330069;   --list_header_id of the adjustment
   v_line_adj_tbl (1).list_line_id := 322476;   --list_line_id of the adjustment
   v_line_adj_tbl (1).list_line_type_code := 'DIS';
   v_line_adj_tbl (1).adjusted_amount := -500;
   v_line_adj_tbl (1).operand := 0;

   -- CALL TO PROCESS ORDER
   oe_order_pub.process_order (p_api_version_number =>          1.0
                              ,p_init_msg_list =>               fnd_api.g_false
                              ,p_return_values =>               fnd_api.g_false
                              ,p_action_commit =>               fnd_api.g_false
                              ,x_return_status =>               v_return_status
                              ,x_msg_count =>                   v_msg_count
                              ,x_msg_data =>                    v_msg_data
                              ,p_header_rec =>                  v_header_rec
                              ,p_line_tbl =>                    v_line_tbl
                              ,p_line_adj_tbl =>                v_line_adj_tbl
                              ,p_action_request_tbl =>          v_action_request_tbl
-- OUT PARAMETERS
   ,                           x_header_rec =>                  v_header_rec
                              ,x_header_val_rec =>              x_ou_header_val_rec
                              ,x_header_adj_tbl =>              x_ou_header_adj_tbl
                              ,x_header_adj_val_tbl =>          x_ou_header_adj_val_tbl
                              ,x_header_price_att_tbl =>        x_ou_header_price_att_tbl
                              ,x_header_adj_att_tbl =>          x_ou_header_adj_att_tbl
                              ,x_header_adj_assoc_tbl =>        x_ou_header_adj_assoc_tbl
                              ,x_header_scredit_tbl =>          x_ou_header_scredit_tbl
                              ,x_header_scredit_val_tbl =>      x_ou_header_scredit_val_tbl
                              ,x_line_tbl =>                    v_line_tbl
                              ,x_line_val_tbl =>                x_ou_line_val_tbl
                              ,x_line_adj_tbl =>                x_ou_line_adj_tbl
                              ,x_line_adj_val_tbl =>            x_ou_line_adj_val_tbl
                              ,x_line_price_att_tbl =>          x_ou_line_price_att_tbl
                              ,x_line_adj_att_tbl =>            x_ou_line_adj_att_tbl
                              ,x_line_adj_assoc_tbl =>          x_ou_line_adj_assoc_tbl
                              ,x_line_scredit_tbl =>            x_ou_line_scredit_tbl
                              ,x_line_scredit_val_tbl =>        x_ou_line_scredit_val_tbl
                              ,x_lot_serial_tbl =>              x_ou_lot_serial_tbl
                              ,x_lot_serial_val_tbl =>          x_ou_lot_serial_val_tbl
                              ,x_action_request_tbl =>          v_action_request_tbl
                              );
   COMMIT;
   DBMS_OUTPUT.put_line (   v_msg_count
                         || 'OM Debug file: '
                         || oe_debug_pub.g_dir
                         || '/'
                         || oe_debug_pub.g_file);
   DBMS_OUTPUT.put_line (v_header_rec.header_id);

   --DBMS_OUTPUT.put_line ('adjustment.return_status IS: ' || x_line_adj_tbl(1).return_status);

   -- Retrieve messages
   FOR i IN 1 .. v_msg_count
   LOOP
      oe_msg_pub.get (p_msg_index =>          i
                     ,p_encoded =>            fnd_api.g_false
                     ,p_data =>               v_msg_data
                     ,p_msg_index_out =>      v_msg_index_out
                     );
      DBMS_OUTPUT.put_line ('message is: ' || v_msg_data);
      DBMS_OUTPUT.put_line ('message index is: ' || v_msg_index_out);
   END LOOP;

-- Check the return status
   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('Line Quantity Update Successful');
   ELSE
      DBMS_OUTPUT.put_line ('Line Quantity update Failed');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Read More

Create a Salesrep in Oracle R12 from API

set serveroutput on;            
DECLARE
v_api_version            varchar2(100) := '1.0';
v_sales_credit_type_id   number        := 1;
v_salesrep_num             number        := '10129';
v_resource_id             varchar2(100) := '1006433';
v_return_status             varchar2(100);
v_msg_count                 varchar2(100);
v_msg_data                 varchar2(100);
v_salesrep_id            number;
BEGIN
    mo_global.set_policy_context('S',80);    --Always set if running from backend otherwise Salesrep wont be created
   
    jtf_rs_salesreps_pub.create_salesrep       (p_api_version            =>     v_api_version
                                              ,p_resource_id           =>      v_resource_id
                                              ,p_sales_credit_type_id  =>      v_sales_credit_type_id
                                              ,p_salesrep_number       =>      v_salesrep_num
                                              ,x_return_status         =>      v_return_status
                                              ,x_msg_count             =>      v_msg_count
                                              ,x_msg_data              =>      v_msg_data
                                              ,x_salesrep_id           =>      v_salesrep_id
                                              );
                                             
    dbms_output.put_line('Salesrep ID : '||v_salesrep_id);                                         
    dbms_output.put_line(SubStr('x_return_status = '||v_return_status,1,255));
    dbms_output.put_line('x_msg_count = '||TO_CHAR(v_msg_count));
    dbms_output.put_line(SubStr('x_msg_data = '||v_msg_data,1,255));
   
    IF v_msg_count >1 THEN
    FOR I IN 1..v_msg_count
    LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
    END LOOP;
    END IF;                             
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More