[Home] [Help]
386: , header_id
387: , uom_code
388: , quantity - NVL(quantity_delivered,0)
389: , secondary_quantity - NVL(secondary_quantity_delivered,0) --OPM Convergence
390: FROM mtl_txn_request_lines
391: WHERE inventory_item_id = k_inventory_item_id
392: AND organization_id = k_organization_id
393: AND lpn_id = k_lpn_id
394: AND (revision = k_revision
1137:
1138: -- Activate the INSPECT operation
1139: --Update the wms_process_flag for the current MOL so that one else
1140: --messes with it
1141: UPDATE mtl_txn_request_lines
1142: SET wms_process_flag = 2
1143: WHERE line_id = l_new_mol_id;
1144: ELSE
1145:
3555: SELECT project_id
3556: , task_id
3557: INTO l_rti_project_id
3558: , l_rti_task_id
3559: FROM mtl_txn_request_lines
3560: WHERE lpn_id = p_lpn_id
3561: AND inventory_item_id = l_item_id
3562: -- Bug 3366617
3563: -- The following check was not needed as the process_flag is not yet updated.
4207: l_tolerable_qty NUMBER;
4208:
4209: CURSOR c_txn_lines IS
4210: SELECT uom_code, quantity
4211: FROM mtl_txn_request_lines
4212: WHERE inspection_status = 1
4213: AND organization_id = p_organization_id
4214: AND inventory_item_id = p_item_id
4215: AND lpn_id = p_lpn_id;
4609: --BUG 3444196: Modify the following query to avoid the
4610: --'Non-mergable view exists for the following SQL' complaints
4611: SELECT COUNT(DISTINCT pha.po_header_id)
4612: INTO v_count_po
4613: FROM mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha
4614: WHERE reference = 'PO_LINE_LOCATION_ID'
4615: AND mtrl.reference_id = plla.line_location_id
4616: AND plla.po_header_id = pha.po_header_id
4617: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4633: x_msg_data := ' ';
4634:
4635: select distinct pha.po_header_id, pha.segment1, pv.vendor_id, pv.vendor_name, plla.po_line_id
4636: into x_po_id, x_po_number, x_vendor_id, x_vendor_name, v_po_line_id
4637: from mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha, po_vendors pv
4638: where reference = 'PO_LINE_LOCATION_ID'
4639: and mtrl.reference_id = plla.line_location_id
4640: and plla.po_header_id = pha.po_header_id
4641: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4680: begin
4681: -- obtain receipt number
4682: select distinct rsh.receipt_num, '0'
4683: into x_receipt_number, x_receipt_return_status
4684: from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
4685: where reference = 'PO_LINE_LOCATION_ID'
4686: and mtrl.reference_id = rt.po_line_location_id
4687: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4688: and rt.shipment_header_id = rsh.shipment_header_id
4724: --BUG 3444196: Modify the following query to avoid the
4725: --'Non-mergable view exists for the following SQL' complaints
4726: SELECT COUNT(DISTINCT oeh.header_id)
4727: INTO v_count_rma
4728: FROM mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh
4729: WHERE reference = 'ORDER_LINE_ID'
4730: AND mtrl.reference_id = oel.line_id
4731: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4732: AND oel.header_id = oeh.header_id
4744: x_msg_data := ' ';
4745:
4746: select distinct oeh.header_id, oeh.order_number, oest.customer_id, oest.customer_number, oest.name
4747: into x_rma_id, x_rma_number, x_customer_id, x_customer_number, x_customer_name
4748: from mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh, oe_sold_to_orgs_v oest
4749: where reference = 'ORDER_LINE_ID'
4750: and mtrl.reference_id = oel.line_id
4751: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4752: and oel.header_id = oeh.header_id
4762: begin
4763: --obtain the receipt number
4764: select distinct rsh.receipt_num, '0'
4765: into x_receipt_number, x_receipt_return_status
4766: from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
4767: where reference = 'ORDER_LINE_ID'
4768: and mtrl.reference_id = rt.oe_order_line_id
4769: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4770: and rt.shipment_header_id = rsh.shipment_header_id
4800:
4801: -- obtain SHIPMENT RECEIPT INFORMATION
4802: SELECT COUNT(DISTINCT rsl.shipment_header_id)
4803: INTO v_count_intshp
4804: FROM mtl_txn_request_lines mtrl, rcv_shipment_lines rsl
4805: WHERE reference = 'SHIPMENT_LINE_ID'
4806: AND mtrl.reference_id = rsl.shipment_line_id
4807: AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4808: AND mtrl.lpn_id = p_lpn_id
4820: x_msg_data := ' ';
4821:
4822: select distinct rsl.shipment_header_id, rsh.shipment_num, rsh.receipt_num
4823: into x_intshp_id, x_intshp_number, x_receipt_number
4824: from mtl_txn_request_lines mtrl, rcv_shipment_lines rsl, rcv_shipment_headers rsh
4825: where reference = 'SHIPMENT_LINE_ID'
4826: and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4827: and mtrl.reference_id = rsl.shipment_line_id
4828: and rsl.shipment_header_id = rsh.shipment_header_id