[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
INTO l_dummy
FROM OE_HOLD_DEFINITIONS
WHERE HOLD_ID = p_hold_id
AND SYSDATE
BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
AND NVL(END_DATE_ACTIVE, SYSDATE );
SELECT 'x'
INTO l_dummy
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id;
SELECT 'x'
INTO l_dummy
FROM OE_SHIP_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id
UNION
SELECT 'x'
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id;
SELECT 'x'
INTO l_dummy
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_entity_id;
SELECT 'x'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE header_id = p_entity_id;
SELECT 'x'
INTO l_dummy
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id2;
SELECT 'x'
INTO l_dummy
FROM OE_SHIP_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id2
UNION
SELECT 'x'
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_entity_id2;
SELECT 'x'
INTO l_dummy
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_entity_id2;
SELECT 'x'
INTO l_dummy
FROM OE_ORDER_HEADERS
WHERE header_id = p_entity_id2;
SELECT count(*)
INTO l_count
FROM OE_HOLD_SOURCES
WHERE hold_id = p_hold_source_rec.hold_id
AND hold_entity_code = p_hold_source_rec.hold_entity_code
AND hold_entity_id = p_hold_source_rec.hold_entity_id
AND nvl(hold_entity_code2, 'NO_ENTITY_CODE2') =
nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(hold_entity_id2, -99) =
nvl(p_hold_source_rec.hold_entity_id2, -99)
AND NVL(released_flag, 'N') = 'N';
SELECT OE_HOLD_SOURCES_S.NEXTVAL
INTO x_hold_source_id
FROM DUAL;
INSERT INTO OE_HOLD_SOURCES_ALL
( HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, HOLD_COMMENT
, ORG_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, HOLD_RELEASE_ID
,HOLD_ENTITY_CODE2
,HOLD_ENTITY_ID2
)
VALUES
( x_hold_source_id
, sysdate
, l_user_id
, sysdate
, l_user_id
, p_hold_source_rec.LAST_UPDATE_LOGIN
, p_hold_source_rec.PROGRAM_APPLICATION_ID
, p_hold_source_rec.PROGRAM_ID
, p_hold_source_rec.PROGRAM_UPDATE_DATE
, p_hold_source_rec.REQUEST_ID
, p_hold_source_rec.HOLD_ID
, p_hold_source_rec.HOLD_ENTITY_CODE
, p_hold_source_rec.HOLD_ENTITY_ID
, p_hold_source_rec.HOLD_UNTIL_DATE
, p_hold_source_rec.RELEASED_FLAG
, p_hold_source_rec.HOLD_COMMENT
, l_org_id
, p_hold_source_rec.CONTEXT
, p_hold_source_rec.ATTRIBUTE1
, p_hold_source_rec.ATTRIBUTE2
, p_hold_source_rec.ATTRIBUTE3
, p_hold_source_rec.ATTRIBUTE4
, p_hold_source_rec.ATTRIBUTE5
, p_hold_source_rec.ATTRIBUTE6
, p_hold_source_rec.ATTRIBUTE7
, p_hold_source_rec.ATTRIBUTE8
, p_hold_source_rec.ATTRIBUTE9
, p_hold_source_rec.ATTRIBUTE10
, p_hold_source_rec.ATTRIBUTE11
, p_hold_source_rec.ATTRIBUTE12
, p_hold_source_rec.ATTRIBUTE13
, p_hold_source_rec.ATTRIBUTE14
, p_hold_source_rec.ATTRIBUTE15
, p_hold_source_rec.HOLD_RELEASE_ID
,p_hold_source_rec.HOLD_ENTITY_CODE2
,p_hold_source_rec.HOLD_ENTITY_ID2
);
SELECT HS.HOLD_SOURCE_ID
FROM OE_HOLD_SOURCES HS
WHERE HS.HOLD_ID = p_hold_id
AND HS.RELEASED_FLAG = 'N'
AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
AND HS.HOLD_ENTITY_CODE = p_entity_code
AND HS.HOLD_ENTITY_ID = p_entity_id
AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
nvl(p_entity_code2, 'NO_ENTITY_CODE2')
AND nvl(HS.HOLD_ENTITY_ID2, -99) =
nvl(p_entity_id2, -99);
OE_Hold_Sources_Pvt.Insert_Hold_Release
( p_hold_release_rec => l_hold_release_rec
, p_validation_level => p_validation_level
, x_hold_release_id => l_hold_release_id
, x_return_status => x_return_status
);
UPDATE oe_order_holds_all
SET hold_release_id = l_hold_release_id
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_DATE = sysdate
WHERE hold_source_id = l_hold_source_id
AND hold_release_id IS NULL;
UPDATE oe_hold_sources
SET hold_release_id = l_hold_release_id
, released_flag = 'Y'
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_DATE = sysdate
WHERE hold_source_id = l_hold_source_id;
SELECT
HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, HOLD_COMMENT
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORG_ID
, HOLD_RELEASE_ID
, HOLD_ENTITY_CODE2
, HOLD_ENTITY_ID2
FROM OE_HOLD_SOURCES
WHERE hold_source_id IN (SELECT hold_source_id
FROM OE_ORDER_HOLDS
WHERE header_id = p_header_id
AND line_id IS NULL
AND hold_release_id IS NULL
);
SELECT
HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, HOLD_COMMENT
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORG_ID
, HOLD_RELEASE_ID
, HOLD_ENTITY_CODE2
, HOLD_ENTITY_ID2
FROM OE_HOLD_SOURCES
WHERE hold_source_id IN (SELECT hold_source_id
FROM OE_ORDER_HOLDS
WHERE line_id = p_line_id
AND hold_release_id IS NULL
);
PROCEDURE Insert_Hold_Release
( p_hold_release_rec IN OE_Hold_Sources_Pvt.Hold_Release_Rec
, p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL
, x_hold_release_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
--l_hold_entity_id NUMBER;
oe_debug_pub.add( 'IN INSERT_HOLD_RELEASE' ) ;
SAVEPOINT insert_hold_release;
SELECT 'x'
INTO l_dummy
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'RELEASE_REASON'
AND LOOKUP_CODE = p_hold_release_rec.release_reason_code;
oe_debug_pub.add( 'BEFORE INSERT' ) ;
SELECT OE_HOLD_RELEASES_S.NEXTVAL
INTO x_hold_release_id
FROM DUAL;
INSERT INTO OE_HOLD_RELEASES
( HOLD_RELEASE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_SOURCE_ID
-- , HOLD_ENTITY_ID
-- , HOLD_ENTITY_CODE
, RELEASE_REASON_CODE
, RELEASE_COMMENT
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
)
VALUES
( x_hold_release_id
, sysdate
, l_user_id
, sysdate
, l_user_id
, p_hold_release_rec.LAST_UPDATE_LOGIN
, p_hold_release_rec.PROGRAM_APPLICATION_ID
, p_hold_release_rec.PROGRAM_ID
, p_hold_release_rec.PROGRAM_UPDATE_DATE
, p_hold_release_rec.REQUEST_ID
, p_hold_release_rec.HOLD_SOURCE_ID
-- To_do : Remove the following code when the redundant columns hold_entity_id
-- and hold_entity_code are removed from OE_HOLD_RELEASES.
-- , l_hold_entity_id
-- , l_hold_entity_code
, p_hold_release_rec.RELEASE_REASON_CODE
, p_hold_release_rec.RELEASE_COMMENT
, p_hold_release_rec.CONTEXT
, p_hold_release_rec.ATTRIBUTE1
, p_hold_release_rec.ATTRIBUTE2
, p_hold_release_rec.ATTRIBUTE3
, p_hold_release_rec.ATTRIBUTE4
, p_hold_release_rec.ATTRIBUTE5
, p_hold_release_rec.ATTRIBUTE6
, p_hold_release_rec.ATTRIBUTE7
, p_hold_release_rec.ATTRIBUTE8
, p_hold_release_rec.ATTRIBUTE9
, p_hold_release_rec.ATTRIBUTE10
, p_hold_release_rec.ATTRIBUTE11
, p_hold_release_rec.ATTRIBUTE12
, p_hold_release_rec.ATTRIBUTE13
, p_hold_release_rec.ATTRIBUTE14
, p_hold_release_rec.ATTRIBUTE15
);
oe_debug_pub.add( 'AFTER INSERT' ) ;
ROLLBACK TO insert_hold_release;
ROLLBACK TO insert_hold_release;
,'Insert_Hold_Release');
ROLLBACK TO insert_hold_release;
END Insert_Hold_Release;
SELECT SITE.SITE_USE_CODE
INTO l_site_code
FROM HZ_CUST_SITE_USES SITE, -- Bug 2138398
HR_ORGANIZATION_INFORMATION INFO
WHERE INFO.ORGANIZATION_ID = p_entity_id
AND INFO.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
AND SITE.SITE_USE_ID = TO_NUMBER ( INFO.ORG_INFORMATION2 );