[Home] [Help]
2484: l_api_name CONSTANT VARCHAR2(30) := 'Process_IO_Arrival_Txn';
2485: l_error VARCHAR2(600);
2486:
2487: --Fix bug 3364811, move order lines and delivery table out of
2488: --io_arrival_crsr so that no duplicate rcv transactions picked
2489: --in outer loop.
2490: --This new cursor is created to fetch shipping org etc info
2491: /*bug 8548641 added new cursor Picking_line_id to fetch picking_line_detail_id and changed
2492: shipping_org cursor to fetch only shipping_organization_id on the basis of picking_line_detail_id
2520: , wdd.delivery_detail_id picking_line_detail_id
2521: FROM
2522: oe_order_lines_all oola
2523: , wsh_delivery_details_ob_grp_v wdd
2524: , rcv_transactions rcv
2525: , rcv_shipment_lines rsl
2526: , mtl_material_transactions mmt
2527: WHERE oola.order_source_id = 10 --combine 1st and 2nd condition to use index 11
2528: AND oola.orig_sys_document_ref = l_req_number
2929: mms.movement_id
2930: , mms.movement_status
2931: FROM
2932: mtl_movement_statistics mms
2933: , rcv_transactions rt
2934: WHERE mms.rcv_transaction_id = rt.parent_transaction_id
2935: AND rt.transaction_id = x_mvt_rtv_transaction.rcv_transaction_id
2936: AND mms.entity_org_id = x_mvt_rtv_transaction.entity_org_id
2937: AND mms.zone_code = x_mvt_rtv_transaction.zone_code
3472:
3473: --Bugfix 13556992: New cursor
3474: cursor l_subinventory is
3475: select subinventory
3476: from rcv_transactions
3477: where po_header_id = l_movement_transaction.po_header_id
3478: and po_line_id = l_movement_transaction.po_line_id
3479: and po_line_location_id = l_movement_transaction.po_line_location_id
3480: and shipment_header_id = l_shipment_header_id
4896:
4897: l_api_name CONSTANT VARCHAR2(30) := 'Process_RMA_Transaction';
4898: l_error VARCHAR2(600);
4899: l_sold_from_org_code VARCHAR2(2); -- 7165989
4900: l_mvt_stat_status RCV_TRANSACTIONS.mvt_stat_status%TYPE; -- 7165989
4901: l_dispatch mtl_movement_statistics.dispatch_territory_code%TYPE;-- 7165989
4902: l_destination mtl_movement_statistics.destination_territory_code%TYPE;-- 7165989
4903: l_insert VARCHAR2(1);-- 7165989
4904: l_ship_from_loc VARCHAR2(10);-- 7165989
4920: , G_MODULE_NAME || l_api_name || '.begin'
4921: ,'enter procedure'
4922: );
4923: END IF;
4924: l_mvt_stat_status := NULL ; -- 7165989 : initialize the mvt_stat_status in rcv_transactions
4925: x_return_status := FND_API.G_RET_STS_SUCCESS;
4926: l_movement_transaction := p_movement_transaction;
4927:
4928: -- Call the transaction proxy which processes all the transactions.
5219: --Initialize insert flag
5220: l_insert_flag := 'N';
5221:
5222: --Fix bug3057775. Initialize process flag, only when this flag is 'Y'
5223: --the mvt_stat_status in rcv_transactions will be upgrade to 'PROCESSED'
5224: --otherwise the status remians to 'NEW', so that it will be picked up again
5225: --when run processor in the other legal entity
5226: --Fix bug 5453241 do not initialize here. This will cause status reset
5227: --to 'N' after successfully create a record in first loop. If the
5235: THEN
5236: --Processor is run at the legal entity where the RMA is created
5237: IF l_rma_le_id = l_movement_transaction.entity_org_id
5238: THEN
5239: /* 7165989 - check for mvt_stat_status in RCV_TRANSACTIONS before creating triangulation records */
5240: /* Create records only when the status is 'NEW' or 'FORDISP'(record already created in the receiving LE)*/
5241: IF ((NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') = 'INVOICE_BASED')
5242: AND (l_mvt_stat_status is NOT NULL )
5243: AND (l_mvt_stat_status = 'NEW' OR l_mvt_stat_status = 'FORDISP')
5271: , p_stat_typ_transaction => l_stat_typ_transaction
5272: , x_return_status => x_return_status
5273: );
5274:
5275: /* 7165989 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
5276: /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
5277: /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
5278: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5279: IF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'NEW') THEN
5380: ( p_movement_transaction => l_movement_transaction
5381: , p_stat_typ_transaction => l_stat_typ_transaction
5382: , x_return_status => x_return_status
5383: );
5384: /* 6732517 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
5385: /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
5386: /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
5387:
5388:
5460: END IF;
5461:
5462: /********************** Process RMA Triangulation at receiving LE- 7165989 - Start ****************************/
5463:
5464: /* 7165989 - check for mvt_stat_status in RCV_TRANSACTIONS before creating triangulation records */
5465: /* Create records only when the status is 'NEW' or 'ARVL(record already created in the selling LE)*/
5466:
5467: IF ((NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') = 'INVOICE_BASED')
5468: AND (l_mvt_stat_status is NOT NULL )
5513: ':mvt_status:' || l_mvt_stat_status
5514: );
5515: END IF;
5516: END IF;
5517: /* 6732517 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
5518: /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
5519: /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
5520:
5521: IF (l_insert_flag = 'Y') THEN
6152: SELECT
6153: SUM(quantity)
6154: , SUM(primary_quantity)
6155: FROM
6156: rcv_transactions
6157: WHERE parent_transaction_id = l_movement_transaction.rcv_transaction_id
6158: AND mvt_stat_status = 'NEW'
6159: AND transaction_type = 'CORRECT';
6160: BEGIN
6416: );
6417:
6418: IF l_return_status = FND_API.G_RET_STS_SUCCESS
6419: THEN
6420: UPDATE rcv_transactions
6421: SET
6422: mvt_stat_status = 'PROCESSED'
6423: , movement_id = l_movement_transaction.movement_id
6424: WHERE mvt_stat_status = 'NEW'