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

Override Scientific format in Excel output of RTF template in BI Publisher Report

Use below syntax to achieve it, if your tag name is invoice_number


<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?INVOICE_NUMBER?></fo:bidi-override>

Same can be used for issue in Date format changes

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"> <?period_name?></fo:bidi-override>

Moving Oracle Apps into Oracle Cloud

Useful Doc:

https://apexapps.oracle.com/pls/apex/f?p=44785:141:13706953039635::::P141_PAGE_ID,P141_SECTION_ID:523,3680

Getting Started with Oracle NoSQL

https://docs.oracle.com/cd/NOSQL/get_started.htm

How to Install Dockers on windows

Please follow link of my git account:

https://github.com/abhi20aug/learn-docker/blob/master/windows/Step-1.txt