[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Create_Update_Txns_History(
p_txns_history_rec IN OUT NOCOPY Txns_History_Record_Type,
P_xml_document_id IN NUMBER,
x_txns_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
-- local variables
l_txns_id NUMBER;
SELECT transaction_id, transaction_status
FROM MTL_TXNS_HISTORY
WHERE document_type = p_txns_history_rec.document_type AND
document_number = p_txns_history_rec.document_number AND
document_direction = p_txns_history_rec.document_direction AND
action_type = p_txns_history_rec.action_type AND
entity_number = p_txns_history_rec.entity_number AND
entity_type = p_txns_history_rec.entity_type AND
trading_partner_id = p_txns_history_rec.trading_partner_id AND
event_name = p_txns_history_rec.event_name AND
event_key = p_txns_history_rec.event_key AND
transaction_status = 'IP'
FOR UPDATE NOWAIT;
asn_debug.put_line('Entering Create_Update_Txns_History');
SELECT MTL_TXNS_HISTORY_S.nextval
INTO x_transaction_id
FROM dual;
asn_debug.put_line('Inserting into MTL_TXNS_HISTORY');
INSERT INTO MTL_TXNS_HISTORY(
TRANSACTION_ID,
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
DOCUMENT_DIRECTION,
TRANSACTION_STATUS,
ACTION_TYPE,
ENTITY_NUMBER,
ENTITY_TYPE,
TRADING_PARTNER_ID,
EVENT_NAME,
EVENT_KEY,
ITEM_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DOCUMENT_REVISION,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CLIENT_CODE)
VALUES( x_transaction_id,
p_txns_history_rec.document_type,
p_txns_history_rec.document_number,
p_txns_history_rec.document_direction,
-- k proj bmso p_txns_history_rec.transaction_status,
l_trans_status,
p_txns_history_rec.action_type,
p_txns_history_rec.entity_number,
p_txns_history_rec.entity_type,
p_txns_history_rec.trading_partner_id,
p_txns_history_rec.EVENT_NAME,
p_txns_history_rec.EVENT_KEY,
p_txns_history_rec.ITEM_TYPE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
p_txns_history_rec.DOCUMENT_REVISION,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
fnd_global.conc_request_id,
p_txns_history_rec.ATTRIBUTE_CATEGORY,
p_txns_history_rec.ATTRIBUTE1,
p_txns_history_rec.ATTRIBUTE2,
p_txns_history_rec.ATTRIBUTE3,
p_txns_history_rec.ATTRIBUTE4,
p_txns_history_rec.ATTRIBUTE5,
p_txns_history_rec.ATTRIBUTE6,
p_txns_history_rec.ATTRIBUTE7,
p_txns_history_rec.ATTRIBUTE8,
p_txns_history_rec.ATTRIBUTE9,
p_txns_history_rec.ATTRIBUTE10,
p_txns_history_rec.ATTRIBUTE11,
p_txns_history_rec.ATTRIBUTE12,
p_txns_history_rec.ATTRIBUTE13,
p_txns_history_rec.ATTRIBUTE14,
p_txns_history_rec.ATTRIBUTE15,
p_txns_history_rec.client_code);
asn_debug.put_line('Record inserted into MTL_TXNS_HISTORY');
asn_debug.put_line('Record already exists. So need to update in MTL_TXNS_HISTORY');
UPDATE MTL_TXNS_HISTORY
SET entity_number = p_txns_history_rec.entity_number,
entity_type = p_txns_history_rec.entity_type,
transaction_status = p_txns_history_rec.transaction_status,
event_name = p_txns_history_rec.event_name,
event_key = p_txns_history_rec.event_key,
item_type = p_txns_history_rec.item_type,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE,
request_id = fnd_global.conc_request_id,
attribute_category = p_txns_history_rec.ATTRIBUTE_CATEGORY,
attribute1 = p_txns_history_rec.ATTRIBUTE1,
attribute2 = p_txns_history_rec.ATTRIBUTE2,
attribute3 = p_txns_history_rec.ATTRIBUTE3,
attribute4 = p_txns_history_rec.ATTRIBUTE4,
attribute5 = p_txns_history_rec.ATTRIBUTE5,
attribute6 = p_txns_history_rec.ATTRIBUTE6,
attribute7 = p_txns_history_rec.ATTRIBUTE7,
attribute8 = p_txns_history_rec.ATTRIBUTE8,
attribute9 = p_txns_history_rec.ATTRIBUTE9,
attribute10 = p_txns_history_rec.ATTRIBUTE10,
attribute11 = p_txns_history_rec.ATTRIBUTE11,
attribute12 = p_txns_history_rec.ATTRIBUTE12,
attribute13 = p_txns_history_rec.ATTRIBUTE13,
attribute14 = p_txns_history_rec.ATTRIBUTE14,
attribute15 = p_txns_history_rec.ATTRIBUTE15
WHERE transaction_id = l_transaction_id;
asn_debug.put_line('MTL_TXNS_HISTORY record updated');
UPDATE rcv_transactions rt
SET rt.receipt_confirmation_extracted = 'Y',
rt.xml_document_id = p_txns_history_rec.document_number
WHERE rt.shipment_header_id = p_txns_history_rec.document_number
AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
AND xml_document_id is null
AND (rt.TRANSACTION_TYPE = 'DELIVER' OR
(rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
AND EXISTS (SELECT '1' FROM rcv_transactions rt2
WHERE rt.parent_transaction_id = rt2.transaction_id
AND rt2.transaction_type = 'DELIVER')
) OR
(rt.TRANSACTION_TYPE IN ('CORRECT')
AND EXISTS (SELECT '1' FROM rcv_transactions rt3
WHERE rt.parent_transaction_id = rt3.transaction_id
AND rt3.transaction_type = 'RETURN TO RECEIVING')
)
)
AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND wms_deploy.get_client_code(rsl.item_id) = p_txns_history_rec.client_code);
UPDATE rcv_transactions rt
SET rt.receipt_confirmation_extracted = 'Y',
rt.xml_document_id = p_txns_history_rec.document_number
WHERE rt.shipment_header_id = p_txns_history_rec.document_number
AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
and xml_document_id is null
AND (rt.TRANSACTION_TYPE = 'DELIVER' OR
(rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
AND EXISTS (SELECT '1' FROM rcv_transactions rt2
WHERE rt.parent_transaction_id = rt2.transaction_id
AND rt2.transaction_type = 'DELIVER')
) OR
(rt.TRANSACTION_TYPE IN ('CORRECT')
AND EXISTS (SELECT '1' FROM rcv_transactions rt3
WHERE rt.parent_transaction_id = rt3.transaction_id
AND rt3.transaction_type = 'RETURN TO RECEIVING')
)
);
asn_debug.put_line('Exiting Create_Update_Txns_History call');
END Create_Update_Txns_History;
SELECT wth.transaction_id,
wth.document_type,
wth.document_direction,
wth.document_number,
wth.entity_number,
wth.entity_type,
wth.trading_partner_id,
wth.action_type,
wth.transaction_status,
wth.event_name,
wth.event_key ,
wth.item_type,
wth.document_revision,
wth.attribute_category,
wth.attribute1,
wth.attribute2,
wth.attribute3,
wth.attribute4,
wth.attribute5,
wth.attribute6,
wth.attribute7,
wth.attribute8,
wth.attribute9,
wth.attribute10,
wth.attribute11,
wth.attribute12,
wth.attribute13,
wth.attribute14,
wth.attribute15,
wth.client_code
FROM MTL_TXNS_HISTORY wth
WHERE wth.item_type = p_item_type
and wth.event_key = p_event_key
and wth.document_direction = p_direction
and wth.document_type = p_document_type
and rownum = 1;