[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inc.incident_id,
inc.incident_number,
trunc(inc.incident_date) incident_date,
inc.incident_type_id,
edt.estimate_detail_id,
hzp.party_name,
edt.bill_to_party_id,
edt.currency_code,
edt.list_price,
edt.quantity_required,
edt.selling_price,
nvl(edt.contract_discount_amount,0) contract_discount_amount,
edt.after_warranty_cost
FROM cs_estimate_details edt,
cs_incidents_all_b inc,
hz_parties hzp
WHERE edt.incident_id = inc.incident_id
AND edt.bill_to_party_id = hzp.party_id;
SELECT ced.incident_id,
ciab.incident_number,
ciab.incident_type_id,
cit.name incident_type,
nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
ciab.creation_date,
ced.currency_code,
sum(ced.after_warranty_cost) Total_Charges
FROM cs_incidents_all_b ciab,
cs_incident_types cit,
cs_estimate_details ced
WHERE ciab.incident_id = ced.incident_id
AND ciab.incident_type_id = cit.incident_type_id
AND ced.charge_line_type IN ('ACTUAL','IN_PROGRESS')
AND ced.incident_id = p_incident_id
GROUP BY ced.currency_code,ced.incident_id,ciab.incident_number,ciab.incident_date,ciab.creation_date,ciab.incident_type_id,cit.name;
SELECT sum(decode(edt.charge_line_type,'ESTIMATE',edt.after_warranty_cost, NULL)) Estimates,
sum(decode(edt.charge_line_type,'ESTIMATE', NULL, edt.after_warranty_cost)) Actuals,
edt.currency_code,
inc.incident_number,
inc.incident_date,
inc.incident_id
FROM cs_estimate_details edt,
cs_incidents_all_b inc
WHERE edt.incident_id = p_incident_id
AND inc.incident_id = edt.incident_id
GROUP BY currency_code,inc.incident_id,inc.incident_number,inc.incident_date;
SELECT restriction_id,
restriction_type,
condition,
value_object_id,
value_amount,
currency_code,
trunc(start_date_active) start_date_active,
trunc(end_date_active) end_date_active
FROM cs_chg_sub_restrictions
ORDER BY restriction_type;
SELECT nvl(edt.no_charge_flag,'N') chg_no_charge_flag,
nvl(tt.no_charge_flag,'N') txn_no_charge_flag
FROM cs_estimate_details edt,
cs_transaction_types tt
WHERE edt.estimate_detail_id = p_estimate_detail_id
AND tt.transaction_type_id = edt.transaction_type_id;
/* Querying the right select statement into the cursor variable */
IF l_auto_submit_mode = 'AS_AVAILABLE' THEN
IF NOT autosubmit_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN autosubmit_cv FOR SELECT ciab.incident_id,
ciab.incident_number,
nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
ciab.incident_type_id,
ced.estimate_detail_id,
hzp.party_name,
ced.bill_to_party_id,
ced.currency_code,
ced.list_price,
ced.quantity_required,
ced.selling_price,
nvl(ced.contract_discount_amount,0) contract_discount_amount,
ced.after_warranty_cost
FROM cs_incidents_all_b ciab,
cs_estimate_details ced,
hz_parties hzp
WHERE ciab.incident_id = ced.incident_id
AND ced.bill_to_party_id = hzp.party_id
AND ced.line_submitted = 'N'
AND ced.order_line_id IS NULL --bug 7692111
AND ced.charge_line_type = 'ACTUAL'
AND ced.source_code = 'SD'
AND ced.original_source_code = 'SR'
AND ced.interface_to_oe_flag = 'Y'
ORDER BY ciab.incident_id;
OPEN autosubmit_cv FOR SELECT ciab.incident_id,
ciab.incident_number,
nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
ciab.incident_type_id,
ced.estimate_detail_id,
hzp.party_name,
ced.bill_to_party_id,
ced.currency_code,
ced.list_price,
ced.quantity_required,
ced.selling_price,
nvl(ced.contract_discount_amount,0) contract_discount_amount,
ced.after_warranty_cost
FROM cs_incidents_all_b ciab,
cs_estimate_details ced,
hz_parties hzp
WHERE ciab.incident_id = ced.incident_id
AND ced.bill_to_party_id = hzp.party_id
AND ced.line_submitted = 'N'
AND ced.charge_line_type = 'ACTUAL'
AND ced.order_line_id IS NULL --bug 7692111
AND ced.source_code = 'SD'
AND ced.original_source_code = 'SR'
AND ced.interface_to_oe_flag = 'Y'
AND ciab.incident_id NOT IN (SELECT jtv.source_object_id
FROM jtf_tasks_vl jtv,
jtf_task_statuses_b jts
-- jtf_task_assignments jta,
-- csf_debrief_headers cdh
WHERE jtv.source_object_id = ciab.incident_id
-- AND jta.task_id = jtv.task_id
AND jtv.source_object_type_code = 'SR'
-- checking for closed tasks.
AND jtv.task_status_id = jts.task_status_id
AND nvl(jts.closed_flag,'N') = 'N')
--AND cdh.task_assignment_id = jta.task_assignment_id
--AND cdh.processed_flag = 'COMPLETED')
ORDER BY ciab.incident_id;
OPEN autosubmit_cv FOR SELECT ciab.incident_id,
ciab.incident_number,
nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
ciab.incident_type_id,
ced.estimate_detail_id,
hzp.party_name,
ced.bill_to_party_id,
ced.currency_code,
ced.list_price,
ced.quantity_required,
ced.selling_price,
nvl(ced.contract_discount_amount,0) contract_discount_amount,
ced.after_warranty_cost
FROM cs_incidents_all_b ciab,
cs_estimate_details ced,
hz_parties hzp
WHERE ciab.incident_id = ced.incident_id
AND ced.bill_to_party_id = hzp.party_id
AND ced.line_submitted = 'N'
AND ced.order_line_id IS NULL --bug 7647091
AND ced.charge_line_type = 'ACTUAL'
AND ced.source_code = 'SD'
AND ced.original_source_code = 'SR'
AND ced.interface_to_oe_flag = 'Y'
AND ciab.incident_id IN (SELECT jtv.source_object_id
FROM jtf_tasks_vl jtv,
jtf_task_statuses_b jts,
jtf_task_assignments jta,
csf_debrief_headers cdh
WHERE jtv.source_object_id = ciab.incident_id
AND jta.task_id = jtv.task_id
AND jtv.source_object_type_code = 'SR'
-- checking for closed tasks.
AND jtv.task_status_id = jts.task_status_id
AND nvl(jts.closed_flag,'N') = 'Y'
AND cdh.task_assignment_id = jta.task_assignment_id)
ORDER BY ciab.incident_id;
OPEN autosubmit_cv FOR SELECT ciab.incident_id,
ciab.incident_number,
nvl(trunc(ciab.incident_date),trunc(ciab.creation_date)) incident_date,
ciab.incident_type_id,
ced.estimate_detail_id,
hzp.party_name,
ced.bill_to_party_id,
ced.currency_code,
ced.list_price,
ced.quantity_required,
ced.selling_price,
nvl(ced.contract_discount_amount,0) contract_discount_amount,
ced.after_warranty_cost
FROM cs_incidents_all_b ciab,
hz_parties hzp,
cs_estimate_details ced
WHERE ciab.incident_id = ced.incident_id
AND ced.bill_to_party_id = hzp.party_id
AND ciab.status_flag = 'C'
AND ced.line_submitted = 'N'
AND ced.order_line_id IS NULL --bug 7692111
AND ced.charge_line_type = 'ACTUAL'
AND ced.source_code = 'SD'
AND ced.original_source_code = 'SR'
AND ced.interface_to_oe_flag = 'Y'
ORDER BY ciab.incident_id;
-- Calling Update_Charge_Details to clear existing messages before
-- logging new ones.
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
NULL,
NULL,
NULL,
'N',
'CLEAR',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_LINE_AMT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_LINE_AMT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_LINE_AMT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_LINE_AMT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_LINE_AMT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_BILL_TO_CT_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
-- Call Update_Charge_Lines.
Update_Charge_Lines(AutosubmitTAB(i).incident_id,
AutosubmitTAB(i).incident_number,
AutosubmitTab(i).estimate_detail_id,
AutosubmitTAB(i).currency_code,
'CS_CHG_AMT_OVERIDE_RESTRICTION',
'N',
'LINE',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_TOTAL_CHRG_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_TOTAL_CHRG_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_TOTAL_CHRG_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_TOTAL_CHRG_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_TOTAL_CHRG_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgSrTotTAB(k).incident_id,
ChgSrTotTAB(k).incident_number,
NULL,
RestrulesTab(j).currency_code,
'CS_CHG_SR_TYPE_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(ChgEstActTotTAB(n).incident_id,
ChgEstActTotTAB(n).incident_number,
NULL,
ChgEstActTotTAB(n).currency_code,
'CS_CHG_A_EXCEED_ET_RESTRICTION',
'N',
'HEADER',
x_return_status,
x_msg_data);
PROCEDURE Update_Charge_Lines(p_incident_id NUMBER,
p_incident_number VARCHAR2,
p_estimate_detail_id NUMBER,
p_currency_code VARCHAR2,
p_submit_restriction_message VARCHAR2,
p_line_submitted VARCHAR2,
p_restriction_type VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Number of Charge Lines
-- Only actual charge lines are stamped with the restriction message
-- Bug fix:3608980
CURSOR Charge_Line_Count(p_incident_id NUMBER,p_currency_code VARCHAR2) IS
SELECT estimate_detail_id
FROM cs_estimate_details
WHERE incident_id = p_incident_id
AND charge_line_type = 'ACTUAL'
AND source_code = 'SD'
AND original_source_code = 'SR'
AND currency_code = nvl(p_currency_code,currency_code)
AND line_submitted = 'N'
AND order_line_id IS NULL ; --bug 7692111
UPDATE CS_ESTIMATE_DETAILS
SET submit_restriction_message = (submit_restriction_message || lx_msg_data),
line_submitted = p_line_submitted,
last_update_date = sysdate, -- bug 8838622
last_update_login = fnd_global.login_id, -- bug 8838622
last_updated_by = fnd_global.user_id -- bug 8838622
WHERE Estimate_Detail_Id = p_estimate_detail_id
AND incident_id = p_incident_id;
UPDATE CS_ESTIMATE_DETAILS
SET submit_restriction_message = (submit_restriction_message || lx_msg_data),
line_submitted = p_line_submitted,
last_update_date = sysdate, -- bug 8838622
last_update_login = fnd_global.login_id, -- bug 8838622
last_updated_by = fnd_global.user_id -- bug 8838622
WHERE incident_id = p_incident_id
AND estimate_detail_id = chglnctTAB(t).estimate_detail_id;
UPDATE CS_ESTIMATE_DETAILS
SET submit_restriction_message = NULL,
line_submitted = p_line_submitted,
last_update_date = sysdate, -- bug 8838622
last_update_login = fnd_global.login_id, -- bug 8838622
last_updated_by = fnd_global.user_id -- bug 8838622
WHERE incident_id = p_incident_id
AND estimate_detail_id = chglnctTAB(t).estimate_detail_id; -- For bug 5697830
FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Update_Charge_Lines');
END Update_Charge_Lines;
select inc.customer_id,inc.account_id,inc.incident_number
from cs_incidents_all_b inc
where inc.incident_id = p_incident_id;
FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'CS_Chg_Auto_Sub_CON_PKG.Update_Charge_Lines');
l_deb_status CSF_DEBRIEF_UPDATE_PKG.debrief_status_tbl_type;
csf_debrief_update_pkg.debrief_status_check(
p_incident_id => p_incident_id,
p_api_version => 1.0,
p_validation_level => 0,
x_debrief_status => l_deb_status,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
Update_Charge_Lines(p_incident_id,
p_incident_number,
p_estimate_detail_id,
p_currency_code,
'CS_CHG_DEBRIEF_PENDING',
'N',
'HEADER',
x_return_status,
x_msg_data);
Update_Charge_Lines(p_incident_id,
p_incident_number,
p_estimate_detail_id,
p_currency_code,
'CS_CHG_DEBRIEF_ERRORS',
'N',
'HEADER',
x_return_status,
x_msg_data);