[Home] [Help]
419: -- have multiple RTs associated for the same mol. In that case, the second
420: -- and further calls to this api for same mol will have wrong qty.
421: begin
422: select quantity into l_mol_qty
423: from mtl_txn_request_lines
424: where line_id = l_mol_line_id;
425: exception
426: when others then
427: if l_debug = 1 then
891:
892: -- Activate the INSPECT operation
893: --Update the wms_process_flag for the current MOL so that one else
894: --messes with it
895: UPDATE mtl_txn_request_lines
896: SET wms_process_flag = 2
897: WHERE line_id = l_new_mol_id;
898:
899: l_progress := '200';
1107: secondary_quantity
1108: - NVL (secondary_quantity_delivered, 0), --OPM Convergence
1109: REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID,
1110: TXN_SOURCE_ID, inspection_status
1111: FROM mtl_txn_request_lines
1112: WHERE inventory_item_id = k_inventory_item_id
1113: AND organization_id = k_organization_id
1114: AND lpn_id = k_lpn_id
1115: AND (revision = k_revision OR revision IS NULL AND p_revision IS NULL
2212: , secondary_quantity - NVL(secondary_quantity_delivered,0) --OPM Convergence
2213: , REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID
2214: , TXN_SOURCE_ID, inspection_status,
2215: PRIMARY_QUANTITY--Bug 13484877
2216: FROM mtl_txn_request_lines
2217: WHERE inventory_item_id = k_inventory_item_id
2218: AND organization_id = k_organization_id
2219: AND lpn_id = k_lpn_id
2220: AND (revision = k_revision
3028:
3029: -- Activate the INSPECT operation
3030: --Update the wms_process_flag for the current MOL so that one else
3031: --messes with it
3032: UPDATE mtl_txn_request_lines
3033: SET wms_process_flag = 2
3034: WHERE line_id = l_new_mol_id;
3035: ELSE
3036:
5455: SELECT project_id
5456: , task_id
5457: INTO l_rti_project_id
5458: , l_rti_task_id
5459: FROM mtl_txn_request_lines
5460: WHERE lpn_id = p_lpn_id
5461: AND inventory_item_id = l_item_id
5462: -- Bug 3366617
5463: -- The following check was not needed as the process_flag is not yet updated.
6107: l_tolerable_qty NUMBER;
6108:
6109: CURSOR c_txn_lines IS
6110: SELECT uom_code, (quantity - Nvl(quantity_delivered,0)) quantity --bug#12663552
6111: FROM mtl_txn_request_lines
6112: WHERE inspection_status is not null --8405606
6113: AND organization_id = p_organization_id
6114: AND line_status = 7 --bug#12663552
6115: AND inventory_item_id = p_item_id
6508: --BUG 3444196: Modify the following query to avoid the
6509: --'Non-mergable view exists for the following SQL' complaints
6510: SELECT COUNT(DISTINCT pha.po_header_id)
6511: INTO v_count_po
6512: FROM mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha
6513: WHERE reference = 'PO_LINE_LOCATION_ID'
6514: AND mtrl.reference_id = plla.line_location_id
6515: AND plla.po_header_id = pha.po_header_id
6516: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6532: x_msg_data := ' ';
6533:
6534: select distinct pha.po_header_id, pha.segment1, pv.vendor_id, pv.vendor_name, plla.po_line_id
6535: into x_po_id, x_po_number, x_vendor_id, x_vendor_name, v_po_line_id
6536: from mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha, po_vendors pv
6537: where reference = 'PO_LINE_LOCATION_ID'
6538: and mtrl.reference_id = plla.line_location_id
6539: and plla.po_header_id = pha.po_header_id
6540: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6579: begin
6580: -- obtain receipt number
6581: select distinct rsh.receipt_num, '0'
6582: into x_receipt_number, x_receipt_return_status
6583: from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
6584: where reference = 'PO_LINE_LOCATION_ID'
6585: and mtrl.reference_id = rt.po_line_location_id
6586: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6587: and rt.shipment_header_id = rsh.shipment_header_id
6623: --BUG 3444196: Modify the following query to avoid the
6624: --'Non-mergable view exists for the following SQL' complaints
6625: SELECT COUNT(DISTINCT oeh.header_id)
6626: INTO v_count_rma
6627: FROM mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh
6628: WHERE reference = 'ORDER_LINE_ID'
6629: AND mtrl.reference_id = oel.line_id
6630: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6631: AND oel.header_id = oeh.header_id
6643: x_msg_data := ' ';
6644:
6645: select distinct oeh.header_id, oeh.order_number, oest.customer_id, oest.customer_number, oest.name
6646: into x_rma_id, x_rma_number, x_customer_id, x_customer_number, x_customer_name
6647: from mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh, oe_sold_to_orgs_v oest
6648: where reference = 'ORDER_LINE_ID'
6649: and mtrl.reference_id = oel.line_id
6650: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6651: and oel.header_id = oeh.header_id
6661: begin
6662: --obtain the receipt number
6663: select distinct rsh.receipt_num, '0'
6664: into x_receipt_number, x_receipt_return_status
6665: from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
6666: where reference = 'ORDER_LINE_ID'
6667: and mtrl.reference_id = rt.oe_order_line_id
6668: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6669: and rt.shipment_header_id = rsh.shipment_header_id
6699:
6700: -- obtain SHIPMENT RECEIPT INFORMATION
6701: SELECT COUNT(DISTINCT rsl.shipment_header_id)
6702: INTO v_count_intshp
6703: FROM mtl_txn_request_lines mtrl, rcv_shipment_lines rsl
6704: WHERE reference = 'SHIPMENT_LINE_ID'
6705: AND mtrl.reference_id = rsl.shipment_line_id
6706: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6707: AND mtrl.lpn_id = p_lpn_id
6719: x_msg_data := ' ';
6720:
6721: select distinct rsl.shipment_header_id, rsh.shipment_num, rsh.receipt_num
6722: into x_intshp_id, x_intshp_number, x_receipt_number
6723: from mtl_txn_request_lines mtrl, rcv_shipment_lines rsl, rcv_shipment_headers rsh
6724: where reference = 'SHIPMENT_LINE_ID'
6725: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6726: and mtrl.reference_id = rsl.shipment_line_id
6727: and rsl.shipment_header_id = rsh.shipment_header_id