[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT description
INTO l_element_desc
FROM cs_kb_elements_vl
WHERE element_id = p_element_id;
SELECT 'x'
FROM jtf_tasks_b TASK, jtf_task_statuses_b status
WHERE TASK.source_object_id = p_repair_line_id
AND TASK.source_object_type_code = 'DR'
AND TASK.task_status_id = status.task_status_id
AND NVL(status.closed_flag,'N') <> 'Y'
AND ROWNUM =1;
SELECT 'x' /* crx.repair_job_xref_id */
FROM csd_repair_job_xref crx, wip_discrete_jobs wdj
WHERE crx.repair_line_id = p_repair_line_id
AND crx.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type NOT IN (4,5,7,12)
/* 4: Complete,5: Complete-No charges,7: Cancelled,12: Closed */
UNION ALL
SELECT 'x' /* repair_job_xref_id*/
FROM csd_repair_job_xref
WHERE repair_line_id = p_repair_line_id
AND wip_entity_id IS NULL;
SELECT repair_mode FROM csd_repairs
WHERE repair_line_id = p_repair_line_id;
SELECT REPAIR_LINE_ID
FROM CSD_REPAIRS
WHERE repair_number = p_repair_number;
SELECT FLOW_STATUS_ID
FROM CSD_FLOW_STATUSES_B
WHERE FLOW_STATUS_CODE = p_repair_status;
l_sql_str := 'Select ''x'' from csd_repairs ro '
|| ' Where ro.repair_line_id = :1 and '
|| l_predicate ;
l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
+ ' cs_estimate_details csd Where ro.repair_line_id = :1'
+ ' And prd.repair_line_id = ro.repair_line_id '
+ ' And prd.action_code = :2 '
+ ' And prd.action_type = :3 '
+ ' And csd.estimate_detail_id = prd.estimate_detail_id '
+ ' And ' + l_predicate
;
l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
+ ' cs_estimate_details csd Where ro.repair_line_id = :1'
+ ' And prd.repair_line_id = ro.repair_line_id '
+ ' And prd.action_code = :2 '
+ ' And prd.action_type = :3 '
+ ' And csd.estimate_detail_id = prd.estimate_detail_id '
+ ' And ' + l_predicate ;
Select instance_party_id,
object_version_number
from csi_i_parties
where instance_id = p_instance_id
and relationship_type_code = 'OWNER'
and sysdate between nvl(active_start_date, sysdate-1)
and nvl(active_end_date, sysdate+1);
Select object_version_number from csi_item_instances
where instance_id = p_instance_id;
Select ip_account_id,
object_version_number
from csi_ip_accounts
where instance_party_id = p_instance_party_id;
Select party_type
from hz_parties
where party_id = p_party_id;
Select count(relationship_id)
from hz_relationships
where subject_id = p_subject_id
and subject_type = p_subject_type
and subject_table_name = 'HZ_PARTIES'
and object_id = p_object_id
and object_type = p_object_type
and object_table_name = 'HZ_PARTIES'
and relationship_code = p_relationship_code
and sysdate between nvl(start_date, sysdate-1)
and nvl(end_date, sysdate+1);
SELECT bill_to_address,ship_to_address
FROM CSI_IP_ACCOUNTS
WHERE INSTANCE_PARTY_ID =
(SELECT instance_party_id FROM CSI_I_PARTIES
WHERE INSTANCE_ID=p_instance_id
AND relationship_type_code='OWNER');
csi_item_instance_pub.update_item_instance
(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => x_instance_id_lst,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT user_profile_option_name
FROM fnd_profile_options_tl
WHERE profile_option_name = p_profile_name
AND language = userenv('lang');
procedure auto_update_ro_status(
p_api_version in number,
p_commit in varchar2,
p_init_msg_list in varchar2,
p_repair_line_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2,
p_event in varchar2,
p_reason_code in varchar2 default null,
p_comments in varchar2 default null,
p_validation_level in number)
is
l_from_flow_status_id number;
lc_mod_name varchar2(100) := 'csd.plsql.csd_repairs_util.auto_update_ro_status';
lc_api_name varchar2(60) := 'auto_update_ro_status';
fnd_log.string(lc_log_level,lc_mod_name,'Begin auto_update_ro_status');
fnd_log.string(lc_log_level,lc_mod_name,'Profile CSD: Enable Auto Update of Repair
Order Status upon Receiving is not set to yes.');
select 'x'
into l_un_rcvd_lines_exists
from (
select 'x'
from csd_product_transactions cpt
where cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code in ('CUST_PROD','EXCHANGE') and
cpt.prod_txn_status <> 'RECEIVED'
and rownum < 2
) where rownum < 2 ;
select distinct cr.flow_status_id,
cr.repair_type_id,
cr.object_version_number
into l_from_flow_status_id,
l_repair_type_id,
l_object_version_number
from csd_repairs cr
where cr.repair_line_id = p_repair_line_id;
fnd_log.string(lc_log_level,lc_mod_name,'the new status is same as the old status. Do not update the status');
fnd_log.string(lc_log_level,lc_mod_name,'calling csd_repairs_pvt.update_flow_status API');
csd_repairs_pvt.update_flow_status(p_api_version => 1,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_repair_line_id => p_repair_line_id,
p_repair_type_id => l_repair_type_id,
p_from_flow_status_id => l_from_flow_status_id,
p_to_flow_status_id => l_to_flow_status_id,
p_reason_code => p_reason_code,
p_comments => p_comments,
p_check_access_flag => 'Y',
p_object_version_number => l_object_version_number,
x_object_version_number => x_object_version_number );
fnd_log.string(lc_log_level,lc_mod_name,'Error in csd_repairs_pvt.update_flow_status
['||x_msg_data||']');
end auto_update_ro_status;
select distinct repair_mode, nvl(auto_process_rma,'N')
from csd_repair_types_vl
where repair_type_id = p_repair_type_id;
select customer_id,
account_id,
bill_to_site_use_id,
ship_to_site_use_id,
inventory_item_id,
category_id,
contract_id,
problem_code,
customer_product_id
from CS_INCIDENTS_ALL_VL
where incident_id = p_incident_id;
/* Description: This will insert the records into requisition interface */
/* table and subsequently launches the concurrent request to */
/* create the requisitions.If the concurrent program is */
/* launched successfully then the concurrent request id is */
/* is returned. */
/* Created by: subhat - 02-09-09 */
/**************************************************************************/
PROCEDURE create_requisition
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_wip_entity_id_tbl IN JTF_NUMBER_TABLE,
p_quantity_tbl IN JTF_NUMBER_TABLE,
p_uom_code_tbl IN VARCHAR2_TABLE_100,
p_op_seq_num_tbl IN JTF_NUMBER_TABLE,
p_item_id_tbl IN JTF_NUMBER_TABLE,
p_item_description_tbl IN VARCHAR2_TABLE_100,
p_organization_id IN NUMBER,
x_request_id OUT NOCOPY NUMBER,
p_repair_line_id IN NUMBER DEFAULT NULL
) IS
lc_api_version CONSTANT NUMBER default 1.0;
select employee_id
into l_person_id
from fnd_user
where user_id = fnd_global.user_id;
select wdj.material_account, wdj.material_variance_account, wdj.project_id, wdj.task_id
into l_material_account(i),l_material_variance_account(i), l_project_id(i), l_task_id(i)
from wip_discrete_jobs wdj
where wdj.wip_entity_id = l_wip_entity_id_tbl(i)
and wdj.organization_id = p_organization_id;
select bd.location_id
into l_location_id(i)
from bom_departments bd, wip_operations wo
where bd.department_id = wo.department_id
and bd.organization_id = wo.organization_id
and wo.wip_entity_id = l_wip_entity_id_tbl(i)
and wo.operation_seq_num = l_op_seq_num_tbl(i)
and wo.organization_id = p_organization_id;
select gb.currency_code, to_number(ho.ORG_INFORMATION3)
into l_currency, l_ou_id
from hr_organization_information ho, gl_sets_of_books gb
where gb.set_of_books_id = ho.ORG_INFORMATION1
and ho.organization_id = p_organization_id
and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
'Just before calling the FORALL insert to po_requisitions_interface_all');
insert into po_requisitions_interface_all (
interface_source_code,
destination_type_code,
authorization_status,
preparer_id, -- person id of the user name
quantity,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
source_type_code,
category_id,
item_description,
uom_code,
unit_price,
need_by_date,
wip_entity_id,
wip_operation_seq_num,
charge_account_id,
variance_account_id,
item_id,
wip_resource_seq_num,
suggested_vendor_id,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_phone,
suggested_vendor_item_num,
currency_code,
project_id,
task_id,
project_accounting_context,
last_updated_by,
last_update_date,
created_by,
creation_date,
org_id,
reference_num,
source_organization_id,
source_subinventory,
autosource_flag,
group_code
)
values (
'CSD',
'INVENTORY',
'INCOMPLETE',
l_person_id,
l_quantity_tbl(j),
p_organization_id,
l_location_id(j),
l_person_id,
decode(x_requisition_type(j),'I','INVENTORY','V','VENDOR',decode(l_source_org_id_tbl(j),null,'VENDOR','INVENTORY')),
null,
l_item_description_tbl(j),
l_uom_code_tbl(j),
0,
l_needby_date ,
l_wip_entity_id_tbl(j),
l_op_seq_num_tbl(j),
l_material_account(j),
l_material_variance_account(j),
l_item_id_tbl(j),
null,
null,
null,
null,
null,
null,
l_currency,
l_project_id(j),
l_task_id(j),
decode(nvl(l_project_id(j),-1),-1, 'N','Y'),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
l_ou_id,
null,
l_source_org_id_tbl(j),
l_source_subinv_tbl(j),
'Y',
decode(x_requisition_type(j),'I','INVENTORY','V','VENDOR',decode(l_source_org_id_tbl(j),null,'VENDOR','INVENTORY'))
);
-- need to call the mat_issue_update procedure.
l_previous_wip_entity := NULL;
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling the csd_hdv_wip_job_pvt.update_mat_issue_quantities for wip_entity_id '||p_wip_entity_id_tbl(k));
CSD_HV_WIP_JOB_PVT.update_mat_issue_quantities
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => 100,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_wip_entity_id => p_wip_entity_id_tbl(k)
);
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Error in update_mat_issue_quantities API for wip_entity_id '||p_wip_entity_id_tbl(k));
-- update the existing line to have material requirement as 0 and create/update the new line for l_item_id_tbl(k)
IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Item Id '||p_item_id_tbl(k)||' is being replaced by item id '||l_item_id_tbl(k));
SELECT wip_transaction_detail_id
INTO l_mtl_txn_dtls_upd(k).wip_transaction_detail_id
FROM csd_wip_transaction_details
WHERE wip_entity_id = p_wip_entity_id_tbl(k)
AND inventory_item_id = p_item_id_tbl(k)
AND operation_seq_num = p_op_seq_num_tbl(k);
SELECT 'N',wip_transaction_detail_id,NVL(cwt.transaction_quantity,
wro.required_quantity-wro.quantity_issued)+l_quantity_tbl(k)
INTO l_mtl_txn_dtls_tab(k).new_row,l_mtl_txn_dtls_tab(k).wip_transaction_detail_id,l_mtl_txn_dtls_tab(k).required_quantity
FROM csd_wip_transaction_details cwt,wip_requirement_operations wro
WHERE cwt.wip_entity_id = l_wip_entity_id_tbl(k)
AND cwt.inventory_item_id = l_item_id_tbl(k)
AND cwt.operation_seq_num = l_op_seq_num_tbl(k)
AND cwt.wip_entity_id = wro.wip_entity_id
AND cwt.operation_seq_num = wro.operation_seq_num
AND cwt.inventory_item_id = wro.inventory_item_id;
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling process_save_mtl_txn_dtls to update the required quantity to 0 since its substituted.');
UPDATE csd_wip_transaction_details cwt SET substitutes = l_mtl_txn_dtls_upd(k).inventory_item_id
WHERE cwt.wip_entity_id = l_mtl_txn_dtls_tab(k).wip_entity_id
AND cwt.operation_seq_num = l_mtl_txn_dtls_tab(k).operation_seq_num
AND cwt.inventory_item_id = l_mtl_txn_dtls_tab(k).inventory_item_id;
UPDATE csd_wip_transaction_details SET is_substituted = 'Y'
WHERE wip_entity_id = l_mtl_txn_dtls_upd(k).wip_entity_id
AND operation_seq_num = l_mtl_txn_dtls_upd(k).operation_seq_num
AND inventory_item_id = l_mtl_txn_dtls_upd(k).inventory_item_id;
SELECT 'Y'
INTO l_dummy
FROM po_requisitions_interface_all
WHERE interface_source_code = 'CSD'
AND process_flag IS NULL
AND ROWNUM < 2;
SELECT requisition_header_id,authorization_status,type_lookup_code
BULK COLLECT INTO l_requisition_id,l_authorization,l_req_type
FROM po_requisition_headers_all
WHERE request_id = l_request_id
AND creation_date > SYSDATE - 1;
l_requisition_id.DELETE(i);
-- SELECT all the requisition id's originated from CSD and are not yet interfaced to OM.
SELECT prh.requisition_header_id
BULK COLLECT INTO l_requisition_id
FROM po_requisition_headers_all prh,po_requisition_lines_all prl,
csd_repair_job_xref crj,csd_wip_transaction_details cwt
WHERE crj.wip_entity_id = cwt.wip_entity_id
AND cwt.wip_entity_id = prl.wip_entity_id
AND cwt.inventory_item_id = prl.item_id
AND cwt.operation_seq_num = prl.wip_operation_seq_num
AND prl.requisition_header_id = prh.requisition_header_id
AND prh.type_lookup_code = 'INTERNAL'
AND prh.authorization_status = 'APPROVED';
-- insert/update the records in the csd_product_transactions table.
MERGE INTO csd_product_transactions cpt
USING (SELECT ooh.header_id,ool.line_id,prl.requisition_line_id,prl.requisition_header_id,wdd.delivery_detail_id,
ooh.org_id source_ou,prl.org_id destination_ou,prl.destination_organization_id dest_org,prl.source_organization_id source_org,
prl.quantity,prl.item_id
FROM oe_order_headers_all ooh, oe_order_lines_all ool,
po_requisition_lines_all prl,wsh_delivery_details wdd
WHERE ooh.source_document_id = l_requisition_id(i)
AND ooh.header_id = ool.header_id
AND ool.source_document_line_id = prl.requisition_line_id
AND prl.requisition_header_id = l_requisition_id(i)
AND ooh.header_id = wdd.source_header_id
AND ool.line_id = wdd.source_line_id
AND wdd.source_code = 'OE') a
ON (cpt.req_header_id = l_requisition_id(i) )
WHEN MATCHED THEN UPDATE SET cpt.order_header_id = NVL(cpt.order_header_id,a.header_id),cpt.order_line_id = NVL(cpt.order_line_id,a.line_id),
cpt.delivery_detail_id = NVL(cpt.delivery_detail_id,a.delivery_detail_id),cpt.req_line_id = NVL(cpt.req_line_id,a.requisition_line_id)
WHEN NOT MATCHED THEN INSERT
(product_transaction_id,
repair_line_id,
exp_quantity,
inventory_item_id,
ship_from_ou,
ship_from_org,
rcv_into_ou,
rcv_into_org,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by,
object_version_number,
order_header_id,
order_line_id,
req_header_id,
req_line_id,
delivery_detail_id)
VALUES
(csd_product_transactions_s1.nextval,
p_repair_line_id,
a.quantity,
a.item_id,
a.source_ou,
a.source_org,
a.destination_ou,
a.dest_org,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id,
1,
a.header_id,
a.line_id,
a.requisition_header_id,
a.requisition_line_id,
a.delivery_detail_id);
l_activity_rec csd_update_programs_pvt.activity_rec_type;
SELECT mmt.reason_id,mmt.inventory_item_id,wdj.primary_item_id,wro.organization_id
FROM mtl_material_transactions mmt, wip_requirement_operations wro,
csd_repair_job_xref crj, wip_discrete_jobs wdj
WHERE crj.repair_line_id = p_repair_line_id
AND crj.wip_entity_id = wro.wip_entity_id
AND wro.operation_seq_num = mmt.operation_seq_num
AND wro.organization_id = mmt.organization_id
AND wro.wip_entity_id = mmt.transaction_source_id
AND wro.inventory_item_id = mmt.inventory_item_id
AND wro.wip_entity_id = wdj.wip_entity_id;
SELECT cpt.repair_line_id,oeh.header_id,ced.inventory_item_id,cr.incident_id
INTO l_repair_line_id,l_header_id,l_ship_inv_item_id,l_incident_id
FROM oe_order_lines_all oel, oe_order_headers_all oeh,
cs_estimate_details ced, csd_product_transactions cpt,wsh_delivery_details wdd,
csd_repairs cr
WHERE oel.line_id = p_order_line_id
AND oel.header_id = oeh.header_id
AND oeh.header_id = ced.order_header_id
AND oel.line_id = ced.order_line_id
AND ced.estimate_detail_id = cpt.estimate_detail_id
AND wdd.source_header_id = oeh.header_id
AND wdd.source_line_id = oel.line_id
AND wdd.source_code = 'OE'
AND cpt.action_type = 'SHIP'
AND cpt.repair_line_id = cr.repair_line_id;
SELECT serial_number
INTO l_serial_number
FROM(
SELECT fm_serial_number serial_number
FROM wsh_serial_numbers wsn,
wsh_delivery_details wdd
WHERE wdd.source_header_id = l_header_id
AND wdd.source_line_id = p_order_line_id
AND wdd.source_code = 'OE'
AND wdd.delivery_detail_id = wsn.delivery_detail_id
UNION ALL
SELECT wdd.serial_number serial_number
FROM wsh_delivery_details wdd
WHERE wdd.source_header_id = l_header_id
AND wdd.source_line_id = p_order_line_id
AND wdd.source_code = 'OE'
UNION ALL
SELECT msnt.fm_serial_number
FROM wsh_delivery_details wdd,
mtl_serial_numbers_temp msnt
WHERE wdd.source_header_id = l_header_id
AND wdd.source_line_id = p_order_line_id
AND wdd.source_code = 'OE'
AND wdd.transaction_temp_id = msnt.transaction_temp_id
) WHERE rownum < 2;
SELECT instance_id
INTO l_instance_id
FROM csi_item_instances
WHERE inventory_item_id = l_ship_inv_item_id
AND serial_number = l_serial_number;
SELECT service_duration_period_code, service_duration, service_starting_delay
INTO l_service_durn_period_code,l_service_duration,l_service_starting_delay
FROM mtl_system_items_b
WHERE inventory_item_id = l_service_item_id
AND organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
SELECT 'Y'
INTO l_dummy
FROM oks_reprocessing
WHERE order_id = l_header_id
AND order_line_id = x_line_tbl(1).line_id;
SELECT okh.contract_number,okhtl.short_description
INTO l_contract_number,l_contract_description
FROM oks_reprocessing okr,okc_k_headers_all_b okh,okc_k_headers_tl okhtl
WHERE okr.order_line_id = x_line_tbl(1).line_id
AND okr.order_id = l_header_id
AND okr.contract_id = okh.id
AND okh.id = okhtl.id
AND okhtl.LANGUAGE = userenv('LANG');
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG')
AND inventory_item_id = l_ship_inv_item_id;
SELECT unit_of_measure
INTO l_duration_desc
FROM mtl_units_of_measure_vl
WHERE uom_code = l_service_durn_period_code;
fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling csd_update_programs_pvt.log_activity');
csd_update_programs_pvt.log_activity
(p_api_version => 1.0,
p_commit => p_commit,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_activity_rec => l_activity_rec
);
fnd_log.string(fnd_log.level_statement, lc_mod_name, 'csd_update_programs_pvt.log_activity. Error msg is: '||x_msg_data);
SELECT 'Y'
INTO l_exists
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = p_relationship_type
AND party_id = p_sr_party_id
AND SYSDATE BETWEEN NVL(active_start_date,SYSDATE - 1) AND NVL(active_end_date, SYSDATE + 1);