[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_lpn_group BOOLEAN := FALSE;
SELECT DISTINCT (GROUP_ID)
FROM rcv_transactions_interface
WHERE processing_request_id = p_request_id
AND processing_status_code = 'RUNNING';
SELECT DISTINCT (to_organization_id)
FROM rcv_transactions_interface
WHERE processing_request_id = p_request_id
AND (group_id = p_group_id or p_group_id = 0)
AND processing_status_code = 'RUNNING'
AND to_organization_id IS NOT NULL;
SELECT header_interface_id
FROM (SELECT rsh.header_interface_id,
DECODE(NVL(poll.outsourced_assembly, get_oe_osa_flag(rti.oe_order_line_id)),
1, 1,
NULL
) osa_flag
FROM rcv_headers_interface rsh,
rcv_transactions_interface rti,
po_line_locations_all poll
WHERE rsh.asn_type = 'ASBN'
AND rsh.header_interface_id = rti.header_interface_id
AND poll.line_location_id (+) = rti.po_line_location_id
AND rti.processing_status_code = 'RUNNING')
GROUP BY header_interface_id
HAVING COUNT(*) > COUNT(osa_flag)
AND COUNT(osa_flag) > 0;
SELECT *
FROM rcv_headers_interface
WHERE NVL(asn_type, 'STD') IN('ASN', 'ASBN', 'STD', 'WC')
AND processing_status_code IN('RUNNING', 'SUCCESS','ERROR','PENDING')
AND(NVL(validation_flag, 'N') = 'Y'
OR processing_status_code = 'SUCCESS') -- include success row for multi-line asn
AND(processing_request_id IS NULL
OR processing_request_id = x_request_id)
AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id); */
1) The following update statement performs badly in ONLINE mode,
as the processing_request_id of RTI will be null.
This update statement is required only in case of BATCH mode
to clear off the lpn references for Non-WMS organizations in case
user populated the lpn references by mistake.
ONLINE mode is used programatically by the application code, so it is
not possible to get lpn references for Non wms orgn.
So, we can safely skip the following code in case of ONLINE mode.
For ONLINE mode, request_id will be null and ProC treats null value as
zero. So,if p_request_id is zero, then it is ONLINE mode.
2) Removed the group_id condition added as part of this bug fix.
If RTP is launched without group_id, then p_group_id of preprocessor()
would be null. So, removed that condition.
3) Added close distinct_org_id, as there is no close cursor statement.
*/
if p_request_id <> 0 then --Bug#6862487
OPEN distinct_org_id(p_request_id, p_group_id);
UPDATE rcv_transactions_interface
SET transfer_lpn_id = NULL,
transfer_license_plate_number = NULL
WHERE processing_request_id = p_request_id
AND to_organization_id = l_to_org_id
AND ( (transaction_type = 'DELIVER')
OR ( transaction_type = 'RECEIVE'
AND auto_transact_code = 'DELIVER'));
asn_debug.put_line('After update to order_transaction_id');
UPDATE rcv_headers_interface
SET processing_status_code = 'ERROR'
WHERE header_interface_id = bad_shikyu.header_interface_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE header_interface_id = bad_shikyu.header_interface_id;
x_cascaded_table.DELETE;
l_update_lpn_group := FALSE;
l_update_lpn_group := TRUE;
SELECT processing_status_code
INTO l_proc_status_code
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT processing_status_code
INTO l_proc_status_code
FROM rcv_transactions_interface
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
SELECT processing_status_code
INTO l_proc_status_code
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).interface_transaction_id;
SELECT COUNT(*)
INTO l_failed_rows_exist
FROM rcv_transactions_interface
WHERE processing_status_code = 'ERROR'
AND header_interface_id = x_cascaded_table(n).header_interface_id;
UPDATE rcv_headers_interface
SET processing_status_code = 'ERROR'
WHERE header_interface_id = x_cascaded_table(n).header_interface_id;
asn_debug.put_line('update_rti_error after rhi error ');
update_rti_error(p_group_id => x_cascaded_table(n).GROUP_ID,
p_interface_id => NULL,
p_header_interface_id => x_header_record.header_record.header_interface_id,
p_lpn_group_id => NULL
);
UPDATE rcv_headers_interface
SET processing_status_code = 'SUCCESS',
validation_flag = 'N',
receipt_header_id = x_header_record.header_record.receipt_header_id
WHERE header_interface_id = x_header_record.header_record.header_interface_id
AND processing_status_code <> 'SUCCESS';
l_update_lpn_group := TRUE;
will delete all the pending RTI rows and insert new RTI rows ready for the processor.
Processing these deleted rows causes an unhandled exception when deleting the ROWID later on
which will produce the invalid transaction error and then will cause all the subsequent transactions
in the group to fail. What's the point of even having transactions to a cancel because it's entirely
determined by the header. The only thing a transaction is needed for it to produce an entry in the
looping cursor.
*/
IF (NVL(x_header_record.header_record.transaction_type,'NEW') <> 'CANCEL') THEN --{
BEGIN --{ processing lines
IF ( x_cascaded_table(n).header_interface_id IS NOT NULL
AND x_header_record.error_record.error_status IN('S', 'W')
AND x_cascaded_table(n).error_status IN('S', 'W')) THEN --{
-- header has been processed and is valid
-- process the line
/* Receipt_source_code is mandatory for
* rhi. Get the value and default it if it is null
* in x_Cascaded_table.
*/
IF (x_cascaded_table(n).receipt_source_code IS NULL) THEN
x_cascaded_table(n).receipt_source_code := x_header_record.header_record.receipt_source_code;
AND l_update_lpn_group) THEN
-- write to po_interface_errors for all shipments
IF ( --(X_cascaded_table(n).header_interface_id is not null) and
( x_fail_if_one_line_fails
AND x_cascaded_table(n).transaction_type = 'SHIP')
OR ( x_cascaded_table(n).error_message = 'RCV_REJECT_ASBN_CONSIGNED_PO'
OR x_cascaded_table(n).error_message = 'RCV_REJECT_CONSUMPTION_PO'
OR x_cascaded_table(n).error_message = 'RCV_REJECT_CONSUMPTION_RELEASE')
) THEN
rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT');
* so that the rsh record will not be deleted later on.
*
* rsh.asn_status will remain as 'NEW_SHIP' until one line goes through. If all the
* lines have failed, the asn_status will remain as 'NEW_SHIP' and the rsh will be deleted.
**/
UPDATE rcv_shipment_headers
SET asn_status = null
WHERE (shipment_header_id = x_cascaded_table(n).shipment_header_id
OR shipment_num = (select shipment_num
from rcv_headers_interface
where header_interface_id =
x_cascaded_table(n).header_interface_id)
OR shipment_header_id = (select receipt_header_id
from rcv_headers_interface
where header_interface_id =
x_cascaded_table(n).header_interface_id))
AND asn_status = 'NEW_SHIP';
asn_debug.put_line(sql%rowcount || ' new_ship RSH updated');
SELECT count(*)
INTO l_drop_ship_exists
FROM po_line_locations_all plla,
rcv_transactions_interface rti
WHERE rti.header_interface_id = x_header_record.header_record.header_interface_id
and rti.po_line_location_id = plla.line_location_id
and plla.drop_ship_flag = 'Y';
UPDATE RCV_SHIPMENT_HEADERS
SET RECEIPT_NUM = x_header_record.header_record.receipt_num
WHERE SHIPMENT_HEADER_ID = x_header_record.header_record.receipt_header_id;
AND l_update_lpn_group) THEN --{ line processing failed
l_ship_header_id := nvl(x_cascaded_table(n).shipment_header_id,
x_header_record.header_record.receipt_header_id);
DELETE FROM rcv_shipment_headers
WHERE shipment_header_id = l_ship_header_id;
asn_debug.put_line(sql%rowcount || ' RSH record deleted');
DELETE FROM rcv_shipment_lines
WHERE shipment_header_id = l_ship_header_id;
asn_debug.put_line(sql%rowcount || ' RSL record deleted');
asn_debug.put_line('update_rti_error for a fail all ASN transaction ');
update_rti_error(p_group_id => x_cascaded_table(n).group_id,
p_interface_id => NULL,
p_header_interface_id => x_header_record.header_record.header_interface_id,
p_lpn_group_id => NULL
);
/* Bug 4779020 .Update the RHI to error.Exit the Loop to prevent processing the line */
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('error out rhi for a fail all ASN transaction ');
UPDATE rcv_headers_interface
SET processing_status_code = 'ERROR',
validation_flag = 'Y',
receipt_header_id = NULL
WHERE header_interface_id = x_header_record.header_record.header_interface_id;
asn_debug.put_line('update_rti_error for an non fail all ASN transaction ');
update_rti_error(p_group_id => x_cascaded_table(n).group_id,
p_interface_id => x_cascaded_table(n).interface_transaction_id,
p_header_interface_id => NULL,
p_lpn_group_id => x_cascaded_table(n).lpn_group_id
);
asn_debug.put_line('update rhi for non fail-all-ASN transaction ');
UPDATE rcv_headers_interface rhi
SET rhi.processing_status_code = 'ERROR',
rhi.validation_flag = 'Y',
rhi.receipt_header_id = NULL
WHERE header_interface_id = x_cascaded_table(n).header_interface_id
AND NOT EXISTS ( SELECT rti.interface_transaction_id
FROM rcv_transactions_interface rti
WHERE rhi.header_interface_id = rti.header_interface_id
AND rti.processing_status_code in ('RUNNING', 'PENDING'));
asn_debug.put_line(sql%rowcount || ' RHI record updated to error. ');
* Only delete the shipment header that was created in this trxn loop
* for PO/RMA receipt or ASN import.
*
* Bug 5024414: Only delete shipment headers where there is no running
* or pending interface line under the interface header, so that we only
* delete rsh after all rti lines have failed.
* */
DELETE FROM rcv_shipment_headers
WHERE shipment_header_id = l_ship_header_id
AND asn_status = 'NEW_SHIP'
AND NOT EXISTS ( SELECT rti.interface_transaction_id
FROM rcv_transactions_interface rti,
rcv_headers_interface rhi
WHERE rhi.header_interface_id = rti.header_interface_id
AND rti.processing_status_code in ('RUNNING', 'PENDING')
AND rhi.receipt_header_id = l_ship_header_id );
asn_debug.put_line(sql%rowcount || ' rsh record deleted');
* Need to call 824 Interface to insert records into ECE_ADVO_HEADERS
* and ECE_ADVO_DETAILS for errored out transactions if it was an ASN
* import and EDI is installed. Data in these 2 tables will be extracted
* to generate outbound 824 Application Advice.
*/
/* Bug 4881909 Start :
** Call 824 interface only when RHI or RTI row is in error.
*/
IF (x_header_record.header_record.asn_type IN('ASN', 'ASBN')) THEN
IF g_is_edi_installed = 'I' THEN
begin
select 1
into l_count
from dual where exists (select 'Errored RHI'
from rcv_headers_interface
where asn_type in ('ASN','ASBN')
and processing_status_code = 'ERROR'
and header_interface_id = x_header_record.header_record.header_interface_id);
select 1
into l_count
from dual
where exists (select 'Errored RTIs'
from rcv_transactions_interface
where processing_status_code = 'ERROR'
and header_interface_id = x_header_record.header_record.header_interface_id);
rcv_824_sv.rcv_824_insert(x_header_record, 'ASN');
since vendor_site_id is derived and populated into RTI get the same and update
RSH */
if (x_header_record.header_record.transaction_type <> 'CANCEL') and
(x_header_record.header_record.vendor_site_id is null) and
(x_header_record.header_record.header_interface_id is not null) and
(x_header_record.error_record.error_status IN('S', 'W')) then
if (x_header_record.header_record.receipt_source_code='VENDOR') then
select count(count(vendor_site_id))
into x_site_id_count
from rcv_transactions_interface
where shipment_header_id=x_header_record.header_record.receipt_header_id
and vendor_site_id is not null
group by vendor_site_id;
update rcv_shipment_headers
set vendor_site_id=(select distinct vendor_site_id
from rcv_transactions_interface
where shipment_header_id=x_header_record.header_record.receipt_header_id
and vendor_site_id is not null)
where shipment_header_id=x_header_record.header_record.receipt_header_id;
asn_debug.put_line('update_rti_error in exception with group_id ');
update_rti_error(p_group_id => p_group_id,
p_interface_id => NULL,
p_header_interface_id => NULL,
p_lpn_group_id => NULL
);
asn_debug.put_line('update_rti_error in exception with request_id ');
update_rti_error(p_group_id => l_exception_group_id(n),
p_interface_id => NULL,
p_header_interface_id => NULL,
p_lpn_group_id => NULL
);
SELECT --mandatory matching values
rt.shipment_header_id,
rt.shipment_line_id,
rt.source_document_code,
rt.po_header_id,
rt.po_release_id,
rt.po_line_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.po_revision_num,
rt.requisition_line_id,
rt.po_unit_price,
rt.currency_code,
rt.currency_conversion_type,
rt.vendor_id,
rt.vendor_site_id,
rt.source_doc_unit_of_measure,
rt.oe_order_header_id,
rt.oe_order_line_id,
rt.customer_id,
rt.customer_site_id,
rt.job_id,
rt.timecard_id,
rt.timecard_ovn,
rt.project_id,
rt.task_id,
rsl.category_id,
rsl.item_description,
rsl.item_id,
rsl.item_revision,
rsl.vendor_item_num,
rsl.vendor_lot_num,
rsl.from_organization_id,
rsl.to_organization_id,
--defaulting values
rt.unit_of_measure,
rt.primary_unit_of_measure,
rt.uom_code,
rt.employee_id,
rt.currency_conversion_rate,
rt.currency_conversion_date,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.secondary_unit_of_measure,
rt.secondary_uom_code
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE transaction_id = p_transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id(+);
SELECT --mandatory matching values
shipment_header_id,
shipment_line_id,
source_document_code,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_revision_num,
requisition_line_id,
po_unit_price,
currency_code,
currency_conversion_type,
vendor_id,
vendor_site_id,
source_doc_unit_of_measure,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
job_id,
timecard_id,
timecard_ovn,
project_id,
task_id,
category_id,
item_description,
item_id,
item_revision,
vendor_item_num,
vendor_lot_num,
from_organization_id,
to_organization_id,
--defaulting values
unit_of_measure,
primary_unit_of_measure,
uom_code,
employee_id,
currency_conversion_rate,
currency_conversion_date,
deliver_to_person_id,
deliver_to_location_id,
secondary_unit_of_measure,
secondary_uom_code
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_transaction_id;
SELECT --mandatory matching values
x_cascaded_table(p_parent_index).shipment_header_id shipment_header_id,
x_cascaded_table(p_parent_index).shipment_line_id shipment_line_id,
x_cascaded_table(p_parent_index).source_document_code source_document_code,
x_cascaded_table(p_parent_index).po_header_id po_header_id,
x_cascaded_table(p_parent_index).po_release_id po_release_id,
x_cascaded_table(p_parent_index).po_line_id po_line_id,
x_cascaded_table(p_parent_index).po_line_location_id po_line_location_id,
x_cascaded_table(p_parent_index).po_distribution_id po_distribution_id,
x_cascaded_table(p_parent_index).po_revision_num po_revision_num,
x_cascaded_table(p_parent_index).requisition_line_id requisition_line_id,
x_cascaded_table(p_parent_index).po_unit_price po_unit_price,
x_cascaded_table(p_parent_index).currency_code currency_code,
x_cascaded_table(p_parent_index).currency_conversion_type currency_conversion_type,
x_cascaded_table(p_parent_index).vendor_id vendor_id,
x_cascaded_table(p_parent_index).vendor_site_id vendor_site_id,
x_cascaded_table(p_parent_index).source_doc_unit_of_measure source_doc_unit_of_measure,
x_cascaded_table(p_parent_index).oe_order_header_id oe_order_header_id,
x_cascaded_table(p_parent_index).oe_order_line_id oe_order_line_id,
x_cascaded_table(p_parent_index).customer_id customer_id,
x_cascaded_table(p_parent_index).customer_site_id customer_site_id,
x_cascaded_table(p_parent_index).job_id job_id,
x_cascaded_table(p_parent_index).timecard_id timecard_id,
x_cascaded_table(p_parent_index).timecard_ovn timecard_ovn,
x_cascaded_table(p_parent_index).project_id project_id,
x_cascaded_table(p_parent_index).task_id task_id,
x_cascaded_table(p_parent_index).category_id category_id,
x_cascaded_table(p_parent_index).item_description item_description,
x_cascaded_table(p_parent_index).item_id item_id,
x_cascaded_table(p_parent_index).item_revision item_revision,
x_cascaded_table(p_parent_index).vendor_item_num vendor_item_num,
x_cascaded_table(p_parent_index).vendor_lot_num vendor_lot_num,
x_cascaded_table(p_parent_index).from_organization_id from_organization_id,
x_cascaded_table(p_parent_index).to_organization_id to_organization_id,
--defaulting values
x_cascaded_table(p_parent_index).unit_of_measure unit_of_measure,
x_cascaded_table(p_parent_index).primary_unit_of_measure primary_unit_of_measure,
x_cascaded_table(p_parent_index).uom_code uom_code,
x_cascaded_table(p_parent_index).employee_id employee_id,
x_cascaded_table(p_parent_index).currency_conversion_rate currency_conversion_rate,
x_cascaded_table(p_parent_index).currency_conversion_date currency_conversion_date,
x_cascaded_table(p_parent_index).deliver_to_person_id deliver_to_person_id,
x_cascaded_table(p_parent_index).deliver_to_location_id deliver_to_location_id,
x_cascaded_table(p_parent_index).secondary_unit_of_measure secondary_unit_of_measure,
x_cascaded_table(p_parent_index).secondary_uom_code secondary_uom_code
FROM DUAL;
used_for_cascaded_rows.DELETE;
asn_debug.put_line('Delete the cascaded rows');
used_for_cascaded_rows.DELETE;
/* update interface_available_qty in rti .We now will look at
* this column to get the available qty if the current row
* is a child of another row in rti.
*/
IF used_for_cascaded_rows(i).matching_basis = 'AMOUNT' THEN
asn_debug.put_line('calling update interface amt ');
rcv_roi_transaction.update_interface_available_amt(used_for_cascaded_rows, i);
asn_debug.put_line('calling update interface qty ');
rcv_roi_transaction.update_interface_available_qty(used_for_cascaded_rows, i);
asn_debug.put_line('Delete the cascaded rows');
used_for_cascaded_rows.DELETE;
used_for_cascaded_rows.DELETE(j);
asn_debug.put_line('update_rti_error in explode_lpn_failed ');
update_rti_error(p_group_id => x_group_id,
p_interface_id => x_interface_txn_id,
p_header_interface_id => NULL,
p_lpn_group_id => x_lpn_group_id
);
PROCEDURE update_rti_error(
p_group_id IN rcv_transactions_interface.GROUP_ID%TYPE,
p_interface_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
p_header_interface_id IN rcv_transactions_interface.header_interface_id%TYPE,
p_lpn_group_id IN rcv_transactions_interface.lpn_group_id%TYPE
) IS
l_return_status VARCHAR2(1);
SELECT DISTINCT (processing_mode_code)
INTO l_processing_mode_code
FROM rcv_transactions_interface
WHERE header_interface_id = p_header_interface_id;
SELECT processing_mode_code
INTO l_processing_mode_code
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_id;
SELECT DISTINCT (processing_mode_code)
INTO l_processing_mode_code
FROM rcv_transactions_interface
WHERE GROUP_ID = p_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE GROUP_ID = p_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE header_interface_id = p_header_interface_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE lpn_group_id = p_lpn_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE interface_transaction_id = p_interface_id;
asn_debug.put_line('update all rti rows to error');
UPDATE rcv_headers_interface
SET processing_status_code = 'ERROR'
WHERE GROUP_ID = p_group_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE GROUP_ID = p_group_id;
asn_debug.put_line('Exception in update_rti_error');
END update_rti_error;