[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: )
572: and (mp.trading_partner_org_flag is NULL OR mp.trading_partner_org_flag = 'N')
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
578: Bug 4670527 */
579: delete rcv_staging_table
580: where transaction_id IN
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=
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;
1218: l_post_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.
1472: -- created later.
1473: -- skolluku
1474: --
1475: SELECT open_flag INTO l_open_flag
1476: FROM oe_order_lines_all
1477: WHERE line_id = l_replen_so_line_id;
1478:
1479: IF l_open_flag = 'N' THEN
1480: l_return_status := po_actions.close_po(p_docid => l_replen_po_header_id,