Oracle Blog
Menu
  • Oracle
    • PL/SQL
    • E-Business Suite
    • Oracle Application Framework (OAF)
    • Oracle ADF
    • Oracle Workflow
  • C/C++
  • Hakkımda
  • İletişim
Oracle, C/C++, JAVA
Browse: Home » Oracle » Page 2

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

Oracle APPS Profil Sorgusu
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
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.

cancel_ap_invoice
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
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;
 

 

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.

Günler
Oracle PL/SQL
1
2
3
4
5
 
    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;

Default
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
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.
 

 

← Previous 1 2 3 4 Next →

Visitors

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 7 other subscribers

Copyright © 2019 Oracle Blog

Powered by WordPress and Origin