[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'populate_default_values: 3 - before inv_rcv_std_rcpt_apis.insert_txn_interface '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4
);
inv_rcv_std_rcpt_apis.insert_txn_interface(
p_rcv_transaction_rec
, p_rcv_rcpt_rec
, p_group_id
, l_transaction_type
, p_organization_id
, p_rcv_transaction_rec.deliver_to_location_id
, p_source_type
, NULL
, p_project_id
, p_task_id
, p_express_transaction--Bug 5550783
);
'populate_default_values: 4 - after inv_rcv_std_rcpt_apis.insert_txn_interface ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4
);
'populate_default_values: 5 - before inv_rcv_std_deliver_apis.insert_lot_serial '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4
);
inv_rcv_std_deliver_apis.insert_lot_serial(
l_lot_serial_break_tbl
, p_transaction_temp_id
, p_lot_control_code
, p_serial_control_code
, l_interface_transaction_id);
'About exit populate_default_values: 6 - after inv_rcv_std_deliver_apis.insert_lot_serial '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT 'N' line_chkbox
, 'VENDOR' source_type_code
, 'VENDOR' receipt_source_code
, 'PO' order_type_code
, '' order_type
, poll.po_header_id po_header_id
, poh.segment1 po_number
, poll.po_line_id po_line_id
, pol.line_num po_line_number
, poll.line_location_id po_line_location_id
, poll.shipment_num po_shipment_number
, poll.po_release_id po_release_id
, por.release_num po_release_number
, TO_NUMBER(NULL) req_header_id
, NULL req_number
, TO_NUMBER(NULL) req_line_id
, TO_NUMBER(NULL) req_line
, TO_NUMBER(NULL) req_distribution_id
--Passing as NULL for the columns for which values are not known. --Bug #3878174
, TO_NUMBER(NULL) rcv_shipment_header_id
, NULL rcv_shipment_number
, TO_NUMBER(NULL) rcv_shipment_line_id
, TO_NUMBER(NULL) rcv_line_number
, TO_NUMBER(NULL) from_organization_id
/*
, poh.po_header_id rcv_shipment_header_id
, poh.segment1 rcv_shipment_number
, pol.po_line_id rcv_shipment_line_id
, pol.line_num rcv_line_number
, poh.po_header_id from_organization_id
*/
, poll.ship_to_organization_id to_organization_id
, poh.vendor_id vendor_id
, '' SOURCE
, poh.vendor_site_id vendor_site_id
, '' outside_operation_flag
, pol.item_id item_id
, NULL uom_code
-- , pol.unit_meas_lookup_code primary_uom
, msi.primary_unit_of_measure primary_uom /* Bug 5665041:Primary UOM should be taken from MSI*/
, mum.uom_class primary_uom_class
, NULL item_allowed_units_lookup_code
, NULL item_locator_control
, '' restrict_locators_code
, '' restrict_subinventories_code
, NULL shelf_life_code
, NULL shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, NULL item_rev_control_flag_from
, NULL item_number
, pol.item_revision item_revision
, pol.item_description item_description
, pol.category_id item_category_id
, '' hazard_class
, '' un_number
, pol.vendor_product_num vendor_item_number
, poll.ship_to_location_id ship_to_location_id
, '' ship_to_location
, NULL packing_slip
, poll.receiving_routing_id routing_id
, '' routing_name
, poll.need_by_date need_by_date
, NVL(poll.promised_date, poll.need_by_date) expected_receipt_date
, poll.quantity ordered_qty
, pol.unit_meas_lookup_code ordered_uom
, NULL ussgl_transaction_code
, poll.government_context government_context
, poll.inspection_required_flag inspection_required_flag
, poll.receipt_required_flag receipt_required_flag
, poll.enforce_ship_to_location_code enforce_ship_to_location_code
, NVL(poll.price_override, pol.unit_price) unit_price
, poh.currency_code currency_code
, poh.rate_type currency_conversion_type
, poh.rate_date currency_conversion_date
, poh.rate currency_conversion_rate
, poh.note_to_receiver note_to_receiver
, pod.destination_type_code destination_type_code
, pod.deliver_to_person_id deliver_to_person_id
, pod.deliver_to_location_id deliver_to_location_id
, pod.destination_subinventory destination_subinventory
, poll.attribute_category attribute_category
, poll.attribute1 attribute1
, poll.attribute2 attribute2
, poll.attribute3 attribute3
, poll.attribute4 attribute4
, poll.attribute5 attribute5
, poll.attribute6 attribute6
, poll.attribute7 attribute7
, poll.attribute8 attribute8
, poll.attribute9 attribute9
, poll.attribute10 attribute10
, poll.attribute11 attribute11
, poll.attribute12 attribute12
, poll.attribute13 attribute13
, poll.attribute14 attribute14
, poll.attribute15 attribute15
, poll.closed_code closed_code
, NULL asn_type
, NULL bill_of_lading
, TO_DATE(NULL) shipped_date
, NULL freight_carrier_code
, NULL waybill_airbill_num
, NULL freight_bill_num
, NULL vendor_lot_num
, NULL container_num
, NULL truck_num
, NULL bar_code_label
, '' rate_type_display
, poll.match_option match_option
, poll.country_of_origin_code country_of_origin_code
, TO_NUMBER(NULL) oe_order_header_id
, TO_NUMBER(NULL) oe_order_num
, TO_NUMBER(NULL) oe_order_line_id
, TO_NUMBER(NULL) oe_order_line_num
, TO_NUMBER(NULL) customer_id
, TO_NUMBER(NULL) customer_site_id
, NULL customer_item_num
, NULL pll_note_to_receiver
, pod.po_distribution_id
, pod.quantity_ordered - pod.quantity_delivered qty_ordered
, 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
, '' destination_type
, '' LOCATION
, pod.rate currency_conversion_rate_pod
, pod.rate_date currency_conversion_date_pod
, pod.project_id project_id
, pod.task_id task_id
, pol.secondary_uom secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, pol.secondary_qty secondary_quantity --OPM Convergence
FROM po_headers poh
, po_line_locations poll
, po_lines pol
, po_releases por
, mtl_system_items msi
, mtl_units_of_measure mum
, po_distributions pod
WHERE pod.po_distribution_id = v_po_distribution_id
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.po_release_id = por.po_release_id(+)
AND pod.line_location_id = poll.line_location_id
AND mum.unit_of_measure(+) = pol.unit_meas_lookup_code
AND NVL(msi.organization_id, poll.ship_to_organization_id) = poll.ship_to_organization_id
AND msi.inventory_item_id(+) = pol.item_id;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT 'N' line_chkbox
, 'VENDOR' source_type_code
, 'VENDOR' receipt_source_code
, 'PO' order_type_code
, '' order_type
, poll.po_header_id po_header_id
, poh.segment1 po_number
, poll.po_line_id po_line_id
, pol.line_num po_line_number
, poll.line_location_id po_line_location_id
, poll.shipment_num po_shipment_number
, poll.po_release_id po_release_id
, por.release_num po_release_number
, TO_NUMBER(NULL) req_header_id
, NULL req_number
, TO_NUMBER(NULL) req_line_id
, TO_NUMBER(NULL) req_line
, TO_NUMBER(NULL) req_distribution_id
--Passing as NULL for the columns for which values are not known. --Bug #3878174
, TO_NUMBER(NULL) rcv_shipment_header_id
, NULL rcv_shipment_number
, TO_NUMBER(NULL) rcv_shipment_line_id
, TO_NUMBER(NULL) rcv_line_number
, TO_NUMBER(NULL) from_organization_id
/*
, poh.po_header_id rcv_shipment_header_id
, poh.segment1 rcv_shipment_number
, pol.po_line_id rcv_shipment_line_id
, pol.line_num rcv_line_number
, poh.po_header_id from_organization_id
*/
, poll.ship_to_organization_id to_organization_id
, poh.vendor_id vendor_id
, '' SOURCE
, poh.vendor_site_id vendor_site_id
, '' outside_operation_flag
, pol.item_id item_id
, -- Bug 2073164
NULL uom_code
-- , pol.unit_meas_lookup_code primary_uom
, msi.primary_unit_of_measure primary_uom /* Bug 5665041:Primary UOM should be taken from MSI*/
, mum.uom_class primary_uom_class
, NULL item_allowed_units_lookup_code
, NULL item_locator_control
, '' restrict_locators_code
, '' restrict_subinventories_code
, NULL shelf_life_code
, NULL shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, NULL item_rev_control_flag_from
, NULL item_number
, pol.item_revision item_revision
, pol.item_description item_description
, pol.category_id item_category_id
, '' hazard_class
, '' un_number
, pol.vendor_product_num vendor_item_number
, poll.ship_to_location_id ship_to_location_id
, '' ship_to_location
, NULL packing_slip
, poll.receiving_routing_id routing_id
, '' routing_name
, poll.need_by_date need_by_date
, NVL(poll.promised_date, poll.need_by_date) expected_receipt_date
, poll.quantity ordered_qty
, pol.unit_meas_lookup_code ordered_uom
, NULL ussgl_transaction_code
, poll.government_context government_context
, poll.inspection_required_flag inspection_required_flag
, poll.receipt_required_flag receipt_required_flag
, poll.enforce_ship_to_location_code enforce_ship_to_location_code
, NVL(poll.price_override, pol.unit_price) unit_price
, poh.currency_code currency_code
, poh.rate_type currency_conversion_type
, poh.rate_date currency_conversion_date
, poh.rate currency_conversion_rate
, poh.note_to_receiver note_to_receiver
, pod.destination_type_code destination_type_code
, pod.deliver_to_person_id deliver_to_person_id
, pod.deliver_to_location_id deliver_to_location_id
, pod.destination_subinventory destination_subinventory
, poll.attribute_category attribute_category
, poll.attribute1 attribute1
, poll.attribute2 attribute2
, poll.attribute3 attribute3
, poll.attribute4 attribute4
, poll.attribute5 attribute5
, poll.attribute6 attribute6
, poll.attribute7 attribute7
, poll.attribute8 attribute8
, poll.attribute9 attribute9
, poll.attribute10 attribute10
, poll.attribute11 attribute11
, poll.attribute12 attribute12
, poll.attribute13 attribute13
, poll.attribute14 attribute14
, poll.attribute15 attribute15
, poll.closed_code closed_code
, NULL asn_type
, NULL bill_of_lading
, TO_DATE(NULL) shipped_date
, NULL freight_carrier_code
, NULL waybill_airbill_num
, NULL freight_bill_num
, NULL vendor_lot_num
, NULL container_num
, NULL truck_num
, NULL bar_code_label
, '' rate_type_display
, poll.match_option match_option
, poll.country_of_origin_code country_of_origin_code
, TO_NUMBER(NULL) oe_order_header_id
, TO_NUMBER(NULL) oe_order_num
, TO_NUMBER(NULL) oe_order_line_id
, TO_NUMBER(NULL) oe_order_line_num
, TO_NUMBER(NULL) customer_id
, TO_NUMBER(NULL) customer_site_id
, NULL customer_item_num
, NULL pll_note_to_receiver
, --POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
pod.po_distribution_id
, pod.quantity_ordered - pod.quantity_delivered qty_ordered
, 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
, '' destination_type
, '' LOCATION
, pod.rate currency_conversion_rate_pod
, pod.rate_date currency_conversion_date_pod
, pod.project_id project_id
, pod.task_id task_id
, pol.secondary_uom secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, pol.secondary_qty secondary_quantity --OPM Convergence
FROM po_headers poh
, po_line_locations poll
, po_lines pol
, po_releases por
, mtl_system_items msi
, mtl_units_of_measure mum
, po_distributions pod
WHERE pod.po_distribution_id = v_po_distribution_id
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.po_release_id = por.po_release_id(+)
AND pod.line_location_id = poll.line_location_id
AND mum.unit_of_measure(+) = pol.unit_meas_lookup_code
AND NVL(msi.organization_id, poll.ship_to_organization_id) = poll.ship_to_organization_id
AND msi.inventory_item_id(+) = pol.item_id
AND (p_project_id IS NULL
OR(p_project_id = -9999
AND pod.project_id IS NULL) -- bug 2669021
OR pod.project_id = p_project_id)
AND (p_task_id IS NULL
OR pod.task_id = p_task_id);
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
--BUG 3326408,3346758,3405320
--If there are any serials confirmed from the UI for an item that is
--lot controlled and serial control dynamic at SO issue,
--do not NULL out serial_transaction_temp_id. In other cases,
--NULL OUT serial_temp_id so that split_lot_serial does not look at MSNI
IF (l_rcv_rcpt_rec.lot_control_code = 2 AND
l_rcv_rcpt_rec.serial_number_control_code IN(1,6)) THEN
IF (l_debug = 1) THEN
print_debug('create_po_drct_dlvr_rti_rec 9.6: serial_control_code IS 6, need TO NULL OUT mtli', 4);
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT 'N' line_chkbox
, 'INTERNAL' source_type_code
, DECODE(rsl.source_document_code, 'INVENTORY', 'INVENTORY', 'REQ', 'INTERNAL ORDER') receipt_source_code
, rsl.source_document_code order_type_code
, '' order_type
--Passing as NULL for the columns for which value is not known. --Bug #3878174
, TO_NUMBER(NULL) po_header_id
, NULL po_number
, TO_NUMBER(NULL) po_line_id
, TO_NUMBER(NULL) po_line_number
, TO_NUMBER(NULL) po_line_location_id
, NULL po_shipment_number
, TO_NUMBER(NULL) po_release_id
, TO_NUMBER(NULL) po_release_number
/*
, rsh.shipment_header_id po_header_id
, rsh.shipment_num po_number
, rsl.shipment_line_id po_line_id
, rsl.line_num po_line_number
, rsl.shipment_line_id po_line_location_id
, rsl.line_num po_shipment_number
, rsh.shipment_header_id po_release_id
, rsh.shipment_header_id po_release_number
*/
, porh.requisition_header_id req_header_id
, porh.segment1 req_number
, porl.requisition_line_id req_line_id
, porl.line_num req_line
, rsl.req_distribution_id req_distribution_id
, rsl.shipment_header_id rcv_shipment_header_id
, rsh.shipment_num rcv_shipment_number
, rsl.shipment_line_id rcv_shipment_line_id
, rsl.line_num rcv_line_number
, rsl.from_organization_id from_organization_id
, rsl.to_organization_id to_organization_id
, rsl.shipment_line_id vendor_id
, '' SOURCE
, TO_NUMBER(NULL) vendor_site_id
, 'N' outside_operation_flag
, rsl.item_id item_id
, -- Bug 2073164
NULL uom_code
, rsl.unit_of_measure primary_uom
, mum.uom_class primary_uom_class
, NVL(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
, NVL(msi.location_control_code, 1) item_locator_control
, DECODE(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
, DECODE(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
, NVL(msi.shelf_life_code, 1) shelf_life_code
, NVL(msi.shelf_life_days, 0) shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, DECODE(msi1.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_from
, NULL item_number
, rsl.item_revision item_revision
, rsl.item_description item_description
, rsl.category_id item_category_id
, '' hazard_class
, '' un_number
, rsl.vendor_item_num vendor_item_number
, rsh.ship_to_location_id ship_to_location_id
, '' ship_to_location
, rsh.packing_slip packing_slip
, rsl.routing_header_id routing_id
, '' routing_name
, porl.need_by_date need_by_date
, rsh.expected_receipt_date expected_receipt_date
, rsl.quantity_shipped ordered_qty
, rsl.primary_unit_of_measure ordered_uom
, rsh.ussgl_transaction_code ussgl_transaction_code
, rsh.government_context government_context
, NULL inspection_required_flag
, NULL receipt_required_flag
, NULL enforce_ship_to_location_code
, TO_NUMBER(NULL) unit_price
, NULL currency_code
, NULL currency_conversion_type
, TO_DATE(NULL) currency_conversion_date
, TO_NUMBER(NULL) currency_conversion_rate
, NULL note_to_receiver
, --PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
rsl.destination_type_code destination_type_code
, rsl.deliver_to_person_id deliver_to_person_id
, rsl.deliver_to_location_id deliver_to_location_id
, rsl.to_subinventory destination_subinventory
, rsl.attribute_category attribute_category
, rsl.attribute1 attribute1
, rsl.attribute2 attribute2
, rsl.attribute3 attribute3
, rsl.attribute4 attribute4
, rsl.attribute5 attribute5
, rsl.attribute6 attribute6
, rsl.attribute7 attribute7
, rsl.attribute8 attribute8
, rsl.attribute9 attribute9
, rsl.attribute10 attribute10
, rsl.attribute11 attribute11
, rsl.attribute12 attribute12
, rsl.attribute13 attribute13
, rsl.attribute14 attribute14
, rsl.attribute15 attribute15
, 'OPEN' closed_code
, NULL asn_type
, rsh.bill_of_lading bill_of_lading
, rsh.shipped_date shipped_date
, rsh.freight_carrier_code freight_carrier_code
, rsh.waybill_airbill_num waybill_airbill_num
, rsh.freight_bill_number freight_bill_num
, rsl.vendor_lot_num vendor_lot_num
, rsl.container_num container_num
, rsl.truck_num truck_num
, rsl.bar_code_label bar_code_label
, NULL rate_type_display
, 'P' match_option
, NULL country_of_origin_code
, TO_NUMBER(NULL) oe_order_header_id
, TO_NUMBER(NULL) oe_order_num
, TO_NUMBER(NULL) oe_order_line_id
, TO_NUMBER(NULL) oe_order_line_num
, TO_NUMBER(NULL) customer_id
, TO_NUMBER(NULL) customer_site_id
, NULL customer_item_num
, NULL pll_note_to_receiver
, --PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
NULL po_distribution_id
, NULL qty_ordered
, NULL wip_entity_id
, NULL wip_operation_seq_num
, NULL wip_resource_seq_num
, NULL wip_repetitive_schedule_id
, NULL wip_line_id
, NULL bom_resource_id
, '' destination_type
, '' LOCATION
, NULL currency_conversion_rate_pod
, NULL currency_conversion_date_pod
, NULL project_id
, NULL task_id
, NULL secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, NULL secondary_quantity --OPM Convergence
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_requisition_headers porh
, po_requisition_lines porl
, mtl_system_items msi
, mtl_system_items msi1
, mtl_units_of_measure mum
WHERE rsh.receipt_source_code <> 'VENDOR'
AND rsl.requisition_line_id = porl.requisition_line_id(+)
AND porl.requisition_header_id = porh.requisition_header_id(+)
AND rsh.shipment_header_id = rsl.shipment_header_id
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND msi.organization_id(+) = rsl.to_organization_id
AND msi.inventory_item_id(+) = rsl.item_id
AND msi1.organization_id(+) = rsl.from_organization_id
AND msi1.inventory_item_id(+) = rsl.item_id
AND rsl.shipment_line_id = v_shipment_line_id
AND (
(
rsl.source_document_code = 'REQ'
AND EXISTS(
SELECT '1'
FROM po_req_distributions_all prd
WHERE prd.requisition_line_id = porl.requisition_line_id
AND
(
p_project_id IS NULL
OR(p_project_id = -9999
AND prd.project_id IS NULL)
OR -- bug 2669021
prd.project_id = p_project_id
)
AND (p_task_id IS NULL
OR prd.task_id = p_task_id))
)
OR rsl.source_document_code <> 'REQ'
);
SELECT lot_number, transaction_quantity
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_transaction_temp_id ;
SELECT receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id
AND ship_to_org_id = p_organization_id;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT lot_number
INTO l_lot_number
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_transaction_temp_id ;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
l_rcpt_match_table_detail.DELETE; --13972742
SELECT cost_group_id
INTO l_rcv_transaction_rec.transfer_cost_group_id
FROM rcv_shipment_lines
WHERE shipment_line_id = l_rcv_transaction_rec.rcv_shipment_line_id;
print_debug('create_int_shp_dr_del_rti_rec: 125 - before update_rcv_serials_supply'
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
* No updates to rcv_serials_supply if INV J and PO J are installed
*/
IF ((inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) OR
(inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
IF l_rcv_rcpt_rec.req_line_id IS NOT NULL
AND p_serial_control_code NOT IN(1, 6) THEN
-- update rss for req
inv_rcv_std_deliver_apis.update_rcv_serials_supply(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => x_message
, p_shipment_line_id => l_rcv_transaction_rec.rcv_shipment_line_id
);
print_debug('create_int_shp_dr_del_rti_rec: 127 - after update_rcv_serials_supply'
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
--BUG 3326408,3346758,3405320
--If there are any serials confirmed from the UI for an item that is
--lot controlled and serial control dynamic at SO issue,
--do not NULL out serial_transaction_temp_id. In other cases,
--NULL OUT serial_temp_id so that split_lot_serial does not look at MSNI
l_msni_count := 0;
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT 'N' line_chkbox
, 'CUSTOMER' source_type_code
, 'CUSTOMER' receipt_source_code
, '' order_type_code
, '' order_type
, TO_NUMBER(NULL) po_header_id
, NULL po_number
, TO_NUMBER(NULL) po_line_id
, TO_NUMBER(NULL) po_line_number
, TO_NUMBER(NULL) po_line_location_id
, TO_NUMBER(NULL) po_shipment_number
, TO_NUMBER(NULL) po_release_id
, TO_NUMBER(NULL) po_release_number
, TO_NUMBER(NULL) req_header_id
, NULL req_number
, TO_NUMBER(NULL) req_line_id
, TO_NUMBER(NULL) req_line
, TO_NUMBER(NULL) req_distribution_id
, TO_NUMBER(NULL) rcv_shipment_header_id
, NULL rcv_shipment_number
, TO_NUMBER(NULL) rcv_shipment_line_id
, TO_NUMBER(NULL) rcv_line_number
, NVL(oel.ship_to_org_id, oeh.ship_to_org_id) from_organization_id
, NVL(oel.ship_from_org_id, oeh.ship_from_org_id) to_organization_id
, TO_NUMBER(NULL) vendor_id
, '' SOURCE
, TO_NUMBER(NULL) vendor_site_id
, NULL outside_operation_flag
, oel.inventory_item_id item_id
, -- Bug 2073164
NULL uom_code
, mum.unit_of_measure primary_uom
, mum.uom_class primary_uom_class
, NVL(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
, NVL(msi.location_control_code, 1) item_locator_control
, DECODE(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
, DECODE(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
, NVL(msi.shelf_life_code, 1) shelf_life_code
, NVL(msi.shelf_life_days, 0) shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, NULL item_rev_control_flag_from
, msi.segment1 item_number
, oel.item_revision item_revision
, msi.description item_description
, TO_NUMBER(NULL) item_category_id
, NULL hazard_class
, NULL un_number
, NULL vendor_item_number
, oel.ship_from_org_id ship_to_location_id
, '' ship_to_location
, NULL packing_slip
, TO_NUMBER(NULL) routing_id
, NULL routing_name
, oel.request_date need_by_date
, NVL(oel.promise_date, oel.request_date) expected_receipt_date
, oel.ordered_quantity ordered_qty
, '' ordered_uom
, NULL ussgl_transaction_code
, NULL government_context
, DECODE(msi.return_inspection_requirement, 1, 'Y', 'N') inspection_required_flag--bug 4700067
, 'Y' receipt_required_flag
, 'N' enforce_ship_to_location_code
, oel.unit_selling_price unit_price
, oeh.transactional_curr_code currency_code
, oeh.conversion_type_code currency_conversion_type
, oeh.conversion_rate_date currency_conversion_date
, oeh.conversion_rate currency_conversion_rate
, NULL note_to_receiver
, NULL destination_type_code
, oel.deliver_to_contact_id deliver_to_person_id
, oel.deliver_to_org_id deliver_to_location_id
, NULL destination_subinventory
, oel.CONTEXT attribute_category
, oel.attribute1 attribute1
, oel.attribute2 attribute2
, oel.attribute3 attribute3
, oel.attribute4 attribute4
, oel.attribute5 attribute5
, oel.attribute6 attribute6
, oel.attribute7 attribute7
, oel.attribute8 attribute8
, oel.attribute9 attribute9
, oel.attribute10 attribute10
, oel.attribute11 attribute11
, oel.attribute12 attribute12
, oel.attribute13 attribute13
, oel.attribute14 attribute14
, oel.attribute15 attribute15
, NULL closed_code
, NULL asn_type
, NULL bill_of_lading
, TO_DATE(NULL) shipped_date
, NULL freight_carrier_code
, NULL waybill_airbill_num
, NULL freight_bill_num
, NULL vendor_lot_num
, NULL container_num
, NULL truck_num
, NULL bar_code_label
, NULL rate_type_display
, NULL match_option
, NULL country_of_origin_code
, oel.header_id oe_order_header_id
, oeh.order_number oe_order_num
, oel.line_id oe_order_line_id
, oel.line_number oe_order_line_num
, oel.sold_to_org_id customer_id
, NVL(oel.ship_to_org_id, oeh.ship_to_org_id) customer_site_id
, '' customer_item_num
, '' pll_note_to_receiver
, NULL po_distribution_id
, NULL qty_ordered
, NULL wip_entity_id
, NULL wip_operation_seq_num
, NULL wip_resource_seq_num
, NULL wip_repetitive_schedule_id
, NULL wip_line_id
, NULL bom_resource_id
, '' destination_type
, '' LOCATION
, NULL currency_conversion_rate_pod
, NULL currency_conversion_date_pod
, NULL project_id
, NULL task_id
, NULL secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, NULL secondary_quantity --OPM Convergence
FROM oe_order_lines_all oel
, oe_order_headers_all oeh
, mtl_system_items msi
, mtl_units_of_measure mum
WHERE oel.line_category_code = 'RETURN'
AND oel.header_id = oeh.header_id
AND oel.inventory_item_id = msi.inventory_item_id
AND oel.ship_from_org_id = msi.organization_id
AND msi.primary_uom_code = mum.uom_code
AND oel.booked_flag = 'Y'
AND oel.ordered_quantity > NVL(oel.shipped_quantity, 0)
AND msi.mtl_transactions_enabled_flag = 'Y'
AND oel.line_id = v_oe_order_line_id
AND (p_project_id IS NULL
OR(p_project_id = -9999
AND oel.project_id IS NULL)
OR -- bug 2669021
oel.project_id = p_project_id)
AND (p_task_id IS NULL
OR oel.task_id = p_task_id);
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
l_split_lot_serial_ok := inv_rcv_integration_apis.split_lot_serial(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => x_message
, p_new_rti_info => l_new_rti_info);
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT 'N' line_chkbox
, p_source_type source_type_code
, 'VENDOR' receipt_source_code
, 'PO' order_type_code
, '' order_type
, poll.po_header_id po_header_id
, poh.segment1 po_number
, poll.po_line_id po_line_id
, pol.line_num po_line_number
, poll.line_location_id po_line_location_id
, poll.shipment_num po_shipment_number
, poll.po_release_id po_release_id
, por.release_num po_release_number
, TO_NUMBER(NULL) req_header_id
, NULL req_number
, TO_NUMBER(NULL) req_line_id
, TO_NUMBER(NULL) req_line
, TO_NUMBER(NULL) req_distribution_id
, rsh.shipment_header_id rcv_shipment_header_id
, rsh.shipment_num rcv_shipment_number
, rsl.shipment_line_id rcv_shipment_line_id
, rsl.line_num rcv_line_number
, rsl.from_organization_id from_organization_id --Bug #3878174
/*
, NVL(rsl.from_organization_id, poh.po_header_id) from_organization_id
*/
, rsl.to_organization_id to_organization_id
, rsh.vendor_id vendor_id
, '' SOURCE
, poh.vendor_site_id vendor_site_id -- Bug 6403165
, '' outside_operation_flag
, rsl.item_id item_id
, -- Bug 2073164
NULL uom_code
, rsl.unit_of_measure primary_uom
, mum.uom_class primary_uom_class
, NVL(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
, NVL(msi.location_control_code, 1) item_locator_control
, DECODE(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
, DECODE(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
, NVL(msi.shelf_life_code, 1) shelf_life_code
, NVL(msi.shelf_life_days, 0) shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, NULL item_rev_control_flag_from
, NULL item_number
, rsl.item_revision item_revision
, rsl.item_description item_description
, rsl.category_id item_category_id
, '' hazard_class
, '' un_number
, rsl.vendor_item_num vendor_item_number
, rsl.ship_to_location_id ship_to_location_id
, '' ship_to_location
, rsl.packing_slip packing_slip
, rsl.routing_header_id routing_id
, '' routing_name
, poll.need_by_date need_by_date
, rsh.expected_receipt_date expected_receipt_date
, poll.quantity ordered_qty
, pol.unit_meas_lookup_code ordered_uom
, rsl.ussgl_transaction_code ussgl_transaction_code
, rsl.government_context government_context
, poll.inspection_required_flag inspection_required_flag
, poll.receipt_required_flag receipt_required_flag
, poll.enforce_ship_to_location_code enforce_ship_to_location_code
, NVL(poll.price_override, pol.unit_price) unit_price
, poh.currency_code currency_code
, poh.rate_type currency_conversion_type
, poh.rate_date currency_conversion_date
, poh.rate currency_conversion_rate
, poh.note_to_receiver note_to_receiver
, pod.destination_type_code destination_type_code
, pod.deliver_to_person_id deliver_to_person_id
, pod.deliver_to_location_id deliver_to_location_id
, pod.destination_subinventory destination_subinventory
, rsl.attribute_category attribute_category
, rsl.attribute1 attribute1
, rsl.attribute2 attribute2
, rsl.attribute3 attribute3
, rsl.attribute4 attribute4
, rsl.attribute5 attribute5
, rsl.attribute6 attribute6
, rsl.attribute7 attribute7
, rsl.attribute8 attribute8
, rsl.attribute9 attribute9
, rsl.attribute10 attribute10
, rsl.attribute11 attribute11
, rsl.attribute12 attribute12
, rsl.attribute13 attribute13
, rsl.attribute14 attribute14
, rsl.attribute15 attribute15
, poll.closed_code closed_code
, rsh.asn_type asn_type
, rsh.bill_of_lading bill_of_lading
, rsh.shipped_date shipped_date
, rsh.freight_carrier_code freight_carrier_code
, rsh.waybill_airbill_num waybill_airbill_num
, rsh.freight_bill_number freight_bill_num
, rsl.vendor_lot_num vendor_lot_num
, rsl.container_num container_num
, rsl.truck_num truck_num
, rsl.bar_code_label bar_code_label
, '' rate_type_display
, poll.match_option match_option
, rsl.country_of_origin_code country_of_origin_code
, TO_NUMBER(NULL) oe_order_header_id
, TO_NUMBER(NULL) oe_order_num
, TO_NUMBER(NULL) oe_order_line_id
, TO_NUMBER(NULL) oe_order_line_num
, TO_NUMBER(NULL) customer_id
, TO_NUMBER(NULL) customer_site_id
, NULL customer_item_num
, NULL pll_note_to_receiver
, --POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
pod.po_distribution_id po_distribution_id
, pod.quantity_ordered - pod.quantity_delivered qty_ordered
, pod.wip_entity_id wip_entity_id
, pod.wip_operation_seq_num wip_operation_seq_num
, pod.wip_resource_seq_num wip_resource_seq_num
, pod.wip_repetitive_schedule_id wip_repetitive_schedule_id
, pod.wip_line_id wip_line_id
, pod.bom_resource_id bom_resource_id
, '' destination_type
, '' LOCATION
, pod.rate currency_conversion_rate_pod
, pod.rate_date currency_conversion_date_pod
, pod.project_id project_id
, pod.task_id task_id
, NULL secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, NULL secondary_quantity --OPM Convergence
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_headers poh
, po_line_locations poll
, po_lines pol
, po_releases por
, mtl_system_items msi
, mtl_units_of_measure mum
, po_distributions pod
WHERE pod.po_distribution_id = v_po_distribution_id
AND pod.line_location_id = poll.line_location_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.po_release_id = por.po_release_id(+)
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND NVL(msi.organization_id, rsl.to_organization_id) = rsl.to_organization_id
AND msi.inventory_item_id(+) = rsl.item_id
AND poll.line_location_id = rsl.po_line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.asn_type IN('ASN', 'ASBN', 'LCM') -- For LCM Project
AND rsl.shipment_line_status_code <> 'CANCELLED'
AND rsl.shipment_line_id = v_shipment_line_id
AND (p_project_id IS NULL
OR(p_project_id = -9999
AND pod.project_id IS NULL)
OR -- bug 2669021
pod.project_id = p_project_id)
AND (p_task_id IS NULL
OR pod.task_id = p_task_id)
UNION
SELECT 'N' line_chkbox
, 'INTERNAL' source_type_code
, DECODE(rsl.source_document_code, 'INVENTORY', 'INVENTORY', 'REQ', 'INTERNAL ORDER') receipt_source_code
, rsl.source_document_code order_type_code
, '' order_type
, rsh.shipment_header_id po_header_id
, rsh.shipment_num po_number
, rsl.shipment_line_id po_line_id
, rsl.line_num po_line_number
, rsl.shipment_line_id po_line_location_id
, rsl.line_num po_shipment_number
, rsh.shipment_header_id po_release_id
, rsh.shipment_header_id po_release_number
, porh.requisition_header_id req_header_id
, porh.segment1 req_number
, porl.requisition_line_id req_line_id
, porl.line_num req_line
, rsl.req_distribution_id req_distribution_id
, rsl.shipment_header_id rcv_shipment_header_id
, rsh.shipment_num rcv_shipment_number
, rsl.shipment_line_id rcv_shipment_line_id
, rsl.line_num rcv_line_number
, rsl.from_organization_id from_organization_id
, rsl.to_organization_id to_organization_id
, rsl.shipment_line_id vendor_id
, '' SOURCE
, TO_NUMBER(NULL) vendor_site_id
, 'N' outside_operation_flag
, rsl.item_id item_id
, -- Bug 2073164
NULL uom_code
, rsl.unit_of_measure primary_uom
, mum.uom_class primary_uom_class
, NVL(msi.allowed_units_lookup_code, 2) item_allowed_units_lookup_code
, NVL(msi.location_control_code, 1) item_locator_control
, DECODE(msi.restrict_locators_code, 1, 'Y', 'N') restrict_locators_code
, DECODE(msi.restrict_subinventories_code, 1, 'Y', 'N') restrict_subinventories_code
, NVL(msi.shelf_life_code, 1) shelf_life_code
, NVL(msi.shelf_life_days, 0) shelf_life_days
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, DECODE(msi.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_to
, DECODE(msi1.revision_qty_control_code, 1, 'N', 2, 'Y', 'N') item_rev_control_flag_from
, NULL item_number
, rsl.item_revision item_revision
, rsl.item_description item_description
, rsl.category_id item_category_id
, '' hazard_class
, '' un_number
, rsl.vendor_item_num vendor_item_number
, rsh.ship_to_location_id ship_to_location_id
, '' ship_to_location
, rsh.packing_slip packing_slip
, rsl.routing_header_id routing_id
, '' routing_name
, porl.need_by_date need_by_date
, rsh.expected_receipt_date expected_receipt_date
, rsl.quantity_shipped ordered_qty
, rsl.primary_unit_of_measure ordered_uom
, rsh.ussgl_transaction_code ussgl_transaction_code
, rsh.government_context government_context
, NULL inspection_required_flag
, NULL receipt_required_flag
, NULL enforce_ship_to_location_code
, TO_NUMBER(NULL) unit_price
, NULL currency_code
, NULL currency_conversion_type
, TO_DATE(NULL) currency_conversion_date
, TO_NUMBER(NULL) currency_conversion_rate
, NULL note_to_receiver
, --PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
rsl.destination_type_code destination_type_code
, rsl.deliver_to_person_id deliver_to_person_id
, rsl.deliver_to_location_id deliver_to_location_id
, rsl.to_subinventory destination_subinventory
, rsl.attribute_category attribute_category
, rsl.attribute1 attribute1
, rsl.attribute2 attribute2
, rsl.attribute3 attribute3
, rsl.attribute4 attribute4
, rsl.attribute5 attribute5
, rsl.attribute6 attribute6
, rsl.attribute7 attribute7
, rsl.attribute8 attribute8
, rsl.attribute9 attribute9
, rsl.attribute10 attribute10
, rsl.attribute11 attribute11
, rsl.attribute12 attribute12
, rsl.attribute13 attribute13
, rsl.attribute14 attribute14
, rsl.attribute15 attribute15
, 'OPEN' closed_code
, NULL asn_type
, rsh.bill_of_lading bill_of_lading
, rsh.shipped_date shipped_date
, rsh.freight_carrier_code freight_carrier_code
, rsh.waybill_airbill_num waybill_airbill_num
, rsh.freight_bill_number freight_bill_num
, rsl.vendor_lot_num vendor_lot_num
, rsl.container_num container_num
, rsl.truck_num truck_num
, rsl.bar_code_label bar_code_label
, NULL rate_type_display
, 'P' match_option
, NULL country_of_origin_code
, TO_NUMBER(NULL) oe_order_header_id
, TO_NUMBER(NULL) oe_order_num
, TO_NUMBER(NULL) oe_order_line_id
, TO_NUMBER(NULL) oe_order_line_num
, TO_NUMBER(NULL) customer_id
, TO_NUMBER(NULL) customer_site_id
, NULL customer_item_num
, NULL pll_note_to_receiver
, --PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
TO_NUMBER(NULL) po_distribution_id
, TO_NUMBER(NULL) qty_ordered
, TO_NUMBER(NULL) wip_entity_id
, TO_NUMBER(NULL) wip_operation_seq_num
, TO_NUMBER(NULL) wip_resource_seq_num
, TO_NUMBER(NULL) wip_repetitive_schedule_id
, TO_NUMBER(NULL) wip_line_id
, TO_NUMBER(NULL) bom_resource_id
, '' destination_type
, '' LOCATION
, TO_NUMBER(NULL) currency_conversion_rate_pod
, TO_DATE(NULL) currency_conversion_date_pod
, TO_NUMBER(NULL) project_id
, TO_NUMBER(NULL) task_id
, NULL secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, NULL secondary_quantity --OPM Convergence
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_requisition_headers porh
, po_requisition_lines porl
, mtl_system_items msi
, mtl_system_items msi1
, mtl_units_of_measure mum
WHERE rsh.receipt_source_code <> 'VENDOR'
AND rsl.requisition_line_id = porl.requisition_line_id(+)
AND porl.requisition_header_id = porh.requisition_header_id(+)
AND rsh.shipment_header_id = rsl.shipment_header_id
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND msi.organization_id(+) = rsl.to_organization_id
AND msi.inventory_item_id(+) = rsl.item_id
AND msi1.organization_id(+) = rsl.from_organization_id
AND msi1.inventory_item_id(+) = rsl.item_id
AND rsh.asn_type IS NULL
AND rsl.shipment_line_id = v_shipment_line_id
AND (
(
rsl.source_document_code = 'REQ'
AND EXISTS(
SELECT '1'
FROM po_req_distributions_all prd
WHERE prd.requisition_line_id = porl.requisition_line_id
AND (
p_project_id IS NULL
OR(p_project_id = -9999
AND prd.project_id IS NULL)
OR -- bug 2669021
prd.project_id = p_project_id
)
AND (p_task_id IS NULL
OR prd.task_id = p_task_id))
)
OR rsl.source_document_code <> 'REQ'
);
SELECT receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id
AND ship_to_org_id = p_organization_id;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
SELECT cost_group_id
INTO l_rcv_transaction_rec.cost_group_id
FROM wms_lpn_contents wlpnc
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_lpn_id
AND wlpnc.inventory_item_id = p_item_id
AND EXISTS(SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_organization_id
AND cost_group_id = wlpnc.cost_group_id);
UPDATE wms_lpn_contents wlpnc
SET cost_group_id = NULL
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_lpn_id
AND wlpnc.inventory_item_id = p_item_id
AND NOT EXISTS(SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_organization_id
AND cost_group_id = wlpnc.cost_group_id);
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
l_msni_count := 0;
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
UPDATE wms_license_plate_numbers
SET lpn_context = 3
WHERE lpn_id = p_lpn_id;
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
SELECT lpnc.lpn_id
, lpnc.inventory_item_id
, lpnc.revision
, lpnc.quantity
, lpnc.uom_code
, lpnc.lot_control_code
, lpnc.serial_number_control_code
, lpnc.primary_uom_code
, p_po_header_id
, lpnc.lot_number
, mln.expiration_date
, mln.status_id
, lpnc.lpn_org_id
, lpnc.secondary_quantity --Bug 7656734
FROM mtl_lot_numbers mln
, (SELECT wlpn.lpn_id
, wlpnc.inventory_item_id
, msi.organization_id
, msi.lot_control_code
, msi.serial_number_control_code
, msi.primary_uom_code
, wlpnc.revision
, wlpnc.quantity
, wlpnc.uom_code
, wlpnc.lot_number
, wlpnc.source_line_id
, wlpn.organization_id lpn_org_id
, wlpnc.secondary_quantity --Bug 7656734
FROM wms_lpn_contents wlpnc, wms_license_plate_numbers wlpn, mtl_system_items msi, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = p_shipment_header_id
AND (wlpn.source_header_id = rsh.shipment_header_id
OR wlpn.source_name = rsh.shipment_num)
AND wlpn.lpn_context IN(6, 7) -- only those pre-ASN receiving ones
-- Nested LPN changes to explode the LPN
--AND wlpnc.parent_lpn_id = Nvl(p_lpn_id, wlpn.lpn_id)
-- In case user tries to to ASN reciept by giving only PO Number
-- LPN id will be NULL, In this case we should not expand the LPN
-- in which case start with lpn_id = p_lpn_id will fail.
AND (wlpnc.parent_lpn_id = NVL(p_lpn_id, wlpn.lpn_id)
OR wlpnc.parent_lpn_id IN(SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id))
AND wlpnc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id
AND wlpn.lpn_id = wlpnc.parent_lpn_id
AND (
wlpnc.source_line_id IN(SELECT pola.po_line_id
FROM po_lines_all pola
WHERE pola.po_header_id = NVL(p_po_header_id, pola.po_header_id))
OR wlpnc.source_line_id IS NULL
)) lpnc
WHERE lpnc.inventory_item_id = mln.inventory_item_id(+)
AND lpnc.lot_number = mln.lot_number(+)
AND lpnc.organization_id = mln.organization_id(+);
SELECT serial_number
, status_id
FROM mtl_serial_numbers
WHERE inventory_item_id = v_inventory_item_id
AND (revision = v_revision
OR(revision IS NULL
AND v_revision IS NULL))
AND (lot_number = v_lot_number
OR(lot_number IS NULL
AND v_lot_number IS NULL))
AND lpn_id = v_lpn_id;
l_msnt_last_update_date date_tab_tp;
l_msnt_last_updated_by number_tab_tp;
SELECT NVL(lpn_controlled_flag,1)
INTO l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory
AND organization_id = p_organization_id;
inv_rcv_std_rcpt_apis.update_lpn_org(
p_organization_id => p_organization_id
, p_lpn_id => l_lpn_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_item_uoms_view
WHERE uom_code = l_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = l_inventory_item_id;
print_debug('create_asn_exp_dd_intf_rec: 25 before inv_rcv_common_apis.insert_lot'
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
SELECT expiration_date
INTO l_lot_expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_lpn_org
AND lot_number = l_lot_number;
inv_rcv_common_apis.insert_lot(
p_transaction_temp_id => l_transaction_temp_id
, p_created_by => fnd_global.user_id
, p_transaction_qty => l_quantity
, p_primary_qty => l_primary_qty
, p_lot_number => l_lot_number
, p_expiration_date => l_lot_expiration_date
, p_status_id => NULL
, x_serial_transaction_temp_id => l_serial_txn_temp_id
, x_return_status => l_return_status
, x_msg_data => l_msg_data
);
'create_asn_exp_dd_intf_rec 25.1: inv_rcv_common_apis.insert_lot RAISE FND_API.G_EXC_ERROR;'
'create_asn_exp_dd_intf_rec 25.2: inv_rcv_common_apis.insert_lot RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
print_debug('create_asn_exp_dd_intf_rec 27.1 - insert serial temp' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_txn_temp_id
FROM DUAL;
l_msnt_last_update_date(l_serial_number_count) := SYSDATE;
l_msnt_last_updated_by(l_serial_number_count) := fnd_global.user_id;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_msnt_transaction_temp_id(i)
, l_msnt_last_update_date(i)
, l_msnt_last_updated_by(i)
, l_msnt_creation_date(i)
, l_msnt_created_by(i)
, l_msnt_fm_serial_number(i)
, l_msnt_to_serial_number(i)
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_serial_txn_temp_id
WHERE inventory_item_id = l_inventory_item_id
AND serial_number = l_msnt_fm_serial_number(i);
inv_rcv_integration_apis.insert_mtli(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_transaction_interface_id
, p_lot_number => l_lot_number
, p_transaction_quantity => l_quantity
, p_primary_quantity => l_lot_prm_quantity
, p_secondary_quantity => l_secondary_quantity --Bug 7656734
, p_organization_id => l_from_org_id
, p_inventory_item_id => l_inventory_item_id
, p_expiration_date => l_lot_expiration_date
, p_status_id => l_lot_status_id
, x_serial_transaction_temp_id => l_serial_transaction_temp_id
, p_product_transaction_id => l_product_transaction_id
, p_product_code => 'RCV'
, p_att_exist => 'Y'
, p_update_mln => 'N'
);
inv_rcv_integration_apis.insert_msni(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_serial_transaction_temp_id
, p_fm_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_organization_id => p_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_status_id => l_serial_status_id
, p_product_transaction_id => l_product_transaction_id
, p_product_code => 'RCV'
, p_att_exist => 'Y'
, p_update_msn => 'N'
);
inv_rcv_integration_apis.insert_msni(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_transaction_interface_id
, p_fm_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_organization_id => p_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_status_id => l_serial_status_id
, p_product_transaction_id => l_product_transaction_id
, p_product_code => 'RCV'
, p_att_exist => 'Y'
, p_update_msn => 'N'
);
'create_asn_exp_dd_intf_rec: 50.1 - Before inserting into wlpni for p_lpn_id with parent NULL '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
inv_rcv_integration_apis.insert_wlpni(
p_api_version => 1.0
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_organization_id => l_from_org_id -- BUG 4096028: should
-- from org_id
,p_lpn_id => p_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => inv_rcv_common_apis.g_rcv_global_var.interface_group_id
,p_parent_lpn_id => NULL
);
print_debug('create_asn_exp_dd_intf_rec 50.2:create_asn_con_dd_intf_rec - RAISE FND_API.G_EXC_ERROR after insert_wlpni;'|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
'create_asn_exp_dd_intf_rec: 50.1 - After inserting into wlpni for p_lpn_id with parent NULL '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
UPDATE wms_license_plate_numbers
SET lpn_context = 3
WHERE source_header_id = p_shipment_header_id
AND lpn_id = NVL(p_lpn_id, lpn_id);
print_debug(' create_asn_exp_dd_intf_rec: Before Update lpn history ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
UPDATE wms_lpn_histories
SET source_name = 'ASNEXP'
, source_header_id = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
WHERE lpn_context = 7
AND parent_lpn_id IN(SELECT lpn_id
FROM wms_license_plate_numbers
WHERE source_header_id = p_shipment_header_id
AND lpn_id = NVL(p_lpn_id, lpn_id));
/* SELECT MIN(rti.interface_transaction_id) */
/* Group BY LPN_ID is changed for Express Receipts */
/* Also duplicate print of LPN labels is avoided */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY DECODE(p_source_type, 'ASNEXP', rti.interface_transaction_id, 'SHIPMENTEXP', rti.interface_transaction_id, NULL);
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information';
IF (p_source_type <> 'VENDOR') THEN --Bug #4079952 ..For PO receipt, code in rcv_insert_update_header(INVSTDRB.pls) will be called.
l_counter := 1;
/* SELECT MIN(rti.interface_transaction_id) */
/* Group BY LPN_ID is changed for Express Receipts */
/* Also duplicate print of LPN labels is avoided */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY DECODE(p_source_type, 'ASNEXP', rti.interface_transaction_id, 'SHIPMENTEXP', rti.interface_transaction_id, NULL);
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information';
IF (p_source_type <> 'VENDOR') THEN --Bug #4079952.. For PO receipt code in rcv_insert_update_header(INVSTDRB.pls) will be called.
l_counter := 1;