AR_PAYING_RELATIONSHIPS_NEW_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 CASE WHEN REGEXP_LIKE(RF.PK1_VALUE, '^[[:digit:]]+$') THEN rf.PK1_VALUE ELSE NULL END = 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 CASE WHEN REGEXP_LIKE(TOPNODESRF.PK1_VALUE, '^[[:digit:]]+$') THEN TOPNODESRF.PK1_VALUE ELSE NULL END = 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 |