[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mld.locator_id
INTO x_cascaded_table(n).locator_id
FROM mtl_item_loc_defaults mld,
mtl_item_locations mil
WHERE mld.inventory_item_id = x_cascaded_table(n).item_id
AND mld.organization_id = x_cascaded_table(n).to_organization_id
AND mld.subinventory_code = x_cascaded_table(n).subinventory
AND mld.default_type = 2
AND mld.organization_id = mil.organization_id
AND mld.locator_id = mil.inventory_location_id
AND trunc(NVL(mil.disable_date, trunc(sysdate+1))) > trunc(sysdate)
AND mil.segment19 = x_cascaded_table(n).project_id
AND mil.project_id = x_cascaded_table(n).project_id
AND nvl(mil.segment20,-99) = nvl(x_cascaded_table(n).task_id,-99)
AND nvl(mil.task_id,-99) = nvl(x_cascaded_table(n).task_id,-99);
SELECT mld.locator_id,
segment1, segment2, segment3, segment4, segment5, segment6,
segment7, segment8, segment9, segment10, segment11, segment12,
segment13, segment14, segment15, segment16, segment17, segment18
INTO l_loc_id,
l_segmentarray(1), l_segmentarray(2), l_segmentarray(3),
l_segmentarray(4), l_segmentarray(5), l_segmentarray(6),
l_segmentarray(7), l_segmentarray(8), l_segmentarray(9),
l_segmentarray(10), l_segmentarray(11), l_segmentarray(12),
l_segmentarray(13), l_segmentarray(14), l_segmentarray(15),
l_segmentarray(16), l_segmentarray(17), l_segmentarray(18)
FROM mtl_item_loc_defaults mld,
mtl_item_locations mil
WHERE mld.inventory_item_id = x_cascaded_table(n).item_id
AND mld.organization_id = x_cascaded_table(n).to_organization_id
AND mld.subinventory_code = x_cascaded_table(n).subinventory
AND mld.default_type = 2
AND mld.organization_id = mil.organization_id
AND mld.locator_id = mil.inventory_location_id
AND trunc(NVL(mil.disable_date, trunc(sysdate+1))) > trunc(sysdate);
update mtl_item_locations
set subinventory_code = x_cascaded_table(n).subinventory,
status_id = inv_material_status_pkg.get_default_locator_status(x_cascaded_table(n).to_organization_id, x_cascaded_table(n).subinventory),
physical_location_id = l_loc_id
where inventory_location_id = x_cascaded_table(n).locator_id;
SELECT from_subinventory,
from_locator_id
INTO l_from_subinventory,
l_from_locator_id
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT from_subinventory,
from_locator_id
INTO l_from_subinventory,
l_from_locator_id
FROM rcv_transactions rt
WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT ph.revision_num,
pl.line_num,
nvl(pll.description,pl.item_description) item_description,--Complex work
pll.tax_code_id,
pll.po_release_id,
pll.ship_to_location_id,
pll.ship_to_organization_id,
pll.shipment_num,
pll.receiving_routing_id,
pll.country_of_origin_code,
pl.job_id,
pll.value_basis , --Complex Work
pl.purchase_basis,
pll.matching_basis --Complex Work
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND ph.po_header_id = v_header_id
AND pl.po_line_id = v_line_id
AND pll.line_location_id = v_line_location_id
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
SELECT vendor_site_id
INTO x_vendor_site_id
FROM po_headers
WHERE po_header_id = x_cascaded_table(n).po_header_id
AND vendor_id = x_cascaded_table(n).vendor_id;
SELECT vendor_site_code
INTO x_vendor_site_code
FROM po_vendor_sites
WHERE vendor_site_id = x_vendor_site_id
AND vendor_id = x_cascaded_table(n).vendor_id;
SELECT NAME
INTO x_tax_name
FROM ap_tax_codes
WHERE ap_tax_codes.tax_id = default_po_info.tax_code_id;
SELECT MAX(org.organization_code)
INTO x_to_organization_code
FROM hr_locations hl,
mtl_parameters org
WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
AND hl.inventory_organization_id = org.organization_id;
SELECT po_line_id
INTO x_cascaded_table(n).po_line_id
FROM po_lines_trx_v
WHERE po_header_id = x_cascaded_table(n).po_header_id
AND po_line_id = x_cascaded_table(n).po_line_id;
SELECT NVL(order_type_lookup_code, 'QUANTITY'),
NVL(purchase_basis, 'GOODS'),
NVL(matching_basis, 'QUANTITY')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).purchase_basis,
x_cascaded_table(n).matching_basis
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id;
SELECT primary_unit_of_measure
INTO l_primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
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 asn_type
into l_asn_type
from rcv_headers_interface
where header_interface_id =
x_cascaded_table(n).header_interface_id;
SELECT release_num
INTO x_cascaded_table(n).release_num
FROM po_releases_all
WHERE po_header_id = x_cascaded_table(n).po_header_id
AND po_release_id = x_cascaded_table(n).po_release_id;
SELECT MAX(ml.inventory_location_id)
INTO x_cascaded_table(n).from_locator_id
FROM mtl_item_locations_kfv ml
WHERE ml.concatenated_segments = x_cascaded_table(n).from_locator
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND NVL(ml.subinventory_code, 'z') = NVL(x_cascaded_table(n).from_subinventory, 'z')
AND x_cascaded_table(n).to_organization_id = ml.organization_id;
SELECT MAX(ml.inventory_location_id)
INTO x_cascaded_table(n).locator_id
FROM mtl_item_locations_kfv ml
WHERE ml.concatenated_segments = x_cascaded_table(n).LOCATOR
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND NVL(ml.subinventory_code, 'z') = NVL(x_cascaded_table(n).subinventory, 'z')
AND x_cascaded_table(n).to_organization_id = ml.organization_id;
SELECT subinventory,
locator_id,
from_subinventory,
from_locator_id
INTO l_to_subinventory,
l_to_locator_id,
l_from_subinventory,
l_from_locator_id
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT subinventory,
locator_id,
from_subinventory,
from_locator_id
INTO l_to_subinventory,
l_to_locator_id,
l_from_subinventory,
l_from_locator_id
FROM rcv_transactions rt
WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT subinventory_code,
locator_id
INTO x_cascaded_table(n).subinventory,
x_cascaded_table(n).locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = x_cascaded_table(n).transfer_lpn_id;
SELECT subinventory_code,
locator_id
INTO x_cascaded_table(n).subinventory,
x_cascaded_table(n).locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = x_cascaded_table(n).lpn_id;
SELECT subinventory,
locator_id,
from_subinventory,
from_locator_id
INTO l_to_subinventory,
l_to_locator_id,
l_from_subinventory,
l_from_locator_id
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT subinventory,
locator_id,
from_subinventory,
from_locator_id
INTO l_to_subinventory,
l_to_locator_id,
l_from_subinventory,
l_from_locator_id
FROM rcv_transactions rt
WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT drop_ship_flag
INTO x_drop_ship_flag
FROM po_line_locations_all
WHERE line_location_id = x_cascaded_table(n).po_line_location_id;
asn_debug.put_line('AFter Warning insert into po_interface_errors ' || x_cascaded_table(n).error_message);
SELECT pll.line_location_id,
pll.unit_meas_lookup_code,
pll.unit_of_measure_class,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.quantity quantity_ordered,
pll.quantity_shipped,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
NVL(pll.price_override, pl.unit_price) unit_price,
pll.match_option, -- 1845702
pl.category_id,
nvl(pll.description,pl.item_description) item_description,--Complex work
pl.po_line_id,
ph.currency_code,
ph.rate_type, -- 1845702
0 po_distribution_id,
0 code_combination_id,
0 req_distribution_id,
0 deliver_to_location_id,
0 deliver_to_person_id,
ph.rate_date rate_date, --1845702
ph.rate rate, --1845702
'' destination_type_code,
0 destination_organization_id,
'' destination_subinventory,
0 wip_entity_id,
0 wip_operation_seq_num,
0 wip_resource_seq_num,
0 wip_repetitive_schedule_id,
0 wip_line_id,
0 bom_resource_id,
0 project_id, /* Bug 14725305 */
0 task_id, /* Bug 14725305 */
'' ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
TO_NUMBER(NULL) shipment_line_id, --shipment_line_id
pl.item_id
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pll.po_header_id = header_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(v_line_location_id, pll.line_location_id) --Bug: 5357628
AND pll.po_line_id = pl.po_line_id
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
ORDER BY NVL(pll.promised_date, Nvl(pll.need_by_date,pll.creation_date)), Nvl(pll.need_by_date,pll.creation_date), pll.creation_date; --bug10022180
SELECT COUNT(*)
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pll.po_header_id = header_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(v_line_location_id, pll.line_location_id) --Bug: 5357628
AND pll.po_line_id = pl.po_line_id
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
***** was driving through PO_LINE_LOCATIONS_ALL. Modified the Select
***** statement so that it will drive through PO_HEADERS_ALL
***** followed by PO_LINES_ALL which is followed by PO_LINE_LOCATIONS_ALL
***** so that there is an improvement in performance.
*****/
CURSOR distributions(
header_id NUMBER,
v_item_id NUMBER,
v_po_line_num NUMBER,
v_po_release_id NUMBER,
v_shipment_num NUMBER,
v_distribution_num NUMBER,
v_ship_to_org_id NUMBER,
v_ship_to_location_id NUMBER,
v_vendor_product_num VARCHAR2
) IS
SELECT pll.line_location_id,
pll.unit_meas_lookup_code,
pll.unit_of_measure_class,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.quantity quantity_ordered,
pll.quantity_shipped,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
NVL(pll.price_override, pl.unit_price) unit_price,
pll.match_option, -- 1845702
pl.category_id,
nvl(pll.description,pl.item_description) item_description,--Complex work
pl.po_line_id,
ph.currency_code,
ph.rate_type, -- 1845702
pod.po_distribution_id,
pod.code_combination_id,
pod.req_distribution_id,
pod.deliver_to_location_id,
pod.deliver_to_person_id,
pod.rate_date,
pod.rate,
pod.destination_type_code,
pod.destination_organization_id,
pod.destination_subinventory,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.project_id, /* Bug 14725305 */
pod.task_id, /* Bug 14725305 */
pod.ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
TO_NUMBER(NULL) shipment_line_id, -- shipment_line_id
pl.item_id
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pod.line_location_id = pll.line_location_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
ORDER BY NVL(pll.promised_date, Nvl(pll.need_by_date,pll.creation_date)), Nvl(pll.need_by_date,pll.creation_date), pll.creation_date; --bug10022180
***** Modified the Select statement so that it will drive through
***** PO_HEADERS_ALL followed by PO_LINES_ALL which is followed by
***** PO_LINE_LOCATIONS_ALL which in turn is followed by
***** PO_DISTRIBUTIONS_ALL so that there is an improvement in
***** Performance
*****/
CURSOR count_distributions(
header_id NUMBER,
v_item_id NUMBER,
v_po_line_num NUMBER,
v_po_release_id NUMBER,
v_shipment_num NUMBER,
v_distribution_num NUMBER,
v_ship_to_org_id NUMBER,
v_ship_to_location_id NUMBER,
v_vendor_product_num VARCHAR2
) IS
SELECT COUNT(*)
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pod.line_location_id = pll.line_location_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
insert_into_table BOOLEAN := FALSE;
SELECT line_num
INTO temp_cascaded_table(current_n).document_line_num
FROM po_lines
WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
SELECT shipment_num
INTO temp_cascaded_table(current_n).document_shipment_line_num
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
SELECT distribution_num
INTO temp_cascaded_table(current_n).document_distribution_num
FROM po_distributions
WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
INTO x_qty_rcv_exception_code
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
select blind_receiving_flag
into l_blind_receiving_flag
from rcv_parameters
where organization_id = temp_cascaded_table(current_n).to_organization_id;
asn_debug.put_line('Need to insert into po_interface_errors');
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');
SELECT COUNT(*)
INTO x_temp_count
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id;
SELECT NVL(pl.item_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type,
pll.ship_to_organization_id,
pll.ship_to_location_id,
NVL(pl.vendor_product_num, '-999')
INTO x_item_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type,
x_ship_to_organization_id,
x_ship_to_location_id,
x_vendor_product_num
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id;
SELECT COUNT(*)
INTO x_temp_count
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
SELECT NVL(pl.item_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type,
pll.ship_to_organization_id,
pll.ship_to_location_id,
NVL(pl.vendor_product_num, '-999')
INTO x_item_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type,
x_ship_to_organization_id,
x_ship_to_location_id,
x_vendor_product_num
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
SELECT NVL(rsl.item_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type,
rsl.to_organization_id,
rsl.ship_to_location_id,
NVL(pl.vendor_product_num, '-999'),
rsh.receipt_source_code,
rsl.shipment_line_status_code,
NVL(rsl.asn_line_flag, 'N')
INTO x_item_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type,
x_ship_to_organization_id,
x_ship_to_location_id,
x_vendor_product_num,
l_receipt_source_code,
l_shipment_line_status_code,
l_asn_line_flag
FROM po_line_locations pll,
po_lines pl,
po_headers ph,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = temp_cascaded_table(current_n).shipment_header_id
AND rsl.shipment_line_id = temp_cascaded_table(current_n).shipment_line_id
AND rsl.po_header_id = temp_cascaded_table(current_n).po_header_id
AND rsl.po_line_id = temp_cascaded_table(current_n).po_line_id
AND rsl.po_line_location_id = temp_cascaded_table(current_n).po_line_location_id
AND rsl.po_line_id = pl.po_line_id
AND rsl.po_line_location_id = pll.line_location_id
AND ph.po_header_id = rsl.po_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.po_line_id = temp_cascaded_table(current_n).po_line_id
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id,0)
AND pll.line_location_id = temp_cascaded_table(current_n).po_line_location_id
AND pll.po_line_id = pl.po_line_id;
temp_cascaded_table.DELETE(i);
select nvl(max(receipt_asn_exists_code),'none')
into l_asn_exists_code
from mtl_client_parameters
where client_code = l_client_code;
select nvl(max(receipt_asn_exists_code),'none')
into l_asn_exists_code
from rcv_parameters
where organization_id = x_cascaded_table(n).to_organization_id;
select nvl(max(receipt_asn_exists_code),'none')
into l_asn_exists_code
from rcv_parameters
where organization_id = x_cascaded_table(n).to_organization_id ;
* select below.
*/
SELECT COUNT(*)
INTO l_asn_count
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND NVL(rsh.asn_type, 'STD') IN ('ASN','ASBN') --4287932
AND NVL(rsl.shipment_line_status_code, 'EXPECTED') NOT IN('CANCELLED', 'FULLY RECEIVED')
AND rsl.po_line_location_id = x_shipmentdistributionrec.line_location_id;
asn_debug.put_line('AFter Warning insert into po_interface_errors ' || x_cascaded_table(n).error_message);
asn_debug.put_line('AFter Reject insert into po_interface_errors ' || x_cascaded_table(n).error_message);
SELECT primary_unit_of_measure
INTO temp_cascaded_table(current_n).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = temp_cascaded_table(current_n).item_id
AND mtl_system_items.organization_id = temp_cascaded_table(current_n).to_organization_id;
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));
Later reset cum_qty = cum_qty + primary_qty for each insert.Since we always
copy the previous record this should work pretty well */
/* Bug 5203841.
* In complex work POs,there is a case where the rate based
* PO shipment has matching_basis as Quantity based. But
* item_id is null. Since there is no uom conversion in
* the case of complex work POs, the old code would
* return the original quantity whereas the new one
* errors out. Reverting to old code for complex work POs.
*/
if ( l_complex_flag = 'Y' ) then --{
temp_cascaded_table(current_n).vendor_cum_shipped_qty :=
temp_cascaded_table(current_n).vendor_cum_shipped_qty
- rcv_transactions_interface_sv.convert_into_correct_qty
(x_bkp_qty,
temp_cascaded_table(current_n).unit_of_measure,
temp_cascaded_table(current_n).item_id,
temp_cascaded_table(current_n).primary_unit_of_measure);
SELECT set_of_books_id
INTO rcv_transactions_interface_sv.x_set_of_books_id
FROM financials_system_parameters;
SELECT count(po_distribution_id),max(rate),max(rate_date)
INTO l_dist_count,x_rate,l_rate_date
FROM po_distributions
WHERE line_location_id = x_ShipmentDistributionRec.line_location_id
HAVING count(po_distribution_id) = 1;
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 pll.line_location_id,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.amount amount_ordered,
pll.amount_shipped ,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
pll.match_option,
pl.job_id,
pl.po_line_id,
ph.currency_code,
ph.rate_type,
0 po_distribution_id,
0 code_combination_id,
0 req_distribution_id,
0 deliver_to_location_id,
0 deliver_to_person_id,
ph.rate_date rate_date,
ph.rate rate,
'' destination_type_code,
0 destination_organization_id,
'' destination_subinventory,
0 wip_entity_id,
0 wip_operation_seq_num,
0 wip_resource_seq_num,
0 wip_repetitive_schedule_id,
0 wip_line_id,
0 bom_resource_id,
'' ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
pl.item_id
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE pl.po_line_id = v_po_line_id
AND pll.po_line_id = v_po_line_id
AND ph.po_header_id = pl.po_header_id
AND pll.line_location_id = NVL(v_po_line_location_id, pll.line_location_id)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
ORDER BY NVL(pll.promised_date, pll.need_by_date);
SELECT COUNT(*)
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE pl.po_line_id = v_po_line_id
AND pll.po_line_id = v_po_line_id
AND ph.po_header_id = pl.po_header_id
AND pll.line_location_id = NVL(v_po_line_location_id, pll.line_location_id)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
SELECT pll.line_location_id,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.amount amount_ordered,
pll.amount_shipped,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
pll.match_option,
pl.job_id,
pl.po_line_id,
ph.currency_code,
ph.rate_type,
pod.po_distribution_id,
pod.code_combination_id,
pod.req_distribution_id,
pod.deliver_to_location_id,
pod.deliver_to_person_id,
pod.rate_date,
pod.rate,
pod.destination_type_code,
pod.destination_organization_id,
pod.destination_subinventory,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
pl.item_id
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE pl.po_line_id = v_po_line_id
AND pll.po_line_id = v_po_line_id
AND ph.po_header_id = pl.po_header_id
AND pod.line_location_id = pll.line_location_id
AND pll.line_location_id = NVL(v_po_line_location_id, pll.line_location_id)
AND pod.po_distribution_id = NVL(v_po_distribution_id, pod.po_distribution_id)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
ORDER BY NVL(pll.promised_date, pll.need_by_date);
SELECT COUNT(*)
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE pl.po_line_id = v_po_line_id
AND pll.po_line_id = v_po_line_id
AND ph.po_header_id = pl.po_header_id
AND pod.line_location_id = pll.line_location_id
AND pll.line_location_id = NVL(v_po_line_location_id, pll.line_location_id)
AND pod.po_distribution_id = NVL(v_po_distribution_id, pod.po_distribution_id)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
insert_into_table BOOLEAN := FALSE;
SELECT COUNT(*)
INTO l_time_count
FROM hxc_time_building_blocks bb,
po_lines pol
WHERE bb.time_building_block_id = x_cascaded_table(n).timecard_id
AND bb.object_version_number = x_cascaded_table(n).timecard_ovn
AND bb.SCOPE = 'TIMECARD'
AND bb.resource_type = 'PERSON'
AND bb.resource_id IN(SELECT person_id
FROM per_all_assignments_f
WHERE assignment_type = 'C')
AND pol.po_line_id = x_cascaded_table(n).po_line_id
AND pol.order_type_lookup_code = 'RATE'
AND pol.purchase_basis = 'TEMP LABOR';
SELECT line_num
INTO temp_cascaded_table(current_n).document_line_num
FROM po_lines
WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
SELECT shipment_num
INTO temp_cascaded_table(current_n).document_shipment_line_num
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
SELECT distribution_num
INTO temp_cascaded_table(current_n).document_distribution_num
FROM po_distributions
WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
INTO x_qty_rcv_exception_code
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
asn_debug.put_line('Need to insert into po_interface_errors');
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE;
asn_debug.put_line('Need to insert a row into po_interface_errors');
SELECT COUNT(*)
INTO x_temp_count
FROM DUAL
WHERE EXISTS(SELECT 1
FROM po_line_locations pll,
po_lines pl
WHERE pl.po_line_id = temp_cascaded_table(current_n).po_line_id
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id);
SELECT NVL(pll.po_release_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type
INTO x_release_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id;
SELECT COUNT(*)
INTO x_temp_count
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
SELECT NVL(pll.po_release_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type
INTO x_release_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.line_location_id = NVL(temp_cascaded_table(current_n).po_line_location_id, pll.line_location_id)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
temp_cascaded_table.DELETE;
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('Amount 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 set_of_books_id
INTO rcv_transactions_interface_sv.x_set_of_books_id
FROM financials_system_parameters;
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 creation_date, shipped_date
INTO x_creation_date, x_shipped_date
FROM rcv_shipment_headers
WHERE shipment_num = x_cascaded_table(n).shipment_num
AND ship_to_org_id = x_cascaded_table(n).to_organization_id;
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_parameters;
SELECT Nvl(rsh.freight_carrier_code,'-999')
INTO X_rsh_freight_carrier_code
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_num = x_cascaded_table(n).shipment_num
AND rsh.ship_to_org_id = x_cascaded_table(n).to_organization_id
AND rsh.receipt_source_code IN ('INVENTORY','INTERNAL ORDER');
SELECT rt.inspection_status_code
INTO l_insp_status
FROM rcv_transactions rt
WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT rti.inspection_status_code
INTO l_insp_status
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
* subinventory is null. See updates on Bug 3367485 */
/* IF (x_drop_ship_flag = 'Y') THEN
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Skip subinventory validation for dropship deliver ');
SELECT NVL(MAX(secondary_inventory_name), 'notfound')
INTO x_subinventory
FROM mtl_secondary_inventories msub,
mtl_system_items msi
WHERE msub.secondary_inventory_name = x_cascaded_table(n).subinventory
AND msub.organization_id = x_cascaded_table(n).to_organization_id
AND x_cascaded_table(n).transaction_date < NVL(msub.disable_date, x_cascaded_table(n).transaction_date + 1)
AND msi.inventory_item_id = x_cascaded_table(n).item_id
AND msi.organization_id = x_cascaded_table(n).to_organization_id
AND ( msi.restrict_subinventories_code = 2
OR ( msi.restrict_subinventories_code = 1
AND EXISTS(SELECT NULL
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = x_cascaded_table(n).to_organization_id
AND mis.inventory_item_id = x_cascaded_table(n).item_id
AND mis.secondary_inventory = x_cascaded_table(n).subinventory)
)
);
asn_debug.put_line('Before select l_job_id ' || l_job_id);
SELECT NVL(MAX(job_id), 0)
INTO l_job_id
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND job_id = x_cascaded_table(n).job_id;
asn_debug.put_line('After select l_job_id ' || l_job_id);
SELECT (description)
INTO l_item_description
FROM po_line_locations
WHERE line_location_id =
x_cascaded_table(n).po_line_location_id
AND description = x_cascaded_table(n).item_description;
SELECT (item_description)
INTO l_item_description
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND item_description =
x_cascaded_table(n).item_description;
SELECT (item_description)
INTO l_item_description
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND item_description = x_cascaded_table(n).item_description;
asn_debug.put_line('After select l_item_description ' || l_item_description);
SELECT NVL(MAX(category_id), 0)
INTO l_category_id
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND category_id = x_cascaded_table(n).category_id;
asn_debug.put_line('After select l_category_id ' || l_category_id);
SELECT COUNT(*)
INTO x_uom_count
FROM mtl_item_uoms_view
WHERE organization_id = x_cascaded_table(n).to_organization_id
AND inventory_item_id(+) = x_cascaded_table(n).substitute_item_id
AND unit_of_measure = x_cascaded_table(n).unit_of_measure;
SELECT MAX(primary_unit_of_measure)
INTO x_primary_uom
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
select description
into l_item_description
from mtl_system_items_vl
where inventory_item_id = x_cascaded_table(n).substitute_item_id
and organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(MAX(po_line_id), 0)
INTO x_po_line_id
FROM po_lines
WHERE po_line_id = x_ref_integrity_rec.po_line_id
AND vendor_product_num = x_ref_integrity_rec.vendor_item_num;
SELECT NVL(MAX(vendor_id), 0)
INTO x_po_vendor_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND vendor_id = x_ref_integrity_rec.vendor_id;
SELECT NVL(MAX(vendor_site_id), 0)
INTO x_po_vendor_site_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
SELECT NVL(MAX(vendor_site_id), 0)
INTO x_po_vendor_site_id
FROM rcv_transactions
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND transaction_id = x_ref_integrity_rec.parent_txn_id
AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
SELECT NVL(MAX(vendor_site_id), 0)
INTO x_po_vendor_site_id
FROM po_headers
WHERE po_header_id = x_ref_integrity_rec.po_header_id
AND revision_num = x_ref_integrity_rec.po_revision_num;
/* Delete only once from the rcv_transactions_interface table for every
group of pl/sql table rows that have the same interface id */
FOR j IN i .. e_o_t LOOP
asn_debug.put_line('Transaction Type ' || v_trans_tab(j).transaction_type);
/* SELECT GROUP_SEQUENCE_ID_S.nextval into V_TRANS_TAB(j).GROUP_ID
from dual; -- Check whether this has to be unique */
asn_debug.put_line('delete from rcv_transactions_interface rowid ' || v_trans_tab(j).row_id);
rcv_trx_interface_delete_pkg.delete_row(v_trans_tab(j).row_id);
SELECT rcv_interface_groups_s.NEXTVAL
INTO v_trans_tab(j).lpn_group_id
FROM DUAL;
asn_debug.put_line('Inserting new RTI');
rcv_asn_interface_trx_ins_pkg.insert_row(v_trans_tab(j).row_id,
v_trans_tab(j).interface_transaction_id,
v_trans_tab(j).GROUP_ID,
v_trans_tab(j).last_update_date,
v_trans_tab(j).last_updated_by,
v_trans_tab(j).creation_date,
v_trans_tab(j).created_by,
v_trans_tab(j).last_update_login,
v_trans_tab(j).request_id,
v_trans_tab(j).program_application_id,
v_trans_tab(j).program_id,
v_trans_tab(j).program_update_date,
v_trans_tab(j).transaction_type,
v_trans_tab(j).transaction_date,
v_trans_tab(j).processing_status_code,
v_trans_tab(j).processing_mode_code,
v_trans_tab(j).processing_request_id,
v_trans_tab(j).transaction_status_code,
v_trans_tab(j).category_id,
v_trans_tab(j).quantity,
v_trans_tab(j).unit_of_measure,
v_trans_tab(j).interface_source_code,
v_trans_tab(j).interface_source_line_id,
v_trans_tab(j).inv_transaction_id,
v_trans_tab(j).item_id,
v_trans_tab(j).item_description,
v_trans_tab(j).item_revision,
v_trans_tab(j).uom_code,
v_trans_tab(j).employee_id,
v_trans_tab(j).auto_transact_code,
NVL(v_trans_tab(j).shipment_header_id, v_header_record.header_record.receipt_header_id),
v_trans_tab(j).shipment_line_id,
v_trans_tab(j).ship_to_location_id,
v_trans_tab(j).primary_quantity,
v_trans_tab(j).primary_unit_of_measure,
v_trans_tab(j).receipt_source_code,
v_trans_tab(j).vendor_id,
v_trans_tab(j).vendor_site_id,
v_trans_tab(j).from_organization_id,
v_trans_tab(j).from_subinventory,
v_trans_tab(j).to_organization_id,
v_trans_tab(j).intransit_owning_org_id,
v_trans_tab(j).routing_header_id,
v_trans_tab(j).routing_step_id,
v_trans_tab(j).source_document_code,
v_trans_tab(j).parent_transaction_id,
v_trans_tab(j).po_header_id,
v_trans_tab(j).po_revision_num,
v_trans_tab(j).po_release_id,
v_trans_tab(j).po_line_id,
v_trans_tab(j).po_line_location_id,
v_trans_tab(j).po_unit_price,
v_trans_tab(j).currency_code,
v_trans_tab(j).currency_conversion_type,
v_trans_tab(j).currency_conversion_rate,
v_trans_tab(j).currency_conversion_date,
v_trans_tab(j).po_distribution_id,
v_trans_tab(j).requisition_line_id,
v_trans_tab(j).req_distribution_id,
v_trans_tab(j).charge_account_id,
v_trans_tab(j).substitute_unordered_code,
v_trans_tab(j).receipt_exception_flag,
v_trans_tab(j).accrual_status_code,
v_trans_tab(j).inspection_status_code,
v_trans_tab(j).inspection_quality_code,
v_trans_tab(j).destination_type_code,
v_trans_tab(j).deliver_to_person_id,
v_trans_tab(j).location_id,
v_trans_tab(j).deliver_to_location_id,
v_trans_tab(j).subinventory,
v_trans_tab(j).locator_id,
v_trans_tab(j).wip_entity_id,
v_trans_tab(j).wip_line_id,
v_trans_tab(j).department_code,
v_trans_tab(j).wip_repetitive_schedule_id,
v_trans_tab(j).wip_operation_seq_num,
v_trans_tab(j).wip_resource_seq_num,
v_trans_tab(j).bom_resource_id,
v_trans_tab(j).shipment_num,
v_trans_tab(j).freight_carrier_code,
v_trans_tab(j).bill_of_lading,
v_trans_tab(j).packing_slip,
v_trans_tab(j).shipped_date,
v_trans_tab(j).expected_receipt_date,
v_trans_tab(j).actual_cost,
v_trans_tab(j).transfer_cost,
v_trans_tab(j).transportation_cost,
v_trans_tab(j).transportation_account_id,
v_trans_tab(j).num_of_containers,
v_trans_tab(j).waybill_airbill_num,
v_trans_tab(j).vendor_item_num,
v_trans_tab(j).vendor_lot_num,
v_trans_tab(j).rma_reference,
v_trans_tab(j).comments,
v_trans_tab(j).attribute_category,
v_trans_tab(j).attribute1,
v_trans_tab(j).attribute2,
v_trans_tab(j).attribute3,
v_trans_tab(j).attribute4,
v_trans_tab(j).attribute5,
v_trans_tab(j).attribute6,
v_trans_tab(j).attribute7,
v_trans_tab(j).attribute8,
v_trans_tab(j).attribute9,
v_trans_tab(j).attribute10,
v_trans_tab(j).attribute11,
v_trans_tab(j).attribute12,
v_trans_tab(j).attribute13,
v_trans_tab(j).attribute14,
v_trans_tab(j).attribute15,
v_trans_tab(j).ship_head_attribute_category,
v_trans_tab(j).ship_head_attribute1,
v_trans_tab(j).ship_head_attribute2,
v_trans_tab(j).ship_head_attribute3,
v_trans_tab(j).ship_head_attribute4,
v_trans_tab(j).ship_head_attribute5,
v_trans_tab(j).ship_head_attribute6,
v_trans_tab(j).ship_head_attribute7,
v_trans_tab(j).ship_head_attribute8,
v_trans_tab(j).ship_head_attribute9,
v_trans_tab(j).ship_head_attribute10,
v_trans_tab(j).ship_head_attribute11,
v_trans_tab(j).ship_head_attribute12,
v_trans_tab(j).ship_head_attribute13,
v_trans_tab(j).ship_head_attribute14,
v_trans_tab(j).ship_head_attribute15,
v_trans_tab(j).ship_line_attribute_category,
v_trans_tab(j).ship_line_attribute1,
v_trans_tab(j).ship_line_attribute2,
v_trans_tab(j).ship_line_attribute3,
v_trans_tab(j).ship_line_attribute4,
v_trans_tab(j).ship_line_attribute5,
v_trans_tab(j).ship_line_attribute6,
v_trans_tab(j).ship_line_attribute7,
v_trans_tab(j).ship_line_attribute8,
v_trans_tab(j).ship_line_attribute9,
v_trans_tab(j).ship_line_attribute10,
v_trans_tab(j).ship_line_attribute11,
v_trans_tab(j).ship_line_attribute12,
v_trans_tab(j).ship_line_attribute13,
v_trans_tab(j).ship_line_attribute14,
v_trans_tab(j).ship_line_attribute15,
v_trans_tab(j).ussgl_transaction_code,
v_trans_tab(j).government_context,
v_trans_tab(j).reason_id,
v_trans_tab(j).destination_context,
v_trans_tab(j).source_doc_quantity,
v_trans_tab(j).source_doc_unit_of_measure,
v_trans_tab(j).movement_id,
v_trans_tab(j).header_interface_id,
v_trans_tab(j).vendor_cum_shipped_qty,
v_trans_tab(j).item_num,
v_trans_tab(j).document_num,
v_trans_tab(j).document_line_num,
v_trans_tab(j).truck_num,
v_trans_tab(j).ship_to_location_code,
v_trans_tab(j).container_num,
v_trans_tab(j).substitute_item_num,
v_trans_tab(j).notice_unit_price,
v_trans_tab(j).item_category,
v_trans_tab(j).location_code,
v_trans_tab(j).vendor_name,
v_trans_tab(j).vendor_num,
v_trans_tab(j).vendor_site_code,
v_trans_tab(j).from_organization_code,
v_trans_tab(j).to_organization_code,
v_trans_tab(j).intransit_owning_org_code,
v_trans_tab(j).routing_code,
v_trans_tab(j).routing_step,
v_trans_tab(j).release_num,
v_trans_tab(j).document_shipment_line_num,
v_trans_tab(j).document_distribution_num,
v_trans_tab(j).deliver_to_person_name,
v_trans_tab(j).deliver_to_location_code,
v_trans_tab(j).use_mtl_lot,
v_trans_tab(j).use_mtl_serial,
v_trans_tab(j).LOCATOR,
v_trans_tab(j).reason_name,
v_trans_tab(j).validation_flag,
v_trans_tab(j).substitute_item_id,
v_trans_tab(j).quantity_shipped,
v_trans_tab(j).quantity_invoiced,
v_trans_tab(j).tax_name,
v_trans_tab(j).tax_amount,
v_trans_tab(j).req_num,
v_trans_tab(j).req_line_num,
v_trans_tab(j).req_distribution_num,
v_trans_tab(j).wip_entity_name,
v_trans_tab(j).wip_line_code,
v_trans_tab(j).resource_code,
v_trans_tab(j).shipment_line_status_code,
v_trans_tab(j).barcode_label,
v_trans_tab(j).country_of_origin_code,
v_trans_tab(j).from_locator_id, --WMS Change
v_trans_tab(j).qa_collection_id,
v_trans_tab(j).oe_order_header_id,
v_trans_tab(j).oe_order_line_id,
v_trans_tab(j).customer_id,
v_trans_tab(j).customer_site_id,
v_trans_tab(j).customer_item_num,
v_trans_tab(j).create_debit_memo_flag,
v_trans_tab(j).put_away_rule_id,
v_trans_tab(j).put_away_strategy_id,
v_trans_tab(j).lpn_id,
v_trans_tab(j).transfer_lpn_id,
v_trans_tab(j).cost_group_id,
v_trans_tab(j).mobile_txn,
v_trans_tab(j).mmtt_temp_id,
v_trans_tab(j).transfer_cost_group_id,
v_trans_tab(j).secondary_quantity,
v_trans_tab(j).secondary_unit_of_measure,
v_trans_tab(j).secondary_uom_code,
v_trans_tab(j).qc_grade,
v_trans_tab(j).oe_order_num,
v_trans_tab(j).oe_order_line_num,
v_trans_tab(j).customer_account_number,
v_trans_tab(j).customer_party_name,
v_trans_tab(j).source_transaction_num,
v_trans_tab(j).parent_source_transaction_num,
v_trans_tab(j).parent_interface_txn_id,
v_trans_tab(j).customer_item_id,
v_trans_tab(j).interface_available_qty,
v_trans_tab(j).interface_transaction_qty,
v_trans_tab(j).from_locator,
v_trans_tab(j).lpn_group_id,
v_trans_tab(j).order_transaction_id,
v_trans_tab(j).license_plate_number,
v_trans_tab(j).transfer_license_plate_number,
v_trans_tab(j).amount,
v_trans_tab(j).job_id,
v_trans_tab(j).project_id,
v_trans_tab(j).task_id,
v_trans_tab(j).asn_attach_id,
v_trans_tab(j).timecard_id,
v_trans_tab(j).timecard_ovn,
v_trans_tab(j).interface_available_amt,
v_trans_tab(j).interface_transaction_amt,
v_trans_tab(j).org_id, --
v_trans_tab(j).matching_basis,
v_trans_tab(j).amount_shipped,
v_trans_tab(j).requested_amount,
v_trans_tab(j).material_stored_amount,
v_trans_tab(j).replenish_order_line_id,
v_trans_tab(j).express_transaction,--NULL, -- Bug 14067935
v_trans_tab(j).lcm_shipment_line_id, -- lcm changes
v_trans_tab(j).unit_landed_cost -- lcm changes
);
v_trans_tab.DELETE(j);
asn_debug.put_line('insert into rcv_transactions_interface with new id ');
/* Since we are inserting 1-> many rows need to generate a new interface id */
v_trans_tab(j).interface_transaction_id := NULL;
SELECT rcv_interface_groups_s.CURRVAL
INTO v_trans_tab(j).lpn_group_id
FROM DUAL;
rcv_asn_interface_trx_ins_pkg.insert_row(v_trans_tab(j).row_id,
v_trans_tab(j).interface_transaction_id,
v_trans_tab(j).GROUP_ID,
v_trans_tab(j).last_update_date,
v_trans_tab(j).last_updated_by,
v_trans_tab(j).creation_date,
v_trans_tab(j).created_by,
v_trans_tab(j).last_update_login,
v_trans_tab(j).request_id,
v_trans_tab(j).program_application_id,
v_trans_tab(j).program_id,
v_trans_tab(j).program_update_date,
v_trans_tab(j).transaction_type,
v_trans_tab(j).transaction_date,
v_trans_tab(j).processing_status_code,
v_trans_tab(j).processing_mode_code,
v_trans_tab(j).processing_request_id,
v_trans_tab(j).transaction_status_code,
v_trans_tab(j).category_id,
v_trans_tab(j).quantity,
v_trans_tab(j).unit_of_measure,
v_trans_tab(j).interface_source_code,
v_trans_tab(j).interface_source_line_id,
v_trans_tab(j).inv_transaction_id,
v_trans_tab(j).item_id,
v_trans_tab(j).item_description,
v_trans_tab(j).item_revision,
v_trans_tab(j).uom_code,
v_trans_tab(j).employee_id,
v_trans_tab(j).auto_transact_code,
NVL(v_trans_tab(j).shipment_header_id, v_header_record.header_record.receipt_header_id),
v_trans_tab(j).shipment_line_id,
v_trans_tab(j).ship_to_location_id,
v_trans_tab(j).primary_quantity,
v_trans_tab(j).primary_unit_of_measure,
v_trans_tab(j).receipt_source_code,
v_trans_tab(j).vendor_id,
v_trans_tab(j).vendor_site_id,
v_trans_tab(j).from_organization_id,
v_trans_tab(j).from_subinventory,
v_trans_tab(j).to_organization_id,
v_trans_tab(j).intransit_owning_org_id,
v_trans_tab(j).routing_header_id,
v_trans_tab(j).routing_step_id,
v_trans_tab(j).source_document_code,
v_trans_tab(j).parent_transaction_id,
v_trans_tab(j).po_header_id,
v_trans_tab(j).po_revision_num,
v_trans_tab(j).po_release_id,
v_trans_tab(j).po_line_id,
v_trans_tab(j).po_line_location_id,
v_trans_tab(j).po_unit_price,
v_trans_tab(j).currency_code,
v_trans_tab(j).currency_conversion_type,
v_trans_tab(j).currency_conversion_rate,
v_trans_tab(j).currency_conversion_date,
v_trans_tab(j).po_distribution_id,
v_trans_tab(j).requisition_line_id,
v_trans_tab(j).req_distribution_id,
v_trans_tab(j).charge_account_id,
v_trans_tab(j).substitute_unordered_code,
v_trans_tab(j).receipt_exception_flag,
v_trans_tab(j).accrual_status_code,
v_trans_tab(j).inspection_status_code,
v_trans_tab(j).inspection_quality_code,
v_trans_tab(j).destination_type_code,
v_trans_tab(j).deliver_to_person_id,
v_trans_tab(j).location_id,
v_trans_tab(j).deliver_to_location_id,
v_trans_tab(j).subinventory,
v_trans_tab(j).locator_id,
v_trans_tab(j).wip_entity_id,
v_trans_tab(j).wip_line_id,
v_trans_tab(j).department_code,
v_trans_tab(j).wip_repetitive_schedule_id,
v_trans_tab(j).wip_operation_seq_num,
v_trans_tab(j).wip_resource_seq_num,
v_trans_tab(j).bom_resource_id,
v_trans_tab(j).shipment_num,
v_trans_tab(j).freight_carrier_code,
v_trans_tab(j).bill_of_lading,
v_trans_tab(j).packing_slip,
v_trans_tab(j).shipped_date,
v_trans_tab(j).expected_receipt_date,
v_trans_tab(j).actual_cost,
v_trans_tab(j).transfer_cost,
v_trans_tab(j).transportation_cost,
v_trans_tab(j).transportation_account_id,
v_trans_tab(j).num_of_containers,
v_trans_tab(j).waybill_airbill_num,
v_trans_tab(j).vendor_item_num,
v_trans_tab(j).vendor_lot_num,
v_trans_tab(j).rma_reference,
v_trans_tab(j).comments,
v_trans_tab(j).attribute_category,
v_trans_tab(j).attribute1,
v_trans_tab(j).attribute2,
v_trans_tab(j).attribute3,
v_trans_tab(j).attribute4,
v_trans_tab(j).attribute5,
v_trans_tab(j).attribute6,
v_trans_tab(j).attribute7,
v_trans_tab(j).attribute8,
v_trans_tab(j).attribute9,
v_trans_tab(j).attribute10,
v_trans_tab(j).attribute11,
v_trans_tab(j).attribute12,
v_trans_tab(j).attribute13,
v_trans_tab(j).attribute14,
v_trans_tab(j).attribute15,
v_trans_tab(j).ship_head_attribute_category,
v_trans_tab(j).ship_head_attribute1,
v_trans_tab(j).ship_head_attribute2,
v_trans_tab(j).ship_head_attribute3,
v_trans_tab(j).ship_head_attribute4,
v_trans_tab(j).ship_head_attribute5,
v_trans_tab(j).ship_head_attribute6,
v_trans_tab(j).ship_head_attribute7,
v_trans_tab(j).ship_head_attribute8,
v_trans_tab(j).ship_head_attribute9,
v_trans_tab(j).ship_head_attribute10,
v_trans_tab(j).ship_head_attribute11,
v_trans_tab(j).ship_head_attribute12,
v_trans_tab(j).ship_head_attribute13,
v_trans_tab(j).ship_head_attribute14,
v_trans_tab(j).ship_head_attribute15,
v_trans_tab(j).ship_line_attribute_category,
v_trans_tab(j).ship_line_attribute1,
v_trans_tab(j).ship_line_attribute2,
v_trans_tab(j).ship_line_attribute3,
v_trans_tab(j).ship_line_attribute4,
v_trans_tab(j).ship_line_attribute5,
v_trans_tab(j).ship_line_attribute6,
v_trans_tab(j).ship_line_attribute7,
v_trans_tab(j).ship_line_attribute8,
v_trans_tab(j).ship_line_attribute9,
v_trans_tab(j).ship_line_attribute10,
v_trans_tab(j).ship_line_attribute11,
v_trans_tab(j).ship_line_attribute12,
v_trans_tab(j).ship_line_attribute13,
v_trans_tab(j).ship_line_attribute14,
v_trans_tab(j).ship_line_attribute15,
v_trans_tab(j).ussgl_transaction_code,
v_trans_tab(j).government_context,
v_trans_tab(j).reason_id,
v_trans_tab(j).destination_context,
v_trans_tab(j).source_doc_quantity,
v_trans_tab(j).source_doc_unit_of_measure,
v_trans_tab(j).movement_id,
v_trans_tab(j).header_interface_id,
v_trans_tab(j).vendor_cum_shipped_qty,
v_trans_tab(j).item_num,
v_trans_tab(j).document_num,
v_trans_tab(j).document_line_num,
v_trans_tab(j).truck_num,
v_trans_tab(j).ship_to_location_code,
v_trans_tab(j).container_num,
v_trans_tab(j).substitute_item_num,
v_trans_tab(j).notice_unit_price,
v_trans_tab(j).item_category,
v_trans_tab(j).location_code,
v_trans_tab(j).vendor_name,
v_trans_tab(j).vendor_num,
v_trans_tab(j).vendor_site_code,
v_trans_tab(j).from_organization_code,
v_trans_tab(j).to_organization_code,
v_trans_tab(j).intransit_owning_org_code,
v_trans_tab(j).routing_code,
v_trans_tab(j).routing_step,
v_trans_tab(j).release_num,
v_trans_tab(j).document_shipment_line_num,
v_trans_tab(j).document_distribution_num,
v_trans_tab(j).deliver_to_person_name,
v_trans_tab(j).deliver_to_location_code,
v_trans_tab(j).use_mtl_lot,
v_trans_tab(j).use_mtl_serial,
v_trans_tab(j).LOCATOR,
v_trans_tab(j).reason_name,
v_trans_tab(j).validation_flag,
v_trans_tab(j).substitute_item_id,
v_trans_tab(j).quantity_shipped,
v_trans_tab(j).quantity_invoiced,
v_trans_tab(j).tax_name,
v_trans_tab(j).tax_amount,
v_trans_tab(j).req_num,
v_trans_tab(j).req_line_num,
v_trans_tab(j).req_distribution_num,
v_trans_tab(j).wip_entity_name,
v_trans_tab(j).wip_line_code,
v_trans_tab(j).resource_code,
v_trans_tab(j).shipment_line_status_code,
v_trans_tab(j).barcode_label,
v_trans_tab(j).country_of_origin_code,
v_trans_tab(j).from_locator_id, --WMS Change
v_trans_tab(j).qa_collection_id,
v_trans_tab(j).oe_order_header_id,
v_trans_tab(j).oe_order_line_id,
v_trans_tab(j).customer_id,
v_trans_tab(j).customer_site_id,
v_trans_tab(j).customer_item_num,
v_trans_tab(j).create_debit_memo_flag,
v_trans_tab(j).put_away_rule_id,
v_trans_tab(j).put_away_strategy_id,
v_trans_tab(j).lpn_id,
v_trans_tab(j).transfer_lpn_id,
v_trans_tab(j).cost_group_id,
v_trans_tab(j).mobile_txn,
v_trans_tab(j).mmtt_temp_id,
v_trans_tab(j).transfer_cost_group_id,
v_trans_tab(j).secondary_quantity,
v_trans_tab(j).secondary_unit_of_measure,
v_trans_tab(j).secondary_uom_code,
v_trans_tab(j).qc_grade,
v_trans_tab(j).oe_order_num,
v_trans_tab(j).oe_order_line_num,
v_trans_tab(j).customer_account_number,
v_trans_tab(j).customer_party_name,
v_trans_tab(j).source_transaction_num,
v_trans_tab(j).parent_source_transaction_num,
v_trans_tab(j).parent_interface_txn_id,
v_trans_tab(j).customer_item_id,
v_trans_tab(j).interface_available_qty,
v_trans_tab(j).interface_transaction_qty,
v_trans_tab(j).from_locator,
v_trans_tab(j).lpn_group_id,
v_trans_tab(j).order_transaction_id,
v_trans_tab(j).license_plate_number,
v_trans_tab(j).transfer_license_plate_number,
v_trans_tab(j).amount,
v_trans_tab(j).job_id,
v_trans_tab(j).project_id,
v_trans_tab(j).task_id,
v_trans_tab(j).asn_attach_id,
v_trans_tab(j).timecard_id,
v_trans_tab(j).timecard_ovn,
v_trans_tab(j).interface_available_amt,
v_trans_tab(j).interface_transaction_amt,
v_trans_tab(j).org_id, --
v_trans_tab(j).matching_basis,
v_trans_tab(j).amount_shipped,
v_trans_tab(j).requested_amount,
v_trans_tab(j).material_stored_amount,
v_trans_tab(j).replenish_order_line_id,
v_trans_tab(j).express_transaction -- Bug 14067935
);
v_trans_tab.DELETE(j);
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
SELECT GROUP_ID,
header_interface_id,
interface_transaction_id,
transaction_type,
source_document_code,
shipment_header_id
INTO l_group_id,
l_header_interface_id,
l_interface_transaction_id,
l_trx_type,
l_src_doc_code,
l_ship_head_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
rcv_roi_preprocessor.update_rti_error
(null,
null,
l_header_interface_id,
null);
/*Bug 10351737 Deleteing the orphan rsh records*/
IF l_header_interface_id IS NOT NULL THEN
SELECT count(1)
INTO l_count
FROM rcv_transactions_interface
WHERE header_interface_id=l_header_interface_id;
SELECT count(1)
INTO l_cnt
FROM rcv_transactions_interface
WHERE header_interface_id=l_header_interface_id
AND processing_status_code='ERROR';
UPDATE rcv_headers_interface rhi
SET processing_status_code='ERROR'
WHERE header_interface_id=l_header_interface_id;
DELETE FROM rcv_shipment_headers rsh
WHERE shipment_header_id=l_ship_head_id
AND NOT EXISTS (SELECT 1 from rcv_shipment_lines
WHERE shipment_header_id=rsh.shipment_header_id)
AND NOT EXISTS (SELECT 1 from rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id);
/* update rcv_headers_interface rhi
set processing_status_code='ERROR'
where header_interface_id=l_header_interface_id
and not exists(select 1 from rcv_transactions_interface
where header_interface_id=l_header_interface_id
and processing_status_code='RUNNING');
delete from rcv_shipment_headers rsh
where
and not exists(select 1 from rcv_transactions_interface
where shipment_header_id=rsh.shipment_header_id
and processing_status_code='RUNNING');*/
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 transaction_id
INTO x_cascaded_table(n).parent_transaction_id
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
* row in rti or the pl/sql table. Hence need to insert
* into po_interface_errors saying that this is a child
* without any parent in rti or rt.
*/
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('NO PARENT');
SELECT processing_status_code,
/* Bug# 9855182 */
transaction_type,
auto_transact_code
/* Bug# 9855182 End*/
INTO l_processing_status_code,
/* Bug# 9855182 */
l_transaction_type,
l_auto_transact_code
/* Bug# 9855182 End*/
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
END IF; /* Insert error if parent_interface_txn_id is null*/ --}
insert_into_table BOOLEAN := FALSE;
SELECT rsup.po_line_location_id,
rsup.po_header_id po_head_id,
rsup.po_line_id line_id,
rsup.rcv_transaction_id rcv_transaction_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rsup.to_organization_id,
rt.po_unit_price unit_price,
rsl.category_id,
rsl.item_description,
rsup.po_line_id,
rt.location_id,
rsup.item_id,
0 deliver_to_person_id,
0 deliver_to_location_id,
'' destination_subinventory,
0 po_distribution_id,
rsup.destination_type_code,
0 code_combination_id,
0 req_distribution_id,
0 destination_organization_id,
0 wip_entity_id,
0 wip_operation_seq_num,
0 wip_resource_seq_num,
0 wip_repetitive_schedule_id,
0 wip_line_id,
0 bom_resource_id,
0 ussgl_transaction_code,
rt.quantity qty,
0 interface_available_qty
FROM rcv_supply rsup,
rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol
WHERE rt.transaction_id = v_parent_trx_id
AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
AND rsup.supply_type_code = 'RECEIVING'
AND poh.po_header_id = rsup.po_header_id
AND pol.po_line_id = rsup.po_line_id
AND pol.po_header_id = poh.po_header_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
ORDER BY transaction_date DESC,
document_num,
line_number;
SELECT rti.po_line_location_id,
rti.po_header_id po_head_id,
rti.po_line_id line_id,
rti.interface_transaction_id rcv_transaction_id,
rti.document_num,
rti.document_line_num line_number,
rti.transaction_date transaction_date,
rti.transaction_type,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id,
rti.po_unit_price unit_price,
rti.category_id,
rti.item_description,
rti.po_line_id,
rti.location_id,
rti.item_id,
0 deliver_to_person_id,
0 deliver_to_location_id,
'' destination_subinventory,
0 po_distribution_id,
rti.destination_type_code,
0 code_combination_id,
0 req_distribution_id,
0 destination_organization_id,
0 wip_entity_id,
0 wip_operation_seq_num,
0 wip_resource_seq_num,
0 wip_repetitive_schedule_id,
0 wip_line_id,
0 bom_resource_id,
0 ussgl_transaction_code,
rti.quantity qty,
rti.interface_available_qty
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
SELECT rsup.po_line_location_id,
rsup.po_header_id po_head_id,
rsup.po_line_id line_id,
rsup.rcv_transaction_id rcv_transaction_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rsup.to_organization_id,
rt.po_unit_price unit_price,
rsl.category_id,
rsl.item_description,
rsup.po_line_id,
rt.location_id,
rsup.item_id,
pod.deliver_to_person_id,
pod.deliver_to_location_id,
pod.destination_subinventory,
pod.po_distribution_id,
pod.destination_type_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.destination_organization_id,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
rt.quantity qty,
0 interface_available_qty
FROM rcv_supply rsup,
rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol,
po_distributions pod
WHERE rt.transaction_id = v_parent_trx_id
AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
AND rsup.supply_type_code = 'RECEIVING'
AND poh.po_header_id = rsup.po_header_id
AND pol.po_line_id = rsup.po_line_id
AND pol.po_header_id = poh.po_header_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rt.po_header_id = pod.po_header_id
AND pod.po_line_id = pol.po_line_id
AND rt.po_line_location_id = pod.line_location_id
AND ( pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL)
ORDER BY transaction_date DESC,
document_num,
line_number;
SELECT rti.po_line_location_id,
rti.po_header_id po_head_id,
rti.po_line_id line_id,
rti.interface_transaction_id rcv_transaction_id,
rti.document_num,
rti.document_line_num line_number,
rti.transaction_date transaction_date,
rti.transaction_type,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id,
rti.po_unit_price unit_price,
rti.category_id,
rti.item_description,
rti.po_line_id,
rti.location_id,
rti.item_id,
pod.deliver_to_person_id,
pod.deliver_to_location_id,
pod.destination_subinventory,
pod.po_distribution_id,
pod.destination_type_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.destination_organization_id,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
rti.quantity qty,
rti.interface_available_qty
FROM rcv_transactions_interface rti,
po_distributions pod
WHERE interface_transaction_id = v_parent_inter_trx_id
AND pod.line_location_id = rti.po_line_location_id --Bug:5354379
AND pod.po_header_id = rti.po_header_id
AND ( pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL);
SELECT rsup.po_line_location_id,
rsup.po_header_id po_head_id,
rsup.po_line_id line_id,
rsup.rcv_transaction_id rcv_transaction_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rsup.to_organization_id,
rt.po_unit_price unit_price,
rsl.category_id,
rsl.item_description,
rsup.po_line_id,
rt.location_id,
rsup.item_id,
pod.deliver_to_person_id,
pod.deliver_to_location_id,
pod.destination_subinventory,
pod.po_distribution_id,
pod.destination_type_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.destination_organization_id,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
rt.quantity qty,
0 interface_available_qty
FROM rcv_supply rsup,
rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol,
po_distributions pod
WHERE rt.transaction_id = v_parent_trx_id
AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
AND rsup.supply_type_code = 'RECEIVING'
AND poh.po_header_id = rsup.po_header_id
AND pol.po_line_id = rsup.po_line_id
AND pol.po_header_id = poh.po_header_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rt.po_line_location_id = pod.line_location_id
AND pod.distribution_num = x_dist_num
AND ( pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL)
ORDER BY pod.po_distribution_id;
SELECT rti.po_line_location_id,
rti.po_header_id po_head_id,
rti.po_line_id line_id,
rti.interface_transaction_id rcv_transaction_id,
rti.document_num,
rti.document_line_num line_number,
rti.transaction_date transaction_date,
rti.transaction_type,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id,
rti.po_unit_price unit_price,
rti.category_id,
rti.item_description,
rti.po_line_id,
rti.location_id,
rti.item_id,
pod.deliver_to_person_id,
pod.deliver_to_location_id,
pod.destination_subinventory,
pod.po_distribution_id,
pod.destination_type_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.destination_organization_id,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
rti.quantity qty,
rti.interface_available_qty
FROM rcv_transactions_interface rti,
po_distributions pod
WHERE interface_transaction_id = v_parent_inter_trx_id
AND pod.distribution_num = x_dist_num
AND pod.po_header_id = rti.po_header_id
AND pod.line_location_id = rti.po_line_location_id --Bug:5354379
AND ( pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL);
SELECT rsup.po_line_location_id,
rsup.po_header_id po_head_id,
rsup.po_line_id line_id,
rsup.rcv_transaction_id rcv_transaction_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rsup.to_organization_id,
rt.po_unit_price unit_price,
rsl.category_id,
rsl.item_description,
rsup.po_line_id,
rt.location_id,
rsup.item_id,
pod.deliver_to_person_id,
pod.deliver_to_location_id,
pod.destination_subinventory,
pod.po_distribution_id,
pod.destination_type_code,
pod.code_combination_id,
pod.req_distribution_id,
pod.destination_organization_id,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
rt.quantity qty,
0 interface_available_qty
FROM rcv_supply rsup,
rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol,
po_distributions pod
WHERE rt.transaction_id = v_parent_trx_id
AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
AND rsup.supply_type_code = 'RECEIVING'
AND poh.po_header_id = rsup.po_header_id
AND pol.po_line_id = rsup.po_line_id
AND pol.po_header_id = poh.po_header_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rt.po_header_id = pod.po_header_id
AND ( pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL);
SELECT nvl(quantity,0) quantity,unit_of_measure
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND transaction_type = 'DELIVER'
AND group_id = v_group_id
AND po_line_location_id = v_po_line_location_id
AND parent_transaction_id=v_rcv_transaction_id
AND interface_transaction_id < v_interface_transaction_id
ORDER BY interface_transaction_id;
SELECT transaction_id
INTO x_cascaded_table(n).parent_transaction_id
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
SELECT po_header_id
INTO x_cascaded_table(n).po_header_id
FROM rcv_transactions
WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT COUNT(*)
INTO l_num_of_distributions
FROM po_distributions pod,
rcv_supply rs
WHERE pod.line_location_id = rs.po_line_location_id
AND rs.rcv_transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT distribution_num
INTO x_cascaded_table(n).document_distribution_num
FROM po_distributions
WHERE po_distribution_id = x_cascaded_table(n).po_distribution_id;
SELECT COUNT(*)
INTO l_num_of_distributions
FROM po_distributions
WHERE line_location_id = (SELECT po_line_location_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id);
SELECT distribution_num
INTO x_cascaded_table(n).document_distribution_num
FROM po_distributions
WHERE po_distribution_id = x_cascaded_table(n).po_distribution_id;
SELECT quantity
INTO l_total_rec_quantity
FROM rcv_supply
WHERE rcv_transaction_id = x_po_transferrec.rcv_transaction_id
AND supply_type_code = 'RECEIVING';
select segment1
into l_po_num
from po_headers_all
where po_header_id = x_po_transferrec.po_head_id;
select line_num
into l_po_line_num
from po_lines_all
where po_line_id = x_po_transferrec.po_line_id;
select shipment_num
into l_po_line_loc_num
from po_line_locations_all
where line_location_id = x_po_transferrec.po_line_location_id;
select distribution_num
into l_po_dist_num
from po_distributions_all
where po_distribution_id = x_po_transferrec.po_distribution_id;
select release_num
into l_po_rel_num
from po_releases_all
where po_release_id = x_cascaded_table(n).po_release_id;
SELECT po_line_id,po_header_id
INTO l_service_po_line_id,l_po_header_id
FROM rcv_transactions
WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT po_line_id,po_header_id
INTO l_service_po_line_id,l_po_header_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT NVL(order_type_lookup_code, 'QUANTITY'),
NVL(purchase_basis, 'GOODS'),
NVL(matching_basis, 'QUANTITY')
INTO l_value_basis,
l_purchase_basis,
l_matching_basis
FROM po_lines
WHERE po_line_id = l_service_po_line_id;
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');
SELECT transaction_type
INTO l_transaction_type
FROM rcv_transactions
WHERE transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
SELECT parent_interface_txn_id,
parent_transaction_id,
transaction_type
INTO l_parent_interface_txn_id,
l_parent_transaction_id,
l_transaction_type
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_interface_txn_id;
SELECT transaction_type,
parent_transaction_id
INTO l_transaction_type,
l_parent_transaction_id
FROM rcv_transactions
WHERE transaction_id = l_transaction_id;
SELECT nvl(sum(quantity),0)
INTO l_distribution_qty
FROM mtl_supply
WHERE supply_source_id = l_parent_transaction_id
AND po_line_location_id = x_cascaded_table(n).po_line_location_id
AND supply_type_code = 'RECEIVING';
SELECT unit_meas_lookup_code
INTO l_ship_unit
FROM po_line_locations
WHERE line_location_id = x_cascaded_table(n).po_line_location_id;
SELECT nvl(sum(quantity),0)
INTO l_total_deliver_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND transaction_type = 'DELIVER'
AND group_id = x_cascaded_table(n).group_id
AND po_line_location_id = x_po_transferrec.po_line_location_id
AND interface_transaction_id < x_cascaded_table(n).interface_transaction_id;
SELECT nvl(sum(quantity),0)
INTO l_total_receiving_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND transaction_type = 'RECEIVE'
AND auto_transact_code <> 'DELIVER'
AND group_id = x_cascaded_table(n).group_id
AND po_line_location_id = x_po_transferrec.po_line_location_id
AND interface_transaction_id < x_cascaded_table(n).interface_transaction_id;
SELECT nvl(sum(quantity),0)
INTO l_total_deliver_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND transaction_type = 'DELIVER'
AND group_id = x_cascaded_table(n).group_id
AND po_line_location_id = x_po_transferrec.po_line_location_id
AND interface_transaction_id < x_cascaded_table(n).interface_transaction_id;
SELECT quantity
INTO l_receive_quantity
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_parent_interface_txn_id;
SELECT quantity
INTO l_deliver_quantity
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).interface_transaction_id;
select ms.quantity, ms.unit_of_measure
into l_distribution_qty, l_ship_unit
from mtl_supply ms
where ms.supply_source_id = x_po_transferrec.rcv_transaction_id
and ms.po_distribution_id = x_po_transferrec.po_distribution_id
and ms.supply_type_code = 'RECEIVING';
SELECT nvl(sum(ms.quantity),0),
ms.unit_of_measure
INTO l_distribution_qty,
l_ship_unit
FROM mtl_supply ms
WHERE ms.supply_source_id = x_po_transferrec.rcv_transaction_id
AND ms.po_line_location_id = x_po_transferrec.po_line_location_id
AND ms.supply_type_code = 'RECEIVING'
GROUP BY ms.unit_of_measure;
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 --{ --start
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;
SELECT rt.po_revision_num,
rsl.item_description,
rsup.po_release_id,
rt.location_id loc_id,
rt.organization_id,
rt.inspection_status_code,
rt.routing_header_id,
rt.currency_code,
rt.currency_conversion_rate,
rt.currency_conversion_type,
rt.currency_conversion_date,
rt.location_id,
rsup.shipment_header_id,
rsup.shipment_line_id,
rsl.category_id,
rt.vendor_id,
rt.vendor_site_id,
rt.po_unit_price,
rt.movement_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory,
rt.transfer_lpn_id,
rt.oe_order_header_id,
rt.oe_order_line_id,
rt.customer_id,
rt.customer_site_id,
rt.source_document_code
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
rcv_supply rsup
WHERE rt.transaction_id = v_parent_trx_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsup.supply_type_code = 'RECEIVING'
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED';
SELECT rti.po_revision_num,
rti.item_description,
rti.po_release_id,
rti.location_id loc_id,
rti.to_organization_id organization_id,
rti.inspection_status_code,
rti.routing_header_id,
rti.currency_code,
rti.currency_conversion_rate,
rti.currency_conversion_type,
rti.currency_conversion_date,
rti.location_id,
rti.shipment_header_id,
rti.shipment_line_id,
rti.category_id,
rti.vendor_id,
rti.vendor_site_id,
rti.po_unit_price,
rti.movement_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory,
rti.transfer_lpn_id,
rti.oe_order_header_id,
rti.oe_order_line_id,
rti.customer_id,
rti.customer_site_id,
rti.source_document_code
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
SELECT NVL(pll.price_override, pl.unit_price)
INTO x_cascaded_table(n).po_unit_price
FROM po_line_locations pll,
po_lines pl
WHERE pll.line_location_id = x_cascaded_table(n).po_line_location_id
AND pl.po_line_id = x_cascaded_table(n).po_line_id
AND pl.po_line_id = pll.po_line_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = NVL(x_cascaded_table(n).item_id, 0)
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
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 po_line_id,po_header_id
INTO x_cascaded_table(n).po_line_id,l_po_header_id
FROM rcv_transactions
WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
SELECT po_line_id,po_header_id
INTO x_cascaded_table(n).po_line_id,l_po_header_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
select asn_type
into l_asn_type
from rcv_shipment_headers
where shipment_header_id = x_cascaded_table(n).shipment_header_id;
SELECT NVL(order_type_lookup_code, 'QUANTITY'),
NVL(purchase_basis, 'GOODS'),
NVL(matching_basis, 'QUANTITY')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).purchase_basis,
x_cascaded_table(n).matching_basis
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id;
SELECT NVL(value_basis, 'QUANTITY'),
NVL(matching_basis, 'QUANTITY')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).matching_basis
FROM po_line_locations
WHERE line_location_id = x_cascaded_table(n).po_line_location_id;
insert_into_table BOOLEAN := FALSE;
SELECT rt.po_line_location_id,
rt.po_header_id po_head_id,
rt.po_line_id line_id,
rt.transaction_id rcv_transaction_id,
rt.parent_transaction_id grand_parent_txn_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type parent_transaction_type,
rt.quantity qty,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rt.organization_id,
rt.po_unit_price unit_price,
rsl.category_id,
rsl.item_description,
rt.po_line_id,
rt.location_id,
rsl.item_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory destination_subinventory,
rt.po_distribution_id,
rt.destination_type_code,
rt.wip_entity_id,
rt.wip_operation_seq_num,
rt.wip_resource_seq_num,
rt.wip_repetitive_schedule_id,
rt.wip_line_id,
rt.bom_resource_id,
rsl.ussgl_transaction_code
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol
WHERE rt.transaction_id = v_parent_trx_id
AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions rt3
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rt3.transaction_type = 'MATCH'
AND rt3.parent_transaction_id = rt2.transaction_id)
AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions_interface rti
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rti.transaction_type = 'MATCH'
AND rti.parent_transaction_id = rt2.transaction_id)
)
OR ( rt.transaction_type = 'DELIVER'
AND NOT( rt.destination_type_code = 'INVENTORY'
AND rt.source_document_code IN('REQ', 'INVENTORY', 'RMA')))
OR ( rt.transaction_type = 'UNORDERED'
AND NOT EXISTS(SELECT 'processed matched rows'
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = rt.transaction_id
AND rt2.transaction_type = 'MATCH')
AND NOT EXISTS(SELECT 'unprocessed matched rows'
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = rt.transaction_id
AND rti.transaction_type = 'MATCH')
)
)
AND NOT EXISTS(SELECT 'purchase order shipment cancelled or fc'
FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND ( NVL(pll.cancel_flag, 'N') = 'Y'
OR NVL(pll.closed_code, 'OPEN') = 'FINALLY CLOSED'))
AND (rt.shipment_line_id = rsl.shipment_line_id)
AND (poh.po_header_id = rt.po_header_id)
AND (pol.po_line_id = rt.po_line_id)
AND ( rt.dropship_type_code = 3
OR rt.dropship_type_code IS NULL)
ORDER BY rt.transaction_id;
SELECT rti.po_line_location_id,
rti.po_header_id po_head_id,
rti.po_line_id line_id,
rti.interface_transaction_id rcv_transaction_id,
rti.parent_transaction_id grand_parent_txn_id,
rti.document_num,
rti.document_line_num line_number,
rti.transaction_date transaction_date,
rti.transaction_type parent_transaction_type,
rti.quantity qty,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id organization_id,
rti.po_unit_price unit_price,
rti.category_id,
rti.item_description,
rti.po_line_id,
rti.location_id,
rti.item_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory destination_subinventory,
rti.po_distribution_id,
rti.destination_type_code,
rti.wip_entity_id,
rti.wip_operation_seq_num,
rti.wip_resource_seq_num,
rti.wip_repetitive_schedule_id,
rti.wip_line_id,
rti.bom_resource_id,
rti.ussgl_transaction_code
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_interface_txn_id;
SELECT transaction_id
INTO x_cascaded_table(n).parent_transaction_id
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
INTO x_qty_rcv_exception_code
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
asn_debug.put_line('Need to insert into po_interface_errors');
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');
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 correct');
SELECT parent_transaction_id,
transaction_type,
po_line_location_id,
destination_type_code,
source_document_code
INTO l_grand_parent_trx_id,
l_transaction_type,
l_line_loc_id,
l_destination_type_code,
l_source_document_code
FROM rcv_transactions rt
WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
SELECT MAX('record_exist')
INTO l_exist
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rt2.transaction_type = 'MATCH';
SELECT MAX('record_exist')
INTO l_exist1
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rti.transaction_type = 'MATCH';
SELECT MAX('record_exist')
INTO l_exist
FROM rcv_transactions rt2,
rcv_transactions rt3
WHERE rt2.transaction_id = l_grand_parent_trx_id
AND rt2.transaction_type = 'UNORDERED'
AND rt3.transaction_type = 'MATCH'
AND rt3.parent_transaction_id = rt2.transaction_id;
SELECT MAX('record_exist')
INTO l_exist1
FROM rcv_transactions rt2,
rcv_transactions_interface rti
WHERE rt2.transaction_id = l_grand_parent_trx_id
AND rt2.transaction_type = 'UNORDERED'
AND rti.transaction_type = 'MATCH'
AND rti.parent_transaction_id = rt2.transaction_id;
SELECT MAX('record_exist')
INTO l_exist2
FROM po_line_locations pll
WHERE pll.line_location_id = l_line_loc_id
AND ( NVL(pll.cancel_flag, 'N') = 'Y'
OR NVL(pll.closed_code, 'OPEN') = 'FINALLY CLOSED');
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
SELECT rt.unit_of_measure,
rsl.item_id,
rt.primary_unit_of_measure
INTO l_transaction_uom,
l_item_id,
l_primary_uom
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = x_po_correctrec.rcv_transaction_id;
SELECT NVL(SUM(interface_transaction_qty),0)
INTO l_interface_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
--bug# 9583207 to comment group_id condition
--AND group_id = temp_cascaded_table(current_n).group_id
AND transaction_type = 'CORRECT'
AND parent_transaction_id IN ( SELECT transaction_id
FROM rcv_transactions
WHERE parent_transaction_id = x_po_correctrec.rcv_transaction_id);
* but not have inserted it back into rti.
*/
l_grand_parent_trx_id := temp_cascaded_table(current_n).po_line_location_id;
insert_into_table := TRUE;
insert_into_table := TRUE;
insert_into_table := TRUE;
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));
insert_into_table BOOLEAN := FALSE;
SELECT rt.po_line_location_id,
rt.po_header_id,
rt.po_line_id,
rt.transaction_id rcv_transaction_id,
rt.parent_transaction_id grand_parent_txn_id,
poh.segment1 document_num,
pol.line_num line_number,
rt.transaction_date transaction_date,
rt.transaction_type parent_transaction_type,
rt.amount,
rt.organization_id,
rt.location_id,
rsl.job_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.po_distribution_id,
rt.destination_type_code,
rsl.ussgl_transaction_code
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
po_headers poh,
po_lines pol
WHERE rt.transaction_id = v_parent_trx_id
AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions rt3
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rt3.transaction_type = 'MATCH'
AND rt3.parent_transaction_id = rt2.transaction_id)
AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions_interface rti
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rti.transaction_type = 'MATCH'
AND rti.parent_transaction_id = rt2.transaction_id)
)
OR ( rt.transaction_type = 'DELIVER'
AND NOT( rt.destination_type_code = 'INVENTORY'
AND rt.source_document_code IN('REQ', 'INVENTORY', 'RMA')))
OR ( rt.transaction_type = 'UNORDERED'
AND NOT EXISTS(SELECT 'processed matched rows'
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = rt.transaction_id
AND rt2.transaction_type = 'MATCH')
AND NOT EXISTS(SELECT 'unprocessed matched rows'
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = rt.transaction_id
AND rti.transaction_type = 'MATCH')
)
)
AND NOT EXISTS(SELECT 'purchase order shipment cancelled or fc'
FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND ( NVL(pll.cancel_flag, 'N') = 'Y'
OR NVL(pll.closed_code, 'OPEN') = 'FINALLY CLOSED'))
AND (rt.shipment_line_id = rsl.shipment_line_id)
AND (poh.po_header_id = rt.po_header_id)
AND (pol.po_line_id = rt.po_line_id)
ORDER BY rt.transaction_id;
SELECT rti.po_line_location_id,
rti.po_header_id,
rti.po_line_id,
rti.interface_transaction_id rcv_transaction_id,
rti.parent_transaction_id grand_parent_txn_id,
rti.document_num,
rti.document_line_num line_number,
rti.transaction_date transaction_date,
rti.transaction_type parent_transaction_type,
rti.amount,
rti.to_organization_id organization_id,
rti.location_id,
rti.job_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.po_distribution_id,
rti.destination_type_code,
rti.ussgl_transaction_code
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_interface_txn_id;
SELECT transaction_id
INTO x_cascaded_table(n).parent_transaction_id
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
SELECT COUNT(*)
INTO l_time_count
FROM hxc_time_building_blocks bb,
po_lines pol
WHERE bb.time_building_block_id = x_cascaded_table(n).timecard_id
AND bb.object_version_number = x_cascaded_table(n).timecard_ovn
AND bb.SCOPE = 'TIMECARD'
AND bb.resource_type = 'PERSON'
AND bb.resource_id IN(SELECT person_id
FROM per_all_assignments_f
WHERE assignment_type = 'C')
AND pol.po_line_id = x_cascaded_table(n).po_line_id
AND pol.order_type_lookup_code = 'RATE'
AND pol.purchase_basis = 'TEMP LABOR';
SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
INTO x_qty_rcv_exception_code
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
asn_debug.put_line('Need to insert into po_interface_errors');
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE;
asn_debug.put_line('Need to insert a row into po_interface_errors');
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE;
asn_debug.put_line('Need to insert a row into po_interface_errors for correct');
SELECT parent_transaction_id,
transaction_type,
po_line_location_id,
destination_type_code,
source_document_code
INTO l_grand_parent_trx_id,
l_transaction_type,
l_line_loc_id,
l_destination_type_code,
l_source_document_code
FROM rcv_transactions rt
WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
SELECT MAX('record_exist')
INTO l_exist
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rt2.transaction_type = 'MATCH';
SELECT MAX('record_exist')
INTO l_exist1
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rti.transaction_type = 'MATCH';
SELECT MAX('record_exist')
INTO l_exist
FROM rcv_transactions rt2,
rcv_transactions rt3
WHERE rt2.transaction_id = l_grand_parent_trx_id
AND rt2.transaction_type = 'UNORDERED'
AND rt3.transaction_type = 'MATCH'
AND rt3.parent_transaction_id = rt2.transaction_id;
SELECT MAX('record_exist')
INTO l_exist1
FROM rcv_transactions rt2,
rcv_transactions_interface rti
WHERE rt2.transaction_id = l_grand_parent_trx_id
AND rt2.transaction_type = 'UNORDERED'
AND rti.transaction_type = 'MATCH'
AND rti.parent_transaction_id = rt2.transaction_id;
SELECT MAX('record_exist')
INTO l_exist2
FROM po_line_locations pll
WHERE pll.line_location_id = l_line_loc_id
AND ( NVL(pll.cancel_flag, 'N') = 'Y'
OR NVL(pll.closed_code, 'OPEN') = 'FINALLY CLOSED');
temp_cascaded_table.DELETE;
insert_into_table := FALSE;
* but not have inserted it back into rti.
*/
l_grand_parent_trx_id := temp_cascaded_table(current_n).po_line_location_id;
insert_into_table := TRUE;
insert_into_table := TRUE;
insert_into_table := TRUE;
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 rt.po_revision_num,
rsl.item_description,
rt.po_release_id,
rt.location_id loc_id,
rt.organization_id,
rt.routing_header_id,
rt.destination_type_code,
rt.destination_context,
rt.inspection_status_code,
rt.currency_code,
rt.currency_conversion_rate,
rt.currency_conversion_type,
rt.currency_conversion_date,
rt.location_id,
rt.shipment_header_id,
rt.shipment_line_id,
rsl.category_id,
rt.vendor_id,
rt.vendor_site_id,
rt.po_unit_price,
rt.movement_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory,
rt.lpn_id,
rt.transfer_lpn_id,
rt.oe_order_header_id,
rt.oe_order_line_id,
rt.customer_id,
rt.customer_site_id,
rt.source_document_code,
rt.job_id
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.transaction_id = v_parent_trx_id
AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions rt3
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rt3.transaction_type = 'MATCH'
AND rt3.parent_transaction_id = rt2.transaction_id)
AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
FROM rcv_transactions rt2,
rcv_transactions_interface rti
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'UNORDERED'
AND rti.transaction_type = 'MATCH'
AND rti.parent_transaction_id = rt2.transaction_id)
)
OR ( rt.transaction_type = 'DELIVER'
AND NOT( rt.destination_type_code = 'INVENTORY'
AND rt.source_document_code IN('REQ', 'INVENTORY')))
OR ( rt.transaction_type = 'UNORDERED'
AND NOT EXISTS(SELECT 'processed matched rows'
FROM rcv_transactions rt2
WHERE rt2.parent_transaction_id = rt.transaction_id
AND rt2.transaction_type = 'MATCH')
AND NOT EXISTS(SELECT 'unprocessed matched rows'
FROM rcv_transactions_interface rti
WHERE rti.parent_transaction_id = rt.transaction_id
AND rti.transaction_type = 'MATCH')
)
)
AND NOT EXISTS(SELECT 'purchase order shipment cancelled or fc'
FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND ( NVL(pll.cancel_flag, 'N') = 'Y'
OR NVL(pll.closed_code, 'OPEN') = 'FINALLY CLOSED'))
AND (rt.shipment_line_id = rsl.shipment_line_id)
ORDER BY rt.transaction_id;
SELECT rti.po_revision_num,
rti.item_description,
rti.po_release_id,
rti.location_id loc_id,
rti.to_organization_id organization_id,
rti.routing_header_id,
rti.destination_type_code,
rti.destination_context,
rti.inspection_status_code,
rti.currency_code,
rti.currency_conversion_rate,
rti.currency_conversion_type,
rti.currency_conversion_date,
rti.location_id,
rti.shipment_header_id,
rti.shipment_line_id,
rti.category_id,
rti.vendor_id,
rti.vendor_site_id,
rti.po_unit_price,
rti.movement_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory,
rti.lpn_id,
rti.transfer_lpn_id,
rti.oe_order_header_id,
rti.oe_order_line_id,
rti.customer_id,
rti.customer_site_id,
rti.source_document_code,
rti.job_id
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
SELECT NVL(pll.price_override, pl.unit_price)
INTO x_cascaded_table(n).po_unit_price
FROM po_line_locations pll,
po_lines pl
WHERE pll.line_location_id = x_cascaded_table(n).po_line_location_id
AND pl.po_line_id = x_cascaded_table(n).po_line_id
AND pl.po_line_id = pll.po_line_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
SELECT type_lookup_code
INTO po_lookup_code_record.lookup_code
FROM po_headers
WHERE po_header_id = x_cascaded_table(n).po_header_id;
SELECT quantity_shipped,
secondary_quantity_shipped
INTO l_quantity_shipped,
l_secondary_quantity_shipped
FROM rcv_shipment_lines
WHERE shipment_header_id = x_cascaded_table(n).shipment_header_id
AND shipment_line_id = x_cascaded_table(n).shipment_line_id;
SELECT quantity,
secondary_quantity
INTO l_quantity_shipped,
l_secondary_quantity_shipped
FROM po_line_locations
WHERE line_location_id = x_cascaded_table(n).po_line_location_id;
SELECT parent_interface_txn_id,
transaction_type,
unit_of_measure,
interface_available_qty
INTO l_grandparent_interface_trx_id,
l_parent_trx_type,
l_parent_uom,
l_interface_qty
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT interface_available_qty,
unit_of_measure
INTO l_interface_qty,
l_grandparent_uom
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_grandparent_interface_trx_id;
SELECT parent_interface_txn_id,
transaction_type,
interface_available_amt
INTO l_grandparent_interface_trx_id,
l_parent_trx_type,
l_interface_amt
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
SELECT interface_available_amt
INTO x_interface_amt
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_grandparent_interface_trx_id;
PROCEDURE update_interface_available_qty(
x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
n IN BINARY_INTEGER
) IS
l_grandparent_interface_trx_id NUMBER;
/* Update interface_available_qty for the current rti row. */
x_cascaded_table(n).interface_available_qty := x_cascaded_table(n).quantity;
asn_debug.put_line('update_interace_qty x_interface_qty ' || x_cascaded_table(n).interface_available_qty);
/* Update interface_available_qty in the parent rti row */
BEGIN
SELECT parent_interface_txn_id,
transaction_type
INTO l_grandparent_interface_trx_id,
l_parent_trx_type
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
/* For an rti of receive or an rti row with parent in RT, select
* will not result any rows.
*/
NULL;
asn_debug.put_line('update_interace_qty l_grandparent_interface_trx_id ' || l_grandparent_interface_trx_id);
asn_debug.put_line('update_interace_qty l_parent_trx_type ' || l_parent_trx_type);
UPDATE rcv_transactions_interface rti
SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
asn_debug.put_line('update_interace_qty calling update_total_transaction_qty ');
/* Update temp table with the correct quantity*/
update_total_transaction_qty(x_cascaded_table(n).interface_transaction_id,
x_cascaded_table(n).parent_transaction_id,
x_cascaded_table(n).parent_interface_txn_id,
x_cascaded_table(n).primary_quantity,
x_cascaded_table(n).transaction_type,
l_interface_transaction_qty
);
rcv_error_pkg.set_sql_error_message('update_interface_available_qty', '000');
END update_interface_available_qty;
PROCEDURE update_interface_available_amt(
x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
n IN BINARY_INTEGER
) IS
l_grandparent_interface_trx_id NUMBER;
/* Update interface_available_qty for the current rti row. */
x_cascaded_table(n).interface_available_amt := x_cascaded_table(n).amount;
asn_debug.put_line('update_interace_qty x_interface_amt ' || x_cascaded_table(n).interface_available_amt);
/* Update interface_available_qty in the parent rti row */
BEGIN
SELECT parent_interface_txn_id,
transaction_type
INTO l_grandparent_interface_trx_id,
l_parent_trx_type
FROM rcv_transactions_interface
WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
/* For an rti of receive or an rti row with parent in RT, select
* will not result any rows.
*/
NULL;
asn_debug.put_line('update_interface_amt l_grandparent_interface_trx_id ' || l_grandparent_interface_trx_id);
asn_debug.put_line('update_interface_amt l_parent_trx_type ' || l_parent_trx_type);
UPDATE rcv_transactions_interface rti
SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
asn_debug.put_line('update_interface_amt calling update_total_transaction_amt ');
/* Update temp table with the correct quantity */
update_total_transaction_amt(x_cascaded_table(n).interface_transaction_id,
x_cascaded_table(n).parent_transaction_id,
x_cascaded_table(n).parent_interface_txn_id,
x_cascaded_table(n).amount,
x_cascaded_table(n).transaction_type,
l_interface_transaction_amt
);
rcv_error_pkg.set_sql_error_message('update_interface_available_amt', '000');
END update_interface_available_amt;
PROCEDURE update_total_transaction_qty(
p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
) IS
l_child_count NUMBER;
/* Insert into temp table since we need to update this value
* when we process children.
*/
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('update_total_transaction_qty p_parent_transaction_id ' || p_parent_transaction_id);
asn_debug.put_line('update_total_transaction_qty child count ' || l_child_count);
asn_debug.put_line('update_total_transaction_qty p_primary_quantity ' || p_primary_quantity);
* We need to update interface_transaction_qty for SHIP transaction
* also. Otherwise we will be able to create 2 asn shipment lines
* for the same shipment if they are run in the same group.
*/
IF ( ( p_parent_transaction_id IS NULL
AND p_transaction_type IN('RECEIVE', 'SHIP'))
OR (p_parent_transaction_id IS NOT NULL)) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('update_total_transaction_qty updating rti row with parent' || p_interface_transaction_id || ',quantity ' || p_primary_quantity);
asn_debug.put_line('update_total_transaction_qty parent_interface_txn_id is not null ');
asn_debug.put_line('update_total_transaction_qty p_primary_quantity ' || p_primary_quantity);
SELECT parent_transaction_id
INTO l_grand_parent_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_parent_interface_txn_id;
asn_debug.put_line('update_total_transaction_qty l_grand_parent_id ' || l_grand_parent_id);
UPDATE rcv_transactions_interface rti
SET rti.interface_transaction_qty = rti.interface_transaction_qty + p_primary_quantity
WHERE interface_transaction_id = p_parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_transaction_qty = rti.interface_transaction_qty - p_primary_quantity
WHERE interface_transaction_id = p_parent_interface_txn_id;
rcv_error_pkg.set_sql_error_message('update_total_transaction_qty', '000');
END update_total_transaction_qty;
PROCEDURE update_total_transaction_amt(
p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
p_amount IN rcv_transactions_interface.amount%TYPE,
p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
) IS
l_child_count NUMBER;
/* Insert into temp table since we need to update this value
* when we process children.
*/
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('update_total_transaction_amt p_parent_transaction_id ' || p_parent_transaction_id);
asn_debug.put_line('update_total_transaction_amt child count ' || l_child_count);
asn_debug.put_line('update_total_transaction_amt p_primary_quantity ' || p_amount);
asn_debug.put_line('update_total_transaction_amt updating rti row with parent' || p_interface_transaction_id || ',amount ' || p_amount);
asn_debug.put_line('update_total_transaction_amt parent_interface_txn_id is not null ');
asn_debug.put_line('update_total_transaction_amt p_amount ' || p_amount);
SELECT parent_transaction_id
INTO l_grand_parent_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_parent_interface_txn_id;
asn_debug.put_line('update_total_transaction_amt l_grand_parent_id ' || l_grand_parent_id);
UPDATE rcv_transactions_interface rti
SET rti.interface_transaction_amt = rti.interface_transaction_amt + p_amount
WHERE interface_transaction_id = p_parent_interface_txn_id;
UPDATE rcv_transactions_interface rti
SET rti.interface_transaction_amt = rti.interface_transaction_amt - p_amount
WHERE interface_transaction_id = p_parent_interface_txn_id;
rcv_error_pkg.set_sql_error_message('update_total_transaction_amt', '000');
END update_total_transaction_amt;
SELECT NVL(pll.value_basis, 'QUANTITY'),
NVL(pll.matching_basis, 'QUANTITY'),
NVL(pl.purchase_basis,'GOODS')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).matching_basis,
x_cascaded_table(n).purchase_basis
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pl.po_line_id = x_cascaded_table(n).po_line_id
and pl.po_line_id = pll.po_line_id
and pll.line_location_id =x_cascaded_table(n).po_line_location_id;
SELECT NVL(pll.value_basis, 'QUANTITY'),
NVL(pll.matching_basis, 'QUANTITY'),
NVL(pl.purchase_basis,'GOODS')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).matching_basis,
x_cascaded_table(n).purchase_basis
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pl.po_line_id = x_cascaded_table(n).po_line_id
and pl.po_line_id = pll.po_line_id
and pll.shipment_num =
x_cascaded_table(n).document_shipment_line_num;
SELECT NVL(order_type_lookup_code, 'QUANTITY'),
NVL(purchase_basis, 'GOODS'),
NVL(matching_basis, 'QUANTITY')
INTO x_cascaded_table(n).value_basis,
x_cascaded_table(n).purchase_basis,
x_cascaded_table(n).matching_basis
FROM po_lines_all
WHERE po_line_id = x_cascaded_table(n).po_line_id;
'update_total_transaction_amt',
'000');
select deliver_to_person_id
into l_deliver_to_person_id
from po_distributions_all
where po_distribution_id = x_cascaded_table(n).po_distribution_id;
select count(po_distribution_id)
into l_distribution_count
from po_distributions_all
where line_location_id = x_cascaded_table(n).po_line_location_id;
select deliver_to_person_id
into l_deliver_to_person_id
from po_distributions_all
where line_location_id = x_cascaded_table(n).po_line_location_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;
SELECT count(1)
INTO l_lsl_exists
FROM rcv_shipment_lines
WHERE lcm_shipment_line_id is not null
AND lcm_shipment_line_id = x_cascaded_table(n).lcm_shipment_line_id;
SELECT count(1)
INTO l_lsl_exists
FROM rcv_transactions_interface
WHERE lcm_shipment_line_id is not null
AND lcm_shipment_line_id = x_cascaded_table(n).lcm_shipment_line_id
AND processing_status_code = 'RUNNING'
AND interface_transaction_id <> x_cascaded_table(n).interface_transaction_id;
SELECT rsl.quantity_shipped,
rsl.unit_of_measure,
rsl.secondary_quantity_shipped,
rsl.secondary_unit_of_measure,
rsl.lcm_shipment_line_id
INTO l_rsl_qty, l_rsl_uom, l_sec_rsl_qty, l_sec_rsl_uom, l_lsl_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = x_cascaded_table(n).shipment_line_id;
SELECT cod.currency_code, poh.currency_code
INTO l_functional_currency, l_po_currency
FROM cst_organization_definitions cod,
po_headers_all poh
WHERE cod.operating_unit = poh.org_id
AND cod.organization_id = x_cascaded_table(n).to_organization_id
AND poh.po_header_id = x_cascaded_table(n).po_header_id;
SELECT count(1)
INTO l_count
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).source_transaction_num;
SELECT count(1)
INTO l_count
FROM rcv_transactions_interface
WHERE source_transaction_num = x_cascaded_table(n).source_transaction_num
AND processing_status_code = 'RUNNING'
AND interface_transaction_id <> x_cascaded_table(n).interface_transaction_id;
SELECT mmt_transaction_id
INTO l_mmt_transaction_id
FROM rcv_shipment_lines
WHERE shipment_line_id = x_cascaded_table(n).shipment_line_id;
SELECT COUNT(*)
INTO l_record_exists
FROM mtl_material_transactions
WHERE transaction_id = l_mmt_transaction_id;
select count(*)
into l_count
from rcv_transactions rt,
wsh_delivery_details wdd
where rt.transaction_id = x_cascaded_table(n).parent_transaction_id
and rt.transaction_type in ('RETURN TO RECEIVING', 'RETURN TO VENDOR')
and rt.interface_source_line_id = wdd.delivery_detail_id
and wdd.source_code ='RTV';