APPS.HZ_HIERARCHY_NODES_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4

PROCEDURE Insert_Row (
    X_ROWID                      OUT NOCOPY     ROWID,
    X_HIERARCHY_TYPE             IN      VARCHAR2,
    X_PARENT_ID                  IN      NUMBER,
    X_PARENT_TABLE_NAME          IN      VARCHAR2,
    X_PARENT_OBJECT_TYPE         IN      VARCHAR2,
    X_CHILD_ID                   IN      NUMBER,
    X_CHILD_TABLE_NAME           IN      VARCHAR2,
    X_CHILD_OBJECT_TYPE          IN      VARCHAR2,
    X_LEVEL_NUMBER               IN      NUMBER,
    X_TOP_PARENT_FLAG            IN      VARCHAR2,
    X_LEAF_CHILD_FLAG            IN      VARCHAR2,
    X_EFFECTIVE_START_DATE       IN      DATE,
    X_EFFECTIVE_END_DATE         IN      DATE,
    X_STATUS                     IN      VARCHAR2,
    X_RELATIONSHIP_ID            IN      NUMBER,
    X_ACTUAL_CONTENT_SOURCE      IN      VARCHAR2
) IS

BEGIN

    INSERT INTO HZ_HIERARCHY_NODES (
        HIERARCHY_TYPE,
        PARENT_ID,
        PARENT_TABLE_NAME,
        PARENT_OBJECT_TYPE,
        CHILD_ID,
        CHILD_TABLE_NAME,
        CHILD_OBJECT_TYPE,
        LEVEL_NUMBER,
        TOP_PARENT_FLAG,
        LEAF_CHILD_FLAG,
        EFFECTIVE_START_DATE,
        EFFECTIVE_END_DATE,
        STATUS,
        RELATIONSHIP_ID,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,
        ACTUAL_CONTENT_SOURCE
    )
    VALUES (
        DECODE( X_HIERARCHY_TYPE, FND_API.G_MISS_CHAR, NULL, X_HIERARCHY_TYPE ),
        DECODE( X_PARENT_ID, FND_API.G_MISS_NUM, NULL, X_PARENT_ID ),
        DECODE( X_PARENT_TABLE_NAME, FND_API.G_MISS_CHAR, NULL, X_PARENT_TABLE_NAME ),
        DECODE( X_PARENT_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL,  X_PARENT_OBJECT_TYPE ),
        DECODE( X_CHILD_ID, FND_API.G_MISS_NUM, NULL, X_CHILD_ID ),
        DECODE( X_CHILD_TABLE_NAME, FND_API.G_MISS_CHAR, NULL, X_CHILD_TABLE_NAME ),
        DECODE( X_CHILD_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_CHILD_OBJECT_TYPE ),
        DECODE( X_LEVEL_NUMBER, FND_API.G_MISS_NUM, NULL, X_LEVEL_NUMBER ),
        DECODE( X_TOP_PARENT_FLAG, FND_API.G_MISS_CHAR, NULL, X_TOP_PARENT_FLAG ),
        DECODE( X_LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR, NULL, X_LEAF_CHILD_FLAG ),
        DECODE( X_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), X_EFFECTIVE_START_DATE ),
        DECODE( X_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), X_EFFECTIVE_END_DATE ),
--      Bug#11871389
--      DECODE( X_STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
        DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
        DECODE( X_RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
        HZ_UTILITY_V2PUB.CREATED_BY,
        HZ_UTILITY_V2PUB.CREATION_DATE,
        HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
        HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
        HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
        DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
    ) RETURNING
        ROWID
    INTO
        X_ROWID;

Line: 75

END Insert_Row;

Line: 77

PROCEDURE Update_Row (
    X_Rowid                      IN OUT NOCOPY  VARCHAR2,
    X_HIERARCHY_TYPE             IN      VARCHAR2,
    X_PARENT_ID                  IN      NUMBER,
    X_PARENT_TABLE_NAME          IN      VARCHAR2,
    X_PARENT_OBJECT_TYPE         IN      VARCHAR2,
    X_CHILD_ID                   IN      NUMBER,
    X_CHILD_TABLE_NAME           IN      VARCHAR2,
    X_CHILD_OBJECT_TYPE          IN      VARCHAR2,
    X_LEVEL_NUMBER               IN      NUMBER,
    X_TOP_PARENT_FLAG            IN      VARCHAR2,
    X_LEAF_CHILD_FLAG            IN      VARCHAR2,
    X_EFFECTIVE_START_DATE       IN      DATE,
    X_EFFECTIVE_END_DATE         IN      DATE,
    X_STATUS                     IN      VARCHAR2,
    X_RELATIONSHIP_ID            IN      NUMBER,
    X_ACTUAL_CONTENT_SOURCE      IN     VARCHAR2
) IS

BEGIN

    UPDATE HZ_HIERARCHY_NODES SET
        HIERARCHY_TYPE = DECODE( X_HIERARCHY_TYPE, NULL, HIERARCHY_TYPE, FND_API.G_MISS_NUM, HIERARCHY_TYPE, X_HIERARCHY_TYPE ),
        PARENT_ID = DECODE( X_PARENT_ID, NULL, PARENT_ID, FND_API.G_MISS_NUM, PARENT_ID, X_PARENT_ID ),
        PARENT_TABLE_NAME = DECODE( X_PARENT_TABLE_NAME, NULL, PARENT_TABLE_NAME, FND_API.G_MISS_CHAR, PARENT_TABLE_NAME, X_PARENT_TABLE_NAME ),
        PARENT_OBJECT_TYPE = DECODE( X_PARENT_OBJECT_TYPE, NULL, PARENT_OBJECT_TYPE, FND_API.G_MISS_CHAR, PARENT_OBJECT_TYPE, X_PARENT_OBJECT_TYPE ),
        CHILD_ID = DECODE( X_CHILD_ID, NULL, CHILD_ID, FND_API.G_MISS_NUM, CHILD_ID, X_CHILD_ID ),
        CHILD_TABLE_NAME = DECODE( X_CHILD_TABLE_NAME, NULL, CHILD_TABLE_NAME, FND_API.G_MISS_CHAR, CHILD_TABLE_NAME, X_CHILD_TABLE_NAME ),
        CHILD_OBJECT_TYPE = DECODE( X_CHILD_OBJECT_TYPE, NULL, CHILD_OBJECT_TYPE, FND_API.G_MISS_CHAR, CHILD_OBJECT_TYPE, X_CHILD_OBJECT_TYPE ),
        LEVEL_NUMBER = DECODE( X_LEVEL_NUMBER, NULL, LEVEL_NUMBER, FND_API.G_MISS_NUM, NULL, X_LEVEL_NUMBER ),
        TOP_PARENT_FLAG = DECODE( X_TOP_PARENT_FLAG, NULL, TOP_PARENT_FLAG, FND_API.G_MISS_CHAR, NULL, X_TOP_PARENT_FLAG ),
        LEAF_CHILD_FLAG = DECODE( X_LEAF_CHILD_FLAG, NULL, LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR, NULL, X_LEAF_CHILD_FLAG ),
        EFFECTIVE_START_DATE = DECODE( X_EFFECTIVE_START_DATE, NULL, EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_START_DATE ),
        EFFECTIVE_END_DATE = DECODE( X_EFFECTIVE_END_DATE, NULL, EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_END_DATE ),
        STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
        RELATIONSHIP_ID = DECODE( X_RELATIONSHIP_ID, NULL, RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
        LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
        LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
        CREATION_DATE = CREATION_DATE,
        CREATED_BY = CREATED_BY,
        LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
        ACTUAL_CONTENT_SOURCE = DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
    WHERE ROWID = X_Rowid;

Line: 125

END Update_Row;

Line: 128

PROCEDURE Select_Row (
    X_HIERARCHY_TYPE             IN      VARCHAR2,
    X_PARENT_ID                  IN      NUMBER,
    X_PARENT_TABLE_NAME          IN      VARCHAR2,
    X_PARENT_OBJECT_TYPE         IN      VARCHAR2,
    X_CHILD_ID                   IN      NUMBER,
    X_CHILD_TABLE_NAME           IN      VARCHAR2,
    X_CHILD_OBJECT_TYPE          IN      VARCHAR2,
    X_EFFECTIVE_START_DATE       IN      DATE,
    X_EFFECTIVE_END_DATE         IN      DATE,
    X_LEVEL_NUMBER               OUT NOCOPY     NUMBER,
    X_TOP_PARENT_FLAG            OUT NOCOPY     VARCHAR2,
    X_LEAF_CHILD_FLAG            OUT NOCOPY     VARCHAR2,
    X_STATUS                     OUT NOCOPY     VARCHAR2,
    X_RELATIONSHIP_ID            OUT NOCOPY     NUMBER
) IS

BEGIN

    SELECT
        NVL( LEVEL_NUMBER, FND_API.G_MISS_NUM ),
        NVL( TOP_PARENT_FLAG, FND_API.G_MISS_CHAR ),
        NVL( LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR ),
        NVL( STATUS, FND_API.G_MISS_CHAR ),
        NVL( RELATIONSHIP_ID, FND_API.G_MISS_NUM )
    INTO
        X_LEVEL_NUMBER,
        X_TOP_PARENT_FLAG,
        X_LEAF_CHILD_FLAG,
        X_STATUS,
        X_RELATIONSHIP_ID
    FROM HZ_HIERARCHY_NODES
    WHERE HIERARCHY_TYPE = X_HIERARCHY_TYPE
	AND PARENT_ID = X_PARENT_ID
        AND PARENT_TABLE_NAME = X_PARENT_TABLE_NAME
	AND PARENT_OBJECT_TYPE = X_PARENT_OBJECT_TYPE
	AND CHILD_ID = X_CHILD_ID
        AND CHILD_TABLE_NAME = X_CHILD_TABLE_NAME
	AND CHILD_OBJECT_TYPE = X_CHILD_OBJECT_TYPE
        AND EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE
        AND EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE ;

Line: 177

END Select_Row;