[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
4225: , l_rti_inspection_status_code
4226: , l_requisition_line_id
4227: , l_po_header_id
4228: , l_reason_id -- Bug No.9099299
4229: FROM rcv_transactions
4230: WHERE interface_transaction_id = p_rti_id;
4231:
4232: EXCEPTION
4233: WHEN no_data_found THEN
4271: , l_from_sub
4272: , l_from_loc_id
4273: , l_pt_inspection_status_code
4274: , l_pt_routing_id
4275: FROM rcv_transactions
4276: WHERE transaction_id = l_parent_transaction_id;
4277: EXCEPTION
4278: WHEN no_data_found THEN
4279: l_from_sub := NULL; --Review I think no data found in this
4297: INTO l_great_grand_parent_txn_id
4298: , l_grand_parent_txn_type
4299: , l_grand_pt_insp_status_code
4300: , l_grand_pt_routing_id
4301: FROM rcv_transactions
4302: WHERE transaction_id = l_grand_parent_txn_id;
4303: EXCEPTION
4304: WHEN no_data_found THEN
4305: l_grand_parent_txn_type := '';
4317: select inspection_status_code
4318: ,routing_header_id
4319: into l_grt_gr_parent_insp_stat_code
4320: ,l_grt_gr_routing_id
4321: from rcv_transactions
4322: where transaction_id = l_great_grand_parent_txn_id;
4323: EXCEPTION
4324: WHEN no_data_found THEN
4325: l_grt_gr_parent_insp_stat_code := null;
4342: INTO l_grand_parent_txn_id
4343: , l_parent_txn_type
4344: , l_from_sub
4345: , l_from_loc_id
4346: FROM rcv_transactions
4347: WHERE transaction_id = l_parent_transaction_id;
4348: EXCEPTION
4349: WHEN no_data_found THEN
4350: l_from_sub := NULL;
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:
8174: into l_parent_txn_type,
8175: l_parent_parent_txn_id,
8176: l_parent_project_id,
8177: l_parent_task_id
8178: from rcv_transactions rt
8179: where rt.transaction_id = l_rti_rec.parent_transaction_id;
8180: Exception
8181: When others then
8182: -- Review Later
8198: IF l_rti_rec.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND
8199: l_parent_txn_type = 'DELIVER' THEN
8200: SELECT 'Y'
8201: INTO l_sec_txn_for_rtv
8202: FROM rcv_transactions
8203: WHERE transaction_type = 'RETURN TO RECEIVING'
8204: AND interface_transaction_id = p_txn_id
8205: AND exists (SELECT 1
8206: FROM rcv_transactions
8202: FROM rcv_transactions
8203: WHERE transaction_type = 'RETURN TO RECEIVING'
8204: AND interface_transaction_id = p_txn_id
8205: AND exists (SELECT 1
8206: FROM rcv_transactions
8207: WHERE transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
8208: AND interface_transaction_id = p_txn_id);
8209: END IF;
8210: EXCEPTION
8227: -- Get The grand Parent Txn Details
8228: Begin
8229: select transaction_type
8230: into l_parent_parent_txn_type
8231: from rcv_transactions rt
8232: where rt.transaction_id = l_parent_parent_txn_id;
8233: Exception
8234: When others then
8235: -- Review Later
10234: Nvl(l_rti_rec.auto_transact_code, '@#$#$@') = 'DELIVER') THEN
10235: BEGIN
10236: SELECT requisition_line_id
10237: INTO l_requisition_line_id
10238: FROM rcv_transactions
10239: WHERE interface_transaction_id = p_txn_id;
10240: EXCEPTION
10241: WHEN OTHERS THEN
10242: IF (l_debug = 1) THEN
10922:
10923: l_num_serial_inserted NUMBER;
10924:
10925: --< R12 MOAC>
10926: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10927: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10928: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10929: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10930:
10923: l_num_serial_inserted NUMBER;
10924:
10925: --< R12 MOAC>
10926: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10927: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10928: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10929: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10930:
10931:
10924:
10925: --< R12 MOAC>
10926: l_po_header_id RCV_TRANSACTIONS.PO_HEADER_ID%TYPE;
10927: l_req_line_id RCV_TRANSACTIONS.REQUISITION_LINE_ID%TYPE;
10928: l_oe_order_header_id RCV_TRANSACTIONS.OE_ORDER_HEADER_ID%TYPE;
10929: l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
10930:
10931:
10932: BEGIN
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';
11001: --get the receipt_source_code and source_document_code
11002: IF (p_txn_source_id IS NOT NULL) THEN
11003:
11004: /* R12 MOAC : Fetch the po_header_id, requisition_line_id, oe_order_header_id from
11005: ** rcv_transactions and derive the operating unit id
11006: */
11007:
11008: BEGIN
11009: SELECT rt.source_document_code
11015: , l_receipt_source_code
11016: , l_po_header_id
11017: , l_req_line_id
11018: , l_oe_order_header_id
11019: FROM rcv_transactions rt
11020: , rcv_shipment_headers rsh
11021: WHERE rt.transaction_id = p_txn_source_id
11022: AND rt.shipment_header_id = rsh.shipment_header_id
11023: AND rt.organization_id = p_to_org_id
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
11710: , rt.project_id project_id
11711: , rt.task_id task_id
11712: FROM rcv_supply rs
11713: , rcv_lots_supply rls
11714: , rcv_transactions rt
11715: WHERE rs.lpn_id = v_lpn_id
11716: AND rs.rcv_transaction_id = rls.transaction_id (+)
11717: AND rs.supply_type_code = 'RECEIVING'
11718: AND rs.rcv_transaction_id = rt.transaction_id
11752: , rt.project_id project_id
11753: , rt.task_id task_id
11754: FROM rcv_supply rs
11755: , rcv_serials_supply rss
11756: , rcv_transactions rt
11757: WHERE rs.lpn_id = v_lpn_id
11758: AND rs.rcv_transaction_id = rss.transaction_id
11759: AND rs.supply_type_code = 'RECEIVING'
11760: AND rs.rcv_transaction_id = rt.transaction_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;
15322: -- Get The Parent Txn Details
15323: Begin
15324: select transaction_type
15325: into l_parent_txn_type
15326: from rcv_transactions rt
15327: where rt.transaction_id = l_lpn_rec.parent_transaction_id;
15328: Exception
15329: When others then
15330: -- Review Later
15337: -- May not be Needed for ALL CASES
15338: Begin
15339: select parent_transaction_id
15340: into l_parent_parent_txn_id
15341: from rcv_transactions rt
15342: where rt.transaction_id = l_lpn_rec.parent_transaction_id;
15343: Exception
15344: When others then
15345: null;
15349: -- May not be Needed for ALL CASES
15350: Begin
15351: select transaction_type
15352: into l_parent_parent_txn_type
15353: from rcv_transactions rt
15354: where rt.transaction_id = l_parent_parent_txn_id;
15355: Exception
15356: When others then
15357: null;
15361: -- May not be Needed for ALL CASES
15362: Begin
15363: select parent_transaction_id
15364: into l_parent_parent_parent_txn_id
15365: from rcv_transactions rt
15366: where rt.transaction_id = l_parent_parent_txn_id;
15367: Exception
15368: When others then
15369: null;
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
19387: if(l_source_document_code='PO' and (l_transaction_type in ('RETURN TO RECEIVING','RETURN TO VENDOR')))
19388: THEN
19389: select transaction_type
19390: into l_parent_transaction_type
19391: from rcv_transactions
19392: where transaction_id=l_parent_transaction_id;
19393:
19394: if( l_parent_transaction_type='DELIVER') THEN
19395: inv_serial_number_pub.is_serial_controlled(
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)
23958: SELECT transaction_type
23959: , parent_transaction_id
23960: INTO l_parent_txn_type
23961: , l_grand_parent_txn_id
23962: FROM rcv_transactions
23963: WHERE transaction_id = l_parent_transaction_id;
23964: EXCEPTION
23965: WHEN no_data_found THEN
23966: --raise an error
23979: SELECT transaction_type
23980: , parent_transaction_id
23981: INTO l_grand_parent_txn_type
23982: , l_great_grand_parent_txn_id
23983: FROM rcv_transactions
23984: WHERE transaction_id = l_grand_parent_txn_id;
23985: EXCEPTION
23986: WHEN no_data_found THEN
23987: --raise an error
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'