[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rcv_roi_header_common.default_last_update_info(p_header_record);
rcv_int_order_pp_pvt.update_header() procedure.*/
p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
SELECT MAX(shipment_header_id)
INTO p_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE shipment_num = p_header_record.header_record.shipment_num
AND receipt_source_code IN('INVENTORY', 'INTERNAL ORDER');
PROCEDURE update_header(
p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
) IS
BEGIN
UPDATE rcv_shipment_headers
SET shipment_header_id = p_header_record.header_record.receipt_header_id,
last_update_date = p_header_record.header_record.last_update_date,
last_updated_by = p_header_record.header_record.last_updated_by,
creation_date = p_header_record.header_record.creation_date,
created_by = p_header_record.header_record.created_by,
last_update_login = p_header_record.header_record.last_update_login,
receipt_source_code = p_header_record.header_record.receipt_source_code,
vendor_id = p_header_record.header_record.vendor_id,
vendor_site_id = p_header_record.header_record.vendor_site_id,
shipment_num = p_header_record.header_record.shipment_num,
receipt_num = NVL(receipt_num, p_header_record.header_record.receipt_num),
ship_to_location_id = p_header_record.header_record.location_id,
ship_to_org_id = p_header_record.header_record.ship_to_organization_id,
bill_of_lading = p_header_record.header_record.bill_of_lading,
packing_slip = p_header_record.header_record.packing_slip,
shipped_date = Nvl(p_header_record.header_record.shipped_date,shipped_date),--BUG 5087622
freight_carrier_code = p_header_record.header_record.freight_carrier_code,
expected_receipt_date = p_header_record.header_record.expected_receipt_date,
employee_id = p_header_record.header_record.employee_id,
num_of_containers = p_header_record.header_record.num_of_containers,
waybill_airbill_num = p_header_record.header_record.waybill_airbill_num,
comments = p_header_record.header_record.comments,
attribute_category = p_header_record.header_record.attribute_category,
attribute1 = p_header_record.header_record.attribute1,
attribute2 = p_header_record.header_record.attribute2,
attribute3 = p_header_record.header_record.attribute3,
attribute4 = p_header_record.header_record.attribute4,
attribute5 = p_header_record.header_record.attribute5,
attribute6 = p_header_record.header_record.attribute6,
attribute7 = p_header_record.header_record.attribute7,
attribute8 = p_header_record.header_record.attribute8,
attribute9 = p_header_record.header_record.attribute9,
attribute10 = p_header_record.header_record.attribute10,
attribute11 = p_header_record.header_record.attribute11,
attribute12 = p_header_record.header_record.attribute12,
attribute13 = p_header_record.header_record.attribute13,
attribute14 = p_header_record.header_record.attribute14,
attribute15 = p_header_record.header_record.attribute15,
ussgl_transaction_code = p_header_record.header_record.usggl_transaction_code,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE,
asn_type = p_header_record.header_record.asn_type,
edi_control_num = p_header_record.header_record.edi_control_num,
notice_creation_date = p_header_record.header_record.notice_creation_date,
gross_weight = p_header_record.header_record.gross_weight,
gross_weight_uom_code = p_header_record.header_record.gross_weight_uom_code,
net_weight = p_header_record.header_record.net_weight,
net_weight_uom_code = p_header_record.header_record.net_weight_uom_code,
tar_weight = p_header_record.header_record.tar_weight,
tar_weight_uom_code = p_header_record.header_record.tar_weight_uom_code,
packaging_code = p_header_record.header_record.packaging_code,
carrier_method = p_header_record.header_record.carrier_method,
carrier_equipment = p_header_record.header_record.carrier_equipment,
special_handling_code = p_header_record.header_record.special_handling_code,
hazard_code = p_header_record.header_record.hazard_code,
hazard_class = p_header_record.header_record.hazard_class,
hazard_description = p_header_record.header_record.hazard_description,
freight_terms = p_header_record.header_record.freight_terms,
freight_bill_number = p_header_record.header_record.freight_bill_number,
invoice_date = p_header_record.header_record.invoice_date,
invoice_amount = p_header_record.header_record.total_invoice_amount,
tax_name = p_header_record.header_record.tax_name,
tax_amount = p_header_record.header_record.tax_amount,
freight_amount = p_header_record.header_record.freight_amount,
invoice_status_code = p_header_record.header_record.invoice_status_code,
currency_code = p_header_record.header_record.currency_code,
conversion_rate_type = p_header_record.header_record.conversion_rate_type,
conversion_rate = p_header_record.header_record.conversion_rate,
conversion_date = p_header_record.header_record.conversion_rate_date,
payment_terms_id = p_header_record.header_record.payment_terms_id,
invoice_num = p_header_record.header_record.invoice_num
WHERE shipment_header_id = p_header_record.header_record.receipt_header_id;
END update_header;
SELECT muom.uom_code
INTO x_cascaded_table(n).uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
SELECT muom.uom_code
INTO x_cascaded_table(n).uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
SELECT NVL(receiving_routing_id, 0)
INTO l_routing_header_id
FROM mtl_system_items
WHERE inventory_item_id = x_cascaded_table(n).item_id
AND organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(routing_header_id, 0)
INTO l_routing_header_id
FROM mtl_interorg_parameters
WHERE from_organization_id = x_cascaded_table(n).from_organization_id
AND to_organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(receiving_routing_id, 0)
INTO l_routing_header_id
FROM rcv_parameters
WHERE organization_id = x_cascaded_table(n).to_organization_id;
SELECT rsh.shipment_header_id shipment_header_id,
rsh.shipment_num shipment_num,
rsl.shipment_line_id shipment_line_id,
rsl.item_id item_id,
rsl.item_description item_description,
rsl.to_organization_id to_organization_id,
rsl.from_organization_id from_organization_id,
rsl.routing_header_id routing_header_id,
rsl.category_id category_id,
rsh.currency_code currency_code,
rsh.conversion_rate currency_conversion_rate,
rsh.conversion_rate_type currency_conversion_type,
rsh.conversion_date currency_conversion_date,
rsl.to_subinventory to_subinventory,
rsl.ship_to_location_id ship_to_location_id,
rsl.deliver_to_location_id deliver_to_location_id,
rsl.deliver_to_person_id deliver_to_person_id,
rsl.ussgl_transaction_code ussgl_transaction_code,
rsl.destination_type_code destination_type_code,
rsl.destination_context destination_context,
rsl.unit_of_measure unit_of_measure,
rsl.primary_unit_of_measure primary_unit_of_measure,
rsl.requisition_line_id requisition_line_id,
rsl.po_line_location_id po_line_location_id,
rsl.employee_id employee_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_requisition_lines_all porl
-- Following 2 lines are commented out for Bugfix 5201155
-- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
-- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201155
AND rsl.shipment_header_id = rsh.shipment_header_id
AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
AND porl.line_num = NVL(v_document_line_num, porl.line_num)--bug 5483231
AND porl.requisition_line_id = rsl.requisition_line_id--bug 5483231
AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
AND rsh.receipt_source_code = 'INTERNAL ORDER';
SELECT COUNT(*) AS line_count
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_requisition_lines_all porl
-- Following 2 lines are commented out for Bugfix 5201155
-- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
-- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201155
AND rsl.shipment_header_id = rsh.shipment_header_id
AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
AND porl.line_num = NVL(v_document_line_num, porl.line_num)--bug 5483231
AND porl.requisition_line_id = rsl.requisition_line_id--bug 5483231
AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
AND rsh.receipt_source_code = 'INTERNAL ORDER';
insert_into_table BOOLEAN := FALSE;
SELECT distinct rsh.shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE shipment_num = temp_cascaded_table(current_n).shipment_num
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.to_organization_id = NVL(temp_cascaded_table(current_n).to_organization_id, to_organization_id)
AND rsl.from_organization_id = NVL(temp_cascaded_table(current_n).from_organization_id, from_organization_id)
AND rsh.receipt_source_code = 'INTERNAL ORDER';--Bug: 6313315
asn_debug.put_line('Error while selecting shipment_header_id for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE(i);
asn_debug.put_line('Need to insert a row into po_interface_errors for transfer');
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
insert_into_table := TRUE;
insert_into_table := TRUE;
IF rows_fetched = x_record_count THEN --{ last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance checks
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('quantity is less then 0 but last record');
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN --{
IF (x_first_trans) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('first time ' || TO_CHAR(current_n));
SELECT NVL(MAX(hre.full_name), 'notfound')
INTO x_full_name
FROM hr_employees_current_v hre
WHERE ( hre.inactive_date IS NULL
OR hre.inactive_date > SYSDATE)
AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
END IF; --} matches if insert into table
SELECT rsh.shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_headers rsh
WHERE shipment_num = x_cascaded_table(n).shipment_num
AND receipt_source_code = 'INTERNAL ORDER';
SELECT rsl.shipment_line_id
INTO l_shipment_line_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_description = x_cascaded_table(n).item_description
AND ROWNUM = 1;
SELECT rt.shipment_header_id,
rt.shipment_line_id
INTO l_shipment_header_id,
l_shipment_line_id
FROM rcv_transactions rt
WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT rsl.requisition_line_id,
rsl.ship_to_location_id,
rsl.to_subinventory,
rsl.po_line_location_id,
rsl.destination_type_code,
rsl.to_organization_id,
rsl.item_id,
rsl.category_id,
rsl.employee_id
INTO l_requisition_line_id,
l_ship_to_location_id,
l_subinventory,
l_po_line_location_id,
l_destination_type_code,
l_to_organization_id,
l_item_id,
l_category_id,
l_employee_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.shipment_line_id = l_shipment_line_id;
select deliver_to_person_id
into l_deliver_to_person_id
from rcv_shipment_lines
where shipment_line_id = x_cascaded_table(n).shipment_line_id;
select count(shipment_line_id)
into l_rsl_count
from rcv_shipment_lines
where shipment_header_id = x_cascaded_table(n).shipment_header_id;
select deliver_to_person_id
into l_deliver_to_person_id
from rcv_shipment_lines
where shipment_header_id = x_cascaded_table(n).shipment_header_id;
select deliver_to_person_id
into l_parent_deliver_to_person_id
from rcv_transactions
where transaction_id = x_cascaded_table(n).parent_transaction_id;
select deliver_to_person_id
into l_parent_deliver_to_person_id
from rcv_transactions_interface
where interface_transaction_id = x_cascaded_table(n).parent_transaction_id;