[Home] [Help]
647:
648: SELECT rt.transaction_id,
649: rss.serial_num
650: BULK COLLECT INTO l_rt_serial_tbl
651: FROM rcv_transactions rt, rcv_serials_supply rss
652: WHERE rt.transaction_id = l_rcv_transaction_id
653: AND rss.transaction_id = rt.transaction_id
654: AND rss.serial_num IN (SELECT *
655: FROM TABLE (list_serials));
1132: IS
1133: SELECT rs.rcv_transaction_id, rsh.receipt_source_code,
1134: rs.unit_of_measure,
1135: rs.secondary_unit_of_measure --OPM Convergence
1136: FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
1137: WHERE rs.rcv_transaction_id = rt.transaction_id
1138: AND rsh.shipment_header_id = rs.shipment_header_id
1139: AND rs.supply_type_code = 'RECEIVING'
1140: AND rt.transaction_type <> 'UNORDERED'
1158: ,
1159: rls.quantity quantity
1160: FROM rcv_supply rs,
1161: rcv_lots_supply rls,
1162: rcv_transactions rt,
1163: rcv_shipment_headers rsh
1164: WHERE rs.rcv_transaction_id = rt.transaction_id
1165: AND rsh.shipment_header_id = rs.shipment_header_id
1166: AND rs.supply_type_code = 'RECEIVING'
1371: 4
1372: );
1373: END IF;
1374:
1375: -- Open Rcv Transactions cursor
1376: -- MOLCON
1377: --IF (l_serial_number IS NOT NULL)
1378: -- 12942776
1379: -- Motive of the entire change in code is to get a table
1398: COUNT (rs.rcv_transaction_id)
1399: BULK COLLECT INTO t_tmp_tbl
1400: FROM rcv_supply rs,
1401: rcv_serials_supply rss,
1402: rcv_transactions rt,
1403: rcv_shipment_headers rsh
1404: WHERE rs.rcv_transaction_id = rt.transaction_id
1405: and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
1406: and decode(l_mol_ref_code,4,Decode(l_mol_ref, 'PO_LINE_LOCATION_ID',rs.po_line_location_id,'SHIPMENT_LINE_ID',rs.shipment_line_id), -- 13597819
1568: BEGIN
1569: SELECT SUM (NVL (mtli.primary_quantity, 0))
1570: INTO l_processed_lot_prim_qty
1571: FROM mtl_transaction_lots_interface mtli,
1572: rcv_transactions_interface rti
1573: WHERE mtli.product_code = 'RCV'
1574: AND mtli.product_transaction_id =
1575: rti.interface_transaction_id
1576: AND mtli.lot_number = l_lot_number
2241: SELECT rs.rcv_transaction_id
2242: , rsh.receipt_source_code
2243: , rs.unit_of_measure
2244: , rs.secondary_unit_of_measure --OPM Convergence
2245: FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh,rcv_shipment_lines rsl --BugFix:14554730
2246: WHERE rs.rcv_transaction_id = rt.transaction_id
2247: AND rsl.shipment_line_id = rs.shipment_line_id --BugFix:14554730
2248: AND rsh.shipment_header_id = rs.shipment_header_id
2249: AND ((rsl.shipment_line_id = k_reference_id AND k_reference='SHIPMENT_LINE_ID') --BugFix:14554730
2268: , rsh.receipt_source_code
2269: , rs.unit_of_measure
2270: , rs.secondary_unit_of_measure --OPM Convergence
2271: , Sum(rls.quantity) quantity --Bug 14117094
2272: FROM rcv_supply rs, rcv_lots_supply rls, rcv_transactions rt, rcv_shipment_headers rsh
2273: WHERE rs.rcv_transaction_id = rt.transaction_id
2274: AND rsh.shipment_header_id = rs.shipment_header_id
2275: AND rs.supply_type_code = 'RECEIVING'
2276: AND rt.transaction_type <> 'UNORDERED'
2294: SELECT rs.rcv_transaction_id
2295: , rsh.receipt_source_code
2296: , rs.unit_of_measure
2297: , rs.secondary_unit_of_measure --OPM Convergence
2298: FROM rcv_supply rs, rcv_serials_supply rss, rcv_transactions rt, rcv_shipment_headers rsh
2299: WHERE rs.rcv_transaction_id = rt.transaction_id
2300: -- added 12942776 start
2301: and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
2302: AND DECODE(l_mol_ref_code,4, Decode(l_mol_ref, 'PO_LINE_LOCATION_ID' , rs.po_line_location_id,
2506: IF (l_debug = 1) THEN
2507: print_debug('main process: l_remaining_mol_qty = min(l_mol_qty, l_remaining_qty) = ' || l_remaining_mol_qty, 4);
2508: END IF;
2509:
2510: -- Open Rcv Transactions cursor
2511: -- MOLCON
2512: IF (l_serial_number IS NOT NULL) THEN
2513: OPEN rtv_serial_cursor(
2514: l_inventory_item_id
2635: BEGIN
2636: SELECT SUM(Nvl(mtli.primary_quantity,0))
2637: INTO l_processed_lot_prim_qty
2638: FROM mtl_transaction_lots_interface mtli
2639: , rcv_transactions_interface rti
2640: WHERE mtli.product_code = 'RCV'
2641: AND mtli.product_transaction_id = rti.interface_transaction_id
2642: AND mtli.lot_number = l_lot_number
2643: AND rti.parent_transaction_id = l_rcv_transaction_id
3461: select
3462: rcv_transaction_id
3463: , receipt_source_code
3464: , unit_of_measure
3465: from rcv_transactions_v
3466: where po_header_id = k_po_header_id
3467: and to_organization_id = k_organization_id
3468: and item_id = k_inventory_item_id
3469: and (item_revision = k_revision OR
3485: rs.rcv_transaction_id
3486: , rsh.receipt_source_code
3487: , rs.unit_of_measure
3488: , rs.secondary_unit_of_measure --OPM Convergence
3489: from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
3490: where rs.po_header_id = k_po_header_id
3491: and rs.to_organization_id = k_organization_id
3492: and rs.item_id = k_inventory_item_id
3493: and (k_revision_control = 2
3566:
3567: END IF;
3568: -- dbms_output.put_line('main_process_po: Convert inspection uom code into uom');
3569:
3570: -- Open RCV Transactions V cursor
3571: open rtv_cursor(
3572: l_po_header_id
3573: , l_organization_id
3574: , l_inventory_item_id
3875: select
3876: rcv_transaction_id
3877: , receipt_source_code
3878: , unit_of_measure
3879: from rcv_transactions_v
3880: where receipt_source_code <> 'VENDOR'
3881: and shipment_header_id = k_shipment_header_id
3882: and to_organization_id = k_organization_id
3883: and item_id = k_inventory_item_id
3900: rs.rcv_transaction_id
3901: , rsh.receipt_source_code
3902: , rs.unit_of_measure
3903: , rs.secondary_unit_of_measure --OPM Convergence
3904: from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
3905: where rsh.receipt_source_code <> 'VENDOR'
3906: and rs.shipment_header_id = k_shipment_header_id
3907: and rs.to_organization_id = k_organization_id
3908: and rs.item_id = k_inventory_item_id
3983: END IF;
3984:
3985: -- dbms_output.put_line('main_process_intransit: Convert inspection uom code into uom');
3986:
3987: -- Open RCV Transactions V cursor
3988: open rtv_cursor(
3989: l_shipment_header_id
3990: , l_organization_id
3991: , l_inventory_item_id
4291: select
4292: rcv_transaction_id
4293: , receipt_source_code
4294: , unit_of_measure
4295: from rcv_transactions_v
4296: where receipt_source_code = 'CUSTOMER'
4297: and oe_order_header_id = k_oe_order_header_id
4298: and to_organization_id = k_organization_id
4299: and item_id = k_inventory_item_id
4317: rs.rcv_transaction_id
4318: , rsh.receipt_source_code
4319: , rs.unit_of_measure
4320: , rs.secondary_unit_of_measure --OPM Convergence
4321: from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
4322: where rsh.receipt_source_code = 'CUSTOMER'
4323: and rs.oe_order_header_id = k_oe_order_header_id
4324: and rs.to_organization_id = k_organization_id
4325: and rs.item_id = k_inventory_item_id
4398:
4399: END IF;
4400: -- dbms_output.put_line('main_process_rma: Convert inspection uom code into uom');
4401:
4402: -- Open RCV Transactions V cursor
4403: open rtv_cursor(
4404: l_oe_order_header_id
4405: , l_organization_id
4406: , l_inventory_item_id
4706: select
4707: rcv_transaction_id
4708: , receipt_source_code
4709: , unit_of_measure
4710: from rcv_transactions_v
4711: where receipt_num = k_receipt_num
4712: and to_organization_id = k_organization_id
4713: and item_id = k_inventory_item_id
4714: and (item_revision = k_revision OR
4731: rs.rcv_transaction_id
4732: , rsh.receipt_source_code
4733: , rs.unit_of_measure
4734: , rs.secondary_unit_of_measure --OPM Convergence
4735: from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
4736: where rsh.receipt_num = k_receipt_num
4737: and rs.to_organization_id = k_organization_id
4738: and rs.item_id = k_inventory_item_id
4739: and (k_revision_control = 2
4811:
4812: END IF;
4813: -- dbms_output.put_line('main_process_receipt: Convert inspection uom code into uom');
4814:
4815: -- Open RCV Transactions V cursor
4816: open rtv_cursor(
4817: l_receipt_num
4818: , l_organization_id
4819: , l_inventory_item_id
5297: , l_customer_item_number
5298: FROM rcv_supply rsup
5299: ,rcv_shipment_headers rsh
5300: ,rcv_shipment_lines rsl
5301: ,rcv_transactions rt
5302: ,po_line_locations pll
5303: ,po_lines pol
5304: ,mtl_system_items msi
5305: ,mtl_customer_items mci
5357: ELSE
5358: l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
5359: END IF;
5360:
5361: select rcv_transactions_interface_s.nextval
5362: into l_interface_transaction_id from dual;
5363:
5364:
5365: if l_inspection_code = 'ACCEPT' then
5369: end if;
5370:
5371: SELECT RT.MOVEMENT_ID
5372: INTO l_movement_id
5373: FROM RCV_TRANSACTIONS RT
5374: WHERE RT.TRANSACTION_ID = l_rcv_transaction_id;
5375:
5376: --
5377: l_operating_unit_id := inv_rcv_common_apis.get_operating_unit_id( l_receipt_source_code,
5502: ELSE
5503: l_transaction_date := Trunc(Sysdate);
5504: END IF;
5505:
5506: insert into RCV_TRANSACTIONS_INTERFACE
5507: (
5508: receipt_source_code,
5509: interface_transaction_id,
5510: group_id,
5715: INTO v_lcm_ship_line_id, v_unit_landed_cost
5716: FROM rcv_shipment_lines
5717: WHERE shipment_line_id = l_shipment_line_id;
5718:
5719: UPDATE rcv_transactions_interface
5720: SET lcm_shipment_line_id = v_lcm_ship_line_id,
5721: unit_landed_cost = v_unit_landed_cost
5722: WHERE interface_transaction_id = l_interface_transaction_id
5723: AND to_organization_id = l_organization_id;
5895: -- Bug 2377796
5896: -- LPN lables are not getting printed for rejected LPNS
5897: --SELECT MIN(rti.interface_transaction_id)
5898: SELECT rti.interface_transaction_id
5899: FROM rcv_transactions_interface rti
5900: WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
5901: -- GROUP BY rti.lpn_id;
5902: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5903: BEGIN
6119:
6120: CURSOR c_po_source_lines IS
6121: SELECT rs.rcv_transaction_id
6122: FROM rcv_supply rs
6123: , rcv_transactions rt
6124: WHERE rs.item_id = p_item_id
6125: AND rs.po_header_id = p_po_header_id
6126: AND nvl(rs.po_release_id,-1) = nvl(p_po_release_id,nvl(rs.po_release_id,-1))
6127: AND nvl(rs.po_line_id,-1) = nvl(p_po_line_id, nvl(rs.po_line_id,-1))
6135: -- use this for receipts also --
6136: CURSOR c_intship_source_lines IS
6137: SELECT rs.rcv_transaction_id
6138: FROM rcv_supply rs
6139: , rcv_transactions rt
6140: WHERE rs.item_id = p_item_id
6141: AND rs.shipment_header_id = p_shipment_header_id
6142: AND rs.rcv_transaction_id = rt.transaction_id
6143: AND rs.supply_type_code = 'RECEIVING'
6146:
6147: CURSOR c_rma_source_lines IS
6148: SELECT rs.rcv_transaction_id
6149: FROM rcv_supply rs
6150: , rcv_transactions rt
6151: WHERE rs.item_id = p_item_id
6152: AND rs.oe_order_header_id = p_oe_order_header_id
6153: AND rs.rcv_transaction_id = rt.transaction_id
6154: AND rs.supply_type_code = 'RECEIVING'
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
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
6779: BEGIN
6780: SELECT 1
6781: INTO l_count
6782: FROM rcv_supply rs
6783: , rcv_transactions rt
6784: WHERE rs.item_id = p_item_id
6785: AND rs.item_revision IS NULL
6786: AND rs.po_header_id = p_source_id
6787: AND rs.rcv_transaction_id = rt.transaction_id
6797: BEGIN
6798: SELECT 1
6799: INTO l_count
6800: FROM rcv_supply rs
6801: , rcv_transactions rt
6802: WHERE rs.item_id = p_item_id
6803: AND rs.item_revision IS NULL
6804: AND rs.shipment_header_id = p_source_id
6805: AND rs.rcv_transaction_id = rt.transaction_id
6815: BEGIN
6816: SELECT 1
6817: INTO l_count
6818: FROM rcv_supply rs
6819: , rcv_transactions rt
6820: WHERE rs.item_id = p_item_id
6821: AND rs.item_revision IS NULL
6822: AND rs.oe_order_header_id = p_source_id
6823: AND rs.rcv_transaction_id = rt.transaction_id