[Home] [Help]
582: ,msnt.cycles_since_mark cycles_since_mark
583: ,msnt.number_of_repairs number_of_repairs
584: from
585: mtl_serial_numbers_temp msnt
586: ,rcv_transactions_interface rti
587: where
588: msnt.product_code = 'RCV'
589: AND msnt.product_transaction_id = p_prod_txn_tmp_id
590: AND rti.interface_transaction_id = msnt.product_transaction_id;
826: ,mtlt.attribute14
827: ,mtlt.attribute15
828: FROM
829: mtl_transaction_lots_temp mtlt
830: ,rcv_transactions_interface rti
831: WHERE
832: mtlt.product_transaction_id = p_prod_txn_tmp_id AND
833: mtlt.product_code = 'RCV' AND
834: rti.interface_transaction_id = p_prod_txn_tmp_id;
2122: ,quantity
2123: ,primary_quantity
2124: ,document_num
2125: FROM
2126: rcv_transactions_interface
2127: WHERE
2128: group_id = l_grp_id AND
2129: transaction_type = 'SHIP';
2130:
2501:
2502: l_progress := 'WMSINB-11917';
2503:
2504: BEGIN
2505: UPDATE rcv_transactions_interface
2506: SET lpn_id = l_wlpnci_rec.lpn_id,
2507: license_plate_number = l_wlpnci_rec.license_plate_number
2508: WHERE interface_transaction_id = l_rti_rec.interface_transaction_id;
2509: EXCEPTION
2817: /*************************** We do not need to do this check as PO will*
2818: *********************** update the lpn_group_id for all RTIs with LPNs*
2819: for l_error_row in ( select interface_transaction_id,
2820: group_id
2821: from rcv_transactions_interface
2822: where lpn_group_id is null
2823: and ( lpn_id is not null
2824: or license_plate_number is not null
2825: or transfer_lpn_id is not null
2850: -- See if there are any rtis which require explosion but have null
2851: -- lpn_group_id and update the lpn_group_id for such cases
2852:
2853: FOR l_lpn_grp_null IN (SELECT interface_transaction_id
2854: FROM rcv_transactions_interface
2855: WHERE Nvl(item_id, -1) = -1
2856: AND item_num IS NULL
2857: AND item_description IS NULL
2858: AND lpn_group_id IS NULL
2871: SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
2872: INTO l_lpn_grp_id
2873: FROM DUAL;
2874:
2875: UPDATE rcv_transactions_interface
2876: SET lpn_group_id = l_lpn_grp_id
2877: WHERE interface_transaction_id = l_lpn_grp_null.interface_transaction_id;
2878:
2879: END LOOP;
2879: END LOOP;
2880:
2881: -- Following will be removed after po gives us the patch for corrections/returns.
2882: /************************************************************************/
2883: --UPDATE rcv_transactions_interface SET lpn_group_id = group_id
2884: --WHERE lpn_group_id IS NULL
2885: --AND (lpn_id IS NOT NULL
2886: -- OR license_plate_number IS NOT NULL
2887: -- OR transfer_lpn_id IS NOT NULL
2894: /***********************************************************************/
2895: l_progress := 'WMSINB-12314';
2896:
2897: FOR l_rti_rec IN ( SELECT DISTINCT lpn_group_id, group_id
2898: FROM rcv_transactions_interface rti
2899: WHERE lpn_group_id IS NOT NULL
2900: AND Nvl(item_id, -1) = -1
2901: AND item_num IS NULL
2902: AND item_description IS NULL
2926: --FOR l_asn_lpn_grp_null IN (SELECT DISTINCT shipment_num
2927:
2928: FOR l_asn_lpn_grp_null IN (SELECT DISTINCT header_interface_id,
2929: shipment_num
2930: FROM rcv_transactions_interface rti
2931: WHERE rti.lpn_group_id IS NULL
2932: AND ( ( (rti.lpn_id IS NOT NULL
2933: OR rti.license_plate_number IS NOT NULL)
2934: ) or
2954: SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
2955: INTO l_lpn_grp_id
2956: FROM DUAL;
2957:
2958: UPDATE rcv_transactions_interface
2959: SET lpn_group_id = l_lpn_grp_id
2960: -- don't use shipment_num see update above ****
2961: -- WHERE shipment_num = l_asn_lpn_grp_null.shipment_num
2962: WHERE header_interface_id = l_asn_lpn_grp_null.header_interface_id
2970: END LOOP;
2971:
2972: -- This PART Below is for ASN creation only
2973: FOR l_asn_rec IN ( SELECT DISTINCT group_id,processing_mode_code
2974: FROM rcv_transactions_interface rti
2975: WHERE processing_status_code = 'RUNNING'
2976: AND (processing_request_id IS NULL
2977: OR processing_request_id = p_request_id)
2978: AND transaction_type = 'SHIP'
2996: x_group_id => l_asn_rec.group_id,
2997: x_lpn_group_id => NULL);
2998: ELSE
2999: FOR l_err_row IN (SELECT interface_transaction_id
3000: FROM rcv_transactions_interface
3001: WHERE group_id = l_asn_rec.group_id) LOOP
3002: -- Call PO's API to mark the Error
3003: rcv_roi_preprocessor.explode_lpn_failed
3004: (x_interface_txn_id => l_err_row.interface_transaction_id,
3011:
3012: -- Delete the Original row for Explosion
3013: -- Bug 3618348. The code below is changed from performance reasons
3014: /*
3015: DELETE FROM rcv_transactions_interface
3016: WHERE Nvl(item_id, -1) = -1
3017: AND item_description IS NULL
3018: AND Nvl(quantity,0) = 0
3019: AND lpn_group_id IS NOT NULL
3022: OR processing_request_id = p_request_id)
3023: AND group_id = Decode(p_group_id,0,group_id,NULL,group_id,p_group_id);
3024: */
3025: if nvl(p_group_id,0) = 0 then
3026: DELETE FROM rcv_transactions_interface
3027: WHERE Nvl(item_id, -1) = -1
3028: AND item_description IS NULL
3029: AND Nvl(quantity,0) = 0
3030: AND lpn_group_id IS NOT NULL
3033: and ( lpn_id is not null or license_plate_number is not null )
3034: AND (processing_request_id IS NULL
3035: OR processing_request_id = p_request_id) ;
3036: Else
3037: DELETE FROM rcv_transactions_interface
3038: WHERE Nvl(item_id, -1) = -1
3039: AND item_description IS NULL
3040: AND Nvl(quantity,0) = 0
3041: AND lpn_group_id IS NOT NULL
3203: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3204: l_progress VARCHAR2(15) := '10';
3205: l_count NUMBER; --bug 5008139
3206: l_comms_nl_trackable_flag mtl_system_items.comms_nl_trackable_flag%type;--Bug6374074
3207: l_destination_type rcv_transactions_interface.destination_type_code%type;--Bug6374074
3208: l_clear_line_mark_id number;--Bug6374074( if l_clear_line_mark_id = 1 then clear the line_mark_id field
3209: -- else preserve the line_mark_id field
3210:
3211:
3261: --Check whether this transaction is for EXPENSE destination
3262: --Then we have to preserve the line_mark_id and lot_line_mark_id
3263: SELECT nvl(destination_type_code,'dummy')
3264: INTO l_destination_type
3265: FROM rcv_transactions_interface
3266: WHERE interface_transaction_id = p_product_txn_id;
3267:
3268: IF l_destination_type = 'EXPENSE' THEN
3269: --Don't clear the line_mark_id and lot_line_mark_id
7684: -- OPMConvergence
7685: ,requisition_line_id
7686: -- Bug# 7154105
7687: ,req_distribution_id
7688: from rcv_transactions_interface rti
7689: where rti.interface_transaction_id = p_txn_id;
7690:
7691: l_rti_rec c_rti%rowtype;
7692:
10509: END IF;
10510:
10511: l_progress := 'WMSINB-15850';
10512:
10513: SELECT rcv_transactions_interface_s.NEXTVAL
10514: INTO l_interface_transaction_id
10515: FROM DUAL;
10516:
10517: l_progress := 'WMSINB-15856';
10763: transfer_cost_group_id ,
10764: secondary_quantity ,
10765: secondary_unit_of_measure ,
10766: org_id --
10767: from rcv_transactions_interface rti
10768: where rti.interface_transaction_id = p_interface_transaction_id )
10769: Loop
10770: -- Insert the row
10771: IF (l_debug = 1) THEN
10970: -- This part may not be nneded when license_plate_numbver, transfer_license_plate_number
10971: -- is inserted by insert_row itself.
10972: -- added update to validation_flag
10973: -- *******************
10974: update rcv_transactions_interface
10975: set transfer_license_plate_number = p_transfer_lpn,
10976: validation_flag = 'Y',
10977: project_id = p_project_id,
10978: task_id = p_task_id
12298: ,shipment_header_id
12299: ,routing_header_id
12300: ,to_organization_id
12301: ,from_organization_id
12302: from rcv_transactions_interface rti
12303: where rti.lpn_group_id = p_lpn_group_id
12304: and ( Nvl(rti.item_id,-1) = -1 AND rti.item_description is null)
12305: -- Bug 3714354
12306: and ( lpn_id is not null or license_plate_number is not null )
12402: END If;
12403:
12404: select count(distinct decode(rti.TRANSACTION_TYPE,'ACCEPT','INSPECT TXN','REJECT','INSPECT TXN',rti.TRANSACTION_TYPE))
12405: into l_other_txn_exists
12406: from rcv_transactions_interface rti
12407: where rti.lpn_group_id = p_lpn_group_id
12408: -- Bug 3714354
12409: and ( lpn_id is not null or license_plate_number is not null )
12410: ;
12985: -- NOT YET CONVERTED UOM
12986:
12987: select sum(primary_quantity)
12988: into l_rti_qty
12989: from rcv_transactions_interface rti
12990: where rti.lpn_group_id = p_lpn_group_id
12991: and (rti.lpn_id = l_lpn_contents.lpn_id
12992: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
12993: -- 3397823
13082: -- NOT YET CONVERTED UOM
13083: IF l_lpn_contents.lot_number IS NULL THEN
13084: select sum(primary_quantity)
13085: into l_rti_qty
13086: from rcv_transactions_interface rti
13087: where rti.lpn_group_id = p_lpn_group_id
13088: and (rti.lpn_id = l_lpn_contents.lpn_id
13089: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13090: -- 3397823
13097: AND l_lpn_contents.lot_number IS NULL;
13098: ELSE --IF l_lpn_contents.lot_number IS NULL THEN
13099: select sum(mtli.primary_quantity)
13100: into l_rti_qty
13101: from rcv_transactions_interface rti
13102: , mtl_transaction_lots_interface mtli
13103: where rti.lpn_group_id = p_lpn_group_id
13104: and (rti.lpn_id = l_lpn_contents.lpn_id
13105: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13159: BEGIN
13160: SELECT 1
13161: INTO l_serial_disc
13162: FROM mtl_serial_numbers_interface msni
13163: , rcv_transactions_interface rti
13164: where rti.lpn_group_id = p_lpn_group_id
13165: and (rti.lpn_id = l_lpn_contents.lpn_id
13166: OR rti.license_plate_number = l_lpn_contents.license_plate_number)
13167: -- 3397823
13243: ,processing_mode_code
13244: ,inv_transaction_id
13245: ,mmtt_temp_id
13246: ,Nvl(express_transaction,'N') express_transaction --Bug 5550783
13247: from rcv_transactions_interface rti
13248: where rti.lpn_group_id = p_lpn_group_id
13249: order by interface_transaction_id;
13250:
13251: cursor c_child_lpn(p_child_lpn_id varchar2) is
13571: l_lpn_already_processed := 0;
13572:
13573: select count(distinct interface_transaction_id)
13574: into l_lpn_already_processed
13575: from rcv_transactions_interface rti
13576: where rti.lpn_group_id = p_lpn_group_id
13577: and ( ( rti.lpn_id = l_lpn_rec.lpn_id) or (rti.license_plate_number = l_lpn_rec.license_plate_number))
13578: and rti.interface_transaction_id < l_lpn_rec.interface_transaction_id ;
13579:
13810: -- DON't need to validate context for DELIVER TXN because it
13811: -- might have update the lpn context.
13812:
13813: select count(*) into l_lpn_Count
13814: from rcv_transactions_interface
13815: where lpn_id = l_lpn_rec.lpn_id
13816: and transaction_type ='DELIVER'
13817: and interface_transaction_id < l_lpn_rec.interface_transaction_id
13818: and lpn_group_id = p_lpn_group_id;
15653: end if;
15654:
15655:
15656: IF (l_update_lpn_id = TRUE) THEN
15657: UPDATE rcv_transactions_interface
15658: SET lpn_id = l_lpn_rec.lpn_id
15659: , transfer_lpn_id = l_lpn_rec.transfer_lpn_id
15660: WHERE interface_transaction_id = l_lpn_rec.interface_transaction_id;
15661: END IF;
15755: l_parent_to_organization_id,
15756: l_parent_source_header_id,
15757: l_parent_source_name,
15758: l_parent_txn_type
15759: FROM rcv_transactions_interface rti
15760: WHERE rti.lpn_id = rti.transfer_lpn_id
15761: AND rti.lpn_group_id = p_lpn_group_id
15762: AND rti.lpn_id IN (SELECT lpn_id
15763: FROM wms_license_plate_numbers wlpn2
16520: into l_uom_code,
16521: l_unit_of_measure,
16522: l_item_id,
16523: l_to_organization_id
16524: from rcv_transactions_interface rti
16525: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
16526:
16527: if l_uom_code is null then
16528: BEGIN
16636: into l_uom_code,
16637: l_unit_of_measure,
16638: l_item_id,
16639: l_to_organization_id
16640: from rcv_transactions_interface rti
16641: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
16642:
16643: if l_uom_code is null then
16644: BEGIN
16817: into l_uom_code,
16818: l_unit_of_measure,
16819: l_item_id,
16820: l_to_organization_id
16821: from rcv_transactions_interface rti
16822: where rti.interface_transaction_id = p_rti_tb(newrti).new_interface_trx_id;
16823:
16824: if l_uom_code is null then
16825: BEGIN
18206: , l_rti_UNIT_OF_MEASURE
18207: , l_rti_SEC_UNIT_OF_MEASURE
18208: /* end INVCONV */
18209: , l_lpn_grp_id
18210: FROM RCV_TRANSACTIONS_INTERFACE
18211: WHERE INTERFACE_TRANSACTION_ID = L_RTI_ID;
18212:
18213: IF l_shipment_line_id IS NOT NULL THEN
18214: -- THIS IS CHANGED TO TAKE INTO ACCOUNT ASN_TYPE