[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM pa_projects_all
WHERE project_id = X_project_id;
SELECT mic.category_id
FROM mtl_item_categories mic
, mtl_default_category_sets mdcs
WHERE mdcs.functional_area_id = 1
AND mic.category_set_id = mdcs.category_set_id
AND mic.inventory_item_id = C_item_id
AND mic.organization_id = C_org_id;
UPDATE mtl_material_transactions
SET error_code = X_error_code
, error_explanation = X_error_msg
, pm_cost_collected = 'E'
, last_update_date = sysdate
, last_updated_by = fnd_global.user_id
, request_id = fnd_global.conc_request_id
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = sysdate
WHERE transaction_id = X_transaction_id;
UPDATE wip_transactions
SET pm_cost_collected = 'E'
, last_update_date = sysdate
, last_updated_by = L_user_id
, request_id = L_request_id
, program_application_id = L_prog_appl_id
, program_id = L_prog_id
, program_update_date = sysdate
WHERE transaction_id = X_transaction_id;
UPDATE wip_txn_interface_errors
SET error_message = X_error_msg
, last_update_date = sysdate
, last_updated_by = L_user_id
, request_id = L_request_id
, program_application_id = L_prog_appl_id
, program_id = L_prog_id
, program_update_date = sysdate
WHERE transaction_id = X_transaction_id
AND error_column = 'TASK_ID';
INSERT INTO wip_txn_interface_errors
( transaction_id
, error_message
, error_column
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date)
VALUES ( X_transaction_id
, X_error_msg
, 'TASK_ID'
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, L_request_id
, L_prog_appl_id
, L_prog_id
, sysdate
);
SELECT attribute_code
FROM pjm_task_attr_usages
WHERE assignment_type = 'MATERIAL'
ORDER BY sequence_number;
SELECT task_id
FROM pjm_default_tasks
WHERE organization_id = C_org_id
AND project_id = C_proj_id
AND NVL(inventory_item_id, nvl(C_item_id,-1)) = nvl(C_item_id,-1)
AND NVL(po_header_id, nvl(C_po_header_id,-1)) = nvl(C_po_header_id,-1)
AND NVL(category_id, nvl(C_cat_id,-1)) = nvl(C_cat_id,-1)
AND NVL(subinventory_code, nvl(C_subinv_code,' ')) =
nvl(C_subinv_code,' ')
AND NVL(procure_flag, nvl(C_procured,'*')) = nvl(C_procured,'*')
AND assignment_type = 'MATERIAL'
ORDER BY decode(v_attributes(1), 'ITEM_NUMBER', to_char(inventory_item_id),
'PO_NUMBER', to_char(po_header_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY',subinventory_code,
'PROCURE_FLAG',procure_flag) ASC
, decode(v_attributes(2), 'ITEM_NUMBER', to_char(inventory_item_id),
'PO_NUMBER', to_char(po_header_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY',subinventory_code,
'PROCURE_FLAG',procure_flag) ASC
, decode(v_attributes(3), 'ITEM_NUMBER', to_char(inventory_item_id),
'PO_NUMBER', to_char(po_header_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY',subinventory_code,
'PROCURE_FLAG',procure_flag) ASC
, decode(v_attributes(4), 'ITEM_NUMBER', to_char(inventory_item_id),
'PO_NUMBER', to_char(po_header_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY',subinventory_code,
'PROCURE_FLAG',procure_flag) ASC
, decode(v_attributes(5), 'ITEM_NUMBER', to_char(inventory_item_id),
'PO_NUMBER', to_char(po_header_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY',subinventory_code,
'PROCURE_FLAG',procure_flag) ASC;
SELECT attribute_code
FROM pjm_task_attr_usages
WHERE assignment_type = 'RESOURCE'
ORDER BY sequence_number;
SELECT task_id
FROM pjm_default_tasks
WHERE organization_id = C_org_id
AND project_id = C_proj_id
AND NVL(standard_operation_id,
nvl(C_operation_id,-1)) = nvl(C_operation_id,-1)
AND C_wip_entity like nvl(wip_entity_pattern , '%')
AND NVL(assembly_item_id,
nvl(C_assy_item_id,-1)) = nvl(C_assy_item_id,-1)
AND NVL(department_id, nvl(C_dept_id,-1)) = nvl(C_dept_id,-1)
AND assignment_type = 'RESOURCE'
ORDER BY decode(v_attributes(1),
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern))
) ASC
, decode(v_attributes(2),
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern))
) ASC
, decode(v_attributes(3),
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern))
) ASC
, decode(v_attributes(4),
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern))
) ASC
, instr(wip_entity_pattern||'*%','%') DESC
;
SELECT wip_entity_name
FROM wip_entities
WHERE organization_id = C_org_id
AND wip_entity_id = C_wip_entity_id;
SELECT attribute_code
FROM pjm_task_attr_usages
WHERE assignment_type = 'WIPMAT'
ORDER BY sequence_number;
SELECT task_id
FROM pjm_default_tasks
WHERE organization_id = C_org_id
AND project_id = C_proj_id
AND NVL(inventory_item_id, nvl(C_item_id,-1)) = nvl(C_item_id,-1)
AND NVL(category_id, nvl(C_cat_id,-1)) = nvl(C_cat_id,-1)
AND NVL(subinventory_code, nvl(C_subinv_code,' ')) =
nvl(C_subinv_code,' ')
AND NVL(wip_matl_txn_type, nvl(C_wip_matl_txn_type,'ANY')) =
nvl(C_wip_matl_txn_type,'ANY')
AND C_wip_entity like nvl(wip_entity_pattern , '%')
AND NVL(assembly_item_id, nvl(C_assy_item_id,-1)) =
nvl(C_assy_item_id,-1)
AND NVL(standard_operation_id, nvl(C_operation_id,-1)) =
nvl(C_operation_id,-1)
AND NVL(department_id, nvl(C_dept_id,-1)) = nvl(C_dept_id,-1)
AND assignment_type = 'WIPMAT'
ORDER BY decode(v_attributes(1),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(2),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(3),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(4),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(5),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(6),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(7),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, decode(v_attributes(8),
'ITEM_NUMBER', to_char(inventory_item_id),
'CATEGORY', to_char(category_id),
'SUBINVENTORY', subinventory_code,
'DEPARTMENT', department_id,
'STANDARD_OPERATION', standard_operation_id,
'ASSEMBLY_ITEM', assembly_item_id,
'WIP_ENTITY', sign(length(wip_entity_pattern)),
'WIP_MATL_TXN_TYPE', wip_matl_txn_type
) ASC
, instr(wip_entity_pattern||'*%','%') DESC
;
SELECT wip_entity_name
FROM wip_entities
WHERE organization_id = C_org_id
AND wip_entity_id = C_wip_entity_id;
SELECT task_id
FROM pjm_default_tasks
WHERE organization_id = c_org_id
AND project_id = c_proj_id
AND NVL(inventory_item_id, nvl(c_item_id, -1)) = nvl(c_item_id, -1)
AND NVL(category_id, nvl(c_cat_id, -1)) = nvl(c_cat_id, -1)
AND NVL(to_organization_id, nvl(c_to_org_id, -1)) = nvl(c_to_org_id,-1)
AND assignment_type = 'SUPPLY CHAIN'
ORDER BY inventory_item_id ASC
, category_id ASC
, to_organization_id ASC;
SELECT organization_id
, inventory_item_id
, subinventory_code
, transaction_source_type_id
, decode(transaction_source_type_id,
1, transaction_source_id,
-1)
, project_id
, task_id
, sign(primary_quantity)
, transfer_organization_id
, transfer_transaction_id
, transfer_subinventory
, to_project_id
, to_task_id
, source_project_id
, source_task_id
FROM mtl_material_transactions
WHERE transaction_id = C_transaction_id;
SELECT decode(t.transaction_type_id,
35 , 'ISSUE' , -- WIP component issue
38 , 'ISSUE' , -- WIP Neg Comp Issue
43 , 'ISSUE' , -- WIP Component Return
48 , 'ISSUE' , -- WIP Neg Comp Return
17 , 'COMPLETION' , -- WIP Assembly Return
44 , 'COMPLETION' , -- WIP Assy Completion
NULL)
, decode(t.transaction_source_type_id,
5, t.transaction_source_id,
null)
, e.primary_item_id
, t.department_id
, o.standard_operation_id
FROM mtl_material_transactions t
, wip_entities e
, wip_operations o
WHERE transaction_id = C_transaction_id
AND e.organization_id = t.organization_id
AND e.wip_entity_id = t.transaction_source_id
AND o.organization_id (+) = t.organization_id
AND o.wip_entity_id (+) = t.transaction_source_id
AND o.operation_seq_num (+) = t.operation_seq_num;
SELECT decode( fob_point
, 1 , to_organization_id
, 2 , from_organization_id
, C_txfr_org_id ) intransit_org_id
FROM mtl_interorg_parameters
WHERE C_org_id <> C_txfr_org_id
AND from_organization_id =
decode(C_direction , 1 , C_txfr_org_id , -1 , C_org_id)
AND to_organization_id =
decode(C_direction , 1 , C_org_id , -1 , C_txfr_org_id)
;
UPDATE mtl_material_transactions m
SET task_id = L_task_id
WHERE transaction_id = X_transaction_id;
UPDATE mtl_material_transactions m
SET to_task_id = L_to_task_id
WHERE transaction_id = X_transaction_id;
UPDATE mtl_material_transactions m
SET source_task_id = L_src_task_id
WHERE transaction_id = X_transaction_id;
SELECT t.wip_entity_id
, t.organization_id
, t.operation_seq_num
, t.project_id
, t.task_id
, t.department_id
, t.transaction_type /*Added for Bug 7028109 (FP of 6820737)*/
, e.primary_item_id
, e.entity_type
, wta.cost_element_id /*Added for Bug 7028109 (FP of 6820737)*/
FROM wip_transactions t
, wip_entities e
, wip_transaction_accounts wta
WHERE t.transaction_id = C_transaction_id
AND t.wip_entity_id = e.wip_entity_id
AND t.organization_id = e.organization_id
AND t.transaction_id = wta.transaction_id
AND t.organization_id = wta.organization_id
AND wta.accounting_line_type = 7; /*Accounting line type 7 = WIP Valuation*/
SELECT o.standard_operation_id
FROM wip_entities e
, wip_operations o
WHERE e.organization_id = C_organization_id
AND e.wip_entity_id = C_wip_entity_id
AND o.organization_id = e.organization_id
AND o.wip_entity_id = e.wip_entity_id
AND o.operation_seq_num = C_operation_seq_num;
SELECT s.standard_operation_id
FROM wip_flow_schedules f
, bom_operational_routings r
, bom_operation_sequences s
WHERE f.wip_entity_id = C_wip_entity_id
AND f.organization_id = C_organization_id
AND nvl(f.alternate_routing_designator, 'a') = nvl(r.alternate_routing_designator, 'a')
AND r.assembly_item_id = C_primary_item_id
AND r.routing_sequence_id = s.routing_sequence_id
AND r.organization_id = f.organization_id
AND s.operation_seq_num = C_operation_seq_num
AND f.scheduled_completion_date BETWEEN s.effectivity_date AND nvl(s.disable_date, sysdate + 1)
AND s.operation_type = 1;
DELETE FROM wip_txn_interface_errors
WHERE transaction_id = X_transaction_id
AND error_column = 'TASK_ID';
UPDATE wip_transactions w
SET task_id = L_task_id
WHERE transaction_id = X_transaction_id;