1. Tables and Views for Financials
  2. AR_PAYING_RELATIONSHIPS_V

AR_PAYING_RELATIONSHIPS_V

Details

  • Schema: FUSION

  • Object owner: AR

  • Object type: VIEW

Columns

Name

PARTY_ID

RELATED_PARTY_ID

RELATED_CUST_ACCOUNT_ID

HIERARCHY_TYPE

RELATIONSHIP_TYPE_GROUP_NAME

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement

SELECT RF.ANCESTOR_PK1_VALUE PARTY_ID,

RF.PK1_VALUE RELATED_PARTY_ID,

RELACC.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID,

'PARTY_REL_GRP_AR_PAY_TOP_DOWN' HIERARCHY_TYPE,

'PARTY_REL_GRP_AR_PAY_TOP_DOWN' RELATIONSHIP_TYPE_GROUP_NAME,

GREATEST(VER.EFFECTIVE_START_DATE, ASSIGN.START_DATE_ACTIVE) EFFECTIVE_START_DATE,

LEAST(VER.EFFECTIVE_END_DATE, NVL(ASSIGN.END_DATE_ACTIVE, TO_DATE('4712/12/31', 'YYYY/MM/DD'))) EFFECTIVE_END_DATE

FROM AR_PAY_REL_ASSIGNMENTS ASSIGN,

HZ_LOOKUPS HL,

FND_TREE_STRUCTURE TS,

FND_TREE TR,

FND_TREE_VERSION VER,

FND_TREE_NODE NODE,

FND_TREE_NODE_RF RF,

HZ_CUST_ACCOUNTS RELACC

/** Hierarchy type Pay TopDown (Pay Below) **/

WHERE ASSIGN.ASSIGNMENT_TYPE = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN'

AND HL.LOOKUP_TYPE = 'PARTY_HIERARCHY_TYPE'

AND ASSIGN.TREE_STRUCTURE_CODE = HL.LOOKUP_CODE

AND ASSIGN.TREE_STRUCTURE_CODE = TS.TREE_STRUCTURE_CODE

AND TS.NUMBER_OF_TREES > 0

AND TS.STATUS = 'ACTIVE'

AND TS.TREE_STRUCTURE_CODE = TR.TREE_STRUCTURE_CODE

AND TR.TREE_STRUCTURE_CODE = VER.TREE_STRUCTURE_CODE

AND TR.TREE_CODE = VER.TREE_CODE

AND VER.TREE_STRUCTURE_CODE = NODE.TREE_STRUCTURE_CODE

AND VER.TREE_CODE = NODE.TREE_CODE

AND VER.TREE_VERSION_ID = NODE.TREE_VERSION_ID

AND NODE.TREE_STRUCTURE_CODE = RF.TREE_STRUCTURE_CODE

AND ASSIGN.TREE_STRUCTURE_CODE = RF.TREE_STRUCTURE_CODE

AND NODE.TREE_CODE = RF.TREE_CODE

AND NODE.TREE_VERSION_ID = RF.TREE_VERSION_ID

AND NODE.TREE_NODE_ID = RF.ANCESTOR_TREE_NODE_ID

/** Get Accounts for all children from accounts table ***/

AND RF.PK1_VALUE = RELACC.PARTY_ID

/***

UNION ALL

/***

/** Hierarchy type Pay Any **/

/** Step 1: Reach to the top nodes, get all children nodes' parties and accounts **/

/** Step 2: Identify base party id from each node of the tree version so that **/

/** all parties n accounts selected get related with each of those **/

SELECT NODE.PK1_START_VALUE PARTY_ID,

TOPNODESRF.PK1_VALUE RELATED_PARTY_ID,

RELACC.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID,

'PARTY_REL_GRP_AR_PAY_ANY' HIERARCHY_TYPE,

'PARTY_REL_GRP_AR_PAY_ANY' RELATIONSHIP_TYPE_GROUP_NAME,

GREATEST(VER.EFFECTIVE_START_DATE, ASSIGN.START_DATE_ACTIVE) EFFECTIVE_START_DATE,

LEAST(VER.EFFECTIVE_END_DATE, NVL(ASSIGN.END_DATE_ACTIVE, TO_DATE('4712/12/31', 'YYYY/MM/DD'))) EFFECTIVE_END_DATE

FROM AR_PAY_REL_ASSIGNMENTS ASSIGN,

HZ_LOOKUPS HL,

FND_TREE_STRUCTURE TS,

FND_TREE TR,

FND_TREE_VERSION VER,

FND_TREE_NODE NODE,

FND_TREE_NODE TOPNODES,

FND_TREE_NODE_RF TOPNODESRF,

HZ_CUST_ACCOUNTS RELACC

WHERE ASSIGN.ASSIGNMENT_TYPE = 'PARTY_REL_GRP_AR_PAY_ANY'

AND HL.LOOKUP_TYPE = 'PARTY_HIERARCHY_TYPE'

AND ASSIGN.TREE_STRUCTURE_CODE = HL.LOOKUP_CODE

AND ASSIGN.TREE_STRUCTURE_CODE = TS.TREE_STRUCTURE_CODE

AND TS.NUMBER_OF_TREES > 0

AND TS.STATUS = 'ACTIVE'

AND TS.TREE_STRUCTURE_CODE = TR.TREE_STRUCTURE_CODE

AND TR.TREE_STRUCTURE_CODE = VER.TREE_STRUCTURE_CODE

AND TR.TREE_CODE = VER.TREE_CODE

/** Step 1: Reach to the top nodes, get all children nodes' parties n accounts **/

/** Step 1.1: Reach root(top) nodes of selected tree versions **/

AND VER.TREE_STRUCTURE_CODE = TOPNODES.TREE_STRUCTURE_CODE

AND VER.TREE_CODE = TOPNODES.TREE_CODE

AND VER.TREE_VERSION_ID = TOPNODES.TREE_VERSION_ID

AND TOPNODES.PARENT_TREE_NODE_ID IS NULL

AND TOPNODES.PARENT_PK1_VALUE IS NULL

/** Step 1.2: Apply Pay top down to get all children for top nodes **/

AND TOPNODES.TREE_STRUCTURE_CODE = TOPNODESRF.TREE_STRUCTURE_CODE

AND TOPNODES.TREE_CODE = TOPNODESRF.TREE_CODE

AND TOPNODES.TREE_VERSION_ID = TOPNODESRF.TREE_VERSION_ID

AND TOPNODES.TREE_NODE_ID = TOPNODESRF.ANCESTOR_TREE_NODE_ID

/** Step 1.3: Get all accounts from accounts table for related party **/

AND TOPNODESRF.PK1_VALUE = RELACC.PARTY_ID

/** Step 2: Identify base party id from each node of the tree version so that **/

/** all parties n accounts selected get related with each of those **/

AND VER.TREE_STRUCTURE_CODE = NODE.TREE_STRUCTURE_CODE

AND ASSIGN.TREE_STRUCTURE_CODE = NODE.TREE_STRUCTURE_CODE

AND VER.TREE_CODE = NODE.TREE_CODE

AND VER.TREE_VERSION_ID = NODE.TREE_VERSION_ID