[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pod.project_id project_id
,NVL(pod.task_id, '') task_id
INTO l_project_id
, l_task_id
FROM po_distributions_all pod
WHERE pod.line_location_id = p_po_line_location_id
AND ROWNUM = 1
AND pod.project_id IS NOT NULL
ORDER BY nvl(pod.task_id,-1) DESC;
SELECT project_id
, task_id
INTO l_project_id
, l_task_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id;
FUNCTION insert_txn_interface(
p_rcv_transaction_rec IN OUT NOCOPY rcv_transaction_rec_tp
, p_rcv_rcpt_rec IN OUT NOCOPY rcv_enter_receipts_rec_tp
, p_group_id IN NUMBER
, p_transaction_type IN VARCHAR2
, p_organization_id IN NUMBER
, p_location_id IN NUMBER
, p_source_type IN VARCHAR2
, p_qa_routing_id IN NUMBER DEFAULT -1
, p_project_id IN NUMBER DEFAULT NULL
, p_task_id IN NUMBER DEFAULT NULL
, p_express_transaction IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER IS
l_rcv_transaction_rec rcv_transaction_rec_tp; -- rcv_transaction block
print_debug('Enter insert_txn_interface: 1 ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('insert_txn_interface: p_express_transaction ' || p_express_transaction, 1);
print_debug('insert_txn_interface: l_express_transaction ' || l_express_transaction, 1);
print_debug('insert_txn_interface 2 value of coutry of origin is ' || p_rcv_rcpt_rec.country_of_origin_code, 4);
print_debug('insert_txn_interface: revision1 - ' || p_rcv_transaction_rec.item_revision, 4); -- revision needs to be taken care of by matching
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM DUAL;
print_debug('insert_txn_interface: 2 before RCV_RECEIPTS_QUERY_SV.POST_QUERY ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
print_debug('insert_txn_interface: 3 after RCV_RECEIPTS_QUERY_SV.POST_QUERY ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT MAX(category_id)
INTO l_rcv_transaction_rec.item_category_id
FROM mtl_item_categories
WHERE inventory_item_id = l_rcv_transaction_rec.item_id
AND organization_id = p_organization_id
AND category_set_id = inv_rcv_common_apis.g_po_startup_value.category_set_id;
print_debug('insert_txn_interface: Exception in getting the item categories', 4);
select RMA_RECEIPT_ROUTING_ID
into x_routing_id
from mtl_client_parameters
WHERE client_code = l_client_code;
select rma_receipt_routing_id
into x_routing_id
from rcv_parameters
where organization_id = p_organization_id;
select rma_receipt_routing_id
into x_routing_id
from rcv_parameters
where organization_id = p_organization_id;
print_debug('insert_txn_interface: from rcv parameters: x_routing_id = '|| x_routing_id, 4);
print_debug('insert_txn_interface: Exception in getting the routing id from rcv parameters', 4);
print_debug('insert_txn_interface: routing id is set to what fetched from post_query : x_routing_id ='|| x_routing_id, 4);
select routing_name
into l_rcv_transaction_rec.routing_name
from rcv_routing_headers
where routing_header_id = x_routing_id;
SELECT displayed_field
, lookup_code
INTO x_destination_type_dsp
, x_destination_type_code
FROM po_lookup_codes
WHERE lookup_code = 'INVENTORY'
AND lookup_type = 'RCV DESTINATION TYPE';
print_debug('insert_txn_interface: ERROR in getting the Conversion Rate' , 4);
print_debug('insert_txn_interface : l_rate : ' || l_rate, 4);
print_debug('insert_txn_interface: No Conversion rate has been defined for the currency : ' || l_rcv_transaction_rec.currency_code || ' for the txn date : ' || l_rcv_transaction_rec.currency_conversion_date , 4);
print_debug('insert_txn_interface: revision2 - ' || x_item_revision, 4);
SELECT project_id
, task_id
INTO x_project_id
, x_task_id
FROM po_req_distributions
WHERE requisition_line_id = x_req_line_id;
SELECT project_id
, task_id
INTO x_project_id
, x_task_id
FROM oe_order_lines_all
WHERE line_id = x_oe_order_line_id;
SELECT lookup_code
INTO l_rcv_transaction_rec.destination_type_code
FROM po_lookup_codes
WHERE lookup_code = 'INVENTORY'
AND lookup_type = 'RCV DESTINATION TYPE';
SELECT rcv_headers_interface_s.NEXTVAL
INTO g_header_intf_id
FROM dual;
SELECT rcv_shipment_headers_s.NEXTVAL
INTO g_shipment_header_id
FROM DUAL;
print_debug('insert_txn_interface: 4 before rcv_trx_interface_insert_pkg.insert_row '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
print_debug('insert_txn_interface: revision3 - ' || l_rcv_transaction_rec.item_revision, 4);
print_debug('insert_txn_interface: rcv sub loc - ' || l_subinventory ||' , '|| l_locator_id, 4);
print_debug('insert_txn_interface before insert row value of coutry of origin is '
|| l_rcv_transaction_rec.country_of_origin_code
, 4);
* -> shipment_header_id should be inserted as null but header_interface_id
* should be inserted. Header_interface_id will be non null only if INV
* and PO J are installed (or higher).
* -> Populate values for three new columns lpn_group_id, validation_flag
* and header_interface_id_id
* -> Populate project_id and task_id in RTI from inputs
*/
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
IF ((l_rcv_transaction_rec.source_type_code = 'INTERNAL') OR
-- For Bug 7440217
(l_rcv_transaction_rec.source_type_code IN ('ASN', 'LCM') AND p_source_type <> 'VENDOR')) THEN
-- End for Bug 7440217
IF (l_debug = 1) THEN
print_debug('Setting the shipment_header_id as this may be a INTSHIP/ASN Receive TXN', 4);
print_debug('Nulling the shipment_header_id to insert for J code', 4);
print_debug('b4 inserting l_secondary_quantity ' || l_secondary_quantity,4);
print_debug('b4 insertingl_secondary_uom_code ' || l_secondary_uom_code,4);
print_debug('b4 insertingl_secondary_unit_of_measure ' || l_secondary_unit_of_measure,4);
SELECT item_id
INTO l_po_item_id
FROM po_lines_all pol
WHERE pol.po_line_id = l_po_line_id;
INSERT INTO rcv_transactions_interface
(
interface_transaction_id
, GROUP_ID
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, transaction_type
, transaction_date
, processing_status_code
, processing_mode_code
, processing_request_id
, transaction_status_code
, category_id
, quantity
, unit_of_measure
, interface_source_code
, interface_source_line_id
, inv_transaction_id
, item_id
, item_description
, item_revision
, uom_code
, employee_id
, auto_transact_code
, shipment_header_id
, shipment_line_id
, ship_to_location_id
, primary_quantity
, primary_unit_of_measure
, receipt_source_code
, vendor_id
, vendor_site_id
, from_organization_id
, to_organization_id
, routing_header_id
, routing_step_id
, source_document_code
, parent_transaction_id
, po_header_id
, po_revision_num
, po_release_id
, po_line_id
, po_line_location_id
, po_unit_price
, currency_code
, currency_conversion_type
, currency_conversion_rate
, currency_conversion_date
, po_distribution_id
, requisition_line_id
, req_distribution_id
, charge_account_id
, substitute_unordered_code
, receipt_exception_flag
, accrual_status_code
, inspection_status_code
, inspection_quality_code
, destination_type_code
, deliver_to_person_id
, location_id
, deliver_to_location_id
, subinventory
, locator_id
, wip_entity_id
, wip_line_id
, department_code
, wip_repetitive_schedule_id
, wip_operation_seq_num
, wip_resource_seq_num
, bom_resource_id
, shipment_num
, freight_carrier_code
, bill_of_lading
, packing_slip
, shipped_date
, expected_receipt_date
, actual_cost
, transfer_cost
, transportation_cost
, transportation_account_id
, num_of_containers
, waybill_airbill_num
, vendor_item_num
, vendor_lot_num
, rma_reference
, comments
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_head_attribute_category
, ship_head_attribute1
, ship_head_attribute2
, ship_head_attribute3
, ship_head_attribute4
, ship_head_attribute5
, ship_head_attribute6
, ship_head_attribute7
, ship_head_attribute8
, ship_head_attribute9
, ship_head_attribute10
, ship_head_attribute11
, ship_head_attribute12
, ship_head_attribute13
, ship_head_attribute14
, ship_head_attribute15
, ship_line_attribute_category
, ship_line_attribute1
, ship_line_attribute2
, ship_line_attribute3
, ship_line_attribute4
, ship_line_attribute5
, ship_line_attribute6
, ship_line_attribute7
, ship_line_attribute8
, ship_line_attribute9
, ship_line_attribute10
, ship_line_attribute11
, ship_line_attribute12
, ship_line_attribute13
, ship_line_attribute14
, ship_line_attribute15
, ussgl_transaction_code
, government_context
, reason_id
, destination_context
, source_doc_quantity
, source_doc_unit_of_measure
, use_mtl_lot
, use_mtl_serial
, qa_collection_id
, country_of_origin_code
, oe_order_header_id
, oe_order_line_id
, customer_item_num
, customer_id
, customer_site_id
, put_away_rule_id
, put_away_strategy_id
, lpn_id
, transfer_lpn_id
, cost_group_id
, mmtt_temp_id
, mobile_txn
, transfer_cost_group_id
, lpn_group_id
, validation_flag
, header_interface_id
, project_id
, task_id
, secondary_unit_of_measure --OPM Convergence
, secondary_quantity --OPM Convergence
, secondary_uom_code --OPM Convergence
, org_id --
, document_line_num --BUG 4730474
, substitute_item_id --bug 4697949
, express_transaction --bug 5550783
)
VALUES (
l_interface_transaction_id
, p_group_id
, l_sysdate
, inv_rcv_common_apis.g_po_startup_value.user_id /* Last Updated By */
, l_sysdate /* Created Date */
, inv_rcv_common_apis.g_po_startup_value.user_id /* Created By */
, inv_rcv_common_apis.g_po_startup_value.user_id /* last Update Login */
, p_transaction_type /* transaction type */
, l_txn_date /* transaction date */
, 'PENDING' /* Processing status code */
, inv_rcv_common_apis.g_po_startup_value.transaction_mode
, NULL
, 'PENDING' /* Transaction status code */
, l_rcv_transaction_rec.item_category_id
, l_rcv_transaction_rec.transaction_qty
, l_rcv_transaction_rec.transaction_uom
, 'RCV' /* interface source code */
, NULL /* interface source line id */
, NULL /* inv_transaction id */
, l_rcv_transaction_rec.item_id
, l_rcv_transaction_rec.item_description
, l_rcv_transaction_rec.item_revision
, l_rcv_transaction_rec.uom_code
, inv_rcv_common_apis.g_po_startup_value.employee_id -- Fix for bug 2073164
, l_auto_transact_code /* Auto transact code */
, l_shipment_header_id /* shipment header id */
, l_shipment_line_id /* shipment line id */
, l_rcv_transaction_rec.ship_to_location_id
, l_rcv_transaction_rec.primary_quantity /* primary quantity */
, l_primary_uom /* primary uom */
, l_source_type_code /* receipt source code */
, l_vendor_id
, l_vendor_site_id
, l_from_org_id /* from org id */
, l_to_org_id /* to org id */
, l_rcv_transaction_rec.routing_id
, 1 /* routing step id */
, l_source_doc_code /* source document code */
, NULL /* Parent trx id */
, l_po_header_id
, NULL /* PO Revision number */
, l_po_release_id
, l_po_line_id
, l_po_line_location_id
, l_rcv_transaction_rec.unit_price
, l_rcv_transaction_rec.currency_code /* Currency_Code */
, l_rcv_transaction_rec.currency_conversion_type
, l_rcv_transaction_rec.currency_conversion_rate
, TRUNC(l_rcv_transaction_rec.currency_conversion_date)
, l_po_distribution_id
, l_req_line_id
, l_rcv_transaction_rec.req_distribution_id
, NULL /* Charge_Account_Id */
, l_sub_unordered_code /* Substitute_Unordered_Code */
, l_rcv_transaction_rec.receipt_exception /* Receipt_Exception_Flag forms check box?*/
, NULL /* Accrual_Status_Code */
, 'NOT INSPECTED' /* Inspection_Status_Code */
, NULL /* Inspection_Quality_Code */
, l_rcv_transaction_rec.destination_type_code /* Destination_Type_Code */
, l_deliver_to_person_id /* Deliver_To_Person_Id */
, l_location_id /* Location_Id */
, l_deliver_to_location_id /* Deliver_To_Location_Id */
, l_subinventory /* Subinventory */
, l_locator_id /* Locator_Id */
, l_wip_entity_id /* Wip_Entity_Id */
, l_wip_line_id /* Wip_Line_Id */
, l_department_code /* Department_Code */
, l_wip_rep_sched_id /* Wip_Repetitive_Schedule_Id */
, l_wip_oper_seq_num /* Wip_Operation_Seq_Num */
, l_wip_res_seq_num /* Wip_Resource_Seq_Num */
, l_bom_resource_id /* Bom_Resource_Id */
, l_rcv_transaction_rec.rcv_shipment_number
, NULL
, NULL /* Bill_Of_Lading */
, NULL /* Packing_Slip */
, TRUNC(l_rcv_transaction_rec.shipped_date)
, TRUNC(l_rcv_transaction_rec.expected_receipt_date) /* Expected_Receipt_Date */
, NULL /* Actual_Cost */
, NULL /* Transfer_Cost */
, NULL /* Transportation_Cost */
, NULL /* Transportation_Account_Id */
, NULL /* Num_Of_Containers */
, NULL /* Waybill_Airbill_Num */
, l_rcv_transaction_rec.vendor_item_number /* Vendor_Item_Num */
, l_rcv_transaction_rec.vendor_lot_num /* Vendor_Lot_Num */
, NULL /* Rma_Reference */
, l_rcv_transaction_rec.comments /* Comments ? from form*/
, l_rcv_transaction_rec.attribute_category /* Attribute_Category */
, l_rcv_transaction_rec.attribute1 /* Attribute1 */
, l_rcv_transaction_rec.attribute2 /* Attribute2 */
, l_rcv_transaction_rec.attribute3 /* Attribute3 */
, l_rcv_transaction_rec.attribute4 /* Attribute4 */
, l_rcv_transaction_rec.attribute5 /* Attribute5 */
, l_rcv_transaction_rec.attribute6 /* Attribute6 */
, l_rcv_transaction_rec.attribute7 /* Attribute7 */
, l_rcv_transaction_rec.attribute8 /* Attribute8 */
, l_rcv_transaction_rec.attribute9 /* Attribute9 */
, l_rcv_transaction_rec.attribute10 /* Attribute10 */
, l_rcv_transaction_rec.attribute11 /* Attribute11 */
, l_rcv_transaction_rec.attribute12 /* Attribute12 */
, l_rcv_transaction_rec.attribute13 /* Attribute13 */
, l_rcv_transaction_rec.attribute14 /* Attribute14 */
, l_rcv_transaction_rec.attribute15 /* Attribute15 */
, NULL /* Ship_Head_Attribute_Category */
, NULL /* Ship_Head_Attribute1 */
, NULL /* Ship_Head_Attribute2 */
, NULL /* Ship_Head_Attribute3 */
, NULL /* Ship_Head_Attribute4 */
, NULL /* Ship_Head_Attribute5 */
, NULL /* Ship_Head_Attribute6 */
, NULL /* Ship_Head_Attribute7 */
, NULL /* Ship_Head_Attribute8 */
, NULL /* Ship_Head_Attribute9 */
, NULL /* Ship_Head_Attribute10 */
, NULL /* Ship_Head_Attribute11 */
, NULL /* Ship_Head_Attribute12 */
, NULL /* Ship_Head_Attribute13 */
, NULL /* Ship_Head_Attribute14 */
, NULL /* Ship_Head_Attribute15 */
, NULL /* Ship_Line_Attribute_Category */
, NULL /* Ship_Line_Attribute1 */
, NULL /* Ship_Line_Attribute2 */
, NULL /* Ship_Line_Attribute3 */
, NULL /* Ship_Line_Attribute4 */
, NULL /* Ship_Line_Attribute5 */
, NULL /* Ship_Line_Attribute6 */
, NULL /* Ship_Line_Attribute7 */
, NULL /* Ship_Line_Attribute8 */
, NULL /* Ship_Line_Attribute9 */
, NULL /* Ship_Line_Attribute10 */
, NULL /* Ship_Line_Attribute11 */
, NULL /* Ship_Line_Attribute12 */
, NULL /* Ship_Line_Attribute13 */
, NULL /* Ship_Line_Attribute14 */
, NULL /* Ship_Line_Attribute15 */
, l_rcv_transaction_rec.ussgl_transaction_code /* Ussgl_Transaction_Code */
, l_rcv_transaction_rec.government_context /* Government_Context */
, l_rcv_transaction_rec.reason_id /* ? */
, l_rcv_transaction_rec.destination_type_code /* Destination_Context */
, l_rcv_transaction_rec.transaction_qty
, l_rcv_transaction_rec.transaction_uom
, l_rcv_transaction_rec.lot_control_code
, l_rcv_transaction_rec.serial_number_control_code
, NULL
, l_rcv_transaction_rec.country_of_origin_code
, l_oe_order_header_id
, l_oe_order_line_id
, l_customer_item_num
, l_customer_id
, l_customer_site_id
, NULL /* PUT_AWAY_RULE_ID */
, NULL /* PUT_AWAY_STRATEGY_ID */
, l_rcv_transaction_rec.lpn_id /* LPN_ID */
, l_rcv_transaction_rec.transfer_lpn_id /* Transfer LPN ID */
, l_rcv_transaction_rec.cost_group_id /* cost_group_id */
, NULL /* mmtt_temp_id */
, 'Y' /* mobile_txn */
, l_rcv_transaction_rec.transfer_cost_group_id /* xfer_cost_group_id*/
, l_lpn_group_id
, l_validation_flag
, l_header_interface_id
, l_project_id
, l_task_id
, l_secondary_unit_of_measure
, l_secondary_quantity
, l_secondary_uom_code
, l_operating_unit_id --
, l_rcv_transaction_rec.rcv_line_number--BUG 4730474
, l_substitute_item_id --bug 4697949
, l_express_transaction --bug 5550783
);
SELECT LCM_FLAG
INTO v_lcm_flag
FROM PO_LINE_LOCATIONS_ALL
WHERE LINE_LOCATION_ID = l_po_line_location_id;
SELECT mp.lcm_enabled_flag
INTO v_lcm_enabled_org
FROM mtl_parameters mp
WHERE mp.organization_id = l_to_org_id;
SELECT rp.pre_receive
INTO v_pre_receive
FROM rcv_parameters rp
WHERE rp.organization_id = l_to_org_id;
/* SELECT LCM_FLAG
INTO v_lcm_flag
FROM PO_LINE_LOCATIONS_ALL
WHERE LINE_LOCATION_ID = l_po_line_location_id;
SELECT LCM_SHIPMENT_LINE_ID, UNIT_LANDED_COST
INTO v_lcm_ship_line_id, v_unit_landed_cost
FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
UPDATE rcv_transactions_interface
SET lcm_shipment_line_id = v_lcm_ship_line_id,
unit_landed_cost = v_unit_landed_cost
WHERE interface_transaction_id = l_interface_transaction_id
AND to_organization_id = l_to_org_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'LC_PENDING',
PROCESSING_MODE_CODE = 'BATCH'
WHERE interface_transaction_id = l_interface_transaction_id
AND to_organization_id = l_to_org_id;
update rcv_shipment_lines set ITEM_REVISION = v_item_revision where shipment_line_id = l_shipment_line_id;
SELECT secondary_uom_code, secondary_unit_of_measure,
secondary_quantity
INTO t_sec_uom_code, t_sec_uom, t_sec_qty
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_interface_transaction_id;
print_debug('About exit insert_txn_interface: 5 after rcv_trx_interface_insert_pkg.insert_row '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
END insert_txn_interface;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
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 the values as NULL --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
, NULL destination_type_code
, TO_NUMBER(NULL) deliver_to_person_id
, TO_NUMBER(NULL) deliver_to_location_id
, NULL 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,
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
, pol.secondary_uom secondary_uom --OPM Convergence
, NULL secondary_uom_code --OPM Convergence
, pol.secondary_qty secondary_quantity --OPM Convergence* commented out temp
FROM po_headers poh
, po_line_locations poll
, po_lines pol
, po_releases por
, mtl_system_items msi
, mtl_units_of_measure mum
WHERE poll.line_location_id = v_po_line_location_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 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 poll.line_location_id IN(
SELECT pod.line_location_id
FROM po_distributions_all pod
WHERE (
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)
AND pod.po_header_id = poll.po_header_id
AND pod.po_line_id = poll.po_line_id
AND pod.line_location_id = poll.line_location_id);
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO g_rcpt_match_table_gross(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;
print_debug('create_po_rcpt_intf_rec: 100 - within cursor loop - before insert RTI '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
SELECT nvl(item_revision, '@@@@')
INTO l_item_revision
FROM po_lines_all
WHERE po_line_id = l_rcv_rcpt_rec.po_line_id;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_rcv_transaction_rec.interface_transaction_id
FROM DUAL;
insert_txn_interface(
l_rcv_transaction_rec
, l_rcv_rcpt_rec
, l_group_id
, l_transaction_type
, p_organization_id
, p_location_id
, p_source_type
, l_qa_routing_id
, p_project_id
, p_task_id
);
print_debug('create_po_rcpt_intf_rec: 110 - within cursor loop - after insert RTI '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
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 temp
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 = rsl.requisition_line_id
AND Nvl(rsl.req_distribution_id,Nvl(prd.distribution_id,-999)) = Nvl(prd.distribution_id,-999)--BUG 4946182
AND(
p_project_id IS NULL
OR(p_project_id = -9999
AND prd.project_id IS NULL) --bug#2669021
OR NVL(prd.project_id, -99) = p_project_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 DISTINCT rsl.shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_lines rsl,
po_requisition_lines prl
WHERE
prl.requisition_header_id = p_req_header_id
AND prl.requisition_line_id = rsl.requisition_line_id;
SELECT fm_serial_number INTO l_serial_number
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_original_rti_id ;
SELECT lot_number INTO l_lot_number
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_original_rti_id ;
SELECT lot_control_code,serial_number_control_code
INTO l2_lot_code,l_serial_control_code1
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND ORGANIZATION_id = (SELECT ORGANIZATION_id FROM
rcv_shipment_headers WHERE SHIPMENT_HEADER_ID =p_shipment_header_id);--bug13558642
SELECT primary_unit_of_measure
INTO g_rcpt_match_table_gross(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;
print_debug('create_intship_rcpt_intf_rec: 90 before insert_txn_interface ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
l_interface_transaction_id := insert_txn_interface(
l_rcv_transaction_rec
, l_rcv_rcpt_rec
, l_group_id
, l_transaction_type
, p_organization_id
, p_location_id
, p_source_type
, NULL --p_qa_routing_id
, p_project_id
, p_task_id);
print_debug('create_intship_rcpt_intf_rec: 100 after insert_txn_interface ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
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
, '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
, project_id project_id
, task_id 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) --bug#2669021
OR 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 g_rcpt_match_table_gross(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;
print_debug('create_rma_rcpt_intf_rec: 90 before insert_txn_interface' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
l_interface_transaction_id := insert_txn_interface(
l_rcv_transaction_rec
, l_rcv_rcpt_rec
, l_group_id
, l_transaction_type
, p_organization_id
, p_location_id
, p_source_type
, NULL -- p_qa_routing_id
, p_project_id
, p_task_id);
print_debug('create_rma_rcpt_intf_rec: 100 after insert_txn_interface' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
g_rcpt_lot_qty_rec_tb.DELETE;
SELECT 'N' line_chkbox
-- For Bug 7440217
, p_source_type source_type_code
-- End for Bug 7440217
, '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
, 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
, 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,
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_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
WHERE 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
-- For Bug 7440217 Added type LCM also
AND rsh.asn_type IN('ASN', 'ASBN', 'LCM')
-- End for Bug 7440217
AND rsl.shipment_line_status_code <> 'CANCELLED'
AND rsl.shipment_line_id = v_shipment_line_id
AND poll.line_location_id IN(
SELECT pod.line_location_id
FROM po_distributions_all pod
WHERE (
p_project_id IS NULL
OR(p_project_id = -9999
AND pod.project_id IS NULL)
OR --bug#2669021
NVL(pod.project_id, -9999) = p_project_id
)
AND(p_task_id IS NULL
OR NVL(pod.task_id, -9999) = p_task_id)
AND pod.po_header_id = poll.po_header_id
AND pod.po_line_id = poll.po_line_id
AND pod.line_location_id = poll.line_location_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,
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 temp
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;
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_original_rti_id ;
select lot_num
into l_lot_number
from rcv_lots_supply
where lot_num = l_lot_number
and supply_type_code = 'SHIPMENT'
and primary_quantity > 0
and shipment_line_id in
(select shipment_line_id
from rcv_shipment_headers
where shipment_header_id = p_shipment_header_id)
and rownum = 1;
SELECT primary_unit_of_measure
INTO g_rcpt_match_table_gross(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_from_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);
print_debug('create_asn_con_rcpt_intf_rec: 100 before insert_txn_interface' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_rcv_transaction_rec.interface_transaction_id
FROM DUAL;
l_interface_transaction_id := insert_txn_interface(
l_rcv_transaction_rec
, l_rcv_rcpt_rec
, l_group_id
, l_transaction_type
, p_organization_id
, p_location_id
, p_source_type
, l_rcv_rcpt_rec.routing_id
, p_project_id
, p_task_id
, p_express_transaction
);
print_debug('create_asn_con_rcpt_intf_rec: 110 after insert_txn_interface' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
g_rcpt_lot_qty_rec_tb.DELETE;
SELECT lpn.lpn_id
, lpnc.inventory_item_id
, lpnc.revision
, lpnc.quantity
, lpnc.uom_code
, msi.lot_control_code
, ''
, --uom.unit_of_measure,
p_po_header_id
, lpnc.lot_number
, lpn.organization_id
, msi.serial_number_control_code
, msi.primary_uom_code
, lpnc.secondary_quantity -- Bug 7708998
FROM wms_lpn_contents lpnc, wms_license_plate_numbers lpn, mtl_system_items_b msi, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = p_shipment_header_id
AND((lpn.source_header_id = rsh.shipment_header_id)
OR (lpn.source_header_id IS NULL AND lpn.source_name = rsh.shipment_num)) --BUG14846625/16711363
AND lpn.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(lpn.lpn_id = NVL(p_lpn_id, lpn.lpn_id)
OR lpn.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 lpn.lpn_id = nvl(p_lpn_id, lpn.lpn_id)
AND lpn.lpn_id = lpnc.parent_lpn_id
AND lpnc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id
AND(
lpnc.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 lpnc.source_line_id IS NULL
);
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;
SELECT expiration_date
INTO l_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_dynamic_lot(
p_api_version => 1.0
, --p_init_msg_list => p_init_msg_list,
--p_commit => p_commit,
--p_validation_level => p_validation_level,
p_inventory_item_id => l_inventory_item_id
, p_organization_id => p_organization_id
, p_lot_number => l_lot_number
, p_expiration_date => l_expiration_date
, --p_transaction_temp_id => p_transaction_temp_id,
--p_transaction_action_id => p_transaction_action_id,
p_transfer_organization_id => l_lpn_org, --uncommenting and setting the transfer_org_id to l_lpn_org_id to
--populate lot attributes bug3368089
p_status_id => ''
, p_update_status => 'FALSE'
, x_object_id => l_object_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;
SELECT expiration_date
, status_id
INTO l_expiration_date
, l_lot_status_id
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_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 7708998
, p_organization_id => l_from_org_id
, p_inventory_item_id => l_inventory_item_id
, p_expiration_date => l_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'
);
Moreover, the call insert_msni (see above) inserts 1 record per serial number. This will be the underlying assumption of
following validation.
*/
l_msni_count := 0;
select count(1) into
l_msni_count
from mtl_serial_numbers_interface msni, rcv_transactions_interface rti,
wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
mtl_serial_numbers msn -- Bug 16517269
where rti.interface_transaction_id = msni.product_transaction_id
and rsh.shipment_header_id = p_shipment_header_id
and (lpn.source_header_id = rsh.shipment_header_id
or lpn.source_name = rsh.shipment_num)
and lpn.lpn_id = l_lpn_id
and rti.lpn_id = l_lpn_id
and msn.lpn_id = l_lpn_id -- Bug 16517269
and rti.item_id = l_inventory_item_id
and msn.inventory_item_id = l_inventory_item_id -- Bug 16517269
and nvl (msn.revision,'@@@') = nvl (l_revision,'@@@') -- Bug 16517269
and nvl (rti.item_revision,'@@@') = nvl (l_revision,'@@@')
and nvl (msn.lot_number, '@@@') = nvl (l_lot_number, '@@@') -- Bug 16517269
and msn.serial_number = msni.fm_serial_number -- Bug 16517269
and msni.product_code = 'RCV'
and rti.processing_status_code <> 'ERROR'
and rti.transaction_status_code <> 'ERROR';
'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 use 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.3 - After inserting into wlpni for p_lpn_id with parent NULL '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4
);
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 3630412 for normal PO receipt moved the code to rcv_insert_update_header
l_counter := 1;
SELECT lot_number
, transaction_quantity
, primary_quantity -- 3648908
, lot_expiration_date
, transaction_temp_id
, secondary_quantity --OPM Convergence
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_source_id
UNION ALL
SELECT mtln.lot_number
, mtln.transaction_quantity
, mtln.primary_quantity
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, mtln.secondary_transaction_quantity --OPM Convergence
FROM mtl_material_transactions mmt
, mtl_transaction_lot_numbers mtln
, rcv_transactions rt
, rcv_shipment_lines rsl
, mtl_system_items msi
WHERE rt.interface_transaction_id = p_txn_source_id
AND mmt.rcv_transaction_id = rt.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND(rt.transaction_type = 'RETURN TO RECEIVING'
OR(rt.transaction_type = 'CORRECT'
AND rt.quantity < 0))
AND msi.lot_control_code = 2
AND EXISTS(SELECT 1
FROM rcv_transactions rt1
WHERE rt1.transaction_id = rt.parent_transaction_id
AND rt1.transaction_type = 'DELIVER')
AND rt.user_entered_flag = 'Y'
AND rsl.shipment_line_id = rt.shipment_line_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rt.organization_id;
* so that it selects the lot and qty
* from mtln instead of mtlt, if the txn
* is post-inv txn processing, because
* mtlt records will not exist post-inv
* txn processing.
******************************************************************************/
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
g_rcpt_lot_qty_rec_tb.DELETE;
** mtl_lot_numbers. So, insert the lot number from
** mtl_transactions_lot_temp into mtl_lot_numbers
** by calling API INV_LOT_API_PUB.insertLot.
** This API takes care of populating attributes also.
*/
BEGIN
SELECT '1'
INTO l_dummy
FROM mtl_lot_numbers
WHERE lot_number = i.lot_number
AND inventory_item_id = p_item_id
AND organization_id = p_org_id; --Added bug3466942
print_debug('Lot insertion ' || i.lot_number, 1);
inv_lot_api_pub.insertlot(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_inventory_item_id => p_item_id
, p_organization_id => p_org_id
, p_lot_number => i.lot_number
, p_expiration_date => i.lot_expiration_date
, p_transaction_temp_id => i.transaction_temp_id
, p_transaction_action_id => NULL
, p_transfer_organization_id => NULL
, x_object_id => l_object_id
, x_return_status => x_status
, x_msg_count => l_msg_count
, x_msg_data => x_message
);
print_debug('maintain move order - unable to insert lot ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('Lot inserted ', 1);
g_rcpt_lot_qty_rec_tb.DELETE;
SELECT PROCESSING_STATUS_CODE
INTO v_processing_status_code
FROM RCV_TRANSACTIONS_INTERFACE
WHERE HEADER_INTERFACE_ID in
(SELECT HEADER_INTERFACE_ID
FROM RCV_HEADERS_INTERFACE
WHERE receipt_num = p_lcmReceiptNum
AND SHIP_TO_ORGANIZATION_ID = p_lcmOrgID)
AND ROWNUM < 2;
SELECT rsl.po_line_location_id,rsl.po_distribution_id,
decode(rsh.asn_type,'ASN','Y','N'),Decode(rsh.receipt_source_code,'INTERNAL ORDER','Y','N')
INTO l_po_line_location_id,l_po_distribution_id,l_is_asn,l_is_req
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsl.shipment_line_id = p_shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id;
SELECT po_line_location_id, po_distribution_id, source_document_code
INTO l_po_line_location_id, l_po_distribution_id, l_source_document_code
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
print_debug('Calling to insert RHI for J code', 4);
SELECT cost_group_id
INTO l_from_cost_group_id
FROM wms_lpn_contents wlpnc
WHERE organization_id = p_org_id
AND parent_lpn_id = p_lpn
AND wlpnc.inventory_item_id = p_item_id
AND Nvl(wlpnc.lot_number,'@@@') = Nvl(l_lot_qty_rec_tb(i).lot_number,Nvl(wlpnc.lot_number,'@@@'))
AND EXISTS(
SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_org_id
AND cost_group_id = wlpnc.cost_group_id);
SELECT cost_group_id
INTO l_from_cost_group_id
FROM rcv_shipment_lines rsl
WHERE shipment_line_id = p_shipment_line_id
AND exists (
SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_org_id
AND cost_group_id = rsl.cost_group_id)
AND ROWNUM = 1;
SELECT cost_group_id
INTO l_from_cost_group_id
FROM wms_lpn_contents wlpnc
WHERE organization_id = p_org_id
AND parent_lpn_id = p_lpn
AND wlpnc.inventory_item_id = p_item_id
AND EXISTS(
SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_org_id
AND cost_group_id = wlpnc.cost_group_id);
SELECT cost_group_id
INTO l_from_cost_group_id
FROM rcv_shipment_lines rsl
WHERE shipment_line_id = p_shipment_line_id
AND exists (
SELECT 1
FROM cst_cost_group_accounts
WHERE organization_id = p_org_id
AND cost_group_id = rsl.cost_group_id)
AND ROWNUM = 1;
PROCEDURE rcv_update_rti_from_header(
p_shipment_num VARCHAR
, p_freight_carrier_code VARCHAR2
, p_bill_of_lading VARCHAR2
, p_packing_slip VARCHAR2
, p_num_of_containers NUMBER
, p_waybill_airbill_num VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
print_debug('rcv_update_rti_from_header 5: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('Calling to insert RHI for J code', 4);
SELECT transaction_status_code
INTO l_process_status
FROM rcv_transactions_interface
WHERE group_id = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
AND ROWNUM = 1;
SELECT transaction_status_code
INTO l_process_status
FROM rcv_transactions_interface
WHERE shipment_header_id = g_shipment_header_id
AND ROWNUM < 2;
print_debug('rcv_update_rti_from_header 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('Calling to insert RHI for J code', 4);
UPDATE rcv_transactions_interface
SET shipment_num = p_shipment_num
, freight_carrier_code = p_freight_carrier_code
, bill_of_lading = p_bill_of_lading
, packing_slip = p_packing_slip
, num_of_containers = p_num_of_containers
, waybill_airbill_num = p_waybill_airbill_num
WHERE group_id = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
UPDATE rcv_transactions_interface
SET shipment_num = p_shipment_num
, freight_carrier_code = p_freight_carrier_code
, bill_of_lading = p_bill_of_lading
, packing_slip = p_packing_slip
, num_of_containers = p_num_of_containers
, waybill_airbill_num = p_waybill_airbill_num
WHERE shipment_header_id = g_shipment_header_id;
print_debug('rcv_update_rti_from_header 20: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_RCV_STD_RCPT_APIS.rcv_update_rti_from_header', l_progress, SQLCODE);
END rcv_update_rti_from_header;
PROCEDURE rcv_insert_header_interface(
p_organization_id NUMBER
, p_source_type VARCHAR2
, p_receipt_num OUT NOCOPY VARCHAR2
, p_vendor_id NUMBER
, p_vendor_site_id NUMBER
, p_shipment_num VARCHAR2
, p_ship_to_location_id NUMBER
, p_bill_of_lading VARCHAR2
, p_packing_slip VARCHAR2
, p_shipped_date DATE
, p_freight_carrier_code VARCHAR2
, p_expected_receipt_date DATE
, p_num_of_containers NUMBER
, p_waybill_airbill_num VARCHAR2
, p_comments VARCHAR2
, p_ussgl_transaction_code VARCHAR2
, p_government_context VARCHAR2
, p_request_id NUMBER
, p_program_application_id NUMBER
, p_program_id NUMBER
, p_program_update_date DATE
, p_customer_id NUMBER
, p_customer_site_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_attribute_category IN VARCHAR2 DEFAULT NULL --Bug #4147209 - Added DFF cols
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
) IS
l_header rcv_headers_interface%ROWTYPE;
print_debug('rcv_insert_header_interface 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_insert_ship_header_sp;
l_header.last_update_date := l_sysdate;
l_header.last_update_login := inv_rcv_common_apis.g_po_startup_value.user_id;
l_header.last_updated_by := inv_rcv_common_apis.g_po_startup_value.user_id;
SELECT expected_receipt_date
INTO l_header.expected_receipt_date
FROM rcv_shipment_headers
WHERE shipment_num = p_shipment_num;
SELECT Nvl(expected_receipt_date, SYSDATE)
INTO l_header.expected_receipt_date
FROM rcv_shipment_headers
WHERE shipment_num = p_shipment_num
AND shipment_header_id = g_shipment_header_id;
print_debug('rcv_insert_header_interface 20: before rcv_shipment_headers_pkg.insert_row ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 1);
SELECT rcv_headers_interface_s.NEXTVAL
INTO g_header_intf_id
FROM SYS.DUAL;
INSERT INTO rcv_headers_interface
(
header_interface_id
, group_id
, processing_status_code
, transaction_type
, validation_flag
, auto_transact_code
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, receipt_source_code
, vendor_id
, vendor_site_id
, ship_to_organization_id
, shipment_num
, receipt_num
, bill_of_lading
, packing_slip
, shipped_date
, freight_carrier_code
, expected_receipt_date
, employee_id
, num_of_containers
, waybill_airbill_num
, comments
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, usggl_transaction_code
, processing_request_id
, customer_id
, customer_site_id
)
VALUES (
g_header_intf_id
, inv_rcv_common_apis.g_rcv_global_var.interface_group_id
, 'PENDING' -- processing_status_code
, 'NEW' -- transaction_type
, 'Y' -- validation_flag
, 'RECEIVE' -- auto_transact_code
, l_header.last_update_date
, l_header.last_updated_by
, l_header.creation_date
, l_header.created_by
, l_header.last_update_login
, NVL(l_header.receipt_source_code, 'VENDOR')
, l_header.vendor_id
, l_header.vendor_site_id
, l_header.ship_to_organization_id
, l_header.shipment_num
, l_header.receipt_num
, l_header.bill_of_lading
, l_header.packing_slip
, l_header.shipped_date
, l_header.freight_carrier_code
, l_header.expected_receipt_date
, l_header.employee_id
, l_header.num_of_containers
, l_header.waybill_airbill_num
, l_header.comments
, l_header.attribute_category
, l_header.attribute1
, l_header.attribute2
, l_header.attribute3
, l_header.attribute4
, l_header.attribute5
, l_header.attribute6
, l_header.attribute7
, l_header.attribute8
, l_header.attribute9
, l_header.attribute10
, l_header.attribute11
, l_header.attribute12
, l_header.attribute13
, l_header.attribute14
, l_header.attribute15
, l_header.usggl_transaction_code
, l_header.processing_request_id
, l_header.customer_id
, l_header.customer_site_id
);
print_debug('rcv_insert_header_interface 30: after insert_row ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('rcv_insert_header_interface 40: before rcv_update_rti_from_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
ROLLBACK TO rcv_insert_ship_header_sp;
print_debug('INV_RCV_STD_RCPT_APIS.rcv_insert_header_interface 50.1: RAISE FND_API.G_EXC_ERROR;' || l_progress, 4);
ROLLBACK TO rcv_insert_ship_header_sp;
print_debug('INV_RCV_STD_RCPT_APIS.rcv_insert_header_interface 50.2: RAISE FND_API.G_EXC_ERROR;' || l_progress, 4);
ROLLBACK TO rcv_insert_ship_header_sp;
inv_mobile_helper_functions.sql_error('INV_RCV_STD_RCPT_APIS.rcv_insert_header_interface', l_progress, SQLCODE);
END rcv_insert_header_interface;
PROCEDURE rcv_update_header_interface(
p_organization_id NUMBER
, p_header_intf_id NUMBER
, p_source_type VARCHAR2
, p_receipt_num VARCHAR2
, p_vendor_id NUMBER
, p_vendor_site_id NUMBER
, p_shipment_num VARCHAR2
, p_ship_to_location_id NUMBER
, p_bill_of_lading VARCHAR2
, p_packing_slip VARCHAR2
, p_shipped_date DATE
, p_freight_carrier_code VARCHAR2
, p_expected_receipt_date DATE
, p_num_of_containers NUMBER
, p_waybill_airbill_num VARCHAR2
, p_comments VARCHAR2
, p_ussgl_transaction_code VARCHAR2
, p_program_request_id NUMBER
, p_customer_id NUMBER
, p_customer_site_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_attribute_category IN VARCHAR2 DEFAULT NULL --Bug #4147209
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
) IS
l_sysdate DATE := SYSDATE;
print_debug('rcv_update_header_interface 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_update_ship_header_sp;
print_debug('rcv_update_header_interface 20: before update_row ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4);
SELECT vendor_site_id
INTO l_vendor_site_id
FROM rcv_shipment_headers
WHERE shipment_header_id = g_shipment_header_id;
UPDATE rcv_headers_interface
SET customer_id = Nvl(p_customer_id, customer_id)
, last_update_date = l_sysdate
, last_update_login = NVL(inv_rcv_common_apis.g_po_startup_value.user_id, last_update_login)
, last_updated_by = NVL(inv_rcv_common_apis.g_po_startup_value.user_id, last_updated_by)
, vendor_id = NVL(p_vendor_id, vendor_id)
, vendor_site_id = NVL(l_vendor_site_id, vendor_site_id)--bug9409867
, ship_to_organization_id = NVL(p_organization_id, ship_to_organization_id)
, receipt_num = NVL(p_receipt_num, receipt_num)
, bill_of_lading = p_bill_of_lading
, waybill_airbill_num = Nvl(p_waybill_airbill_num,waybill_airbill_num)--BUG 5111375 (FP of BUG 4500055)
, packing_slip = p_packing_slip
, shipped_date = NVL(p_shipped_date, shipped_date)
, freight_carrier_code = p_freight_carrier_code
, expected_receipt_date = NVL(p_expected_receipt_date, expected_receipt_date)
, employee_id = NVL(inv_rcv_common_apis.g_po_startup_value.employee_id, employee_id)
, num_of_containers = NVL(p_num_of_containers, num_of_containers)
, comments = p_comments
, attribute_category = p_attribute_category
, attribute1 = p_attribute1
, attribute2 = p_attribute2
, attribute3 = p_attribute3
, attribute4 = p_attribute4
, attribute5 = p_attribute5
, attribute6 = p_attribute6
, attribute7 = p_attribute7
, attribute8 = p_attribute8
, attribute9 = p_attribute9
, attribute10 = p_attribute10
, attribute11 = p_attribute11
, attribute12 = p_attribute12
, attribute13 = p_attribute13
, attribute14 = p_attribute14
, attribute15 = p_attribute15
WHERE header_interface_id = p_header_intf_id;
'rcv_update_header_interface 30: after update_row before rcv_update_rti_from_header '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 4
);
rcv_update_rti_from_header(
p_shipment_num => p_shipment_num
, p_freight_carrier_code => p_freight_carrier_code
, p_bill_of_lading => p_bill_of_lading
, p_packing_slip => p_packing_slip
, p_num_of_containers => p_num_of_containers
, p_waybill_airbill_num => p_waybill_airbill_num
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
fnd_message.set_name('INV', 'INV_RCV_UPDATE_RTI_FAIL');
'rcv_update_header_interface 30.1: rcv_update_rti_from_header RAISE FND_API.G_EXC_ERROR;' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
fnd_message.set_name('INV', 'INV_RCV_UPDATE_RTI_FAIL');
'rcv_update_header_interface 30.2: rcv_update_rti_from_header RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
print_debug('rcv_update_header_interface 40: after rcv_update_rti_from_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT NVL(ASN_TYPE,'@@@')
INTO l_check_asn
FROM rcv_shipment_headers
WHERE shipment_header_id = g_shipment_header_id;
INV_CR_ASN_DETAILS.update_asn_item_details
(p_group_id=>inv_rcv_common_apis.g_rcv_global_var.interface_group_id);
print_debug('# of WAD updated:'||SQL%rowcount,4);
ROLLBACK TO rcv_update_ship_header_sp;
ROLLBACK TO rcv_update_ship_header_sp;
ROLLBACK TO rcv_update_ship_header_sp;
inv_mobile_helper_functions.sql_error('INV_RCV_STD_RCPT_APIS.rcv_update_header_interface', l_progress, SQLCODE);
END rcv_update_header_interface;
PROCEDURE rcv_insert_update_header(
p_organization_id IN NUMBER
, p_shipment_header_id IN OUT NOCOPY NUMBER
, p_source_type IN VARCHAR2
, p_receipt_num IN OUT NOCOPY VARCHAR2
, p_vendor_id IN NUMBER
, p_vendor_site_id IN NUMBER
, p_shipment_num IN VARCHAR2
, p_ship_to_location_id IN NUMBER
, p_bill_of_lading IN VARCHAR2
, p_packing_slip IN VARCHAR2
, p_shipped_date IN DATE
, p_freight_carrier_code IN VARCHAR2
, p_expected_receipt_date IN DATE
, p_num_of_containers IN NUMBER
, p_waybill_airbill_num IN VARCHAR2
, p_comments IN VARCHAR2
, p_ussgl_transaction_code IN VARCHAR2
, p_government_context IN VARCHAR2
, p_request_id IN NUMBER
, p_program_application_id IN NUMBER
, p_program_id IN NUMBER
, p_program_update_date IN DATE
, p_customer_id IN NUMBER
, p_customer_site_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_attribute_category IN VARCHAR2 DEFAULT NULL --Bug #4147209 - Added DFF cols
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
SELECT rti.interface_transaction_id
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id = p_shipment_header_id;
print_debug('rcv_insert_update_header 10: before calling rcv_insert_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('Calling to insert RHI for J code', 4);
rcv_insert_header_interface(
p_organization_id => p_organization_id
, p_source_type => p_source_type
, p_receipt_num => p_receipt_num
, p_vendor_id => p_vendor_id
, p_vendor_site_id => p_vendor_site_id
, p_shipment_num => p_shipment_num
, p_ship_to_location_id => p_ship_to_location_id
, p_bill_of_lading => p_bill_of_lading
, p_packing_slip => p_packing_slip
, p_shipped_date => p_shipped_date
, p_freight_carrier_code => p_freight_carrier_code
, p_expected_receipt_date => p_expected_receipt_date
, p_num_of_containers => p_num_of_containers
, p_waybill_airbill_num => p_waybill_airbill_num
, p_comments => p_comments
, p_ussgl_transaction_code => p_ussgl_transaction_code
, p_government_context => p_government_context
, p_request_id => p_request_id
, p_program_application_id => p_program_application_id
, p_program_id => p_program_id
, p_program_update_date => p_program_update_date
, p_customer_id => p_customer_id
, p_customer_site_id => p_customer_site_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_attribute_category => p_attribute_category --Bug #4147209
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
);
print_debug('rcv_insert_update_header 20: after calling rcv_insert_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
'rcv_insert_update_header 20.1: rcv_insert_header RAISE FND_API.G_EXC_ERROR;' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
'rcv_insert_update_header 20.2: rcv_insert_header RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
print_debug('rcv_insert_update_header 30: before calling rcv_update_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
print_debug('Calling to insert RHI for J code', 4);
rcv_update_header_interface
(
p_organization_id => p_organization_id
, p_header_intf_id => p_shipment_header_id
, p_source_type => p_source_type
, p_receipt_num => p_receipt_num
, p_vendor_id => p_vendor_id
, p_vendor_site_id => p_vendor_site_id
, p_shipment_num => p_shipment_num
, p_ship_to_location_id => p_ship_to_location_id
, p_bill_of_lading => p_bill_of_lading
, p_packing_slip => p_packing_slip
, p_shipped_date => p_shipped_date
, p_freight_carrier_code => p_freight_carrier_code
, p_expected_receipt_date => p_expected_receipt_date
, p_num_of_containers => p_num_of_containers
, p_waybill_airbill_num => p_waybill_airbill_num
, p_comments => p_comments
, p_ussgl_transaction_code => p_ussgl_transaction_code
, p_program_request_id => p_request_id
, p_customer_id => p_customer_id
, p_customer_site_id => p_customer_site_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_attribute_category => p_attribute_category --Bug #4147209
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
);
print_debug('rcv_insert_update_header 40: after calling rcv_update_header ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
'rcv_insert_update_header 40.1: rcv_update_header RAISE FND_API.G_EXC_ERROR;' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
'rcv_insert_update_header 40.2: rcv_update_header RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
print_debug('Exit rcv_insert_update_header 50: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
inv_mobile_helper_functions.sql_error('INV_RCV_STD_RCPT_APIS.rcv_update_insert_header', l_progress, SQLCODE);
END rcv_insert_update_header;
SELECT 1
FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
WHERE (lpn.source_header_id = rsh.shipment_header_id
OR lpn.source_name = rsh.shipment_num)
AND rsh.shipment_header_id = p_shipment_header_id
AND (p_lpn_id IS NULL OR lpn.lpn_id=p_lpn_id)
AND lpn.lpn_id = lpnc.parent_lpn_id
AND(
lpnc.source_line_id IN(SELECT pola.po_line_id
FROM po_lines_all pola
WHERE (p_po_header_id IS NULL OR pola.po_header_id=p_po_header_id)
)
OR lpnc.source_line_id IS NULL
);
SELECT 1
FROM wms_lpn_contents_v lpnc, wms_license_plate_numbers lpn, rcv_shipment_headers rsh
WHERE (lpn.source_header_id = rsh.shipment_header_id
OR lpn.source_name = rsh.shipment_num)
AND rsh.shipment_header_id = p_shipment_header_id
AND lpn.lpn_id = p_lpn_id
AND lpn.lpn_id = lpnc.parent_lpn_id
AND(
lpnc.source_line_id IN(SELECT pola.po_line_id
FROM po_lines_all pola
WHERE (p_po_header_id IS NULL OR pola.po_header_id=p_po_header_id)
)
OR lpnc.source_line_id IS NULL
);
ROLLBACK TO rcv_insert_ship_header_sp;
ROLLBACK TO rcv_insert_ship_header_sp;
DELETE wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id;
UPDATE mtl_serial_numbers
SET lpn_id = NULL
WHERE lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 5
WHERE lpn_id = p_lpn_id;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE source_name = (SELECT shipment_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id);
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE source_header_id = p_shipment_header_id;
SELECT SUBSTR(asn_type, 1, 3)
INTO l_asn_type
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT wms_enabled_flag
INTO l_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
print_debug('clear_LPN_for_ASN 20: - calling update_lpn_org for:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
IF (l_wms_enabled = 'Y') THEN --Update lpn org only if the current org is wms enabled
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
);
print_debug('clear_LPN_for_ASN 20.1 : update_lpn_org RAISE FND_API.G_EXC_ERROR;'
'clear_LPN_for_ASN 20.2: update_lpn_org RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
print_debug('clear_LPN_for_ship 20: - calling update_lpn_org for:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
IF (l_wms_enabled = 'Y') THEN --Update lpn org only if the current org is wms enabled
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
);
print_debug('clear_LPN_for_ship 20.1 : update_lpn_org RAISE FND_API.G_EXC_ERROR;'
'clear_LPN_for_ship 20.2: update_lpn_org RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'
SELECT COUNT(*)
INTO l_lpn_count
FROM wms_license_plate_numbers
WHERE organization_id = p_from_organization_id
AND lpn_context = 6
AND source_name = p_shipment_number;
SELECT 1
INTO l_lot_serial_flag
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_system_items msi1
, mtl_system_items msi2
WHERE rsh.shipment_num = p_shipment_number
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.item_id = msi1.inventory_item_id
AND msi1.organization_id = p_from_organization_id
AND (Nvl(msi1.lot_control_code,1) <> Nvl(msi2.lot_control_code,1)
OR (Nvl(msi1.serial_number_control_code,1) in (1,6)
AND Nvl(msi2.serial_number_control_code,1) IN (2,5))
OR (Nvl(msi1.serial_number_control_code,1) in (2,5)
AND Nvl(msi2.serial_number_control_code,1) IN (1,6)))
AND rsl.item_id = msi2.inventory_item_id
AND msi2.organization_id = rsl.to_organization_id
AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_lpn_count
FROM wms_license_plate_numbers
WHERE lpn_context = 7
AND source_header_id = p_shipment_header_id;
SELECT 1
INTO l_lot_serial_flag
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_system_items msi1
, mtl_system_items msi2
WHERE rsh.shipment_header_id = p_shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.item_id = msi1.inventory_item_id
AND msi1.organization_id = rsl.from_organization_id
AND (Nvl(msi1.lot_control_code,1) <> Nvl(msi2.lot_control_code,1)
OR (Nvl(msi1.serial_number_control_code,1) in (1,6)
AND Nvl(msi2.serial_number_control_code,1) IN (2,5))
OR (Nvl(msi1.serial_number_control_code,1) in (2,5)
AND Nvl(msi2.serial_number_control_code,1) IN (1,6)))
AND rsl.item_id = msi2.inventory_item_id
AND msi2.organization_id = rsl.to_organization_id
AND ROWNUM = 1;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = l_order_header_id;
SELECT wdd1.lpn_id
, wdd1.organization_id
FROM wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd1
WHERE wdd.source_header_id = v_order_header_id
AND wdd.source_line_id = v_order_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT header_id
INTO l_order_header_id
FROM oe_order_headers_all
WHERE orig_sys_document_ref = p_req_num
AND order_source_id = 10;
SELECT organization_id
, lpn_context
INTO l_lpn_org
, l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id;
SELECT organization_id
, lpn_context
INTO l_lpn_org
, l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND lpn_id IN(SELECT parent_lpn_id
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id);
SELECT 2
INTO l_lpn_flag
FROM po_requisition_headers prh
, po_requisition_lines prl
, rcv_shipment_lines rsl
, mtl_system_items msi1
, mtl_system_items msi2
WHERE prh.segment1 = p_req_num
AND prl.requisition_header_id = prh.requisition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND rsl.item_id = msi1.inventory_item_id
AND msi1.organization_id = rsl.from_organization_id
AND (Nvl(msi1.lot_control_code,1) <> Nvl(msi2.lot_control_code,1)
OR (Nvl(msi1.serial_number_control_code,1) in (1,6)
AND Nvl(msi2.serial_number_control_code,1) IN (2,5))
OR (Nvl(msi1.serial_number_control_code,1) in (2,5)
AND Nvl(msi2.serial_number_control_code,1) IN (1,6)))
AND rsl.item_id = msi2.inventory_item_id
AND msi2.organization_id = rsl.to_organization_id
AND ROWNUM = 1;
PROCEDURE update_lpn_org(
p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
SELECT distinct inventory_item_id
FROM wms_lpn_contents
WHERE lot_number IS NOT NULL
AND parent_lpn_id = p_lpn_id;
SELECT DISTINCT inventory_item_id
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id;
print_debug('Enter update_LPN_Org 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_update_lpn_org_sp;
UPDATE wms_license_plate_numbers
SET organization_id = p_organization_id
, subinventory_code = ''
, locator_id = NULL
WHERE lpn_id = p_lpn_id;
SELECT lot_control_code
INTO l_lot_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_organization_id;
UPDATE wms_lpn_contents
SET lot_number = NULL /* 3835398 */
WHERE parent_lpn_id = p_lpn_id
AND inventory_item_id = l_item_id;
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = l_item_id;
UPDATE mtl_serial_numbers
SET current_organization_id = p_organization_id
WHERE lpn_id = p_lpn_id
AND inventory_item_id = l_item_id; /* 3835398 */
UPDATE wms_lpn_contents
SET serial_summary_entry = 2
WHERE parent_lpn_id = p_lpn_id
AND inventory_item_id = l_item_id; /* 3835398 */
UPDATE wms_lpn_contents
SET organization_id = p_organization_id
WHERE parent_lpn_id = p_lpn_id;
print_debug('Exit update_LPN_Org 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_update_lpn_org_sp;
inv_mobile_helper_functions.sql_error('INV_RCV_STD_RCPT_APIS.update_LPN_Org', l_progress, SQLCODE);
END update_lpn_org;
SELECT count(1)
INTO l_partial_rcpt
FROM dual
WHERE EXISTS ( SELECT 1
FROM rcv_transactions
WHERE shipment_header_id = p_shipment_header_id
AND organization_id = p_rcv_org_id
);
SELECT count(1)
INTO l_partial_rcpt
FROM dual
WHERE EXISTS ( SELECT 1
FROM rcv_transactions_interface
WHERE shipment_header_id = p_shipment_header_id
AND to_organization_id = p_rcv_org_id
);
SELECT NVL(SUM(rlsu.quantity),0) ,
NVL(SUM(rlsu.primary_quantity),0)
INTO l_rlsu_quantity,
l_rlsu_primary_quantity
FROM rcv_lots_supply rlsu,
rcv_shipment_lines rsl
WHERE rlsu.shipment_line_id = rsl.shipment_line_id
AND rsl.item_id = p_inventory_item_id
AND rsl.to_organization_id = p_rcv_org_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rlsu.lot_num = p_lot_number
AND rlsu.supply_type_code = 'SHIPMENT' ;
g_rcpt_lot_qty_rec_tb.DELETE;
g_rcpt_lot_qty_rec_tb.DELETE(l_index);
SELECT NVL(SUM(rlsu.primary_quantity),0)
INTO l_rlsu_primary_quantity
FROM rcv_lots_supply rlsu,
rcv_shipment_lines rsl
WHERE rlsu.shipment_line_id = rsl.shipment_line_id
AND rsl.item_id = p_inventory_item_id
AND rsl.to_organization_id = p_rcv_org_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rlsu.lot_num = p_lot_number
AND rlsu.supply_type_code = 'SHIPMENT' ;
SELECT NVL(SUM(mtli.primary_quantity),0)
INTO l_mtli_primary_quantity
FROM mtl_transaction_lots_interface mtli,
rcv_transactions_interface rti
WHERE rti.processing_status_code <> 'ERROR'
AND rti.transaction_status_code <> 'ERROR'
AND rti.to_organization_id = p_rcv_org_id
AND rti.item_id = p_inventory_item_id
AND rti.shipment_header_id = p_shipment_header_id
AND rti.interface_transaction_id = mtli.product_transaction_id
AND mtli.lot_number = p_lot_number
AND mtli.product_code = 'RCV';
SELECT Nvl(SUM(primary_quantity),0)
INTO l_current_grp_prim_qty
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_product_txn_id
AND lot_number = p_lot_number
AND product_code = 'RCV';
SELECT lot_control_code
INTO l_lot_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND ORGANIZATION_id = (SELECT ORGANIZATION_id FROM
rcv_shipment_headers WHERE SHIPMENT_HEADER_ID =p_shipment_header_id);
SELECT NVL(SUM(rlsu.primary_quantity),0)
INTO l_rlsu_primary_quantity
FROM rcv_lots_supply rlsu,
rcv_shipment_lines rsl
WHERE rlsu.shipment_line_id = rsl.shipment_line_id
AND rsl.item_id = p_inventory_item_id
AND rsl.to_organization_id = p_rcv_org_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rlsu.lot_num = p_lot_number
AND rlsu.supply_type_code = 'SHIPMENT'
AND Nvl(rsl.asn_lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rsl.asn_lpn_id,-1));
SELECT NVL(SUM(mtli.primary_quantity),0)
INTO l_mtli_primary_quantity
FROM mtl_transaction_lots_interface mtli,
rcv_transactions_interface rti
WHERE rti.processing_status_code <> 'ERROR'
AND rti.transaction_status_code <> 'ERROR'
AND rti.to_organization_id = p_rcv_org_id
AND rti.item_id = p_inventory_item_id
AND rti.shipment_header_id = p_shipment_header_id
AND rti.interface_transaction_id = mtli.product_transaction_id
AND mtli.lot_number = p_lot_number
AND mtli.product_code = 'RCV'
AND Nvl(rti.lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rti.lpn_id,-1));
SELECT Nvl(SUM(primary_quantity),0)
INTO l_current_grp_prim_qty
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_product_txn_id
AND lot_number = p_lot_number
AND product_code = 'RCV';