[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
asn_debug.put_line('Entering UPDATE_RTI_WITH_LC.update_rti' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
asn_debug.put_line('no of records to be updated : ' || p_int_rec.COUNT);
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
asn_debug.put_line('group_id to be updated with: ' || l_group_id);
UPDATE rcv_transactions_interface
SET lcm_shipment_line_id = p_int_rec(i).lcm_shipment_line_id,
unit_landed_cost = p_int_rec(i).unit_landed_cost
WHERE interface_transaction_id = p_int_rec(i).interface_id
AND processing_status_code = 'LC_INTERFACED'
AND lcm_shipment_line_id is NULL
AND unit_landed_cost is NULL;
asn_debug.put_line('updated interface id: ' || p_int_rec(i).interface_id);
asn_debug.put_line('Updated : '||SQL%ROWCOUNT||' rows in RTI');
asn_debug.put_line('did not update interface id: ' || p_int_rec(i).interface_id);
asn_debug.put_line('Updated : '||l_row_count||' rows in RTI');
select lpn_group_id
into l_lpn_group_id
from rcv_transactions_interface
where interface_transaction_id = p_int_rec(i).interface_id;
select count(1)
into l_lpn_group_rti_count
from rcv_transactions_interface
where lpn_group_id = l_lpn_group_id
and (lcm_shipment_line_id is NULL OR unit_landed_cost is NULL)
and processing_status_code in ('LC_PENDING','LC_INTERFACED');
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING',
group_id = l_group_id
WHERE lpn_group_id = l_lpn_group_id
and ( ( lcm_shipment_line_id is not NULL
and unit_landed_cost is not NULL
and processing_status_code = 'LC_INTERFACED'
)
OR processing_status_code = 'WLC_PENDING'
);
UPDATE rcv_headers_interface rhi
SET rhi.processing_status_code = 'PENDING',
group_id = l_group_id -- Bug 7677015
WHERE rhi.processing_status_code <> 'RUNNING'
and exists( select 'exists' from rcv_transactions_interface rti
WHERE rti.lpn_group_id = l_lpn_group_id
and rti.header_interface_id IS NOT NULL
and rti.header_interface_id = rhi.header_interface_id
and ( ( rti.lcm_shipment_line_id is not NULL
and rti.unit_landed_cost is not NULL
and rti.processing_status_code = 'PENDING' -- Bug 7677015
)
OR rti.processing_status_code = 'WLC_PENDING'
)
);
asn_debug.put_line('no of rtis updated: '||sql%rowcount||' for the lpn group id: '||l_lpn_group_id);
select rhi.asn_type, rhi.header_interface_id, rhi.group_id
into l_asn_type, l_header_interface_id, l_rhi_group_id
from rcv_transactions_interface rti, rcv_headers_interface rhi
where rhi.header_interface_id = rti.header_interface_id
and rti.interface_transaction_id = p_int_rec(i).interface_id;
select count(1)
into l_lpn_group_rti_count
from rcv_transactions_interface
where header_interface_id = l_header_interface_id
and group_id = l_rhi_group_id
and (lcm_shipment_line_id is NULL OR unit_landed_cost is NULL)
and processing_status_code in ('LC_PENDING','LC_INTERFACED');
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING',
group_id = l_group_id
WHERE header_interface_id = l_header_interface_id
AND group_id = l_rhi_group_id
and (( lcm_shipment_line_id is not NULL
and unit_landed_cost is not NULL
and processing_status_code = 'LC_INTERFACED'
)
OR processing_status_code = 'WLC_PENDING'
);
UPDATE rcv_headers_interface rhi
SET rhi.processing_status_code = 'PENDING',
group_id = l_group_id -- Bug 7677015
WHERE rhi.processing_status_code <> 'RUNNING'
and exists( select 'exists' from rcv_transactions_interface rti
WHERE rti.header_interface_id = rhi.header_interface_id
and rti.header_interface_id = l_header_interface_id
and group_id = l_rhi_group_id
and ( ( rti.lcm_shipment_line_id is not NULL
and rti.unit_landed_cost is not NULL
and rti.processing_status_code = 'PENDING' -- Bug 7677015
)
OR rti.processing_status_code = 'WLC_PENDING'
)
);
asn_debug.put_line('rtis updated: '||sql%rowcount||' for the header interface id: '||l_header_interface_id);
UPDATE rcv_transactions_interface
SET processing_status_code = 'PENDING',
group_id = l_group_id
WHERE interface_transaction_id = p_int_rec(i).interface_id
AND processing_status_code = 'LC_INTERFACED'
and lcm_shipment_line_id is not NULL
and unit_landed_cost is not NULL;
UPDATE rcv_headers_interface rhi
SET rhi.processing_status_code = 'PENDING',
group_id = l_group_id -- Bug 7677015
WHERE rhi.processing_status_code <> 'RUNNING'
and exists( select 'exists' from rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_int_rec(i).interface_id
and rti.header_interface_id IS NOT NULL
and rti.header_interface_id = rhi.header_interface_id
AND processing_status_code = 'PENDING' -- Bug 7677015
and lcm_shipment_line_id is not NULL
and unit_landed_cost is not NULL
);
asn_debug.put_line('rti updated for the interface id: '||p_int_rec(i).interface_id);
asn_debug.put_line('request id: '||l_req_id, 'insertlcm', '9');
asn_debug.put_line('encountered an error in update_rti: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
asn_debug.put_line('Updated : '||l_row_count||' rows in RTI');
END update_rti;
END RCV_UPDATE_RTI_LC;