[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT rt.shipment_header_id,
GROUP_ID
FROM rcv_transactions rt
WHERE rt.request_id = p_request_id
AND rt.transaction_type = 'RECEIVE'
AND NVL(p_group_id, 0) = 0
UNION ALL
SELECT DISTINCT rt.shipment_header_id,
GROUP_ID
FROM rcv_transactions rt
WHERE rt.GROUP_ID = p_group_id
AND rt.request_id in (0, -1, p_request_id) -- for bug 8422764
AND rt.transaction_type = 'RECEIVE'
AND NVL(p_group_id, 0) <> 0;
SELECT DISTINCT rt.shipment_header_id,rt.group_id
FROM rcv_transactions rt
WHERE rt.request_id = p_request_id
AND rt.transaction_type = 'DELIVER'
AND nvl(p_group_id,0) = 0
UNION ALL
SELECT DISTINCT rt.shipment_header_id,rt.group_id
FROM rcv_transactions rt
WHERE rt.group_id = p_group_id
AND rt.request_id in (0, -1, p_request_id)
AND rt.transaction_type = 'DELIVER'
AND nvl(p_group_id,0) <> 0;
SELECT po_wf_itemkey_s.NEXTVAL
INTO l_wf_item_seq
FROM DUAL;
select po_wf_itemkey_s.nextval into l_wf_item_seq from dual;
INSERT INTO rcv_staging_table
(transaction_id,
team,
status,
transaction_request_id,
transaction_group_id,
creation_date,
created_by,
last_update_login,
request_id,
last_updated_by,
last_update_date
)
SELECT rt.transaction_id,
'JMF' team,
'PENDING' status,
rt.request_id transaction_request_id,
rt.GROUP_ID transaction_group_id,
SYSDATE creation_date,
rt.created_by,
NULL last_update_login,
NULL request_id,
0 last_updated_by,
SYSDATE last_update_date
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.GROUP_ID = p_group_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.osa_flag = 'Y'
AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
OR ( rt.transaction_type = 'CORRECT'
AND EXISTS(SELECT NULL
FROM rcv_transactions prt
WHERE prt.transaction_id = rt.parent_transaction_id
AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
);
INSERT INTO rcv_staging_table
(transaction_id,
team,
status,
transaction_request_id,
transaction_group_id,
creation_date,
created_by,
last_update_login,
request_id,
last_updated_by,
last_update_date
)
SELECT rt.transaction_id,
'JMF' team,
'PENDING' status,
rt.request_id transaction_request_id,
rt.GROUP_ID transaction_group_id,
SYSDATE creation_date,
rt.created_by,
NULL last_update_login,
NULL request_id,
0 last_updated_by,
SYSDATE last_update_date
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.request_id = p_request_id
AND ( rt.GROUP_ID = p_group_id
OR p_group_id = 0
OR p_group_id IS NULL)
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.osa_flag = 'Y'
AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
OR ( rt.transaction_type = 'CORRECT'
AND EXISTS(SELECT NULL
FROM rcv_transactions prt
WHERE prt.transaction_id = rt.parent_transaction_id
AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
);