The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uom_code
INTO l_uom_code
FROM MTL_units_of_measure
WHERE unit_of_measure = p_uom ;
PROCEDURE Update_Consumption_Date
IS
BEGIN
IF g_debug = 1
THEN
INV_LOG_UTIL.trace
( '>> Update_Consumption_Date',null,9);
UPDATE mtl_consigned_diag_errors mcde
SET mcde.consumption_date =
( select MIN(mmt.transaction_date)
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = mcde.inventory_item_id
and NVL(mmt.revision, -980980) = NVL(mcde.revision,-980980)
and mmt.organization_id = mcde.organization_id
and mmt.owning_organization_id = mcde.owning_organization_id
)
WHERE mcde.consumption_date is NULL
and mcde.record_type = 2 ;
( '>> Update_Consumption_Date',null,9);
'Update_Consumption_Date',9);
END Update_Consumption_Date ;
DELETE FROM
mtl_consigned_diag_errors
WHERE ( request_id <> g_request_id) OR (
request_id is NULL ) ;
INSERT INTO
mtl_consigned_diag_errors
( RECORD_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_TP_TYPE
, OWNING_ORGANIZATION_ID
, PLANNING_TP_TYPE
, PLANNING_ORGANIZATION_ID
, PO_HEADER_ID
, AGENT_ID
, RECORD_TYPE
, ERROR_CODE
, ACTION_CODE
, LAST_NOTIFICATION_DATE
, NOTIFICATION_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, error_type
, primary_uom
, purchasing_uom
) SELECT
mtl_consigned_diag_errors_s.NEXTVAL
, mcdet.ORGANIZATION_ID
, mcdet.INVENTORY_ITEM_ID
, mcdet.REVISION
, 1
, mcdet.OWNING_ORGANIZATION_ID
, null --- PLANNING_ORGANIZATION_TYPE
, null --- PLANNING_ORGANIZATION_ID
, mcdet.PO_HEADER_ID
, mcdet.AGENT_ID
, mcdet.RECORD_TYPE
, mcdet.mcde_ERROR_CODE
, mcdet.mcde_ACTION_CODE
, null -- LAST_NOTIFICATION_DATE
, null -- NOTIFICATION_ID
, sysdate -- CREATION_DATE
, g_user_id -- Created_by
, sysdate -- LAST_UPDATE_DATE
, g_user_id
, g_user_id
, g_request_id
, g_program_application_id
, g_program_id
, sysdate
, error_type
, primary_uom
, purchasing_uom
FROM mtl_consigned_diag_temp mcdet
WHERE mcdet.error_code is not null
AND NOT EXISTS
( SELECT 1
FROM mtl_consigned_diag_errors mcde
WHERE mcde.organization_id =
mcdet.organization_id
and mcde.owning_organization_id =
mcdet.owning_organization_id
and mcde.error_code = mcdet.mcde_error_code
and mcde.inventory_item_id = mcdet.inventory_item_id
and mcde.record_type = mcdet.record_type
and NVL(mcde.revision,-9876321)
= NVL(mcdet.revision,-9876321)
);
UPDATE mtl_consigned_diag_errors mcde
SET mcde.request_id =
( SELECT g_request_id
FROM mtl_consigned_diag_temp mcdet
WHERE MCDE.organization_id = mcdet.organization_id
AND MCDE.owning_organization_id =
mcdet.owning_organization_id
AND MCDE.error_code = mcdet.mcde_error_code
AND MCDE.inventory_item_id = mcdet.inventory_item_id
AND mcde.record_type = mcdet.record_type
AND NVL(mcde.revision,-98763245 )
= NVL(mcdet.revision , -98763245 )
)
WHERE ( MCDE.request_id <> g_request_id) OR
( MCDE.request_id IS NULL ) ;
INSERT INTO
mtl_consigned_diag_errors
( RECORD_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_TP_TYPE
, OWNING_ORGANIZATION_ID
, PLANNING_TP_TYPE
, PLANNING_ORGANIZATION_ID
, PO_HEADER_ID
, AGENT_ID
, RECORD_TYPE
, ERROR_CODE
, ACTION_CODE
, LAST_NOTIFICATION_DATE
, NOTIFICATION_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, error_type
, primary_uom
, purchasing_uom
) SELECT
mtl_consigned_diag_errors_s.NEXTVAL
, mcdet.ORGANIZATION_ID
, mcdet.INVENTORY_ITEM_ID
, mcdet.REVISION
, 1
, mcdet.OWNING_ORGANIZATION_ID
, null --- PLANNING_ORGANIZATION_TYPE
, null --- PLANNING_ORGANIZATION_ID
, mcdet.PO_HEADER_ID
, mcdet.AGENT_ID
, mcdet.RECORD_TYPE
, mcdet.mcde_ERROR_CODE
, mcdet.mcde_ACTION_CODE
, null -- LAST_NOTIFICATION_DATE
, null -- NOTIFICATION_ID
, sysdate -- CREATION_DATE
, g_user_id -- Created_by
, sysdate -- LAST_UPDATE_DATE
, g_user_id
, g_user_id
, g_request_id
, g_program_application_id
, g_program_id
, sysdate
, error_type
, primary_uom
, purchasing_uom
FROM mtl_consigned_diag_temp mcdet
WHERE mcdet.error_code is not null
AND NOT EXISTS
( SELECT 1
FROM mtl_consigned_diag_errors mcde
WHERE mcde.organization_id =
mcdet.organization_id
and mcde.owning_organization_id =
mcdet.owning_organization_id
and mcde.error_code = mcdet.mcde_error_code
and mcde.inventory_item_id = mcdet.inventory_item_id
and mcde.record_type = mcdet.record_type
and mcde.po_header_id = mcdet.po_header_id
and NVL(mcde.revision,-9876321)
= NVL(mcdet.revision,-9876321)
)
and mcdet.record_type = 2;
UPDATE mtl_consigned_diag_errors mcde
SET mcde.request_id =
( SELECT g_request_id
FROM mtl_consigned_diag_temp mcdet
WHERE MCDE.organization_id = mcdet.organization_id
AND MCDE.owning_organization_id =
mcdet.owning_organization_id
AND MCDE.error_code = mcdet.mcde_error_code
AND MCDE.inventory_item_id = mcdet.inventory_item_id
AND mcde.record_type = mcdet.record_type
AND mcde.po_header_id = mcdet.po_header_id
AND NVL(mcde.revision,-98763245 )
= NVL(mcdet.revision , -98763245 )
)
WHERE ( MCDE.request_id <> g_request_id) OR
( MCDE.request_id IS NULL ) ;
SELECT
poh.PO_HEADER_ID
, poh.AGENT_ID
, poh.SEGMENT1
FROM
po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id = pol.po_header_id
AND poh.po_header_id = poll.po_header_id
AND pol.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.PO_LINE_ID
AND poll.CONSIGNED_FLAG = 'Y'
AND pol.ITEM_ID = p_inventory_item_id
AND poh.vendor_id = l_vendor_id
AND poh.vendor_site_id = p_vendor_site_id
AND ( pol.item_revision = p_revision
OR pol.item_revision IS NULL ) ;
SELECT agent_id
INTO l_buyer_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id ;
SELECT vendor_id
INTO l_vendor_id
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_site_id ;
IS SELECT
Poh.blanket_total_amount
FROM
po_headers_all poh
, po_lines_all pol
WHERE poh.po_header_id = pol.po_header_id
AND NVL(poh.approved_flag,'Y') = 'Y'
AND (TRUNC(NVL(poh.start_date,sysdate -1)) <= TRUNC(sysdate))
AND (TRUNC(NVL(poh.end_date,sysdate +1)) >= TRUNC(sysdate))
AND (TRUNC(NVL(pol.expiration_date,sysdate )) >= TRUNC(sysdate))
AND (NVL(poh.cancel_flag,'N') = 'N'
OR NVL(pol.cancel_flag,'N') = 'N')
AND (NVL(poh.cancel_flag,'N') = 'N'
OR NVL(pol.cancel_flag,'N') = 'N')
AND NVL(pol.closed_code,'OPEN') = 'OPEN'
AND poh.po_header_id = p_po_header_id
AND pol.item_id = p_inventory_item_id
AND ( pol.item_revision = p_revision
OR pol.item_revision IS NULL );
SELECT
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
, po_header_id
FROM mtl_consigned_diag_temp ;
DELETE FROM mtl_consigned_diag_temp ;
( 'REGULAR - Before Insert into mtl_consigned_diag_temp' ,
'Consumption_Advice_diagnostics',9);
INSERT into mtl_consigned_diag_temp (
PO_header_id
, INVENTORY_ITEM_ID
, REVISION
, organization_id
, OWNING_ORGANIZATION_ID
, RECORD_TYPE )
SELECT
DISTINCT
mmt.transaction_source_id
, mmt.inventory_item_id
, mmt.revision
, mmt.organization_id
, mmt.owning_organization_id
, 2
FROM
mtl_consumption_transactions mct
, mtl_material_transactions mmt
WHERE mct.transaction_id = mmt.transaction_id
AND mct.consumption_processed_flag <> 'Y'
AND mmt.transaction_type_id = 74 ;
( 'REVALIDATE - Before Insert into mtl_consigned_diag_temp' ,
'Consumption_Advice_diagnostics',9);
INSERT into mtl_consigned_diag_temp (
PO_header_id
, INVENTORY_ITEM_ID
, REVISION
, organization_id
, OWNING_ORGANIZATION_ID
, RECORD_TYPE )
SELECT
PO_header_id
, inventory_item_id
, revision
, organization_id
, owning_organization_id
, 2
FROM
MTL_CONSIGNED_DIAG_ERRORS
WHERE record_id = p_error_record_id ;
( 'after Insert into mtl_consigned_diag_temp' ,
'Consumption_Advice_diagnostics',9);
UPDATE mtl_consigned_diag_temp
SET error_code = l_error_code
, mcde_error_code = l_cad_error_code
, mcde_action_code = l_cad_action_code
, agent_id = l_buyer_id
, error_type = l_error_type
WHERE ORGANIZATION_ID = l_cad_temp_rec.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = l_cad_temp_rec.INVENTORY_ITEM_ID
AND OWNING_ORGANIZATION_ID = l_cad_temp_rec.OWNING_ORGANIZATION_ID
AND nvl(revision,-98765432) = nvl(l_cad_temp_rec.revision, -98765432 )
AND po_header_id = l_cad_temp_rec.po_header_id ;
( 'after UPDATE mtl_consigned_diag_temp '
,'Consumption_Advice_diagnostics'
, 9
);
SELECT
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
FROM mtl_consigned_diag_temp ;
DELETE FROM mtl_consigned_diag_temp ;
( ' REgular - Before Insert into Temp ',
'Ownership_transfer_diagnostics' ,9);
INSERT INTO mtl_consigned_diag_temp (
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
, RECORD_TYPE
) SELECT DISTINCT
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
, 1
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE OWNING_TP_TYPE = 1 ;
( ' REvalidate - Before Insert into Temp ',
'Ownership_transfer_diagnostics' ,9);
INSERT INTO mtl_consigned_diag_temp (
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
, RECORD_TYPE
) SELECT
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, OWNING_ORGANIZATION_ID
, 1
FROM
MTL_consigned_diag_errors
WHERE record_id = p_error_record_id ;
( 'after Insert into MCDET TEMP table ',
'Ownership_transfer_diagnostics' ,9);
UPDATE mtl_consigned_diag_temp
SET error_code = l_error_code
, mcde_error_code = l_mcde_error_code
, mcde_action_code = l_mcde_action_code
, agent_id = l_buyer_id
, po_header_id = l_error_po_id
, error_type = l_error_type
, primary_uom = l_primary_uom_code
, purchasing_uom = l_purchasing_uom_code
WHERE ORGANIZATION_ID = l_moqd_temp_rec.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = l_moqd_temp_rec.INVENTORY_ITEM_ID
AND OWNING_ORGANIZATION_ID = l_moqd_temp_rec.OWNING_ORGANIZATION_ID
AND nvl(revision,-98765439) = nvl(l_moqd_temp_rec.revision, -98765439 );
( 'after UPDATE mtl_consigned_diag_temp '
,'Ownership_transfer_diagnostics'
, 9
);
SELECT
record_type
, request_id
, error_code
INTO
l_record_type
, l_request_id
, l_error_code
FROM
mtl_consigned_diag_errors
WHERE record_id = p_error_record_id ;
DELETE from mtl_consigned_diag_errors
WHERE record_id = p_error_record_id ;
DELETE from mtl_consigned_diag_errors
WHERE record_id = p_error_record_id ;
INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;
INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;