[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select repair_line_id
bulk collect into l_repair_line_ids
from csd_repairs_interface
where group_id = p_group_id
and processing_phase = 2;
select cr.repair_number,cs.incident_number,cr.serial_number,csi.instance_number
bulk collect into l_repair_numbers,l_incident_numbers,l_serial_numbers,l_instance_numbers
from csd_repairs cr,
cs_incidents_all_b cs,
csi_item_instances csi
where cr.incident_id = cs.incident_id
and cr.repair_line_id in
(select * from table(cast(l_repair_line_ids as JTF_NUMBER_TABLE)))
and cr.customer_product_id = csi.instance_id(+);
SELECT loc.location_id
INTO l_destn_loc_tbl(l_dest_org)
FROM hr_locations_all loc,
po_location_associations_all ploc,
--ra_customers cust -- bug#12610896, subhat
hz_cust_accounts cust
WHERE loc.inventory_organization_id = l_dest_org
AND loc.location_id = ploc.location_id
AND ploc.org_id = l_dest_ou
AND ploc.customer_id = cust.cust_account_id -- cust.customer_id, bug#12610896, subhat
AND cust.customer_type = 'I'
AND rownum = 1;
'Forall insert into csd_product_transactions. Number of product txn lines = '||l_prod_txn_tbl.COUNT);
INSERT INTO csd_product_transactions
(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
)
VALUES
(csd_product_transactions_s1.nextval,
l_prod_txn_tbl(i).repair_line_id,
l_prod_txn_tbl(i).exp_quantity,
l_prod_txn_tbl(i).inventory_item_id,
l_prod_txn_tbl(i).ship_from_ou,
l_prod_txn_tbl(i).ship_from_org,
l_prod_txn_tbl(i).rcv_into_ou,
l_prod_txn_tbl(i).rcv_into_org,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
1
)
RETURNING product_transaction_id BULK COLLECT INTO l_product_txn_ids;
-- update the values for the other product transaction lines also.
FOR i IN (SELECT req_header_id,req_line_id,order_header_id,order_line_id,inventory_item_id,delivery_detail_id
FROM csd_product_transactions
WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(l_ord_product_txn_ids AS JTF_NUMBER_TABLE)))
)
LOOP
UPDATE csd_product_transactions SET req_header_id = i.req_header_id,req_line_id = i.req_line_id,
order_header_id = i.order_header_id,order_line_id = i.order_line_id,delivery_detail_id = i.delivery_detail_id
WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(l_product_txn_ids AS JTF_NUMBER_TABLE)))
AND req_header_id IS NULL;
select cqt.threshold_qty
into l_threshold
from csd_quality_thresholds_b cqt
where cqt.inventory_org_id = p_organization_id
and cqt.inventory_item_id = p_inventory_item_id
and nvl(cqt.item_revision,'-1') = nvl(p_item_revision,'-1');
select cqt.threshold_qty
into l_threshold
from csd_quality_thresholds_b cqt,
mtl_item_categories mic
where cqt.inventory_org_id = p_organization_id
and cqt.item_category_id = mic.category_id
and mic.organization_id = p_organization_id
and mic.inventory_item_id = p_inventory_item_id;
SELECT to_number('1')
into l_aging_threshold
FROM csd_aging_thresholds_b cat,
csd_repairs cr ,
(select * from csd_repair_history
where repair_line_id = p_repair_line_id and event_code = 'SC') crh ,
csd_flow_statuses_b cfs
WHERE cat.inventory_item_id = p_inventory_item_id
AND cat.inventory_org_id = p_organization_id
AND cr.repair_line_id = p_repair_line_id
AND cr.repair_type_id = nvl(cat.repair_type_id,cr.repair_type_id)
AND cr.inventory_item_id = cat.inventory_item_id
AND decode(p_revision,null,'1',cat.item_revision) = nvl(p_revision,'1')
AND cr.repair_line_id = crh.repair_line_id(+)
AND cr.flow_status_id = cfs.flow_status_id
AND cfs.flow_status_code = DECODE(crh.event_code,'SC',crh.paramc1,cfs.flow_status_code)
AND DECODE(cat.flow_status_id, NULL,
(SELECT SYSDATE-creation_date
FROM csd_repairs
WHERE repair_line_id = cr.repair_line_id
), DECODE(crh.paramc1, NULL,
(SELECT SYSDATE-creation_date
FROM csd_repairs
WHERE repair_line_id = cr.repair_line_id
AND flow_status_id = cat.flow_status_id -- bug#8972971, subhat
),
(SELECT SYSDATE - crh1.creation_date
FROM csd_repair_history crh1
WHERE crh1.repair_line_id = crh.repair_line_id
AND crh1.paramc1 = crh.paramc1
AND rownum = 1
) ) ) > cat.threshold_days ;
/*select threshold_days
into l_aging_threshold
from csd_aging_thresholds_b
where inventory_org_id = p_organization_id
and p_inventory_item_id =
( select mic.inventory_item_id
from mtl_item_categories mic
where mic.organization_id = p_organization_id
and mic.category_id = item_category_id
and mic.inventory_item_id = p_inventory_item_id
);*/
SELECT to_number('1')
INTO l_aging_threshold
FROM csd_aging_thresholds_b cat,
csd_repairs cr ,
(select * from csd_repair_history
where repair_line_id = p_repair_line_id and event_code = 'SC') crh,
csd_flow_statuses_b cfs
WHERE cat.inventory_org_id = p_organization_id
AND p_inventory_item_id in (
select mic.inventory_item_id
from mtl_item_categories mic
where mic.organization_id = p_organization_id
and mic.category_id = cat.item_category_id
and mic.inventory_item_id = p_inventory_item_id
)
AND cr.repair_line_id = p_repair_line_id
AND cr.repair_type_id = nvl(cat.repair_type_id,cr.repair_type_id)
AND decode(p_revision,null,'1',cat.item_revision) = nvl(p_revision,'1')
AND cr.repair_line_id = crh.repair_line_id(+)
AND cr.flow_status_id = cfs.flow_status_id
AND cfs.flow_status_code = DECODE(crh.event_code,'SC',crh.paramc1,cfs.flow_status_code)
AND DECODE(cat.flow_status_id, NULL,
(SELECT SYSDATE-creation_date
FROM csd_repairs
WHERE repair_line_id = cr.repair_line_id
), DECODE(crh.paramc1, NULL,
(SELECT SYSDATE-creation_date
FROM csd_repairs
WHERE repair_line_id = cr.repair_line_id
AND flow_status_id = cat.flow_status_id -- bug#8972971, subhat
),
(SELECT SYSDATE - crh1.creation_date
FROM csd_repair_history crh1
WHERE crh1.repair_line_id = crh.repair_line_id
AND crh1.paramc1 = crh.paramc1
AND rownum = 1
) ) ) > cat.threshold_days ;
/* Procedure Name: mass_update_repair_orders */
/* Description: This procedure provides a utility to mass update the repair */
/* orders. The procedure treats each logical action as a seperate*/
/* transaction. */
/******************************************************************************/
PROCEDURE mass_update_repair_orders(p_api_version IN NUMBER DEFAULT 1.0,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_repair_line_ids IN JTF_NUMBER_TABLE,
p_from_ro_status IN JTF_NUMBER_TABLE,
p_orig_ro_type_ids IN JTF_NUMBER_TABLE,
p_ro_obj_ver_nos IN JTF_NUMBER_TABLE,
p_to_ro_status IN NUMBER DEFAULT NULL,
p_ro_type_id IN NUMBER DEFAULT NULL,
p_ro_owner_id IN NUMBER DEFAULT NULL,
p_ro_org_id IN NUMBER DEFAULT NULL,
p_ro_priority_id IN NUMBER DEFAULT NULL,
p_ro_escalation_code IN VARCHAR2 DEFAULT NULL,
p_note_type IN VARCHAR2 DEFAULT NULL,
p_note_visibility IN VARCHAR2 DEFAULT NULL,
p_attach_title IN VARCHAR2 DEFAULT NULL,
p_attach_descr IN VARCHAR2 DEFAULT NULL,
p_attach_cat_id IN NUMBER DEFAULT NULL,
p_attach_type IN VARCHAR2 DEFAULT NULL,
p_attach_file IN BLOB DEFAULT NULL,
p_attach_url IN VARCHAR2 DEFAULT NULL,
p_attach_text IN VARCHAR2 DEFAULT NULL,
p_file_name IN VARCHAR2 DEFAULT NULL,
p_content_type IN VARCHAR2 DEFAULT NULL,
p_note_text IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
l_error_messages_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_1000,
p_ro_promise_date IN DATE DEFAULT NULL
)
IS
l_api_version_number constant number := 1.0;
l_api_name constant varchar2(100) := 'CSD_REPAIR_MANAGER_UTIL.MASS_UPDATE_REPAIR_ORDERS';
l_update_ros boolean default false;
savepoint mass_update_repair_orders;
'Begin mass update API');
if NOT csd_repairs_pvt.is_flwsts_update_allowed
(p_repair_type_id => p_orig_ro_type_ids(j),
p_from_flow_status_id => p_from_ro_status(j) ,
p_to_flow_status_id => p_to_ro_status,
p_responsibility_id => FND_GLOBAL.RESP_ID)
then
-- flow status update is not allowed.
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_NO_ACCESS');
-- update the flow status.
begin
savepoint update_flow_status;
'Call update flow status API to update the status');
csd_repairs_pvt.update_flow_status
( p_api_version => 1.0,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
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_ids(j),
p_repair_type_id => p_orig_ro_type_ids(j),
p_from_flow_Status_id => p_from_ro_status(j),
p_to_flow_status_id => p_to_ro_status,
p_reason_code => null,
p_comments => null,
p_check_access_flag => 'Y',
p_object_version_number => l_repobj_ver(j),
x_object_version_number => x_object_version_number
);
'Error in update flow status API:'||x_msg_data);
rollback to update_flow_status;
rollback to update_flow_status;
-- repair type update
if p_ro_type_id is not null then
if NOT csd_repairs_pvt.is_rt_update_allowed
(p_from_repair_type_id => p_orig_ro_type_ids(j),
p_to_repair_type_id => p_ro_type_id,
p_common_flow_status_id => p_from_ro_status(j),
p_responsibility_id => fnd_global.resp_id)
then
-- repair type update is not allowed. log a message.
Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_NO_ACCESS');
-- update the repair type.
begin
savepoint update_repair_type;
'Call update repair type API to update the status');
csd_repairs_pvt.update_repair_type
( p_api_version => 1.0,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
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_ids(j),
p_from_repair_type_id => p_orig_ro_type_ids(j),
p_to_repair_type_id => p_ro_type_id,
p_common_flow_status_id => p_from_ro_status(j),
p_reason_code => null,
p_object_Version_number => l_repobj_ver(j),
x_object_version_number => x_object_version_number);
'Error in update repair type API:'||x_msg_data||' message count'||x_msg_count);
rollback to update_repair_type;
rollback to update_flow_status;
end if; -- end ro type update.
-- update the remaining fields.
-- these can be updated by calling update_repair_order API.
--l_repln_rec_tbl(j).repair_line_id := p_repair_line_ids(j);
l_update_ros := true;
l_update_ros := true;
l_update_ros := true;
l_update_ros := true;
-- support promise date update.
if p_ro_promise_date is not null then
l_repln_rec_tbl(j).promise_date := p_ro_promise_date;
l_update_ros := true;
if l_update_ros then
-- update all the repair orders.
for k in 1 ..l_repln_rec_tbl.count
loop
begin
savepoint update_repair_order;
'Call update repair order API');
csd_repairs_pvt.update_repair_order
(p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
p_repair_line_id => p_repair_line_ids(k),
p_repln_rec => l_repln_rec_tbl(k),
x_return_status => x_return_Status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Return status from update_repair_order '||x_return_status);
rollback to update_repair_order;
'Creating the notes for the selected repair orders');
rollback to mass_update_repair_orders;
rollback to mass_update_repair_orders;
END mass_update_repair_orders;
/* repair orders. The API gets called from mass_update_repair_orders */
/******************************************************************************/
PROCEDURE mass_create_attachments(p_api_version IN NUMBER DEFAULT 1.0,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_repair_line_ids IN JTF_NUMBER_TABLE,
p_attach_type IN VARCHAR2,
p_attach_cat_id IN NUMBER,
p_attach_descr IN VARCHAR2 DEFAULT NULL,
p_attach_title IN VARCHAR2,
p_file_input IN BLOB DEFAULT NULL,
p_url IN VARCHAR2 DEFAULT NULL,
p_text IN VARCHAR2 DEFAULT NULL,
p_file_name IN VARCHAR2 DEFAULT NULL,
p_content_type IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
lc_api_name constant varchar(100) := 'CSD_REPAIR_MANAGER_UTIL.MASS_CREATE_ATTACHMENTS';
'Before calling Fnd_Documents_Pkg.Insert_Row');
Fnd_Documents_Pkg.Insert_Row
(X_Rowid => x_rowid_tmp,
X_document_id => x_document_id_tmp,
X_creation_date => sysdate,
X_created_by => l_user_id,
X_last_update_date => sysdate,
X_last_updated_by => l_user_id,
X_last_update_login => l_user_id,
X_datatype_id => l_data_type,
X_category_id => p_attach_cat_id,
X_security_type => 4,
X_publish_flag => 'Y',
X_usage_type => 'O',
X_language => userenv('LANG'),
x_url => p_url,
x_title => p_attach_title,
x_description => p_attach_descr,
x_media_id => x_media_id,
x_file_name => p_file_name);
'Inserting attachments of type short text');
insert into fnd_documents_short_text
( media_id,
short_text
)
values
( x_media_id,
p_text
);
-- than 10 characters. To avoid error during insert we substr it to 10 characters.
if length(l_file_format) > 10 then
l_file_format := substr(l_file_format,0,10);
select value into l_oracle_charset
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
'Inserting attachments of type file into fnd_lobs table');
insert into fnd_lobs
( file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format
)
values
( x_media_id,
p_file_name,
p_content_type,
sysdate,
null,
null,
null,
p_file_input,
userenv('LANG'),
l_oracle_charset,
l_file_format
);
INSERT INTO fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
program_application_id,
program_id,
program_update_date,
request_id,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15,
column1,
category_id)
(select
fnd_attached_documents_s.NEXTVAL,
x_document_id_tmp,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
5,
--'Csd_RepairLineId',
'CSD_REPAIRS', -- bug#9908690, subhat
p_repair_line_ids(i),
null,
null,
null,
null,
'Y',
FND_GLOBAL.prog_appl_id,
null,
sysdate,
null,
null,
null,null,
null,null,
null,null,
null,null,
null,null,
null,null,
null,null,
null,
null,
p_attach_cat_id
from dual);
update csd_repairs_interface set transaction_id = csd_repairs_interface_s2.nextval
where group_id = p_group_id;
select *
bulk collect into l_interface_tbl
from csd_repairs_interface
where group_id = p_group_id
and processing_phase = 1;
update csd_repairs_interface set processing_phase = 2
where group_id = p_group_id;
select 'X'
into l_dummy
from cs_incidents_all_b
where incident_number = l_interface_tbl(i).sr_incident_id;
update csd_repairs_interface set processing_phase = 3
where transaction_id = l_interface_tbl(i).transaction_id;
update csd_repairs_interface set processing_phase = 3
where transaction_id = l_interface_tbl(i).transaction_id;
select primary_uom_code
into l_repln_tbl(i).unit_of_measure
from mtl_system_items_b
where inventory_item_id = l_repln_tbl(i).inventory_item_id
and organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
update csd_repairs_interface set processing_phase = 3
where transaction_id = l_interface_tbl(i).transaction_id;
update csd_repairs_interface set processing_phase = 4
where transaction_id = l_interface_tbl(i).transaction_id;
update csd_repairs_interface set incident_id = x_incident_id,repair_line_id = x_repair_line_id
where transaction_id = l_interface_tbl(i).transaction_id;
-- delete all successfully processed records.
delete from csd_repairs_interface where
group_id = p_group_id and processing_phase = 2;
select party_type
into l_service_request_rec.caller_type
from hz_parties
where party_id = p_sr_rec.sr_party_id;
Select hpu.party_site_use_id
into l_service_request_rec.bill_to_site_use_id
from hz_party_sites hps,
hz_party_site_uses hpu
where
hps.party_id = p_sr_rec.sr_party_id
and hps.party_site_id = hpu.party_site_id
and hpu.site_use_type = 'BILL_TO'
and hpu.primary_per_type = 'Y'
and rownum < 2;
Select hpu.party_site_use_id
into l_service_request_rec.ship_to_site_use_id
from hz_party_sites hps,
hz_party_site_uses hpu
where
hps.party_id = p_sr_rec.sr_party_id
and hps.party_site_id = hpu.party_site_id
and hpu.site_use_type = 'SHIP_TO'
and hpu.primary_per_type = 'Y'
and rownum < 2;
select revision
into p_repln_rec.item_revision
from mtl_serial_numbers
where serial_number = p_repln_rec.serial_number
and inventory_item_id = p_repln_rec.inventory_item_id;
select instance_id
into l_instance_id
from csi_item_instances
where serial_number = p_repln_rec.serial_number
and inventory_item_id = p_repln_rec.inventory_item_id;
update_external_reference(p_external_reference,
l_instance_id,
x_return_status,
x_msg_count,
x_msg_data);
select instance_id
into l_instance_id
from csi_item_instances
where inventory_item_id = p_repln_rec.inventory_item_id
and external_reference = p_external_reference;
update_external_reference(p_external_reference,
l_instance_id,
x_return_status,
x_msg_count,
x_msg_data);
Select ship_to_site_use_id,
contract_id,
bill_to_site_use_id,
problem_code,
incident_severity_id,
contract_service_id
into g_install_site_use_id,
g_contract_id,
g_bill_to_use_id,
g_problem_code,
g_severity_id,
g_contract_service_id
from cs_incidents_all_b
where incident_id = x_incident_id;
SELECT relationship_type_code
INTO l_relationship
FROM csi_i_parties
WHERE instance_id = l_instance_id
AND party_id = p_sr_rec.sr_party_id
AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE - 1) AND NVL(active_end_date,SYSDATE+1)
AND relationship_type_code = NVL(l_crt_inst_party_relation,relationship_type_code)
AND ROWNUM < 2;
Select ship_to_site_use_id,
contract_id,
bill_to_site_use_id,
problem_code,
incident_severity_id,
contract_service_id
into g_install_site_use_id,
g_contract_id,
g_bill_to_use_id,
g_problem_code,
g_severity_id,
g_contract_service_id
from cs_incidents_all_b
where incident_id = x_incident_id;
select business_process_id,
auto_process_rma,
repair_mode
into l_business_process_id,
l_auto_process_rma,
l_repair_mode
from csd_repair_types_b
where repair_type_id = l_repair_type_id;
procedure update_external_reference
(p_external_reference in varchar2,
p_instance_id in number,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2)
is
lc_api_name constant varchar2(100) := 'csd_repair_manager_util.update_external_reference';
select object_version_number
into l_object_version_number
from csi_item_instances
where instance_id = p_instance_id;
-- call the update item instance API.
If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
fnd_log.STRING (fnd_log.level_procedure,
lc_api_name,
'Before calling csi_item_instance_pub.update_item_instance');
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
);
'Update external ref err '||x_msg_count||' Message '||x_msg_data);
end update_external_reference;