[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_rows
( p_draft_id IN NUMBER,
p_notification_id IN NUMBER
) IS
d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
DELETE FROM po_notification_ctrl_draft
WHERE draft_id = p_draft_id
AND notification_id = NVL(p_notification_id, notification_id);
END delete_rows;
p_delete_flag_tbl IN PO_TBL_VARCHAR1,
x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
) IS
d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
INSERT INTO po_notification_ctrl_draft
(
draft_id,
delete_flag,
change_accepted_flag,
notification_id,
po_header_id,
start_date_active,
end_date_active,
notification_amount,
notification_condition_code,
notification_qty_percentage,
last_update_date,
last_update_login,
last_updated_by,
program_id,
program_application_id,
program_update_date,
request_id,
created_by,
creation_date,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9
)
SELECT
p_draft_id_tbl(i),
p_delete_flag_tbl(i),
NULL,
notification_id,
po_header_id,
start_date_active,
end_date_active,
notification_amount,
notification_condition_code,
notification_qty_percentage,
last_update_date,
last_update_login,
last_updated_by,
program_id,
program_application_id,
program_update_date,
request_id,
created_by,
creation_date,
attribute_category,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9
FROM po_notification_controls
WHERE notification_id = p_notification_id_tbl(i)
AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
UPDATE po_notification_ctrl_draft
SET delete_flag = p_delete_flag_tbl(i)
WHERE notification_id = p_notification_id_tbl(i)
AND draft_id = p_draft_id_tbl(i)
AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
' in draft table. Count = ' || SQL%ROWCOUNT);
p_delete_flag IN VARCHAR2,
x_record_already_exist OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
x_record_already_exist_tbl => l_record_already_exist_tbl
);
DELETE FROM po_notification_controls PNC
WHERE PNC.notification_id IN
( SELECT PNCD.notification_id
FROM po_notification_ctrl_draft PNCD
WHERE PNCD.draft_id = p_draft_id
AND PNCD.delete_flag = 'Y'
AND NVL(PNCD.change_accepted_flag, 'Y') = 'Y' );
SELECT
PNCD.draft_id,
PNCD.delete_flag,
PNCD.change_accepted_flag,
PNCD.notification_id,
PNCD.po_header_id,
PNCD.start_date_active,
PNCD.end_date_active,
PNCD.notification_amount,
PNCD.notification_condition_code,
PNCD.notification_qty_percentage,
PNCD.last_update_date,
PNCD.last_update_login,
PNCD.last_updated_by,
PNCD.program_id,
PNCD.program_application_id,
PNCD.program_update_date,
PNCD.request_id,
PNCD.created_by,
PNCD.creation_date,
PNCD.attribute_category,
PNCD.attribute1,
PNCD.attribute10,
PNCD.attribute11,
PNCD.attribute12,
PNCD.attribute13,
PNCD.attribute14,
PNCD.attribute15,
PNCD.attribute2,
PNCD.attribute3,
PNCD.attribute4,
PNCD.attribute5,
PNCD.attribute6,
PNCD.attribute7,
PNCD.attribute8,
PNCD.attribute9
FROM po_notification_ctrl_draft PNCD
WHERE PNCD.draft_id = p_draft_id
AND NVL(PNCD.change_accepted_flag, 'Y') = 'Y'
) PNCDV
ON (PNC.notification_id = PNCDV.notification_id)
WHEN MATCHED THEN
UPDATE
SET
PNC.po_header_id = PNCDV.po_header_id,
PNC.start_date_active = PNCDV.start_date_active,
PNC.end_date_active = PNCDV.end_date_active,
PNC.notification_amount = PNCDV.notification_amount,
PNC.notification_condition_code = PNCDV.notification_condition_code,
PNC.notification_qty_percentage = PNCDV.notification_qty_percentage,
PNC.last_update_date = PNCDV.last_update_date,
PNC.last_update_login = PNCDV.last_update_login,
PNC.last_updated_by = PNCDV.last_updated_by,
PNC.attribute_category = PNCDV.attribute_category,
PNC.attribute1 = PNCDV.attribute1,
PNC.attribute10 = PNCDV.attribute10,
PNC.attribute11 = PNCDV.attribute11,
PNC.attribute12 = PNCDV.attribute12,
PNC.attribute13 = PNCDV.attribute13,
PNC.attribute14 = PNCDV.attribute14,
PNC.attribute15 = PNCDV.attribute15,
PNC.attribute2 = PNCDV.attribute2,
PNC.attribute3 = PNCDV.attribute3,
PNC.attribute4 = PNCDV.attribute4,
PNC.attribute5 = PNCDV.attribute5,
PNC.attribute6 = PNCDV.attribute6,
PNC.attribute7 = PNCDV.attribute7,
PNC.attribute8 = PNCDV.attribute8,
PNC.attribute9 = PNCDV.attribute9
-- DELETE WHERE PNCDV.delete_flag = 'Y'
WHEN NOT MATCHED THEN
INSERT
(
PNC.notification_id,
PNC.po_header_id,
PNC.start_date_active,
PNC.end_date_active,
PNC.notification_amount,
PNC.notification_condition_code,
PNC.notification_qty_percentage,
PNC.last_update_date,
PNC.last_update_login,
PNC.last_updated_by,
PNC.program_id,
PNC.program_application_id,
PNC.program_update_date,
PNC.request_id,
PNC.created_by,
PNC.creation_date,
PNC.attribute_category,
PNC.attribute1,
PNC.attribute10,
PNC.attribute11,
PNC.attribute12,
PNC.attribute13,
PNC.attribute14,
PNC.attribute15,
PNC.attribute2,
PNC.attribute3,
PNC.attribute4,
PNC.attribute5,
PNC.attribute6,
PNC.attribute7,
PNC.attribute8,
PNC.attribute9
)
VALUES
(
PNCDV.notification_id,
PNCDV.po_header_id,
PNCDV.start_date_active,
PNCDV.end_date_active,
PNCDV.notification_amount,
PNCDV.notification_condition_code,
PNCDV.notification_qty_percentage,
PNCDV.last_update_date,
PNCDV.last_update_login,
PNCDV.last_updated_by,
PNCDV.program_id,
PNCDV.program_application_id,
PNCDV.program_update_date,
PNCDV.request_id,
PNCDV.created_by,
PNCDV.creation_date,
PNCDV.attribute_category,
PNCDV.attribute1,
PNCDV.attribute10,
PNCDV.attribute11,
PNCDV.attribute12,
PNCDV.attribute13,
PNCDV.attribute14,
PNCDV.attribute15,
PNCDV.attribute2,
PNCDV.attribute3,
PNCDV.attribute4,
PNCDV.attribute5,
PNCDV.attribute6,
PNCDV.attribute7,
PNCDV.attribute8,
PNCDV.attribute9
) WHERE NVL(PNCDV.delete_flag, 'N') <> 'Y';
SELECT 1
INTO l_dummy
FROM po_notification_ctrl_draft
WHERE notification_id = p_notification_id
AND draft_id = p_draft_id
FOR UPDATE NOWAIT;
SELECT 1
INTO l_dummy
FROM po_notification_controls
WHERE notification_id = p_notification_id
FOR UPDATE NOWAIT;