[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM CS_transaction_types_b
WHERE transaction_type_id = p_transaction_type_id
AND create_cost_flag = 'Y';
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'CS_CHG_LINE_TYPE'
AND lookup_code = p_charge_line_type;
SELECT 'Y'
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT 1
FROM CS_ESTIMATE_DETAILS
WHERE estimate_detail_id = p_estimate_detail_id;
SELECT incident_id
FROM CS_INCIDENTS_ALL_B
WHERE incident_id =p_source_id;
SELECT repair_line_id
FROM CSD_REPAIRS
WHERE repair_line_id = p_source_id;
SELECT debrief_line_id
FROM csf_debrief_lines
WHERE debrief_line_id = p_source_id;
SELECT organization_id
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT 1
FROM mtl_system_items_b
WHERE organization_id =cs_std.get_item_valdn_orgzn_id -- modified by bkanimoz on 21-jan-2007
AND inventory_item_id =p_inventory_item_id;
SELECT To_number(hoi2.org_information3) OPERATING_UNIT
FROM hr_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2,
mtl_parameters mp
WHERE mp.organization_id = p_txn_inv_org
AND mp.organization_id = hou.organization_id
AND hou.organization_id = hoi1.organization_id
AND hoi1.org_information1 = 'INV'
AND hoi1.org_information2 = 'Y'
AND hoi1.org_information_context = 'CLASS'
AND hou.organization_id = hoi2.organization_id
AND hoi1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'Accounting Information';
SELECT mum.uom_code
FROM mtl_system_items_b msi,
mtl_units_of_measure_tl mum
WHERE msi.primary_unit_of_measure = mum.unit_of_measure
AND msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_org_id;
SELECT uom_code
FROM mtl_item_uoms_view
WHERE uom_code = p_uom_code
AND inventory_item_id = P_INV_ID
AND organization_id = p_org_id ;
SELECT currency_code
FROM gl_sets_of_books a,
hr_operating_units b
WHERE a.NAME = b.NAME
AND b.organization_id = p_org_id;
SELECT currency_code
FROM FND_CURRENCIES_TL
WHERE currency_code = p_currency_code
AND language = Userenv('lang');
SELECT 1
FROM cs_cost_details
WHERE cost_id = p_cost_id;
SELECT '1'
FROM cs_bus_process_txns
WHERE transaction_type_id = p_transaction_type_id;
SELECT '1'
FROM cs_txn_billing_oetxn_all
WHERE txn_billing_type_id = p_txn_billing_type_id;
SELECT transaction_type_id,line_order_category_code
FROM cs_transaction_types_b
WHERE transaction_type_id = p_transaction_type_id;
SELECT create_charge_flag,create_cost_flag
FROM cs_transaction_types_b
WHERE transaction_type_id = p_transaction_type_id;
select fnd_profile.value('CS_DEFAULT_LABOR_ITEM') from dual;
SELECT *
INTO x_cost_detail_rec
FROM CS_COST_DETAILS
WHERE COST_ID = p_cost_id
FOR UPDATE OF COST_ID NOWAIT ;
/*1.Disallow Request Update2.Disallow Charge 3.Disallow Charge Update defined in the SR Type Screen
1.Create Charge 2.Create Cost defined in the SAC setup screen
*/
PROCEDURE get_charge_flags_from_sr(p_api_name IN VARCHAR2,
p_incident_id IN NUMBER,
p_transaction_type_id IN NUMBER,
x_create_charge_flag OUT NOCOPY VARCHAR2,
x_create_cost_flag OUT NOCOPY VARCHAR2,
x_disallow_request_update OUT NOCOPY VARCHAR2,
x_disallow_new_charge OUT NOCOPY VARCHAR2,
x_disallow_charge_update OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY NUMBER
)IS
CURSOR SAC_FLAGS IS
SELECT NVL(create_charge_flag,'Y') ,
NVL(create_cost_flag,'N')
FROM cs_transaction_types_b
WHERE transaction_type_id = p_transaction_type_id;
SELECT nvl(csinst.disallow_new_charge, 'N'),
nvl(csinst.disallow_charge_update, 'N'),
nvl(csinst.disallow_request_update,'N') --new check for costing
FROM cs_incident_statuses csinst,
cs_incidents_all csinall
WHERE csinst.incident_status_id = csinall.incident_status_id
AND csinall.incident_id = p_incident_id;
INTO x_disallow_new_charge, x_disallow_charge_update,x_disallow_request_update;
SELECT incident_id ,
transaction_type_id ,
txn_billing_type_id ,
charge_line_type,
inventory_item_id,
quantity_required ,
unit_of_measure_code ,
currency_code ,
source_id ,
source_code ,
org_id ,
fnd_profile.value('CS_INV_VALIDATION_ORG') --Bug 7193528
INTO x_incident_id ,
x_transaction_type_id ,
x_txn_billing_type_id ,
x_charge_line_type ,
x_inventory_item_id ,
x_quantity ,
x_unit_of_measure_code ,
x_currency_code ,
x_source_id ,
x_source_code ,
x_org_id ,
x_txn_inv_org
FROM CS_ESTIMATE_DETAILS
WHERE ESTIMATE_DETAIL_ID = p_estimate_detail_id
FOR UPDATE OF ESTIMATE_DETAIL_ID NOWAIT ;
SELECT inv_organization_id, org_id
FROM cs_incidents_all_b cia
WHERE cia.incident_id = p_incident_id;
SELECT cost_id
INTO l_exist_cost_id
FROM CS_cost_details
WHERE cost_id =p_cost_id
FOR UPDATE OF COST_ID NOWAIT ;
SELECT ced.estimate_detail_id
INTO l_charge_exist
FROM CS_cost_details csd,cs_estimate_details ced
WHERE csd.cost_id =p_cost_id
AND ced.estimate_Detail_id = csd.estimate_Detail_id
FOR UPDATE OF COST_ID NOWAIT ;
SELECT ctbt.txn_billing_type_id
FROM mtl_system_items_kfv kfv,
cs_txn_billing_types ctbt
WHERE kfv.inventory_item_id = p_inventory_item_id
AND organization_id = cs_std.get_item_valdn_orgzn_id
AND ctbt.transaction_type_id = p_txn_type_id
AND ctbt.billing_type = kfv.material_billable_flag;
l_disallow_charge_update VARCHAR2(1);
l_disallow_request_update VARCHAR2(1);
SELECT cost_id
FROM cs_cost_details
WHERE estimate_Detail_id = p_estimate_detail_id;
x_disallow_request_update => l_disallow_request_update,
x_disallow_new_charge => l_disallow_new_charge,
x_disallow_charge_update => l_disallow_charge_update,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);
'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
'l_disallow_charge_update: '||l_disallow_charge_update
);
if l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
'Before calling the Insert_Row procedure'
);
CS_COST_DETAILS_PKG.Insert_Row
(
x_cost_id =>l_cost_id ,
p_incident_id =>lx_cost_rec.incident_id ,
p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
p_quantity =>lx_cost_rec.quantity ,
p_unit_cost =>l_unit_cost ,
p_extended_cost =>lx_cost_rec.extended_cost ,
p_override_ext_cost_flag =>l_override_ext_cost_flag ,
p_transaction_date => sysdate ,
p_source_id =>lx_cost_rec.source_id ,
p_source_code =>lx_cost_rec.source_code ,
p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
p_currency_code =>lx_cost_rec.currency_code ,
p_org_id =>l_cost_org_id ,
p_inventory_org_id =>l_cost_inv_org_id ,
p_attribute1 =>lx_cost_rec.attribute1 ,
p_attribute2 =>lx_cost_rec.attribute2 ,
p_attribute3 =>lx_cost_rec.attribute3 ,
p_attribute4 =>lx_cost_rec.attribute4 ,
p_attribute5 =>lx_cost_rec.attribute5 ,
p_attribute6 =>lx_cost_rec.attribute6 ,
p_attribute7 =>lx_cost_rec.attribute7 ,
p_attribute8 =>lx_cost_rec.attribute8 ,
p_attribute9 =>lx_cost_rec.attribute9 ,
p_attribute10 =>lx_cost_rec.attribute10 ,
p_attribute11 =>lx_cost_rec.attribute11 ,
p_attribute12 =>lx_cost_rec.attribute12 ,
p_attribute13 =>lx_cost_rec.attribute13 ,
p_attribute14 =>lx_cost_rec.attribute14 ,
p_attribute15 =>lx_cost_rec.attribute15 ,
p_last_update_date => sysdate ,
p_last_updated_by => FND_GLOBAL.USER_ID ,
p_last_update_login => FND_GLOBAL.LOGIN_ID ,
p_created_by => FND_GLOBAL.USER_ID ,
p_creation_date => sysdate ,
x_object_version_number => l_object_version_number
);
'After calling the Insert Row '
);
== Comments : API to Update cost details in cs_cost_details
== Modification History:
==
== Date Name Desc
== ---------- --------- ---------------------------------------------
== 15-DEC-2007 | BKANIMOZ | Created the procedure
========================================================================*/
PROCEDURE Update_Cost_Details
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_object_version_number OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := FND_GLOBAL.RESP_APPL_ID,
p_resp_id IN NUMBER := FND_GLOBAL.RESP_ID,
p_user_id IN NUMBER := FND_GLOBAL.USER_ID,
p_login_id IN NUMBER :=FND_GLOBAL.LOGIN_ID,
p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
p_Cost_Rec IN CS_Cost_Details_PUB.Cost_Rec_Type
) IS
l_api_version NUMBER := 1.0 ;
l_api_name VARCHAR2(100) := 'Update_Cost_Details' ;
l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_cost_details';
l_disallow_charge_update VARCHAR2(1);
l_disallow_request_update VARCHAR2(1);
SELECT unit_cost,
extended_cost,
override_ext_cost_flag
FROM cs_cost_details
WHERE cost_id = p_cost_id;
SELECT cost_id
FROM cs_cost_details
WHERE estimate_Detail_id = p_estimate_detail_id;
SAVEPOINT Update_Cost_Details_PVT;
and pass it to the cost update package. This is to make sure that the cost level details are in sync with charge line details.
For example say a Charge line has been Created for 10 Quantities of an Item.
Cost Line is also generated for this 10 quantity.
If while calling the Update Cost API , say the Quantity is passed as 20 for this cost line,
the cost API will not update the cost line with 20 quantities .
But if the Update Cost API is called with Extended Cost , then the Cost API will update the Quantity and Unit Cost
to NULL and extended cost to the value passed.
*/
IF p_cost_rec.cost_id is not null and p_cost_rec.cost_id <> fnd_api.g_miss_num
THEN
begin
select estimate_detail_id
into v_estimate_detail_id
from cs_cost_details csd
where cost_id =p_cost_rec.cost_id;
x_disallow_request_update => l_disallow_request_update,
x_disallow_new_charge => l_disallow_new_charge,
x_disallow_charge_update => l_disallow_charge_update,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);
'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
'l_disallow_charge_update: '||l_disallow_charge_update
);
if l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
'Before calling the Update_Row Procedure'
);
CS_COST_DETAILS_PKG.Update_Row
(
p_cost_id =>lx_cost_rec.cost_id ,
p_incident_id =>lx_cost_rec.incident_id ,
p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
--p_charge_line_type =>lx_cost_rec.charge_line_type ,
p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
p_quantity =>lx_cost_rec.quantity ,
p_unit_cost =>l_unit_cost ,
p_extended_cost =>lx_cost_rec.extended_cost ,
p_override_ext_cost_flag =>l_override_ext_cost_flag ,
p_transaction_date =>sysdate ,
p_source_id =>lx_cost_rec.source_id ,
p_source_code =>lx_cost_rec.source_code ,
p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
p_currency_code =>lx_cost_rec.currency_code ,
p_org_id =>l_cost_org_id ,
p_inventory_org_id =>l_cost_inv_org_id ,
p_attribute1 =>lx_cost_rec.attribute1 ,
p_attribute2 =>lx_cost_rec.attribute2 ,
p_attribute3 =>lx_cost_rec.attribute3 ,
p_attribute4 =>lx_cost_rec.attribute4 ,
p_attribute5 =>lx_cost_rec.attribute5 ,
p_attribute6 =>lx_cost_rec.attribute6 ,
p_attribute7 =>lx_cost_rec.attribute7 ,
p_attribute8 =>lx_cost_rec.attribute8 ,
p_attribute9 =>lx_cost_rec.attribute9 ,
p_attribute10 =>lx_cost_rec.attribute10 ,
p_attribute11 =>lx_cost_rec.attribute11 ,
p_attribute12 =>lx_cost_rec.attribute12 ,
p_attribute13 =>lx_cost_rec.attribute13 ,
p_attribute14 =>lx_cost_rec.attribute14 ,
p_attribute15 =>lx_cost_rec.attribute15 ,
p_last_update_date => sysdate ,
p_last_updated_by => FND_GLOBAL.USER_ID ,
p_last_update_login => FND_GLOBAL.LOGIN_ID ,
p_created_by => FND_GLOBAL.USER_ID ,
p_creation_date => sysdate ,
x_object_version_number => l_object_version_number
);
ROLLBACK TO Update_Cost_Details_PVT;
ROLLBACK TO Update_Cost_Details_PVT;
ROLLBACK TO Update_Cost_Details_PVT;
ROLLBACK TO Update_Cost_Details_PVT;
END Update_Cost_Details;
== Procedure name : delete_cost_details
== Comments : API to Update cost details in cs_cost_details
== Modification History:
==
== Date Name Desc
== ---------- --------- ---------------------------------------------
== 15-DEC-2007 | BKANIMOZ | Created the procedure
========================================================================*/
PROCEDURE Delete_Cost_Details
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_control IN VARCHAR2 ,
p_cost_id IN NUMBER := NULL
)IS
l_api_name CONSTANT VARCHAR2(100) := 'Delete_Cost_Details' ;
SAVEPOINT Delete_Cost_Details_PVT ;
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_DELETE');
delete from cs_cost_details where
cost_id = p_cost_id;
ROLLBACK TO Delete_Cost_Details_PVT;
ROLLBACK TO Delete_Cost_Details_PVT;
ROLLBACK TO Delete_Cost_Details_PVT;
ROLLBACK TO Delete_Cost_Details_PVT;
End Delete_Cost_Details;
== Comments : API to Update cost details in cs_cost_details
== Modification History:
==
== Date Name Desc
== ---------- --------- ---------------------------------------------
== 15-DEC-2007 | BKANIMOZ | Created the procedure
========================================================================*/
PROCEDURE VALIDATE_COST_DETAILS
(
p_api_name IN VARCHAR2,
pv_cost_rec IN CS_COST_DETAILS_PUB.COST_REC_TYPE,
p_validation_mode IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_cost_rec OUT NOCOPY CS_COST_DETAILS_PUB.COST_REC_TYPE,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_valid_check VARCHAR2(1);
l_disallow_charge_update VARCHAR2(1);
l_disallow_request_update VARCHAR2(1);
If the "Disallow Charge Update" flag is Yes (checked) then we should not allow updates for charges or costs.
However, for the scenario when Create Charge="N" and Create Cost="Y"
we should not validate the flags since the costs are not dependent on the charge creation.
In this case we should create the costs.
The costs should not be created only if the Disallow Request Update is Yes (checked).
*/
get_charge_flags_from_sr
(
p_api_name => p_api_name,
p_incident_id => x_cost_rec.incident_id,
p_transaction_type_id => pv_cost_rec.transaction_type_id,
x_create_charge_flag => l_create_charge_flag,
x_create_cost_flag => l_create_cost_flag,
x_disallow_request_update => l_disallow_request_update,
x_disallow_new_charge => l_disallow_new_charge,
x_disallow_charge_update => l_disallow_charge_update,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);
'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
'l_disallow_charge_update: '||l_disallow_charge_update
);
if l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
if l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
'Service:Allow Charge Operating Unit Update'
IF the profile is set to 'Y' then
1.If Org _id is passed then validate that org id and assign it to the out rec
2.If org id is not passed then assign the Multi Org Id
If Profile is set to 'N'
1.If Org Id is passed
1. Check this with the Multi Org id.If not equal Throw an error message
2.If equal assign it to the OUT record
2 If Org Id is not passed assign Multi Org id to the out record
*/
--Get the Multi Org ID
CS_Multiorg_PUB.Get_OrgId
(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
-- Fix bug 3236597 P_COMMIT => 'T',
P_COMMIT => 'F', -- Fix bug 3236597
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_INCIDENT_ID => pv_cost_rec.incident_id,
X_ORG_ID => l_org_id,
X_PROFILE => l_profile
);
-- l_profile = 'N'--Service:Allow Charge Operating Unit Update
if pv_cost_rec.org_id is not null then
-- dbms_output.put_line(' OU 1 l_profile : '||l_profile||pv_cost_rec.org_id||'-'||l_org_id);
SELECT 1
FROM fnd_user
WHERE user_id = p_user_id
AND TRUNC(SYSDATE) <= start_date
AND NVL(end_date, SYSDATE) >= SYSDATE;
SELECT 1
FROM fnd_logins
WHERE login_id = p_login_id
AND user_id = p_user_id;
SELECT 'x' into l_dummy
FROM fnd_user
WHERE user_id = p_user_id;
SELECT 'x' into l_dummy
FROM fnd_user
WHERE user_id = p_user_id
AND trunc(sysdate) BETWEEN trunc(nvl(start_date, sysdate))
AND trunc(nvl(end_date, sysdate));
SELECT 'x' into l_dummy
FROM fnd_logins
WHERE login_id = p_login_id
AND user_id = p_user_id;
DELETE /*+ index(e) */ cs_cost_details e
WHERE
incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
processing_set_id = p_processing_set_id
AND object_type = 'SR'
AND NVL(purge_status, 'S') = 'S'
);
|| l_row_count || ' rows deleted.'
);
select currency_code
into l_from_currency
from gl_sets_of_books
where name = p_ou;