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

Update OSR in Oracle R12 for HZ_PARTY_SITES table

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


set serveroutput on;
DECLARE
      v_xref_rec                hz_orig_system_ref_pub.orig_sys_reference_rec_type;
      v_object_version_number   NUMBER :=1 ;
      v_return_status           VARCHAR2 (240);
      v_msg_count               NUMBER;
      v_msg_data                VARCHAR2 (4000);
      v_error_message           VARCHAR2 (4000) := NULL;
BEGIN
             v_error_message                        := NULL;
             v_return_status                        := NULL;
             v_msg_count                            := NULL;
             v_msg_data                             := NULL;
           
              v_xref_rec.orig_system           := 'Legacy System';
              v_xref_rec.orig_system_reference := '00138888';  --New OSR
              v_xref_rec.owner_table_name      := 'HZ_PARTY_SITES';
              v_xref_rec.owner_table_id        := '3578'; --Party Site ID
              v_xref_rec.old_orig_system_reference := '00138AA2'; --Old OSR
              v_xref_rec.created_by_module         := 'HZ_IMPORT';
           
               hz_orig_system_ref_pub.update_orig_system_reference (
                              p_init_msg_list            => fnd_api.g_true,
                              p_orig_sys_reference_rec   => v_xref_rec,
                              p_object_version_number    => v_object_version_number,
                              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
                     IF v_msg_count > 1
                     THEN
                            FOR i IN 1 .. v_msg_count
                            LOOP
                                v_error_message      :=    (v_error_message ||fnd_msg_pub.get (p_encoded => fnd_api.g_true));
                            END LOOP;
                     ELSE
                            v_error_message   := v_msg_data;
                     END IF;    
               END IF;

            dbms_output.put_line(v_return_status||' '||'OSR Process failed with error: ' || v_error_message);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

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

Variants of NVL and Branching techniques in SQL

NVL

NVL gives you the flexibility to choose an alternate path if your current value is null.

For Example
Select NVL(dept,'Bench') from employees;

The above query will returns Bench if the dept is null for an employee

NVL2

NVL2 give you extended flexibility to choose your path if the value is null or not null

For Example
Select NVL2(dept,'Existing','New') from employees;

The above query returns Existing if dept is not null and New if the dept is null.

DECODE

Decode can be thought as a mapping/lookup. If the value matches to a code, a corresponding meaning is return.

For Example
Select DECODE(dept,
     'SALES','Department of Sales',
     'MKT','Department of Marketing',
     'MFG','Manufacturing',
     'Bench') from employees;

In the above query if dept is equal to SALES, Department of Sales if returned. If none of them matches, Bench is returned.

COALESCE

COALESCE function returns the first non null operand.

For Example
select COALESCE(NULL,last_name,first_name) from employee;

The above function will check the fisrt operand, Since it is null it will move to second operand.
If last_name is null,  first_name is returned
If last_name is not null, first_name will be returned.

NULLIF

Nullif returns null if two operands are equivalent,If they are not equivalent you can return a custom messge.

For example
select NULLIF(first_name,last_name) from employee;

If first_name is equal to last_name null is returned, else fist_name is returned.
It can be thought of as
select decode(first_name,last_name,NULL,first_name) from employee;

Read More

Install and Clone GIT repository in Windows

There are several clients for GIT available in windows. The most famous ones are

• TortoiseSVN
• GITHUB
• GITExtensions

Below are the steps to install  GITExtensions and clone it from a GIT Server

Download the GIT at  : http://sourceforge.net/projects/gitextensions/

Start with the Installation

Choose Putty as the SSH Client

Choose all the defaults and Install

Read More

wget Command In Unix

Introduction

wget is used in the installation script to download a file from a repository into a folder in a server.

The competitors for wget are curl and lynx. wget, nevertheless,is one of the most extensively used tool.

We will cover few basics around the wget and then present some examples.

Basics

GNU Wget is a free utility for non-interactive download of files from the Web. It supports HTTP, HTTPS, and FTP protocols, as well as retrieval through HTTP proxies.
This chapter is a partial overview of Wget’s features.

• Wget is non-interactive, meaning that it can work in the background, while the user is not logged on. This allows you to start a retrieval and disconnect from the system, letting Wget finish the work. By contrast, most of the Web browsers require constant user’s presence, which can be a great hindrance when transferring a lot of data.
• Wget can follow links in HTML, XHTML, and CSS pages, to create local versions of remote web sites, fully recreating the directory structure of the original site. This is sometimes referred to as “recursive downloading.” While doing that, Wget respects the Robot Exclusion Standard (/robots.txt). Wget can be instructed to convert the links in downloaded files to point at the local files, for offline viewing.
• File name wildcard matching and recursive mirroring of directories are available when retrieving via FTP. Wget can read the time-stamp information given by both HTTP and FTP servers, and store it locally. Thus Wget can see if the remote file has changed since last retrieval, and automatically retrieve the new version if it has. This makes Wget suitable for mirroring of FTP sites, as well as home pages.
• Wget has been designed for robustness over slow or unstable network connections; if a download fails due to a network problem, it will keep retrying until the whole file has been retrieved. If the server supports regetting, it will instruct the server to continue the download from where it left off.
• Wget supports proxy servers, which can lighten the network load, speed up retrieval and provide access behind firewalls. Wget uses the passive FTP downloading by default, active FTP being an option.
• Wget supports IP version 6, the next generation of IP. IPv6 is autodetected at compile-time, and can be disabled at either build or run time. Binaries built with IPv6 support work well in both IPv4-only and dual family environments.
• Built-in features offer mechanisms to tune which links you wish to follow (see Following Links).
• The progress of individual downloads is traced using a progress gauge. Interactive downloads are tracked using a “thermometer”-style gauge, whereas non-interactive ones are traced with dots, each dot representing a fixed amount of data received (1KB by default). Either gauge can be customized to your preferences.
• Most of the features are fully configurable, either through command line options, or via the initialization file .wgetrc (see Startup File). Wget allows you to define global startup files (/usr/local/etc/wgetrc by default) for site settings. You can also specify the location of a startup file with the –config option.
• Finally, GNU Wget is free software. This means that everyone may use it, redistribute it and/or modify it under the terms of the GNU General Public License, as published by the Free Software Foundation (see the file COPYING that came with GNU Wget, for details).

Examples

To download the entire folder recursively 
wget -r --no-parent http://mysite.com/configs/.vim/

Edit: To avoid downloading the index.html files, use this command:
wget -r --no-parent --reject "index.html*" http://mysite.com/configs/.vim/

Download a file
wget −c http://mysite.com/configs/.vim/readme.txt

Read More

Query to find rejection Messages during AP Interface : Oracle R12

SELECT air.*
FROM ap_interface_rejections air,
     ap_invoice_lines_interface aili,
     ap_invoices_interface aii
WHERE aii.invoice_id     = aili.invoice_id
AND aili.invoice_line_id = air.parent_id
AND aii.invoice_num      = &Invoice_Num;

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

RECORDS in PL/SQL

Introduction

Record is a data-structure which can hold differnt type of datatypes. Record can be imagined as a database table with multiple columns

We have the following types of record in PL/SQL
1) Cursor Based Records
2) Table Based Records
3) User Defined Records

Example of Cursor Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   CURSOR cur_employee is
      SELECT name, eno, dept
      FROM employees
   employee_rec cur_employee%rowtype;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH cur_employee into employee_rec
      EXIT WHEN cur_employee %notfound;
      DBMS_OUTPUT.put_line(employee_rec.name || ' ' || employee_rec.dept);
   END LOOP;
END;
/



Example of Table Based Record
%ROWTYPE is used for Cursor and Table Based Records


DECLARE
   employee_rec employees%rowtype;
BEGIN
   SELECT * into employee_rec
   FROM employees
   WHERE eno = 293215;

   dbms_output.put_line('Employee Name : ' || employee_rec.name);
   dbms_output.put_line('Employee Number : ' || employee_rec.eno);
   dbms_output.put_line('Department : ' || employee_rec.dept );
END;
/


Example of User Defined Record


DECLARE
   type employee is record
      (name varchar(100),
       eno varchar(10),
       dept varchar(100)
    );
   employee_rec employee;

BEGIN
   -- Employee assignment
      employee_rec.name := 'John';
      employee_rec.eno  := '21232';   
      employee_rec.dept    := 'Sales';

   -- Print Employee record
   dbms_output.put_line('Employee Name : '|| employee_rec.name );
   dbms_output.put_line('Employee Number : '|| employee_rec.eno );
   dbms_output.put_line('Employee Department : '|| employee_rec.dept );

END;
/

Read More

Example : How to Generate Code Combination ID's (CCID) from Backend in Oracle R12

DECLARE
  l_code_combination_id       NUMBER;
  l_boolean                   BOOLEAN;
  l_segment_array             APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_structure_number          APPS.FND_ID_FLEX_STRUCTURES.id_flex_num%TYPE;
  l_id_flex_code              APPS.FND_ID_FLEX_STRUCTURES.id_flex_code%TYPE;
BEGIN
  APPS.FND_GLOBAL.APPS_INITIALIZE(USER_ID => FND_PROFILE.VALUE('USER_ID'),
                                  RESP_ID => FND_PROFILE.VALUE('RESP_ID'),
                                  RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'));
                                 
SELECT fifs.id_flex_num ,
       fifs.id_flex_code
  INTO l_structure_number ,
       l_id_flex_code
  FROM APPS.FND_ID_FLEX_STRUCTURES fifs
 WHERE fifs.id_flex_code         = 'GL#'
   AND fifs.id_flex_structure_code = 'XX_ACCOUNTING_FLEXFIELD';                                
 
  l_segment_array(1) := '00000';
  l_segment_array(2) := '10000';
  l_segment_array(3) := '10324';
  l_segment_array(4) := '00000';
  l_segment_array(5) := '00000';
  l_segment_array(6) := '00000';
 
  l_boolean          := APPS.FND_FLEX_EXT.GET_COMBINATION_ID( APPLICATION_SHORT_NAME =>'SQLGL',
                                                              KEY_FLEX_CODE => l_id_flex_code,
                                                              STRUCTURE_NUMBER => l_structure_number,
                                                              VALIDATION_DATE => SYSDATE,
                                                              N_SEGMENTS => 6,
                                                              SEGMENTS => l_segment_array,
                                                              COMBINATION_ID => l_code_combination_id,
                                                              DATA_SET => -1);
 
  x_ccid             := l_code_combination_id;
 
  IF l_code_combination_id IS NULL THEN
    RAISE;
  ELSE
    dbms_output.put_line(l_code_combination_id);
  END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More