[Home] [Help]
135: WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
136: AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
137: UNION
138: SELECT 1
139: FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
140: WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
141: AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
142: AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
143: )
159: WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
160: AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
161: UNION
162: SELECT 1
163: FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
164: WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
165: AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
166: AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
167: )
182: WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
183: AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
184: UNION
185: SELECT 1
186: FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
187: WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
188: AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
189: AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
190: )
204: WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
205: AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
206: UNION
207: SELECT 1
208: FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
209: WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
210: AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
211: AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
212: )
573: and rst.status = 'PENDING'
574: and rst.team = g_team_name);
575: /* vmutyala added the following query to delete the 'RMA' records which are not processed and
576: a corresponding return reference id doesn't exist in OE_ORDER_LINES_ALL or even if it exists, a corresponding
577: replenishent so line id doesn't exist in JMF_SHIKYU_REPLENISHMENTS.
578: Bug 4670527 */
579: delete rcv_staging_table
580: where transaction_id IN
581: (select distinct(rst.transaction_id)
581: (select distinct(rst.transaction_id)
582: from OE_ORDER_LINES_ALL OOLA, rcv_transactions rt, rcv_staging_table rst
583: where rt.SOURCE_DOCUMENT_CODE = 'RMA'
584: and rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
585: and (OOLA.REFERENCE_LINE_ID is NULL OR NOT EXISTS (select 1 from JMF_SHIKYU_REPLENISHMENTS
586: where REPLENISHMENT_SO_LINE_ID=
587: OOLA.REFERENCE_LINE_ID))
588: and rt.transaction_id = rst.transaction_id
589: and rst.status = 'PENDING'
1209: l_group_id NUMBER;
1210: l_header_interface_id NUMBER;
1211: l_allocable_primary_uom_qty NUMBER;
1212: l_shipped_primary_uom_qty NUMBER;
1213: l_shikyu_primary_uom JMF_SHIKYU_REPLENISHMENTS.PRIMARY_UOM%Type;
1214: l_shipped_qty_uom OE_ORDER_LINES_ALL.ORDER_QUANTITY_UOM%Type;
1215: l_shikyu_unit_of_measure MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%Type;
1216:
1217: l_pre_qty_received NUMBER;
1265: -- get the replenishment SO line id referenced by the rma line
1266:
1267: SELECT JSR.REPLENISHMENT_SO_LINE_ID
1268: INTO l_replen_so_line_id
1269: FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
1270: WHERE OOLA.LINE_ID = p_rma_line_id
1271: AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID;
1272:
1273:
1281: into l_replen_po_header_id, l_replen_po_line_id, l_replen_po_shipment_id,
1282: l_shikyu_component_id, l_tp_organization_id,
1283: l_shikyu_primary_uom, l_allocable_primary_uom_qty,
1284: l_shipped_primary_uom_qty, l_shipped_qty_uom
1285: from JMF_SHIKYU_REPLENISHMENTS jsr, OE_ORDER_LINES_ALL oola
1286: where jsr.REPLENISHMENT_SO_LINE_ID = l_replen_so_line_id
1287: and jsr.REPLENISHMENT_SO_LINE_ID = oola.line_id;
1288:
1289: -- If there is no return quantity against Replenishment SO then return.