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

Hierarchy Query

select level, self_id, child_id, self_name, child_name

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

start with x.self_id  = <start with party id>

connect by prior x.child_id = x.self_id

order by 1,2,3,4;


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

select  
level_info
--,self_id
--,child_id
--,self_name
--,child_name
,(select party_name from hz_parties where party_id = 179695 ) 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
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
start with x.self_id  = 179695
connect by nocycle prior x.child_id = x.self_id)
order by 1,2,3,4;

HZ_RELATIONSHIPS SUBJECT_ID OBJECT_ID Explaination

https://community.oracle.com/thread/574250


Scenario: Relationship between Parties; this relationship is between parties of type ‘PERSON’ and ‘ORGANIZATION’ (John is Employee of Vision)

Before creating relationship, the above party records should exist in TCA. 

Where they exist? 
They exist in HZ_PARTIES table. 
What is the primary key in the HZ_PARTIES to identify these records? 
It is PARTY_ID
Is HZ_PARTIES part of FND_OBJECTS?
Yes

Two records are created in HZ_PARTIES for Vision and John with the PARTY_ID as ‘1234’ and ‘5678’ before you create relationship between them. 

When you create “EMPLOYEE” relationship between them, you have to pass the following values to the API. 


OBJECT_TABLE_NAME = ‘HZ_PARTIES
OBJECT_ID = PARTY_ID i.e.1234
OBJECT_TYPE = ‘ORGANIZATION’ i.e. PARTY TYPE

SUBJECT_TABLE_NAME = ‘HZ_PARTIES
SUBJECT_ID = PARTY_ID i.e.5678
SUBJECT_TYPE = ‘PERSON’ i.e. PARTY TYPE

To answer you question

The purpose of these parameters is to identify the entities and the primary key between which you are creating relationship. Technically, you are creating relationship between 2 database records, which already created and are uniquely identified by a primary key in a table. 

In the above example, the API validates the values ‘1234’ and ‘5678’ against PARTY_ID of HZ_PARTIES and makes sure that those records exist in that table. The reason being, you cannot create relationship between Vision and John if they do not exist. 

The primary key = OBJECT_ID/SUBJECT_ID 
Table name = OBJECT_TABLE_NAME/SUBJECT_TABLE_NAME. 

Note that, the above relationship is between the 2 records existing in HZ_PARTIES table.

You can also use other entities records to create relationship if those entities are defined in FND_OBJECTS. It is normal practice that mostly relationships are created between parties ('ORGANIZATION’/‘PERSON’ and ‘PERSON’/’PERSON’. )

To create relationship between other entities you have to do some setups. 

Hope this helps.

Ram

Handle Not Equal Condition in If statement

Requirement: Hide the row based upon the value in swift, if null don't display row itself

C Swift Code:

swift EC

C => <?if:not(SwiftCode="")?>

EC=> <?end if?>



----------------------------------------------
some cases
<?if:SwiftCode="XXXXX"?> --> worked

<?if:SwiftCode=""?>  -> working

<?if:SwiftCode!=""?> -> did not work

Equality worked but != does not work, use not() instead

<? if xdofx:length(SwiftCode)!=0?> swift<?end if?>  -- worked