EBS Order details along with workflow

 SELECT hr.name ou,h.order_number, 
       h.flow_status_code header_Status,
       l.flow_status_code line_status,
       CUST_ACCT.ACCOUNT_NUMBER,ship_su.location SHIP_TO_LOCATION, bill_su.location  INVOICE_TO_LOCATION,
       ship_ps.party_site_number ship_to_site_number,
       bill_ps.party_site_number bill_to_site_number,
       ship_from_org.organization_code
       SHIP_FROM,
       L.LINE_ID,
       L.ORG_ID,
       L.HEADER_ID,
       s.NAME source_name,
       L.LINE_NUMBER,
       L.ORDERED_ITEM,
       L.ORDERED_QUANTITY,
       l.cancelled_quantity,
       l.unit_selling_price,
       l.unit_list_price,
       l.tax_value,
       l.tax_line_value,
       L.INVENTORY_ITEM_ID,
       (select user_name from fnd_user where user_id=l.created_by) creater,
       (select user_name from fnd_user where user_id=l.last_updated_by) updater,
       L.CREATION_DATE LINE_CREATION,
       L.LAST_UPDATE_DATE LINE_UPDATE,
       (select sh.order_number||'->'||sl.line_number||'->'||sl.ordered_item||'->'||sl.ordered_quantity from oe_order_headers_all sh, oe_order_lines_all sl where sl.header_id=sh.header_id and sl.line_id=l.reference_line_id)
        original_order,
       L.REFERENCE_LINE_ID,
       L.REFERENCE_HEADER_ID,
       L.LINE_CATEGORY_CODE,
       lt.name  LINE_TYPE,
       PARTY.PARTY_NAME  SOLD_TO,
       L.SUBINVENTORY
           SUBINVENTORY,
           ship_loc.location_id,
       ship_loc.address1
           SHIP_TO_ADDRESS1,
       DECODE (ship_loc.city, NULL, NULL, ship_loc.city || ', ')
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ', ',
                  ship_loc.state || ', ')
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ', ')
       || DECODE (ship_loc.country, NULL, NULL, ship_loc.country)
           SHIP_TO_ADDRESS5,
       bill_loc.address1
           INVOICE_TO_ADDRESS1,
          DECODE (bill_loc.city, NULL, NULL, bill_loc.city || ', ')
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ', ',
                  bill_loc.state || ', ')
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ', ')
       || DECODE (bill_loc.country, NULL, NULL, bill_loc.country)
           INVOICE_TO_ADDRESS5,
       H.order_type_id,
       H.ordered_date,
       L.return_reason_code,
       L.ordered_item_id,
       L.item_identifier_type,
       L.booked_flag,
       L.cancelled_flag,
       L.open_flag,
       l.sold_from_org_id,
       l.shipping_instructions,
       l.flow_status_code,
       l.SHIPPABLE_FLAG,L.LINE_TYPE_ID,
       L.SOLD_TO_ORG_ID,
       L.SHIP_FROM_ORG_ID,
       L.SHIP_TO_ORG_ID,
       L.INVOICE_TO_ORG_ID,
        L.ORIG_SYS_DOCUMENT_REF,
       L.ORIG_SYS_LINE_REF,
       l.ATTRIBUTE16  "Return entered by" ,
       l.ATTRIBUTE19  "Return to Customer Order Ref" ,
       l.ATTRIBUTE12  "Return Id" ,
       l.ATTRIBUTE4  "Return Line ID" ,
       l.ATTRIBUTE1  "Return Receipt Date" ,
       l.ATTRIBUTE3  "Return Credit Denial Reason1" ,
       l.ATTRIBUTE4  "Return Credit Denial Reason2" ,
       l.RETURN_ATTRIBUTE4  "Certiport Return Line ID",
       l.ATTRIBUTE18  PO_NUMBER,
       l.attribute10,
       l.context,
       cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOL'
         AND wias.item_key =to_char(l.line_id)
ORDER BY begin_date DESC) workflow_line_status,
 cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOH'
         AND wias.item_key =to_char(l.header_id)
ORDER BY begin_date DESC) workflow_header_status
  FROM mtl_parameters           ship_from_org,
       hz_cust_site_uses_all    ship_su,
       hz_party_sites           ship_ps,
       hz_locations             ship_loc,
       hz_cust_acct_sites_all   ship_cas,
       hz_cust_site_uses_all    bill_su,
       hz_party_sites           bill_ps,
       hz_locations             bill_loc,
       hz_cust_acct_sites_all   bill_cas,
       hz_parties               party,
       hz_cust_accounts         cust_acct,
       oe_order_headers_all         H,
       oe_order_lines_all       L,
       oe_order_sources s,
       OE_TRANSACTION_TYPES_TL  lt,
       hr_operating_units hr
 WHERE     L.line_type_id = LT.transaction_type_id
       AND LT.language = USERENV ('LANG')
       AND L.sold_to_org_id = cust_acct.cust_account_id(+)
       AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
       AND L.ship_from_org_id = ship_from_org.organization_id(+)
       AND l.ship_to_org_id = ship_su.site_use_id(+)
       AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
       AND ship_cas.party_site_id = ship_ps.party_site_id(+)
       AND ship_loc.location_id(+) = ship_ps.location_id
       AND l.invoice_to_org_id = bill_su.site_use_id(+)
       AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
       AND bill_cas.party_site_id = bill_ps.party_site_id(+)
       AND bill_loc.location_id(+) = bill_ps.location_id
       AND L.header_id = H.header_id
       AND h.order_source_id=s.order_source_id
       AND hr.organization_id=L.ORG_ID
     --  AND  L.REFERENCE_LINE_ID =98618576
      -- AND L.REFERENCE_HEADER_ID                                                     
   --    and L.LINE_ID=99176446       
    -- and L.ORDERED_ITEM in('9780132156554')
  --     and h.creation_date>sysdate-5
      -- and l.header_id=31494876
      -- and l.line_id=99176446       
       and h.order_number ='12727162'--
       order by line_id
       
          select ooh.order_number
       ,ool.ordered_item
       ,ool.ordered_quantity
       ,ooh.flow_status_code header_status                   
       ,ool.flow_status_code line_status
       ,prha.segment1 requisition
       ,poh.segment1 po_number
       ,poh.closed_code po_status
       ,pll.quantity
       ,pll.quantity_received
       ,pll.closed_code po_shipment_status
from apps.oe_order_headers_all                   ooh
    ,apps.oe_order_lines_all                     ool
    ,apps.oe_drop_ship_sources                   odss
    ,apps.po_requisition_headers_all             prha
    ,apps.po_headers_all                         poh
    ,apps.po_lines_all                           pol
    ,apps.po_line_locations_all                  pll              
where ool.header_id = ooh.header_id
and   odss.header_id = ooh.header_id
and   odss.line_id = ool.line_id
and   prha.requisition_header_id = odss.requisition_header_id
and   poh.po_header_id = odss.po_header_id
and   pol.po_line_id = odss.po_line_id
and   pol.po_header_id = poh.po_header_id
and   pll.po_line_id = pol.po_line_id
and   ooh.order_number = '12727162'--'12727161';

SELECT ooh.order_number

              ,ool.ordered_item

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,rctl.interface_line_attribute1

              ,rctl.interface_line_attribute3

              ,rctl.interface_line_attribute6

              ,rct.org_id

              ,rct.creation_date

             ,trx_number

              ,rctl.quantity_ordered

              ,rct.interface_header_context

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

             ,ra_customer_trx_all rct

             ,ra_customer_trx_lines_all rctl

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      AND rctl.interface_line_attribute1=to_char(ooh.order_number)

      AND rctl.interface_line_attribute6=to_char(ool.line_id)

      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)

     AND rctl.customer_trx_id=rct.customer_trx_id

      AND rct.interface_header_context='ORDER ENTRY'

      AND ooh.order_number in ('12727161');

SELECT TRX.TRX_NUMBER         INVOICE_NUMBER,

       TRX.TRX_DATE           INVOICE_DATE,

       HZA.ACCOUNT_NUMBER     CUSTOMER_NUMBER,

       RAT.NAME               TERM_NAME,

       ARPS.DUE_DATE          INVOICE_DUE_DATE

  FROM RA_CUSTOMER_TRX_ALL       TRX,

       oe_order_headers_all      OOH,

       HZ_CUST_ACCOUNTS          HZA,

       RA_TERMS                  RAT,

       AR_PAYMENT_SCHEDULES_ALL  ARPS

 WHERE     to_char(TRX.ct_reference) = to_char(OOH.order_number)

       AND OOH.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID

       AND RAT.TERM_ID = TRX.TERM_ID

       AND ARPS.CUSTOMER_TRX_ID(+) = TRX.CUSTOMER_TRX_ID

       AND ooh.order_number = '12727161';

       select request_id,interface_status ,interface_line_attribute6 line_id,a.* from ra_interface_lines_all a   where sales_order='8044839' --req id 124077809

                     and interface_line_attribute6 in (133090970,133090946)

select * from ra_interface_errors_all where MESSAGE_TEXT='A Party Tax Profile does not exist for this Party.'

and org_id=16916 --INTERFACE_LINE_ID in (select a.INTERFACE_LINE_ID from ra_interface_lines_all a where sales_order='8044839')

Random number generation

     select round(dbms_random.value(5,10)) ||'   +   '||round(dbms_random.value(1,10))||'  = '        
        output
       from   dual
       connect by level <= 26

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

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;

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)