[Home] [Help]
606: ,msnt.cycles_since_mark cycles_since_mark
607: ,msnt.number_of_repairs number_of_repairs
608: from
609: mtl_serial_numbers_temp msnt
610: ,rcv_transactions_interface rti
611: where
612: msnt.product_code = 'RCV'
613: AND msnt.product_transaction_id = p_prod_txn_tmp_id
614: AND rti.interface_transaction_id = msnt.product_transaction_id;
850: ,mtlt.attribute14
851: ,mtlt.attribute15
852: FROM
853: mtl_transaction_lots_temp mtlt
854: ,rcv_transactions_interface rti
855: WHERE
856: mtlt.product_transaction_id = p_prod_txn_tmp_id AND
857: mtlt.product_code = 'RCV' AND
858: rti.interface_transaction_id = p_prod_txn_tmp_id;
2151: ,quantity
2152: ,primary_quantity
2153: ,document_num
2154: FROM
2155: rcv_transactions_interface
2156: WHERE
2157: group_id = l_grp_id AND
2158: transaction_type = 'SHIP';
2159:
2530:
2531: l_progress := 'WMSINB-11917';
2532:
2533: BEGIN
2534: UPDATE rcv_transactions_interface
2535: SET lpn_id = l_wlpnci_rec.lpn_id,
2536: license_plate_number = l_wlpnci_rec.license_plate_number
2537: WHERE interface_transaction_id = l_rti_rec.interface_transaction_id;
2538: EXCEPTION
2848: /*************************** We do not need to do this check as PO will*
2849: *********************** update the lpn_group_id for all RTIs with LPNs*
2850: for l_error_row in ( select interface_transaction_id,
2851: group_id
2852: from rcv_transactions_interface
2853: where lpn_group_id is null
2854: and ( lpn_id is not null
2855: or license_plate_number is not null
2856: or transfer_lpn_id is not null
2881: -- See if there are any rtis which require explosion but have null
2882: -- lpn_group_id and update the lpn_group_id for such cases
2883:
2884: FOR l_lpn_grp_null IN (SELECT interface_transaction_id
2885: FROM rcv_transactions_interface
2886: WHERE Nvl(item_id, -1) = -1
2887: AND item_num IS NULL
2888: AND item_description IS NULL
2889: AND lpn_group_id IS NULL
2902: SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
2903: INTO l_lpn_grp_id
2904: FROM DUAL;
2905:
2906: UPDATE rcv_transactions_interface
2907: SET lpn_group_id = l_lpn_grp_id
2908: WHERE interface_transaction_id = l_lpn_grp_null.interface_transaction_id;
2909:
2910: END LOOP;
2910: END LOOP;
2911:
2912: -- Following will be removed after po gives us the patch for corrections/returns.
2913: /************************************************************************/
2914: --UPDATE rcv_transactions_interface SET lpn_group_id = group_id
2915: --WHERE lpn_group_id IS NULL
2916: --AND (lpn_id IS NOT NULL
2917: -- OR license_plate_number IS NOT NULL
2918: -- OR transfer_lpn_id IS NOT NULL
2925: /***********************************************************************/
2926: l_progress := 'WMSINB-12314';
2927:
2928: FOR l_rti_rec IN ( SELECT DISTINCT lpn_group_id, group_id
2929: FROM rcv_transactions_interface rti
2930: WHERE lpn_group_id IS NOT NULL
2931: AND Nvl(item_id, -1) = -1
2932: AND item_num IS NULL
2933: AND item_description IS NULL
2957: --FOR l_asn_lpn_grp_null IN (SELECT DISTINCT shipment_num
2958:
2959: FOR l_asn_lpn_grp_null IN (SELECT DISTINCT header_interface_id,
2960: shipment_num
2961: FROM rcv_transactions_interface rti
2962: WHERE rti.lpn_group_id IS NULL
2963: AND ( ( (rti.lpn_id IS NOT NULL
2964: OR rti.license_plate_number IS NOT NULL)
2965: ) or
2985: SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
2986: INTO l_lpn_grp_id
2987: FROM DUAL;
2988:
2989: UPDATE rcv_transactions_interface
2990: SET lpn_group_id = l_lpn_grp_id
2991: -- don't use shipment_num see update above ****
2992: -- WHERE shipment_num = l_asn_lpn_grp_null.shipment_num
2993: WHERE header_interface_id = l_asn_lpn_grp_null.header_interface_id
3001: END LOOP;
3002:
3003: -- This PART Below is for ASN creation only
3004: FOR l_asn_rec IN ( SELECT DISTINCT group_id,processing_mode_code
3005: FROM rcv_transactions_interface rti
3006: WHERE processing_status_code = 'RUNNING'
3007: AND (processing_request_id IS NULL
3008: OR processing_request_id = p_request_id)
3009: AND transaction_type = 'SHIP'
3027: x_group_id => l_asn_rec.group_id,
3028: x_lpn_group_id => NULL);
3029: ELSE
3030: FOR l_err_row IN (SELECT interface_transaction_id
3031: FROM rcv_transactions_interface
3032: WHERE group_id = l_asn_rec.group_id) LOOP
3033: -- Call PO's API to mark the Error
3034: rcv_roi_preprocessor.explode_lpn_failed
3035: (x_interface_txn_id => l_err_row.interface_transaction_id,
3042:
3043: -- Delete the Original row for Explosion
3044: -- Bug 3618348. The code below is changed from performance reasons
3045: /*
3046: DELETE FROM rcv_transactions_interface
3047: WHERE Nvl(item_id, -1) = -1
3048: AND item_description IS NULL
3049: AND Nvl(quantity,0) = 0
3050: AND lpn_group_id IS NOT NULL
3053: OR processing_request_id = p_request_id)
3054: AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id);
3055: */
3056: if nvl(p_group_id,0) = 0 then
3057: DELETE FROM rcv_transactions_interface
3058: WHERE Nvl(item_id, -1) = -1
3059: AND item_description IS NULL
3060: AND Nvl(quantity,0) = 0
3061: AND lpn_group_id IS NOT NULL
3064: and ( lpn_id is not null or license_plate_number is not null )
3065: AND (processing_request_id IS NULL
3066: OR processing_request_id = p_request_id) ;
3067: Else
3068: DELETE FROM rcv_transactions_interface
3069: WHERE Nvl(item_id, -1) = -1
3070: AND item_description IS NULL
3071: AND Nvl(quantity,0) = 0
3072: AND lpn_group_id IS NOT NULL
3256: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3257: l_progress VARCHAR2(15) := '10';
3258: l_count NUMBER; --bug 5008139
3259: l_comms_nl_trackable_flag mtl_system_items.comms_nl_trackable_flag%type;--Bug6374074
3260: l_destination_type rcv_transactions_interface.destination_type_code%type;--Bug6374074
3261: l_clear_line_mark_id number;--Bug6374074( if l_clear_line_mark_id = 1 then clear the line_mark_id field
3262: -- else preserve the line_mark_id field
3263:
3264:
3314: --Check whether this transaction is for EXPENSE destination
3315: --Then we have to preserve the line_mark_id and lot_line_mark_id
3316: SELECT nvl(destination_type_code,'dummy')
3317: INTO l_destination_type
3318: FROM rcv_transactions_interface
3319: WHERE interface_transaction_id = p_product_txn_id;
3320:
3321: IF l_destination_type = 'EXPENSE' THEN
3322: --Don't clear the line_mark_id and lot_line_mark_id
7991: ,EXPRESS_TRANSACTION --for bug 14235799
7992: -- Bug# 7154105
7993: ,req_distribution_id
7994: ,lpn_group_id --14408061
7995: from rcv_transactions_interface rti
7996: where rti.interface_transaction_id = p_txn_id;
7997:
7998: l_rti_rec c_rti%rowtype;
7999:
10986: END IF;
10987:
10988: l_progress := 'WMSINB-15850';
10989:
10990: SELECT rcv_transactions_interface_s.NEXTVAL
10991: INTO l_interface_transaction_id
10992: FROM DUAL;
10993:
10994: l_progress := 'WMSINB-15856';
11240: transfer_cost_group_id ,
11241: secondary_quantity ,
11242: secondary_unit_of_measure ,
11243: org_id --
11244: from rcv_transactions_interface rti
11245: where rti.interface_transaction_id = p_interface_transaction_id )
11246: Loop
11247: -- Insert the row
11248: IF (l_debug = 1) THEN
11447: -- This part may not be nneded when license_plate_numbver, transfer_license_plate_number
11448: -- is inserted by insert_row itself.
11449: -- added update to validation_flag
11450: -- *******************
11451: update rcv_transactions_interface
11452: set transfer_license_plate_number = p_transfer_lpn,
11453: validation_flag = 'Y',
11454: project_id = p_project_id,
11455: task_id = p_task_id
12872: ,shipment_header_id
12873: ,routing_header_id
12874: ,to_organization_id
12875: ,from_organization_id
12876: from rcv_transactions_interface rti
12877: where rti.lpn_group_id = p_lpn_group_id
12878: and ( Nvl(rti.item_id,-1) = -1 AND rti.item_description is null)
12879: -- Bug 3714354
12880: and ( lpn_id is not null or license_plate_number is not null )
12976: END If;
12977:
12978: select count(distinct decode(rti.TRANSACTION_TYPE,'ACCEPT','INSPECT TXN','REJECT','INSPECT TXN',rti.TRANSACTION_TYPE))
12979: into l_other_txn_exists
12980: from rcv_transactions_interface rti
12981: where rti.lpn_group_id = p_lpn_group_id
12982: -- Bug 3714354
12983: and ( lpn_id is not null or license_plate_number is not null )
12984: ;
13560: -- NOT YET CONVERTED UOM
13561:
13562: select sum(primary_quantity)
13563: into l_rti_qty
13564: from rcv_transactions_interface rti
13565: where rti.lpn_group_id = p_lpn_group_id
13566: and (rti.lpn_id = l_lpn_contents.lpn_id
13567: AND rti.PROCESSING_STATUS_CODE <> 'WSH_INTERFACED' --RTV Change 16197273
13568: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13661: -- NOT YET CONVERTED UOM
13662: IF l_lot_code = 1 THEN --15959206
13663: select sum(primary_quantity)
13664: into l_rti_qty
13665: from rcv_transactions_interface rti
13666: where rti.lpn_group_id = p_lpn_group_id
13667: and (rti.lpn_id = l_lpn_contents.lpn_id
13668: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13669: -- 3397823
13676: AND l_lpn_contents.lot_number IS NULL;
13677: ELSE --IF l_lpn_contents.lot_number IS NULL THEN
13678: select sum(mtli.primary_quantity)
13679: into l_rti_qty
13680: from rcv_transactions_interface rti
13681: , mtl_transaction_lots_interface mtli
13682: where rti.lpn_group_id = p_lpn_group_id
13683: and (rti.lpn_id = l_lpn_contents.lpn_id
13684: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13738: BEGIN
13739: SELECT 1
13740: INTO l_serial_disc
13741: FROM mtl_serial_numbers_interface msni
13742: , rcv_transactions_interface rti
13743: where rti.lpn_group_id = p_lpn_group_id
13744: and (rti.lpn_id = l_lpn_contents.lpn_id
13745: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13746: -- 3397823
13826: ,item_revision -- bug 13938607
13827: ,uom_code
13828: ,validation_flag
13829: ,interface_source_line_id --RTV Change 16197273
13830: from rcv_transactions_interface rti
13831: where rti.lpn_group_id = p_lpn_group_id
13832: AND PROCESSING_STATUS_CODE <> 'WSH_INTERFACED' --RTV Change 16197273
13833: order by interface_transaction_id;
13834:
14262: l_lpn_already_processed := 0;
14263:
14264: select count(distinct interface_transaction_id)
14265: into l_lpn_already_processed
14266: from rcv_transactions_interface rti
14267: where rti.lpn_group_id = p_lpn_group_id
14268: and ( ( rti.lpn_id = l_lpn_rec.lpn_id) or (rti.license_plate_number = l_lpn_rec.license_plate_number))
14269: and rti.interface_transaction_id < l_lpn_rec.interface_transaction_id ;
14270:
14502: -- DON't need to validate context for DELIVER TXN because it
14503: -- might have update the lpn context.
14504:
14505: select count(*) into l_lpn_Count
14506: from rcv_transactions_interface
14507: where lpn_id = l_lpn_rec.lpn_id
14508: and transaction_type ='DELIVER'
14509: and interface_transaction_id < l_lpn_rec.interface_transaction_id
14510: and lpn_group_id = p_lpn_group_id;
16407: end if;
16408:
16409:
16410: IF (l_update_lpn_id = TRUE) THEN
16411: UPDATE rcv_transactions_interface
16412: SET lpn_id = l_lpn_rec.lpn_id
16413: , transfer_lpn_id = l_lpn_rec.transfer_lpn_id
16414: WHERE interface_transaction_id = l_lpn_rec.interface_transaction_id;
16415: END IF;
16509: l_parent_to_organization_id,
16510: l_parent_source_header_id,
16511: l_parent_source_name,
16512: l_parent_txn_type
16513: FROM rcv_transactions_interface rti
16514: WHERE rti.lpn_id = rti.transfer_lpn_id
16515: AND rti.lpn_group_id = p_lpn_group_id
16516: AND rti.lpn_id IN (SELECT lpn_id
16517: FROM wms_license_plate_numbers wlpn2
17305: into l_uom_code,
17306: l_unit_of_measure,
17307: l_item_id,
17308: l_to_organization_id
17309: from rcv_transactions_interface rti
17310: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
17311:
17312: if l_uom_code is null then
17313: BEGIN
17421: into l_uom_code,
17422: l_unit_of_measure,
17423: l_item_id,
17424: l_to_organization_id
17425: from rcv_transactions_interface rti
17426: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
17427:
17428: if l_uom_code is null then
17429: BEGIN
17604: into l_uom_code,
17605: l_unit_of_measure,
17606: l_item_id,
17607: l_to_organization_id
17608: from rcv_transactions_interface rti
17609: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
17610:
17611: if l_uom_code is null then
17612: BEGIN
17645: BEGIN
17646:
17647: select shipment_line_id
17648: into l_shipment_line_id
17649: from rcv_transactions_interface
17650: where interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
17651: EXCEPTION
17652: WHEN OTHERS THEN
17653: l_shipment_line_id := null;
17935: BEGIN
17936:
17937: select shipment_line_id
17938: into l_shipment_line_id
17939: from rcv_transactions_interface
17940: where interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
17941: EXCEPTION
17942: WHEN OTHERS THEN
17943: l_shipment_line_id := null;
18208: x_return_status := g_ret_sts_success;
18209: --Bug 9458159 The MTLT primary quantity should be inserted as the corresponding one for transaction quantity in primary uom.
18210: SELECT item_id,to_organization_id,uom_code,unit_of_measure
18211: INTO l_item_id,l_org_id,l_uom_code,l_unit_of_measure
18212: FROM RCV_TRANSACTIONS_INTERFACE
18213: WHERE interface_transaction_id=p_rti_id;
18214:
18215: IF(l_uom_code is null) then
18216: BEGIN
18483: */
18484: /* BEGIN
18485: SELECT msi.serial_number_control_code
18486: INTO x_serial_number_control_code
18487: FROM mtl_system_items msi, rcv_transactions_interface rti
18488: WHERE msi.inventory_item_id = rti.item_id
18489: AND msi.organization_id = rti.to_organization_id
18490: AND rti.interface_transaction_id = p_rti_id;
18491: EXCEPTION
19222: /* end INVCONV */
19223: , l_lpn_grp_id
19224: , l_from_subinventory -- Bug 14269152
19225: , l_from_locator_id -- Bug 14269152
19226: FROM RCV_TRANSACTIONS_INTERFACE
19227: WHERE INTERFACE_TRANSACTION_ID = L_RTI_ID;
19228:
19229: IF l_shipment_line_id IS NOT NULL THEN
19230: -- THIS IS CHANGED TO TAKE INTO ACCOUNT ASN_TYPE
19777: INTO l_dummy
19778: FROM dual
19779: WHERE EXISTS (SELECT 'comingling exists'
19780: from mtl_onhand_quantities_detail moqd,
19781: rcv_transactions_interface rti
19782: WHERE moqd.organization_id = rti.to_organization_id
19783: AND moqd.inventory_item_id = rti.item_id
19784: and moqd.subinventory_code = rti.subinventory
19785: and nvl(moqd.locator_id, -9999) = nvl(rti.locator_id, -9999)
29419: CURSOR c_rti_cursor IS
29420: SELECT SUM(rti.primary_quantity) primary_quantity,
29421: SUM(rti.quantity) quantity,
29422: rti.unit_of_measure
29423: FROM rcv_transactions_interface rti
29424: WHERE rti.lpn_id = p_lpn_id
29425: AND rti.item_id = p_item_id
29426: AND rti.to_organization_id = p_organization_id
29427: AND(rti.transaction_status_code = 'PENDING'