GL - Segment descriptions

Select GCC.CONCATENATED_SEGMENTS ACCOUNT,

       GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION(GCC.CHART_OF_ACCOUNTS_ID,

                                                GCC.CODE_COMBINATION_ID) DESCRIPTION

  from GL_CODE_COMBINATIONS_KFV GCC

 where segment1 IN ('9000', '9560')

   and segment2 IN ('8002', '9519')

SELECT *
  FROM fnd_flex_key_seg_vset_v
 WHERE application_name = 'General Ledger'
   AND id_flex_name = 'Accounting Flexfield'

select * from fnd_application_vl where application_id = 101

select * from fnd_id_flex_segments WHERE application_id = 101

SELECT LEDGER_ID,
       LEDGER_NAME,
       LEDGER_SHORT_NAME,
       LEGAL_ENTITY_NAME,
       gllv.LOCATION_CODE          "LOCATION",
       LOCATION_DESCRIPTION,
       LEDGER_CATEGORY_CODE,
       CURRENCY_CODE,
       CHART_OF_ACCOUNTS_ID,
       PERIOD_SET_NAME,
       ACCOUNTED_PERIOD_TYPE,
       hr_loc.country              "Country Code",
      -- hr_loc.location_code        "Location Code",
       glev.flex_segment_value     "Company Code"
  FROM gl_ledger_le_v          gllv,
       hr_locations_all        hr_loc,
       gl_legal_entities_bsvs  glev
WHERE     hr_loc.location_id = gllv.location_id
       AND LEDGER_NAME LIKE 'PL%'
       AND glev.LEGAL_ENTITY_ID = gllv.LEGAL_ENTITY_ID

Range of Transaction number

SELECT COUNTROW, MIN(Lvl) starting_val, MAX(Lvl) end_value

  FROM (SELECT LEVEL Lvl,

               ceil(ROW_NUMBER() OVER(order by 1 desc) / 5) countrow

          FROM DUAL

        CONNECT BY LEVEL < 21)

 GROUP BY COUNTROW

 order by 1

select min(trx_number) lb,max(trx_number) ub

from (select trx_number,trx_number- row_number() over(order by trx_number) rn
from ra_customer_trx_all a , ra_cust_trx_types_all b,fnd_user c
where printing_option = 'PRI'
and a.cust_trx_type_id = B.CUST_TRX_TYPE_ID
and a.org_id = b.org_id
and b.type = 'INV'
and c.user_id = a.last_updated_by
and A.PRINTING_PENDING = 'N'
and a.org_id = 3842
--       and printing_count >= 1
and trx_date between '01-JAN-2024' and '30-JAN-2024')
group by rn
order by 1;    

Concurrent Jobs completed through Scheduled

  SELECT b.user_name,

         a.USER_CONCURRENT_PROGRAM_NAME,

         a.Program,

         ROUND (

             (  (a.actual_completion_date - a.actual_start_date)

              * 24

              * 60

              * 60

              / 60),

             2)

             AS Process_time,

         a.request_id,

         TO_CHAR (a.request_date, 'DD-MON-YY HH24:MI:SS')

             AS Request_Date,

         TO_CHAR (a.actual_start_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Start_Date,

         TO_CHAR (a.actual_completion_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Completion_Date,

         ROUND ((a.actual_completion_date - a.request_date) * 24 * 60 * 60, 2)

             AS end_to_end,

         a.phase_code,

         a.status_code,

         a.argument_text,

         a.completion_text

    FROM apps.fnd_conc_req_summary_v a, apps.fnd_user b

   WHERE     a.requested_by = b.user_id

         AND (a.ACTUAL_COMPLETION_DATE) BETWEEN '<start date>' AND '<end date>'

         AND a.status_code = 'C'

         AND (   (    is_sub_request = 'Y'

                  AND request_type = 'P'

                  AND request_id <> priority_request_id

                  AND EXISTS

                          (SELECT 1

                             FROM fnd_concurrent_requests fc

                            WHERE     fc.request_id = a.priority_request_id

                                  AND fc.parent_request_id != -1))

              OR (    is_sub_request = 'N'

                  AND request_id = priority_request_id

                  AND parent_request_id != -1))

         AND b.user_name NOT LIKE 'XX__%' ESCAPE '_'

ORDER BY request_id DESC;

Oracle PL/SQL - PL SQL Cursor CURSOR Expressions

A CURSOR expression returns a nested cursor.

 It has this syntax:

 CURSOR ( subquery )

You can use a CURSOR expression in a SELECT statement or pass it to a function.

 You cannot use a cursor expression with an implicit cursor.

 

The following code declares and defines an explicit cursor for a query that includes a cursor expression.

/* Formatted on 3/16/2023 1:41:03 PM (QP5 v5.388) */
CREATE TABLE emp
(
    empid             NUMBER (6),
    first_name        VARCHAR2 (20),
    last_name         VARCHAR2 (25),
    email             VARCHAR2 (25),
    phone_number      VARCHAR2 (20),
    hire_date         DATE,
    job_id            VARCHAR2 (10),
    salary            NUMBER (8, 2),
    commission_pct    NUMBER (2, 2),
    manager_id        NUMBER (6),
    department_id     NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:11 PM (QP5 v5.388) */
INSERT INTO emp
     VALUES (100,
             'Steven',
             'King',
             'SKING',
             '123.123.4567',
             TO_DATE ('17-JUN-1987', 'dd-MON-yyyy'),
             'CODER',
             24000,
             NULL,
             NULL,
             10);
INSERT INTO emp
     VALUES (200,
             'Joe',
             'Lee',
             'abc',
             '123.123.9999',
             TO_DATE ('17-JUN-1980', 'dd-MON-yyyy'),
             'TESTER',
             25000,
             NULL,
             NULL,
             20);

/* Formatted on 3/16/2023 1:41:17 PM (QP5 v5.388) */
CREATE TABLE departments
(
    department_id      NUMBER (4),
    department_name    VARCHAR2 (30) CONSTRAINT dept_name_nn NOT NULL,
    manager_id         NUMBER (6),
    location_id        NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:20 PM (QP5 v5.388) */
INSERT INTO departments
     VALUES (10,
             'Administration',
             200,
             1700);
INSERT INTO departments
     VALUES (20,
             'Marketing',
             201,
             1000);
INSERT INTO departments
     VALUES (30,
             'Purchasing',
             114,
             1700);

INSERT INTO departments

     VALUES (40,
             'Human Resources',
             203,
             1000);

INSERT INTO departments

     VALUES (50,
             'Shipping',
             121,
             1700);

 

 DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
     emp_cur    emp_cur_typ;
     dept_name  departments.department_name%TYPE;
     emp_name   emp.last_name%TYPE;
     CURSOR c1 IS
       SELECT department_name,
         CURSOR ( SELECT e.last_name
                 FROM emp e
                 WHERE e.department_id = d.department_id
                 ORDER BY e.last_name
                 ) emp
       FROM departments d
       ORDER BY department_name;
 BEGIN
     OPEN c1;
     LOOP 
       FETCH c1 INTO dept_name, emp_cur;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
       LOOP 
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
       END LOOP;
     END LOOP;
     CLOSE c1;
 END;