[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select jtf_tasks_vl.task_id, repair_line_id
from jtf_tasks_vl, csd_repairs
where jtf_tasks_vl.source_object_id = csd_repairs.repair_line_id
and source_object_type_code='DR'
and repair_line_id between p_start_repair_line_id
and p_end_repair_line_id
and not exists( select 'x' from csd_tasks
where csd_tasks.task_id = jtf_tasks_vl.task_id
and csd_tasks.repair_line_id = csd_repairs.repair_line_id);*/
insert into CSD_TASKS (
REPAIR_TASK_ID,
TASK_ID,
REPAIR_LINE_ID,
APPLICABLE_QA_PLANS,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) select
CSD.csd_tasks_s.nextval,
jtf_tasks_vl.task_id,
csd_repairs.repair_line_id,
'N',
1,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id
from jtf_tasks_vl,
CSD_REPAIRS
where jtf_tasks_vl.source_object_id = csd_repairs.repair_line_id
and source_object_type_code='DR'
and repair_line_id between v_min and v_max
and not exists( select 'x' from csd_tasks
where csd_tasks.task_id = jtf_tasks_vl.task_id
and csd_tasks.repair_line_id = csd_repairs.repair_line_id);
Select
repair_line_id,
original_source_reference,
flow_status_id,
inventory_org_id,
inventory_item_id
from csd_repairs
where flow_status_id is null
or inventory_org_id is null;
SELECT oel.ship_from_org_id
FROM oe_order_lines_all oel,
csd_repairs cr
WHERE
cr.repair_line_id = p_repair_line_id
AND cr.original_source_header_id = oel.header_id
AND cr.original_source_line_id = oel.line_id;
SELECT oel.ship_from_org_Id
FROM oe_order_lines_all oel,
csd_product_transactions cp
WHERE cp.repair_line_id = p_repair_line_Id
AND cp.action_type in ('RMA','MOVE_IN')
AND cp.order_line_id = oel.line_id
AND cp.order_header_id = oel.header_id;
SELECT organization_id
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id;
insert into CSD_FLOW_STATUSES_B (
FLOW_STATUS_ID,
FLOW_STATUS_CODE,
STATUS_CODE,
SEEDED_FLAG,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
CSD_FLOW_STATUSES_S1.nextval,
FlowStatusCodes(i),
FlowStatusCodes(i), -- literal value is same
'Y',
1,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
);
insert into CSD_FLOW_STATUSES_TL (
FLOW_STATUS_ID,
EXTERNAL_DISPLAY_STATUS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
FS_B.flow_status_id,
NULL, -- EXTERNAL_DISPLAY_STATUS
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id,
L.LANGUAGE_CODE,
L.LANGUAGE_CODE
from FND_LANGUAGES L,
CSD_FLOW_STATUSES_B FS_B
where L.INSTALLED_FLAG in ('I', 'B')
AND FS_B.flow_status_code in ('C','H','O','D')
and not exists
(select 'x'
from CSD_FLOW_STATUSES_TL T
where T.FLOW_STATUS_ID = FS_B.flow_status_id
and T.LANGUAGE = L.LANGUAGE_CODE);
insert into CSD_FLWSTS_TRANS_B (
REPAIR_TYPE_ID,
FROM_FLOW_STATUS_ID,
TO_FLOW_STATUS_ID,
WF_ITEM_TYPE,
WF_PROCESS_NAME,
REASON_REQUIRED_FLAG,
CAPTURE_ACTIVITY_FLAG,
ALLOW_ALL_RESP_FLAG,
OBJECT_VERSION_NUMBER,
FLWSTS_TRAN_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT RT.repair_type_id,
FS_B1.flow_status_id,
FS_B2.flow_status_id,
NULL, -- P_WF_ITEM_TYPE
NULL, -- P_WF_PROCESS_NAME
'N', -- P_REASON_REQUIRED_FLAG,
'Y', -- P_CAPTURE_ACTIVITY_FLAG,
'Y', -- P_ALLOW_ALL_RESP_FLAG,
1,
CSD_FLWSTS_TRANS_S1.nextval,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
FROM CSD_REPAIR_TYPES_B RT,
CSD_FLOW_STATUSES_B FS_B1,
CSD_FLOW_STATUSES_B FS_B2
WHERE FS_B1.flow_status_code IN ('C','H','O')
AND FS_B2.flow_status_code IN ('C','H','O')
AND FS_B2.flow_status_code <> FS_B1.flow_status_code
AND NOT EXISTS
( SELECT 'x'
FROM CSD_FLWSTS_TRANS_B FLWSTS_B
WHERE FLWSTS_B.repair_type_id = RT.repair_type_id
AND FLWSTS_B.from_flow_status_id = FS_B1.flow_status_id
AND FLWSTS_B.to_flow_status_id = FS_B2.flow_status_id
);
insert into CSD_FLWSTS_TRANS_TL (
FLWSTS_TRAN_ID,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
FLWSTS_B.flwsts_tran_id,
NULL, -- P_DESCRIPTION,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id,
L.LANGUAGE_CODE,
L.LANGUAGE_CODE
from FND_LANGUAGES L,
CSD_FLWSTS_TRANS_B FLWSTS_B
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select 'x'
from CSD_FLWSTS_TRANS_TL T
where T.FLWSTS_TRAN_ID = FLWSTS_B.flwsts_tran_id
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT flow_status_id
INTO FlowStatusIDs(i)
FROM CSD_FLOW_STATUSES_B
WHERE flow_status_code = FlowStatusCodes(i);
UPDATE CSD_REPAIR_TYPES_B
SET start_flow_Status_id = FlowStatusIDs(c_open_index),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE start_flow_Status_id IS NULL;
UPDATE CSD_REPAIRS
SET flow_Status_id = decode(status,
'O', FlowStatusIDs(c_open_index),
'C', FlowStatusIDs(c_closed_index),
'H', FlowStatusIDs(c_hold_index),
'D', FlowStatusIDs(c_draft_index)
),
inventory_org_id = l_inv_org_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE repair_line_id = repair_line_id_arr(i);
SELECT DISTINCT
dc.repair_line_id, rep.inventory_item_id
FROM
CSD_RO_DIAGNOSTIC_CODES dc,
CSD_REPAIRS rep
WHERE dc.diagnostic_item_id IS NULL
AND rep.repair_line_id = dc.repair_line_id;
UPDATE
CSD_RO_DIAGNOSTIC_CODES
SET
diagnostic_item_id = inv_item_id_arr(i)
WHERE repair_line_id = rep_line_id_arr(i);
SELECT DISTINCT
sc.repair_line_id, rep.inventory_item_id
FROM
CSD_RO_SERVICE_CODES sc,
CSD_REPAIRS rep
WHERE sc.service_item_id IS NULL
AND rep.repair_line_id = sc.repair_line_id;
UPDATE
CSD_RO_SERVICE_CODES
SET
service_item_id = inv_item_id_arr(i)
WHERE repair_line_id = rep_line_id_arr(i);