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;

Pipelined function usage

 CREATE or replace TYPE tree_ot AS OBJECT(
CUST_ACCOUNT_ID  NUMBER(15),    
account_name               VARCHAR2(240) ,
account_number    VARCHAR2(240));

create or replace type tree_tt as table of tree_ot;

create or replace function f_tree

   return tree_tt pipelined

is

   l_retval tree_ot := tree_ot (null, null, null);

begin

   for r_dept in (select cust_account_id,account_name,account_number  from hz_cust_accounts where rownum<30 )

    loop

        l_retval.CUST_ACCOUNT_ID :=r_dept.CUST_ACCOUNT_ID;

        l_retval.account_name :=r_dept.account_name;

        l_retval.account_number :=r_dept.account_number;    

         pipe row(l_retval);

   end loop;

   return;

end;

select * from table(f_tree)

Query to get GL batch details for AR receipts and AR invoice

SELECT gjb.name GL_batch_name,
gjh.name Journal_Name,
gjl.period_name,
gjh.JE_CATEGORY,
gjh.JE_SOURCE,
gjh.currency_code,
DECODE (gjh.actual_flag, 'A', 'Actual','B', 'Budget','E', 'Encumbrance') Balance_Type,
DECODE (gjl.status, 'P', 'Posted', 'U', 'Unposted', gjl.status) Batch_Status,
gjh.posted_date,
acra.receipt_number,
acra.doc_sequence_value
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb, 
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte,
ar_cash_receipts_all acra
WHERE gjb.je_batch_id = gjh.je_batch_id
and gjl.je_header_id = gjh.je_header_id
and gjl.je_header_id = gir.je_header_id
and gjl.je_line_num = gir.je_line_num
and gir.gl_sl_link_table = xal.gl_sl_link_table
and gir.gl_sl_link_id = xal.gl_sl_link_id
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
--and xal.ae_line_num = 1
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xe.application_id = xte.application_id
and xe.entity_id = xte.entity_id
and xte.application_id = 222
and xte.entity_code = 'RECEIPTS'
and xte.source_id_int_1 = acra.cash_receipt_id
and acra.cash_receipt_id in(127282,838383);
SELECT gjb.name GL_batch_name,
gjh.name Journal_Name,
gjl.period_name,
gjh.JE_CATEGORY,
gjh.JE_SOURCE,
gjh.currency_code,
DECODE (gjh.ACTUAL_FLAG, 'A', 'Actual','B', 'Budget','E', 'Encumbrance') Balance_Type,
DECODE (gjl.status, 'P', 'Posted', 'U', 'Unposted', gjl.status) Batch_Status,
gjh.posted_date,
rcta.trx_number,
rcta.trx_date
FROM apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_je_batches gjb, 
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_transaction_entities xte,
apps.ra_customer_trx_all rcta
WHERE gjb.je_batch_id = gjh.je_batch_id
and gjl.je_header_id = gjh.je_header_id
and gjl.je_header_id = gir.je_header_id
and gjl.je_line_num = gir.je_line_num
and gir.gl_sl_link_table = xal.gl_sl_link_table
and gir.gl_sl_link_id = xal.gl_sl_link_id
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
and xal.ae_line_num = 1 /*If AR Transaction has multiple lines then xla_ae_lines table contains multiple lines*/
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xe.application_id = xte.application_id
and xe.entity_id = xte.entity_id
and xte.application_id = 222
and xte.entity_code = 'TRANSACTIONS'
and xte.source_id_int_1 = rcta.customer_trx_id
and trim(rcta.trx_number) = trim('&AR_trx_number');

Validate the given field is Number or Varchar only

Select sysdate from dual where DECODE(REGEXP_INSTR (field, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER')='NOT_NUMBER'

this query will help to validate the given field is number or not

Request Set - Running details

SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
 fcr.request_id "REQUEST",
 fcr.parent_request_id "PARENT",
 fcr.oracle_process_id "Process ID",
 fcptl.user_concurrent_program_name "Program Name",
 fcr.argument_text,
 decode(fcr.phase_code,
        'X',
        'Terminated',
        'E',
        'Error',
        'C',
        'Completed',
        'P',
        'Pending',
        'R',
        'Running',
        phase_code) "Phase",
 decode(fcr.status_code,
        'X',
        'Terminated',
        'C',
        'Normal',
        'D',
        'Cancelled',
        'E',
        'Error',
        'G',
        'Warning',
        'Q',
        'Scheduled',
        'R',
        'Normal',
        'W',
        'Paused',
        'Not Sure') "Status",
 fcr.request_date,
 fcr.actual_start_date,
 fcr.actual_completion_date,
 (fcr.actual_completion_date - fcr.actual_start_date) * 1440 "Elapsed"
  FROM (SELECT /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
         fcr1.request_id
          FROM fnd_concurrent_requests fcr1
         WHERE 1 = 1
         START WITH fcr1.request_id = <request_id>
        --CONNECT BY PRIOR fcr1.parent_request_id = fcr1.request_id) x,
        CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcptl
 WHERE fcr.request_id = x.request_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcp.application_id = fcptl.application_id
   AND fcp.concurrent_program_id = fcptl.concurrent_program_id
   AND fcptl.language = 'US'
 ORDER BY 1;

Customer ShipTo BillTo Query - R12

SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = ;