Monday, February 23, 2015

BOM Explosion API - Working Sample Code

Dear All

Maybe you would come across some scenario to require BOM explosion so just sharing a working code to call the seeded API which can do the same

The API will populate a global temporary table BOM_EXPLOSION_TEMP when you are done with the API call and then you can use this data to display the exploded BOM any way you wish

DECLARE
lv_org_id NUMBER := <Inventory Org ID>;
lv_item_id NUMBER := <Item ID>;
lv_group_id NUMBER;
lv_errbuf VARCHAR2(2000);
lv_retcode NUMBER;
lv_quantity NUMBER := 1; --assumes to explode for a single unit of assembly item
BEGIN
  Bompexpl.exploder_userexit
  (
     levels_to_explode    => 20
     ,org_id              => lv_org_id
     ,grp_id              => lv_group_id
     ,item_id             => lv_item_id
     ,rev_date            => NULL
     ,expl_qty            => lv_quantity
     ,impl_flag           => 2
     ,explode_option      => 2
     ,MODULE              => 2
     ,std_comp_flag       => 2
     ,err_msg             => lv_errbuf
     ,error_code          => lv_retcode
  ) ;

END;

Hope this helps

Cheers
A

Tuesday, February 10, 2015

SFTP Batch Mode - Providing Password using EXPECT command

Dear All

Many may have come across requirement to connect to a remote server and SFTP a file from there to your Oracle server for further processing

We can do this in 2 ways as below

-----------------------------------------------------------------------------

Traditional Approach : Using Private-Public Key Pair setups

Steps to perform on Oracle server
1) Login to the app tier of the Oracle box as the applmgr (or user which is used to run HOST programs) user
2) In the home directory of the applmgr user, check to see if there is a directory .ssh (you need to use ls -al to see hidden directories)
3) If not, then create one and grant 755 permission to it
4) Execute the command ssh-keygen -t rsa
5) This will ask you a series of inputs, so just keep hitting enter until back to the prompt (do not provide any passphrase etc.)
6) In the .ssh directory you will now see 2 files id_rsa (your private key) and the id_rsa.pub (your public key)

Steps to perform on Remote server
1) Provide the id_rsa.pub file which was generated above to the remote server admin
2) Ask them to login using the user which will be used for actually performing the SFTP operation
3) Go to the home directory and check to see if a .ssh directory exists
4) If not, create it with 755 permissions
5) Inside the .ssh directory create a file called authorized_keys and provide it with 700 permissions
6) Copy the contents of the id_rsa.pub into the file (if already existing then append to it)

Now, back on the Oracle server try to do sftp username@hostname ... do this from the applmgr user and it should not prompt you for a password and hence batch SFTP connection is established

-----------------------------------------------------------------------------

Alternate Approach: Using EXPECT command to provide password hence not needing key files

The below command can be used as a base to write your script. The text in blue are all parameters for the user name, server, password etc

expect<<EOD
spawn /usr/bin/sftp $SFTP_USERNAME@$REMOTE_SERVER
expect "Enter password:"
send "$SFTP_PASSWORD\r"
expect "sftp>"
send "cd /out\r"
expect "sftp>"
send "get $FILE_NAME_PATTERN\r"
expect "sftp>"
send "quit\r"
EOD

In this approach you need not copy the public key to the remote server to enable batch mode SFTP

-----------------------------------------------------------------------------

Hope this helps you all sometime

Cheers
A

Monday, February 9, 2015

GL Journal WebADI Import - Custom Validations

Dear Readers

Many of you may be aware that Oracle provide a means to load GL journal entries from WebADI sheet via a seeded functionality

This process has several seeded validations on Source, Category, Period etc. But now, let us suppose that you wanted to add some custom validation in there to prevent loading certain records if the custom logic is not met

Let us just say for the case of this example, that we want to prevent user from loading records with DR amount as 100 ... sounds funny but this can be modified as per your requirement I am just taking this as an example

For this, there is a seeded package gl_import_hook_pkg which provides means to add custom logic

In this package in the FUNCTION pre_module_hook add the code as below

  FUNCTION pre_module_hook(sob_id    IN     NUMBER,
         run_id    IN     NUMBER,
         errbuf    IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
  BEGIN
    -- AX section
    IF ax_setup_pkg.ax_installed THEN
      IF NOT ax_ezl_filter_pkg.EZLFilter(sob_id, run_id, errbuf) THEN
        RETURN FALSE;
      END IF;
    END IF;

    -- Please put your function call here.  Make it the following format:
    --    IF (NOT dummy(sob_id, run_id, errbuf)) THEN
    --      RETURN(FALSE);
    --    END IF;

        IF (NOT anand_test_hook_prc(sob_id, run_id, errbuf)) THEN
          RETURN(FALSE);
        END IF;

    RETURN(TRUE);
  END pre_module_hook;


The code for my custom function is as below

CREATE OR REPLACE FUNCTION anand_test_hook_prc
(
sob_id NUMBER, run_id NUMBER, errbuf IN OUT NOCOPY VARCHAR2
)
RETURN BOOLEAN
AS
   lv_count NUMBER;
   lv_group_id NUMBER;
   lv_je_source VARCHAR2(240);
BEGIN
   INSERT INTO anand_test_hook_dbg VALUES('inside hook process run id value is ' || run_id);
   SELECT   COUNT(*)
   INTO     lv_count
   FROM     gl_interface_control
   WHERE    interface_run_id = run_id;
   INSERT INTO anand_test_hook_dbg VALUES('count in control is ' || lv_count);

   IF lv_count = 1 THEN
      SELECT   group_id, je_source_name
      INTO     lv_group_id, lv_je_source
      FROM     gl_interface_control
      WHERE    interface_run_id = run_id;

      IF lv_je_source = 'Spreadsheet' THEN
         SELECT   COUNT(1)
         INTO     lv_count
         FROM     gl_interface
         WHERE    group_id = lv_group_id
         AND      entered_dr = 100;

         INSERT INTO anand_test_hook_dbg VALUES('count in iface is ' || lv_count);

         IF lv_count <> 0 THEN
            fnd_file.put_line(fnd_file.log, ' *** CUSTOM VALIDATION FAILED *** --> There are records in this batch which have 100 as debit amount');
            fnd_file.put_line(fnd_file.output, ' *** CUSTOM VALIDATION FAILED *** --> There are records in this batch which have 100 as debit amount');
            RETURN FALSE;
         END IF;
      END IF;
   END IF;

   RETURN TRUE;

END anand_test_hook_prc;

When you now try to load GL JV with DR = 100, the custom validation will fail and prevent importing of such records :-)

Hope this helps

Cheers
A

Generation of Org-Chart from Oracle HRMS

Dear Readers

Just wanted to share a useful information (maybe it can be used somewhere else in future) of how we can generate an org-chart from Oracle HR data using Google API’s

Step-1: Using the data in PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, generate a list of employees with their supervisor name as per below.
select
ppf.full_name empl_name,
ppfm.full_name mgr_name
from per_all_people_f ppf, per_all_people_f ppfm, per_all_assignments_f paf
where sysdate between ppf.effective_start_date and ppf.effective_end_date
and sysdate between ppfm.effective_start_date and ppfm.effective_end_date
and ppf.person_id = paf.person_id
and sysdate between paf.effective_start_date and paf.effective_end_date
and paf.supervisor_id = ppfm.person_id (+)
and ppfm.full_name like 'Stock%'
order by 2 desc

Step-2: Use the below PLSQL block to write out a HTML file in a directory
DECLARE
   v_file utl_file.file_type;
BEGIN
   v_file := utl_file.fopen('/usr/tmp', 'Sample.htm', 'W');
   utl_file.put_line(v_file, '<html>');
   utl_file.put_line(v_file, '  <head>');
   utl_file.put_line(v_file, '    <script type="text/javascript" src="https://www.google.com/jsapi"></script>');
   utl_file.put_line(v_file, '    <script type="text/javascript">');
   utl_file.put_line(v_file, '      google.load("visualization", "1", {packages:["orgchart"]});');
   utl_file.put_line(v_file, '      google.setOnLoadCallback(drawChart);');
   utl_file.put_line(v_file, '      function drawChart() {');
   utl_file.put_line(v_file, '        var data = new google.visualization.DataTable();');
   utl_file.put_line(v_file, '        data.addColumn(''string'', ''Name'');');
   utl_file.put_line(v_file, '        data.addColumn(''string'', ''Manager'');');
   utl_file.put_line(v_file, '        data.addColumn(''string'', ''ToolTip'');');
   utl_file.put_line(v_file, '        data.addRows([');

   FOR i IN
   (
      select
      ppf.full_name empl_name,
      ppfm.full_name mgr_name
      from per_all_people_f ppf, per_all_people_f ppfm, per_all_assignments_f paf
      where sysdate between ppf.effective_start_date and ppf.effective_end_date
      and sysdate between ppfm.effective_start_date and ppfm.effective_end_date
      and ppf.person_id = paf.person_id
      and sysdate between paf.effective_start_date and paf.effective_end_date
      and paf.supervisor_id = ppfm.person_id (+)
      and ppfm.full_name like 'Stock%'
      order by 2 desc
   ) loop
      utl_file.put_line(v_file, '[''' || i.empl_name || ''', ''' || i.mgr_name || ''', ''''],');
   END LOOP;

   utl_file.put_line(v_file, ']);');
   utl_file.put_line(v_file, '        var chart = new google.visualization.OrgChart(document.getElementById(''chart_div''));');
   utl_file.put_line(v_file, '        chart.draw(data, {allowHtml:true,allowCollapse:true,color:''#ffffff'',selectionColor:''#cc3636''});');
   utl_file.put_line(v_file, '      }');
   utl_file.put_line(v_file, '   </script>');
   utl_file.put_line(v_file, '    </head>');
   utl_file.put_line(v_file, '  <body>');
   utl_file.put_line(v_file, '    <div id="chart_div"></div>');
   utl_file.put_line(v_file, '  </body>');
   utl_file.put_line(v_file, '</html>');
   utl_file.fclose(v_file);
END;

Step-3: Voila!!! The HTML file (when opened in Chrome/Firefox etc.) chart as below



Hope this helps you sometime

Cheers
A