[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
FROM rcv_transactions rt, rcv_staging_table rst
WHERE rst.transaction_id = rt.transaction_id
AND rst.transaction_request_id = p_request_id
AND rst.transaction_group_id = p_group_id
AND rst.team = g_team_name
AND EXISTS(
SELECT 1
FROM jmf_subcontract_orders jso
WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
UNION
SELECT 1
FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
)
ORDER BY rt.transaction_id;
SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
FROM rcv_transactions rt, rcv_staging_table rst
WHERE rst.transaction_id = rt.transaction_id
AND rst.transaction_request_id = p_request_id
AND rst.team = g_team_name
AND EXISTS(
SELECT 1
FROM jmf_subcontract_orders jso
WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
UNION
SELECT 1
FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
)
ORDER BY rt.transaction_id;
SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
FROM rcv_transactions rt, rcv_staging_table rst
WHERE rst.transaction_id = rt.transaction_id
AND rst.transaction_group_id = p_group_id
AND rst.team = g_team_name
AND EXISTS(
SELECT 1
FROM jmf_subcontract_orders jso
WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
UNION
SELECT 1
FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
)
ORDER BY rt.transaction_id;
SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
FROM rcv_transactions rt, rcv_staging_table rst
WHERE rst.transaction_id = rt.transaction_id
AND rst.team = g_team_name
AND EXISTS(
SELECT 1
FROM jmf_subcontract_orders jso
WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
UNION
SELECT 1
FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
)
ORDER BY rt.transaction_id;
select UOM_CODE
into l_primary_uom_code
from MTL_UNITS_OF_MEASURE_VL
where UNIT_OF_MEASURE = l_rcv_pending_trx_tbl(i).primary_unit_of_measure;
parent transactions are deleted from the staging table because of a successful run were inserted
into clubbed transactions but not processed later neither in osa receipt nor in return
because of conditions in if statements. The condition is that if transaction type is 'CORRECT'
and process type is one of 'RECEIPT' or 'RETURN' then process the transaction. Process type of
the above mentioned transactions was not being set in the commented code. Bug 4670527 */
IF NOT l_exists THEN
l_rcv_pending_clubbed_trx_tbl(l_rcv_pending_clubbed_trx_tbl.COUNT+1) :=
l_rcv_pending_trx_tbl(i);
select transaction_type
into l_parent_transaction_type
from rcv_transactions
where transaction_id =
(select PARENT_TRANSACTION_ID
from rcv_transactions
where transaction_id = l_rcv_pending_trx_tbl(i).transaction_id);
UPDATE rcv_staging_table
SET status = 'CL_ERROR'
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATED_BY = FND_GLOBAL.user_id
, LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE transaction_id = l_rcv_pending_trx_tbl(i).clubbed_transaction_id
AND team = g_team_name;
UPDATE rcv_staging_table
SET status = 'CL_ERROR'
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATED_BY = FND_GLOBAL.user_id
, LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE transaction_id = l_rcv_pending_trx_tbl(i).transaction_id
AND team = g_team_name;
UPDATE rcv_staging_table
SET status = l_rcv_pending_clubbed_trx_tbl(l_error_trx_index).error_status
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATED_BY = FND_GLOBAL.user_id
, LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE transaction_id = l_rcv_pending_clubbed_trx_tbl(l_error_trx_index).transaction_id
AND team = g_team_name;
/* vmutyala changed the following code to delete successful records which might have failed in previous attempts Bug 4670527*/
-- Delete successful transactions from staging table.
FORALL i IN 1 .. rcv_success_trx_ids.COUNT
DELETE rcv_staging_table
WHERE transaction_id = rcv_success_trx_ids(i)
AND team = g_team_name;
/* vmutyala added the following query to delete the 'PO' records which are not processed and whose supplier org is not a trading partner org
Bug 4670527 */
delete rcv_staging_table
where transaction_id IN
(select distinct(rst.transaction_id)
from hr_organization_information hoi, rcv_transactions rt, rcv_staging_table rst, mtl_parameters mp
where rt.SOURCE_DOCUMENT_CODE = 'PO'
and hoi.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
and hoi.ORG_INFORMATION3 = to_char(rt.vendor_id)
and hoi.ORG_INFORMATION4 = to_char(rt.vendor_site_id)
and rt.transaction_id = rst.transaction_id
and hoi.organization_id = mp.organization_id
and (mp.trading_partner_org_flag is NULL OR mp.trading_partner_org_flag = 'N')
and rst.status = 'PENDING'
and rst.team = g_team_name);
/* vmutyala added the following query to delete the 'RMA' records which are not processed and
a corresponding return reference id doesn't exist in OE_ORDER_LINES_ALL or even if it exists, a corresponding
replenishent so line id doesn't exist in JMF_SHIKYU_REPLENISHMENTS.
Bug 4670527 */
delete rcv_staging_table
where transaction_id IN
(select distinct(rst.transaction_id)
from OE_ORDER_LINES_ALL OOLA, rcv_transactions rt, rcv_staging_table rst
where rt.SOURCE_DOCUMENT_CODE = 'RMA'
and rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
and (OOLA.REFERENCE_LINE_ID is NULL OR NOT EXISTS (select 1 from JMF_SHIKYU_REPLENISHMENTS
where REPLENISHMENT_SO_LINE_ID=
OOLA.REFERENCE_LINE_ID))
and rt.transaction_id = rst.transaction_id
and rst.status = 'PENDING'
and rst.team = g_team_name);
select JSO.OSA_ITEM_ID, JMF_SHIKYU_UTIL.Get_Primary_Uom_Code(JSO.OSA_ITEM_ID, JSO.OEM_ORGANIZATION_ID)
into l_osa_item_id, l_osa_item_primary_uom
from JMF_SUBCONTRACT_ORDERS JSO
where JSO.SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
UPDATE JMF_SUBCONTRACT_ORDERS SET interlock_status = 'C'
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATED_BY = FND_GLOBAL.user_id
, LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
SELECT JSR.REPLENISHMENT_SO_LINE_ID
INTO l_replen_so_line_id
FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
WHERE OOLA.LINE_ID = p_rma_line_id
AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID;
select jsr.REPLENISHMENT_PO_HEADER_ID,jsr.REPLENISHMENT_PO_LINE_ID, jsr.REPLENISHMENT_PO_SHIPMENT_ID,
jsr.SHIKYU_COMPONENT_ID, jsr.TP_ORGANIZATION_ID, jsr.PRIMARY_UOM,
jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY, oola.SHIPPED_QUANTITY,
oola.ORDER_QUANTITY_UOM
into l_replen_po_header_id, l_replen_po_line_id, l_replen_po_shipment_id,
l_shikyu_component_id, l_tp_organization_id,
l_shikyu_primary_uom, l_allocable_primary_uom_qty,
l_shipped_primary_uom_qty, l_shipped_qty_uom
from JMF_SHIKYU_REPLENISHMENTS jsr, OE_ORDER_LINES_ALL oola
where jsr.REPLENISHMENT_SO_LINE_ID = l_replen_so_line_id
and jsr.REPLENISHMENT_SO_LINE_ID = oola.line_id;
select QUANTITY_RECEIVED
into l_pre_qty_received
from PO_LINE_LOCATIONS_ALL
where LINE_LOCATION_ID = l_replen_po_shipment_id;
select UNIT_OF_MEASURE
into l_shikyu_unit_of_measure
from MTL_UNITS_OF_MEASURE_VL
where UOM_CODE = l_shikyu_primary_uom;
SELECT rt.transaction_type, rt.transaction_id, rt.subinventory, rt.locator_id, rt.project_id
INTO l_transaction_type, l_parent_transaction_id, l_subinventory, l_locator_id, l_project_id
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.organization_id = l_tp_organization_id
AND rt.po_header_id = l_replen_po_header_id
AND rt.po_line_id = l_replen_po_line_id
AND rt.po_line_location_id = l_replen_po_shipment_id
AND rsl.item_id = l_shikyu_component_id
AND rt.SOURCE_DOCUMENT_CODE ='PO'
AND rt.replenish_order_line_id = l_replen_so_line_id
AND
(
(
RT.TRANSACTION_TYPE IN ('RECEIVE', 'TRANSFER', 'ACCEPT' , 'REJECT', 'MATCH')
AND EXISTS
(
SELECT
'POSTIVE RCV SUPPLY'
FROM RCV_SUPPLY RS
WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RS.TO_ORG_PRIMARY_QUANTITY >
(
SELECT
NVL(SUM(RTI.PRIMARY_QUANTITY),0)
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
)
)
)
OR
(
RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE <> 'RMA'
)
)
AND NOT EXISTS
(
SELECT
'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND
(
NVL(PLL.CANCEL_FLAG,'N') = 'Y'
OR NVL(PLL.CLOSED_CODE,'OPEN') = 'FINALLY CLOSED'
OR NVL(PLL.APPROVED_FLAG,'N') <> 'Y'
OR NVL(PLL.MATCHING_BASIS,'QUANTITY') = 'AMOUNT'
OR PLL.PAYMENT_TYPE IS NOT NULL
)
)
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID;
SELECT vendor_id, type_lookup_code
INTO l_vendor_id, l_po_type_lookup_code
FROM po_headers_all
WHERE po_header_id = l_replen_po_header_id;
select QUANTITY_RECEIVED
into l_post_qty_received
from PO_LINE_LOCATIONS_ALL
where LINE_LOCATION_ID = l_replen_po_shipment_id;
SELECT open_flag INTO l_open_flag
FROM oe_order_lines_all
WHERE line_id = l_replen_so_line_id;
select jsc.SHIKYU_COMPONENT_ID shikyu_component_id, jso.TP_ORGANIZATION_ID tp_organization_id, jsc.PRIMARY_UOM primary_uom
from JMF_SHIKYU_COMPONENTS jsc, JMF_SUBCONTRACT_ORDERS jso
where jsc.SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id
and jso.subcontract_po_shipment_id = jsc.SUBCONTRACT_PO_SHIPMENT_ID;
SELECT interlock_status
into l_interlock_status
FROM JMF_SUBCONTRACT_ORDERS
WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
select ALLOCATABLE_FLAG
into l_allocable_flag
from JMF_SHIKYU_ALLOCATIONS_V
where SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
SELECT nvl(SUM(adjustment), 0)
INTO l_adjustment_total
FROM jmf_shikyu_adjustments
WHERE subcontract_po_shipment_id = p_po_shipment_id
AND shikyu_component_id = l_subcontract_po_shikyu_comp.shikyu_component_id
AND request_id IS NOT NULL;