[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
4153: , l_source_document_code
4154: , l_rti_inspection_status_code
4155: , l_requisition_line_id
4156: , l_po_header_id
4157: FROM rcv_transactions
4158: WHERE interface_transaction_id = p_rti_id;
4159:
4160: EXCEPTION
4161: WHEN no_data_found THEN
4199: , l_from_sub
4200: , l_from_loc_id
4201: , l_pt_inspection_status_code
4202: , l_pt_routing_id
4203: FROM rcv_transactions
4204: WHERE transaction_id = l_parent_transaction_id;
4205: EXCEPTION
4206: WHEN no_data_found THEN
4207: l_from_sub := NULL; --Review I think no data found in this
4225: INTO l_great_grand_parent_txn_id
4226: , l_grand_parent_txn_type
4227: , l_grand_pt_insp_status_code
4228: , l_grand_pt_routing_id
4229: FROM rcv_transactions
4230: WHERE transaction_id = l_grand_parent_txn_id;
4231: EXCEPTION
4232: WHEN no_data_found THEN
4233: l_grand_parent_txn_type := '';
4245: select inspection_status_code
4246: ,routing_header_id
4247: into l_grt_gr_parent_insp_stat_code
4248: ,l_grt_gr_routing_id
4249: from rcv_transactions
4250: where transaction_id = l_great_grand_parent_txn_id;
4251: EXCEPTION
4252: WHEN no_data_found THEN
4253: l_grt_gr_parent_insp_stat_code := null;
4270: INTO l_grand_parent_txn_id
4271: , l_parent_txn_type
4272: , l_from_sub
4273: , l_from_loc_id
4274: FROM rcv_transactions
4275: WHERE transaction_id = l_parent_transaction_id;
4276: EXCEPTION
4277: WHEN no_data_found THEN
4278: l_from_sub := NULL;
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:
7864: into l_parent_txn_type,
7865: l_parent_parent_txn_id,
7866: l_parent_project_id,
7867: l_parent_task_id
7868: from rcv_transactions rt
7869: where rt.transaction_id = l_rti_rec.parent_transaction_id;
7870: Exception
7871: When others then
7872: -- Review Later
7888: IF l_rti_rec.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND
7889: l_parent_txn_type = 'DELIVER' THEN
7890: SELECT 'Y'
7891: INTO l_sec_txn_for_rtv
7892: FROM rcv_transactions
7893: WHERE transaction_type = 'RETURN TO RECEIVING'
7894: AND interface_transaction_id = p_txn_id
7895: AND exists (SELECT 1
7896: FROM rcv_transactions
7892: FROM rcv_transactions
7893: WHERE transaction_type = 'RETURN TO RECEIVING'
7894: AND interface_transaction_id = p_txn_id
7895: AND exists (SELECT 1
7896: FROM rcv_transactions
7897: WHERE transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
7898: AND interface_transaction_id = p_txn_id);
7899: END IF;
7900: EXCEPTION
7917: -- Get The grand Parent Txn Details
7918: Begin
7919: select transaction_type
7920: into l_parent_parent_txn_type
7921: from rcv_transactions rt
7922: where rt.transaction_id = l_parent_parent_txn_id;
7923: Exception
7924: When others then
7925: -- Review Later
9804: Nvl(l_rti_rec.auto_transact_code, '@#$#$@') = 'DELIVER') THEN
9805: BEGIN
9806: SELECT requisition_line_id
9807: INTO l_requisition_line_id
9808: FROM rcv_transactions
9809: WHERE interface_transaction_id = p_txn_id;
9810: EXCEPTION
9811: WHEN OTHERS THEN
9812: IF (l_debug = 1) THEN
10445:
10446: l_num_serial_inserted NUMBER;
10447:
10448: --< R12 MOAC>
10449: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10450: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10451: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10452: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10453:
10446: l_num_serial_inserted NUMBER;
10447:
10448: --< R12 MOAC>
10449: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10450: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10451: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10452: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10453:
10454:
10447:
10448: --< R12 MOAC>
10449: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10450: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10451: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10452: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10453:
10454:
10455: BEGIN
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';
10524: --get the receipt_source_code and source_document_code
10525: IF (p_txn_source_id IS NOT NULL) THEN
10526:
10527: /* R12 MOAC : Fetch the po_header_id, requisition_line_id, oe_order_header_id from
10528: ** rcv_transactions and derive the operating unit id
10529: */
10530:
10531: BEGIN
10532: SELECT rt.source_document_code
10538: , l_receipt_source_code
10539: , l_po_header_id
10540: , l_req_line_id
10541: , l_oe_order_header_id
10542: FROM rcv_transactions rt
10543: , rcv_shipment_headers rsh
10544: WHERE rt.transaction_id = p_txn_source_id
10545: AND rt.shipment_header_id = rsh.shipment_header_id
10546: AND rt.organization_id = p_to_org_id
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
11233: , rt.project_id project_id
11234: , rt.task_id task_id
11235: FROM rcv_supply rs
11236: , rcv_lots_supply rls
11237: , rcv_transactions rt
11238: WHERE rs.lpn_id = v_lpn_id
11239: AND rs.rcv_transaction_id = rls.transaction_id (+)
11240: AND rs.supply_type_code = 'RECEIVING'
11241: AND rs.rcv_transaction_id = rt.transaction_id
11275: , rt.project_id project_id
11276: , rt.task_id task_id
11277: FROM rcv_supply rs
11278: , rcv_serials_supply rss
11279: , rcv_transactions rt
11280: WHERE rs.lpn_id = v_lpn_id
11281: AND rs.rcv_transaction_id = rss.transaction_id
11282: AND rs.supply_type_code = 'RECEIVING'
11283: AND rs.rcv_transaction_id = rt.transaction_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;
14619: -- Get The Parent Txn Details
14620: Begin
14621: select transaction_type
14622: into l_parent_txn_type
14623: from rcv_transactions rt
14624: where rt.transaction_id = l_lpn_rec.parent_transaction_id;
14625: Exception
14626: When others then
14627: -- Review Later
14634: -- May not be Needed for ALL CASES
14635: Begin
14636: select parent_transaction_id
14637: into l_parent_parent_txn_id
14638: from rcv_transactions rt
14639: where rt.transaction_id = l_lpn_rec.parent_transaction_id;
14640: Exception
14641: When others then
14642: null;
14646: -- May not be Needed for ALL CASES
14647: Begin
14648: select transaction_type
14649: into l_parent_parent_txn_type
14650: from rcv_transactions rt
14651: where rt.transaction_id = l_parent_parent_txn_id;
14652: Exception
14653: When others then
14654: null;
14658: -- May not be Needed for ALL CASES
14659: Begin
14660: select parent_transaction_id
14661: into l_parent_parent_parent_txn_id
14662: from rcv_transactions rt
14663: where rt.transaction_id = l_parent_parent_txn_id;
14664: Exception
14665: When others then
14666: null;
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
22703: SELECT transaction_type
22704: , parent_transaction_id
22705: INTO l_parent_txn_type
22706: , l_grand_parent_txn_id
22707: FROM rcv_transactions
22708: WHERE transaction_id = l_parent_transaction_id;
22709: EXCEPTION
22710: WHEN no_data_found THEN
22711: --raise an error
22724: SELECT transaction_type
22725: , parent_transaction_id
22726: INTO l_grand_parent_txn_type
22727: , l_great_grand_parent_txn_id
22728: FROM rcv_transactions
22729: WHERE transaction_id = l_grand_parent_txn_id;
22730: EXCEPTION
22731: WHEN no_data_found THEN
22732: --raise an error