AP(Payables) invoice related important table

 AP(Payables) invoice related important table

Invoice

AP_BATCHES_ALL

SELECT
  *
FROM
  AP_BATCHES_ALL
WHERE
  batch_id IN
  (
    SELECT
      batch_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );

AP_INVOICES_ALL

SELECT DISTINCT
  ai.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , ai.accts_pay_code_combination_id
  ) "Accts Pay Account"
FROM
  AP_INVOICES_ALL ai
WHERE
  invoice_id = :P_INVOICE_ID  
ORDER BY
  ai.invoice_id ASC;

AP_INVOICE_LINES_ALL
          
SELECT
  *
FROM
  AP_INVOICE_LINES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_DISTRIBUTIONS_ALL

SELECT
  aid.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aid.accts_pay_code_combination_id) "Accts Pay Account",
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , aid.dist_code_combination_id)
  "Dist Account"
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aid.invoice_id,
  aid.invoice_line_number,
  aid.distribution_line_number ASC;

ZX_LINES 
         
SELECT
  *
FROM
  ZX_LINES
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_summary 
         
SELECT
  *
FROM
  zx_lines_summary
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


zx_rec_nrec_dist
          
SELECT
  *
FROM
  zx_rec_nrec_dist
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_det_factors  
        
SELECT
  *
FROM
  zx_lines_det_factors
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


AP_PAYMENT_SCHEDULES_ALL 
         
SELECT
  *
FROM
  AP_PAYMENT_SCHEDULES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

ap_recurring_payments_all
 
SELECT DISTINCT
  arp.*
FROM
  ap_recurring_payments_all arp,
  ap_invoices_all ai
WHERE
  ai.recurring_payment_id = arp.recurring_payment_id
AND ai.invoice_id         = :P_INVOICE_ID;


AP_HOLDS_ALL 
         
SELECT
  *
FROM
  AP_HOLDS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

AP_HOLD_CODES
          
SELECT
  *
FROM
  AP_HOLD_CODES
WHERE
  hold_lookup_code IN
  (
    SELECT
      hold_lookup_code
    FROM
      AP_HOLDS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_TERMS
          
SELECT
  *
FROM
  AP_TERMS
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );



AP_TERMS_LINES
          
SELECT
  *
FROM
  AP_TERMS_LINES
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_INVOICE_PREPAYS_ALL 
         
SELECT
  *
FROM
  AP_INVOICE_PREPAYS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_CHRG_ALLOCATIONS_ALL 

SELECT DISTINCT
  aca.*
FROM
  AP_CHRG_ALLOCATIONS_ALL aca,
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  (
    aca.charge_dist_id = aid.invoice_distribution_id
  OR aca.item_dist_id  = aid.invoice_distribution_id
  )
AND aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aca.charge_dist_id;

AP_INV_APRVL_HIST_ALL
          
SELECT
  *
FROM
  AP_INV_APRVL_HIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID
ORDER BY
  1;


AP_ALLOCATION_RULES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_ALLOCATION_RULE_LINES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULE_LINES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_SELF_ASSESSED_TAX_DIST_ALL
          
SELECT
  *
FROM
  AP_SELF_ASSESSED_TAX_DIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_RELATIONSHIPS
           
SELECT
  *
FROM
  AP_INVOICE_RELATIONSHIPS
WHERE
  original_invoice_id = :P_INVOICE_ID
OR related_invoice_id = :P_INVOICE_ID;

Oracle AP Tax Joins

 AP Table Relation Oracle Apps

ORACLE PAYABLE TABLE RELATION
Source Table	Dependent Table	Condition
AP_INVOICE_LINES_ALL AIL	ZX_LINES_SUMMARY ZLS	AIL.invoice_id=ZLS.trx_id and ZLS.application_id = 200 and ZLS.entity_code = 'AP_INVOICES' and ZLS.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.summary_tax_line_id =ZLS.summary_tax_line_id
AP_INVOICE_LINES_ALL AIL	ZX_LINES ZL	AIL.invoice_id=ZL.trx_id and ZL.application_id = 200 and ZL.entity_code = 'AP_INVOICES' and ZL.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZL.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL AID	ZX_REC_NREC_DIST ZD	AID.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AID.summary_tax_line_id = ZD.summary_tax_line_id and AID.detail_tax_dist_id = ZD.rec_nrec_tax_dist_id
AP_SELF_ASSESSED_TAX_DIST_ALL ASTD	ZX_REC_NREC_DIST ZD	ASTD.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and ASTD.summary_tax_line_id = ZD.summary_tax_line_id and ASTD.detail_tax_dist_id =ZD.rec_nrec_tax_dist_id and ZD.self_assessed_flag='Y'
AP_INVOICE_LINES_ALL AIL	ZX_LINES_DET_FACTORS ZLDF	AIL.invoice_id=ZLDF.trx_id and ZLDF.application_id = 200 and ZLDF.entity_code = 'AP_INVOICES' and ZLDF.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZLDF.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	XLA_EVENTS XE	Actual Event: -------------- xe.event_id = aid.accounting_event_id and xe.application_id = 200 Budgetary Event: ----------------- xe.event_id = aid.bc_event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	RCV_TRANSACTIONS RT	aid.rcv_transaction_id = rt.transaction_id
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	GL_CODE_COMBINATIONS GCC	aid.dist_code_combination_id =gcc.code_combination_id
AP_PAYMENT_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id    and xe.application_id = 200
AP_INVOICE_PAYMENTS_ALL AIP	XLA_EVENTS XE	aip.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	PO_DISTRIBUTIONS_ALL	PD aid.po_distribution_id = pd.po_distribution_id
AP_INVOICES_ALL AI	PO_HEADERS_ALL PH	ai.po_header_id = ph.po_header_id
AP_SUPPLIER_SITES_ALL ASS	HZ_PARTY_SITES HPS	ass.party_site_id = hps.party_site_id
AP_SUPPLIERS AS	HZ_PARTIES HP	as.party_id = hp.party_id
AP_PRERPAY_APP_DISTS or AP_PAYMENT_HIST_DISTS APA	XLA_EVENTS XE	apa.accounting_event_id = xe.event_id xe.application_id = 200
AP_PREPAY_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICES_ALL AI	XLA.XLA_TRANSACTION_ENTITIES XTE	ai.invoice_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_INVOICES' and xte.application_id = 200
AP_CHECKS_ALL AC	XLA.XLA_TRANSACTION_ENTITIES XTE	ac.check_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_PAYMENTS' and xte.application_id =200

Handle Null or Not Present tags in xml file in BI Publisher

Workaround Option 2 in RTF template

If using RTF templates another option is to use a conditional IF at the template level to verify the presence of a certain tag in the XML source.

Within the XML data there are three possible scenarios for the value of an element:

• The element is present in the XML data, and it has a value
• The element is present in the XML data, but it does not have a value
• The element is not present in the XML data, and therefore there is no value

In the RTF template, specify a different behavior depending on the presence of the element and its value. The following examples show how to check for each of these conditions using an "if" statement.

• To define behavior when the element is present and the value is not null, use the following:

<?if:element_name!=''?> desired behavior <?end if?>

• To define behavior when the element is present, but is null, use the following:

<?if:element_name and element_name="?> desired behavior <?end if?>

• To define behavior when the element is not present, use the following:

<?if:not(element_name)?> desired behavior <?end if?>

<?if:not(//G_TAX/TAX_CODE)?>VAT<?end if?>

Example below checks the presence of a XML tag in the source

<?if:not(tag_name)?>tag not found<?end if?><?if:tag_name?><?tag_name?><?end if?>

If the tag tag_name is NOT present in the XML Source, text 'tag not found' will print on the output. If the tag is present, then the value of the tag will print.

Example below prints the value of the tag when its value is not null

<?if:Sold_To_Addr_Line_3_ID53!=''?><?Sold_To_Addr_Line_3_ID53?><?end if?>

How Hierarchical SQL works

Employee ID	Manager ID
100
101	100
203	101
204	101
102	205

How it works

SEE Data, understand its traversal, it searches each branch till its leaf, when start by root.

1.      To start with node: ex. At 100, then use syntax: start with 100

2.      Level is pseudo column: which shows the level at which query is running

3.      Connect by : tag causes to connect two rows at two different levels.

4.      Prior is identifying: prior <cell> = current <cell value>

When we run

Select LEVEL, EMPLOYEE_ID, MANAGER_ID FROM EMPLOYEES

START WITH EMPLOYEE_ID =100

Employee ID	Manager ID
100

è SO THIS SQL WORKS ON TWO ROWS TOGETHER, SO NEXT ROW IS FOUND BY CONNECT BY TAG

è AS CURSOR/POINTER MOVES TO NEXT ROW BY USING START BY TAG

o   CURRENT ROW BECOMES PRIOR

o   NEXT ROW BECOMES CURRENT

Employee ID	Manager ID	ROW INDICATOR
100		PRIOR
101	100	CURRENT

CONNECT BY PRIOR

Employee ID	Manager ID	ROW INDICATOR
100		PRIOR
101	100	CURRENT

CONNECT BY PRIOR EMPLOYEE_ID =  [CURRENT] MANAGER_ID

Select LEVEL, EMPLOYEE_ID, MANAGER_ID FROM EMPLOYEES

START WITH  EMPLOYEE_ID =100

CONNECT BY PRIOR EMPLOYEE_ID =  [CURRENT] MANAGER_ID

3 Level SQL: GRAND PARENT - PARENT - CHILD

select
distinct root_node_id
from
(
select
g1.subject_id root_node_id
-- g1.object_id ,
-- g2.subject_id ,
-- g2.object_id ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g1.subject_id = :subject_id
and g2.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
-- g1.object_id ,
-- g2.subject_id ,
-- g2.object_id ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g2.subject_id = :subject_id
and g1.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
-- g1.object_id  ,
-- g2.subject_id ,
-- g2.object_id  ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g3.subject_id = :subject_id
and g1.subject_id != :subject_id
and g2.subject_id != :subject_id)
);


--GRAND PARENT: 179695
--PARENT: 172307
--CHILD: 9912

select 
level_info, root_node_id
,(select party_name from hz_parties where party_id = root_node_id ) root_node_name
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 1) level1
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 2) level2
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 3) level3 from (
select level level_info, self_id, child_id, self_name, child_name ,
SYS_CONNECT_BY_PATH(child_name, '//') path_data, rootnode.root_node_id
from
(select parent_id self_id, child_id, parent_name self_name, child_name from
(select HREL.SUBJECT_ID parent_id,
HREL.OBJECT_ID child_id,
HSUB.PARTY_NAME parent_name,
HOBJ.PARTY_NAME child_name
from hz_relationships HREL,
HZ_PARTIES HSUB,
HZ_PARTIES HOBJ
where 1=1
AND HREL.OBJECT_TYPE ='ORGANIZATION'
AND HREL.SUBJECT_TYPE ='ORGANIZATION'
AND HREL.RELATIONSHIP_CODE ='PARENT_OF'
AND HREL.SUBJECT_ID = HSUB.PARTY_ID
AND HREL.OBJECT_ID = HOBJ.PARTY_ID
) y
where 1=1) x,
(select
distinct root_node_id
from
(
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g1.subject_id = :subject_id
and g2.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g2.subject_id = :subject_id
and g1.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g3.subject_id = :subject_id
and g1.subject_id != :subject_id
and g2.subject_id != :subject_id)
)
) rootnode
start with x.self_id  = rootnode.root_node_id
connect by nocycle prior x.child_id = x.self_id)
order by 1,2,3,4;

Finding a Path in General Graph in SQL (Source: Joe Celkos)

Source -------------------- Destination

Each edge is having a wt =1
There are 5 Noes in graph

s,u,x,v,y


CREATE TABLE Graph
(
source CHAR(2) NOT NULL,
destination CHAR(2) NOT NULL,
cost INTEGER NOT NULL,
PRIMARY KEY (source, destination)
);


INSERT INTO graph VALUES ('s','s','0') ;
INSERT INTO graph VALUES ('s','u','3') ;
INSERT INTO graph VALUES ('s','x','5') ;
INSERT INTO graph VALUES ('u','u','0') ;
INSERT INTO graph VALUES ('u','v','6') ;
INSERT INTO graph VALUES ('u','x','2') ;
INSERT INTO graph VALUES ('v','v','0') ;
INSERT INTO graph VALUES ('v','y','2') ;
INSERT INTO graph VALUES ('x','u','1') ;
INSERT INTO graph VALUES ('x','v','4') ;
INSERT INTO graph VALUES ('x','x','0') ;
INSERT INTO graph VALUES ('x','y','6') ;
INSERT INTO graph VALUES ('y','s','3') ;
INSERT INTO graph VALUES ('y','v','7') ;
INSERT INTO graph VALUES ('y','y','0') ;

CREATE TABLE paths (
    step_1        CHAR(2) NOT NULL,
    step_2        CHAR(2) NOT NULL,
    step_3        CHAR(2) NOT NULL,
    step_4        CHAR(2) NOT NULL,
    step_5        CHAR(2) NOT NULL,
    total_cost    INTEGER NOT NULL,
    path_length   INTEGER NOT NULL,
    PRIMARY KEY ( step_1,
                  step_2,
                  step_3,
                  step_4,
                  step_5 )
);   

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

SELECT
    g1.source,  --it is ‘s’ in this example
    g2.source,
    g3.source,
    g4.source,
    g4.destination, --it is ‘y’ in this example
    ( g1.cost + g2.cost + g3.cost + g4.cost ) total_cost,
    (
        CASE
            WHEN g1.source NOT IN (g2.source,g3.source,g4.source) THEN 1
            ELSE 0
        END
        +
        CASE
            WHEN g2.source NOT IN (g1.source,g3.source,g4.source) THEN 1
            ELSE 0
        END
        +
        CASE
            WHEN g3.source NOT IN (g1.source,g2.source,g4.source) THEN 1
            ELSE 0
        END
        +
        CASE
            WHEN g4.source NOT IN (g1.source,g2.source,g3.source) THEN 1
            ELSE 0
        END
    ) path_length
FROM
    graph G1,
graph G2,
Graph  G3,graph  g4
WHERE
    g1.source = 's'
    AND g1.destination = g2.source
        AND g2.destination = g3.source
            AND g3.destination = g4.source
                AND g4.destination = 'y';

Hierarchical Query Examples

Hierarchical Query Examples

https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm#SQLRF52318
CONNECT BY Example The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .

LEVEL Example The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
        200 Whalen                           101          2
        203 Mavris                           101          2
        204 Baer                             101          2
        205 Higgins                          101          2
        206 Gietz                            205          3
        102 De Haan                          100          1
...

START WITH Examples The next example adds a START WITH clause to specify a root row for the hierarchy and an ORDER BY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3

CONNECT_BY_ROOT Examples The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 /Higgins/Gietz
Gietz           King                     3 /King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
Higgins         King                     2 /King/Kochhar/Higgins
Higgins         Kochhar                  1 /Kochhar/Higgins