Oracle APPS SQL Script for Profile Values

January 12, 2015 · by altunkan · in E-Business Suite

Hello, You can use the following sql script in order to find profile values


SELECT fpo.profile_option_name profil_adi,
       fpl.user_profile_option_name profil_kullanici_adi,
       DECODE (fpv.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User')
          profil_seviyesi,
       DECODE (fpv.level_id,
               10001, 'Site',
               10002, fa.application_short_name,
               10003, frt.responsibility_name,
               10004, fu.user_name)
          seviye_degeri,
       profile_option_value profil_degeri
  FROM fnd_profile_options fpo,
       fnd_profile_options_tl fpl,
       fnd_profile_option_values fpv,
       fnd_responsibility_tl frt,
       fnd_application fa,
       fnd_user fu
 WHERE     fpo.profile_option_name = fpl.profile_option_name
       AND fpo.profile_option_id = fpv.profile_option_id
       AND fpv.level_value = fu.user_id(+)
       AND fpv.level_value = frt.responsibility_id(+)
       AND fpv.level_value = fa.application_id(+)
       AND fpl.user_profile_option_name =
              '<User Profile Option Name'

SELECT fpo.profile_option_name profil_adi,

fpl.user_profile_option_name profil_kullanici_adi,

DECODE (fpv.level_id,

10001, 'Site',

10002, 'Application',

10003, 'Responsibility',

10004, 'User')

profil_seviyesi,

DECODE (fpv.level_id,

10001, 'Site',

10002, fa.application_short_name,

10003, frt.responsibility_name,

10004, fu.user_name)

seviye_degeri,

profile_option_value profil_degeri

FROM fnd_profile_options fpo,

fnd_profile_options_tl fpl,

fnd_profile_option_values fpv,

fnd_responsibility_tl frt,

fnd_application fa,

fnd_user fu

WHERE fpo.profile_option_name = fpl.profile_option_name

AND fpo.profile_option_id = fpv.profile_option_id

AND fpv.level_value = fu.user_id(+)

AND fpv.level_value = frt.responsibility_id(+)

AND fpv.level_value = fa.application_id(+)

AND fpl.user_profile_option_name =

'<User Profile Option Name'

Oracle Application Framework (OAF) View Object Extension

January 4, 2015 · by altunkan · in Oracle Application Framework (OAF)

Hello, During extension developments for OAF pages, sometimes we need extra fields which are not available in standard view object. In this kind of situations, we are creating a new view object and make it extend standard view object. Please follow carefully…

Oracle APPS Java Application with Shell Concurrent Program

January 4, 2015 · by altunkan · in E-Business Suite

Hello, In my previous tutorial, I did an example about Java Concurrent Program with External Jars. In this tutorial, I will develop a simple java application which generates an Excel(xlsx) file and run it as a shell concurrent program in Oracle…

Oracle Apps How to Cancel AP Invoice

January 2, 2015 · by altunkan · in E-Business Suite

Hello, In order to cancel AP invoices in Oracle EBS, you can use following script. You do not need to call ap_cancel_pkg.Is_Invoice_Cancellable API since cancel api is already doing the same validation.


DECLARE
   l_invoice_id                     NUMBER;
   l_last_updated_by                NUMBER;
   l_last_update_login              NUMBER;
   l_gl_date                        DATE;
   l_ret_message                    VARCHAR2 (4000);
   l_cancel_api_result              BOOLEAN;
   l_invoice_num                    VARCHAR2 (100);

   --out params
   l_message_name_out               VARCHAR2 (1000);
   l_invoice_amount_out             NUMBER;
   l_base_amount_out                NUMBER;
   l_tax_amount_out                 NUMBER;
   l_temp_cancelled_amount_out      NUMBER;
   l_cancelled_by_out               VARCHAR2 (1000);
   l_cancelled_amount_out           NUMBER;
   l_cancelled_date_out             DATE;
   l_last_update_date_out           DATE;
   l_original_prepayment_amount_o   NUMBER;
   l_pay_curr_invoice_amount_out    NUMBER;
   l_token_out                      VARCHAR2 (100);
BEGIN
   BEGIN
      SELECT last_updated_by,
             last_update_login,
             gl_date,
             invoice_num
        INTO l_last_updated_by,
             l_last_update_login,
             l_gl_date,
             l_invoice_num
        FROM ap_invoices_all
       WHERE invoice_id = l_invoice_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         l_last_updated_by := NULL;
         l_last_update_login := NULL;
         l_gl_date := NULL;
         l_invoice_num := NULL;
   END;

   BEGIN
      l_cancel_api_result :=
         ap_cancel_pkg.
         ap_cancel_single_invoice (
            p_invoice_id                   => l_invoice_id,
            p_last_updated_by              => l_last_updated_by,
            p_last_update_login            => l_last_update_login,
            p_accounting_date              => l_gl_date,
            p_message_name                 => l_message_name_out,
            p_invoice_amount               => l_invoice_amount_out,
            p_base_amount                  => l_base_amount_out,
            p_temp_cancelled_amount        => l_temp_cancelled_amount_out,
            p_cancelled_by                 => l_cancelled_by_out,
            p_cancelled_amount             => l_cancelled_amount_out,
            p_cancelled_date               => l_cancelled_date_out,
            p_last_update_date             => l_last_update_date_out,
            p_original_prepayment_amount   => l_original_prepayment_amount_o,
            p_pay_curr_invoice_amount      => l_pay_curr_invoice_amount_out,
            p_token                        => l_token_out,
            p_calling_sequence             => NULL);

      IF l_cancel_api_result
      THEN
         l_ret_message :=
            l_invoice_num || ' is cancelled.';
         x_status := 'S';
      ELSE
         l_ret_message :=
            l_invoice_num
            || ' : error';
         l_ret_message :=
            l_ret_message || '. l_message_name_out: ' || l_message_name_out;
         l_ret_message := l_ret_message || '. sqlerrm: ' || SQLERRM;
         x_status := 'E';
      END IF;

      DBMS_OUTPUT.put_line ('l_ret_messsage:' || l_ret_message);
   END;
END;

DECLARE

l_invoice_id NUMBER;

l_last_updated_by NUMBER;

l_last_update_login NUMBER;

l_gl_date DATE;

l_ret_message VARCHAR2 (4000);

l_cancel_api_result BOOLEAN;

l_invoice_num VARCHAR2 (100);

--out params

l_message_name_out VARCHAR2 (1000);

l_invoice_amount_out NUMBER;

l_base_amount_out NUMBER;

l_tax_amount_out NUMBER;

l_temp_cancelled_amount_out NUMBER;

l_cancelled_by_out VARCHAR2 (1000);

l_cancelled_amount_out NUMBER;

l_cancelled_date_out DATE;

l_last_update_date_out DATE;

l_original_prepayment_amount_o NUMBER;

l_pay_curr_invoice_amount_out NUMBER;

l_token_out VARCHAR2 (100);

BEGIN

SELECT last_updated_by,

last_update_login,

gl_date,

invoice_num

INTO l_last_updated_by,

l_last_update_login,

l_gl_date,

l_invoice_num

FROM ap_invoices_all

WHERE invoice_id = l_invoice_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_last_updated_by := NULL;

l_last_update_login := NULL;

l_gl_date := NULL;

l_invoice_num := NULL;

END;

BEGIN

l_cancel_api_result :=

ap_cancel_pkg.

ap_cancel_single_invoice (

p_invoice_id => l_invoice_id,

p_last_updated_by => l_last_updated_by,

p_last_update_login => l_last_update_login,

p_accounting_date => l_gl_date,

p_message_name => l_message_name_out,

p_invoice_amount => l_invoice_amount_out,

p_base_amount => l_base_amount_out,

p_temp_cancelled_amount => l_temp_cancelled_amount_out,

p_cancelled_by => l_cancelled_by_out,

p_cancelled_amount => l_cancelled_amount_out,

p_cancelled_date => l_cancelled_date_out,

p_last_update_date => l_last_update_date_out,

p_original_prepayment_amount => l_original_prepayment_amount_o,

p_pay_curr_invoice_amount => l_pay_curr_invoice_amount_out,

p_token => l_token_out,

p_calling_sequence => NULL);

IF l_cancel_api_result

THEN

l_ret_message :=

l_invoice_num || ' is cancelled.';

x_status := 'S';

ELSE

l_ret_message :=

l_invoice_num

|| ' : error';

l_ret_message :=

l_ret_message || '. l_message_name_out: ' || l_message_name_out;

l_ret_message := l_ret_message || '. sqlerrm: ' || SQLERRM;

x_status := 'E';

END IF;

DBMS_OUTPUT.put_line ('l_ret_messsage:' || l_ret_message);

END;

List of Days between two dates in Oracle SQL

January 2, 2015 · by altunkan · in PL/SQL

Hello, You can use the following SQL Script in order to fetch list of days between two dates.


    SELECT to_date('01.01.2014', 'dd.mm.rrrr') + LEVEL - 1, LEVEL
      FROM DUAL
CONNECT BY LEVEL <= to_date('15.01.2014', 'dd.mm.rrrr') + 1 - to_date('01.01.2014', 'dd.mm.rrrr');

SELECT to_date('01.01.2014', 'dd.mm.rrrr') + LEVEL - 1, LEVEL

FROM DUAL

CONNECT BY LEVEL <= to_date('15.01.2014', 'dd.mm.rrrr') + 1 - to_date('01.01.2014', 'dd.mm.rrrr');

Result;


TO_DATE('01.01.2014','DD.MM.RRRR')+LEVEL-1      LEVEL
------------------------------------------ ----------
01/01/2014                                          1
02/01/2014                                          2
03/01/2014                                          3
04/01/2014                                          4
05/01/2014                                          5
06/01/2014                                          6
07/01/2014                                          7
08/01/2014                                          8
09/01/2014                                          9
10/01/2014                                         10
11/01/2014                                         11
12/01/2014                                         12
13/01/2014                                         13
14/01/2014                                         14
15/01/2014                                         15

15 rows selected.

TO_DATE('01.01.2014','DD.MM.RRRR')+LEVEL-1 LEVEL

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

01/01/2014 1

02/01/2014 2

03/01/2014 3

04/01/2014 4

05/01/2014 5

06/01/2014 6

07/01/2014 7

08/01/2014 8

09/01/2014 9

10/01/2014 10

11/01/2014 11

12/01/2014 12

13/01/2014 13

14/01/2014 14

15/01/2014 15

15 rows selected.

Oracle APPS SQL Script for Profile Values

Oracle Application Framework (OAF) View Object Extension

Oracle APPS Java Application with Shell Concurrent Program

Oracle Apps How to Cancel AP Invoice

List of Days between two dates in Oracle SQL

Visitors

Subscribe to Blog via Email