[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select Nvl(ctt.no_charge_flag,'N')
into l_no_chg_flag
from cs_txn_billing_types ctbt,
cs_transaction_types ctt
where ctbt.transaction_type_id = ctt.transaction_type_id
and ctbt.txn_billing_type_id = p_txn_billing_type_id;
IF (p_action not in ('CREATE','UPDATE','DELETE')) THEN
FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_ACTION');
select 'X'
into l_dummy
from cs_incidents_all_b
where incident_id = p_incident_id;
select 'X'
into l_dummy
from csd_repair_types_vl
where repair_type_id = p_repair_type_id
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active, sysdate);
select 'X'
into l_dummy
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select 'X'
into l_dummy
from csd_repair_order_groups
where repair_group_id = p_repair_group_id;
select 'x'
into l_dummy
from bom_calendar_dates
where calendar_date = p_date;
select 'X'
into l_dummy
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = l_org_id
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active, sysdate);
select 'X'
into l_dummy
from mtl_units_of_measure_vl
where uom_code = p_unit_of_measure
and sysdate between nvl(creation_date,sysdate)
and nvl(disable_date, sysdate);
select
incident_id
into x_incident_id
from csd_repairs
where repair_line_id = p_repair_line_id
and ((date_closed is null) OR (date_closed > sysdate));
select tbt.txn_billing_type_id
from cs_transaction_types_b tt,
cs_txn_billing_types tbt,
cs_billing_type_categories cbtc,
cs_txn_billing_oetxn_all tb,
oe_transaction_types_vl oeh,
oe_transaction_types_vl oel
where
tt.transaction_type_id = l_transaction_type_id
and nvl(tt.depot_repair_flag,'N')='Y'
and tt.transaction_type_id=tbt.transaction_type_id
and tbt.billing_type = l_billing_type
and tbt.txn_billing_type_id=tb.txn_billing_type_id
and tb.org_id = l_operating_unit /*Operating unit */
and tb.order_type_id=oeh.transaction_type_id
and tb.line_type_id=oel.transaction_type_id
and sysdate between nvl(cbtc.start_date_active, sysdate) and nvl(cbtc.end_date_active,sysdate)
and (sysdate) between nvl(tt.start_date_active,(sysdate)) and nvl(tt.end_date_active,(sysdate))
and cbtc.billing_type = tbt.billing_type
and nvl(cbtc.billing_category, '-999') ='M';
select MATERIAL_BILLABLE_FLAG
into l_billing_type
from mtl_system_items_B
where inventory_item_id = p_inventory_item_id
and organization_id = l_org_id
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active, sysdate);
select tbt.transaction_type_id
into l_transaction_type_id
from cs_txn_billing_types tbt
where txn_billing_type_id = p_txn_billing_type_id;
select src_return_reqd
,non_src_return_reqd
from csi_ib_txn_types a,
cs_txn_billing_types b
where a.cs_transaction_type_id = b.transaction_type_id
and b.txn_billing_type_id = p_txn_billing_type_id;
SELECT
crt.repair_type_ref,
cr.auto_process_rma,
crt.interface_to_om_flag,
crt.book_sales_order_flag,
cr.inventory_item_id,
cr.item_revision,
cr.contract_line_id,
cr.unit_of_measure,
crt.cps_txn_billing_type_id ,
crt.cpr_txn_billing_type_id ,
crt.ls_txn_billing_type_id ,
crt.lr_txn_billing_type_id ,
cr.price_list_header_id ,
crt.business_process_id,
cr.incident_id,
cr.default_po_num, -- swai bug fix 4535829
cr.inventory_org_id, -- inv_org_change Vijay, 3/20/06
cr.project_id,
cr.task_id,
cr.unit_number,
crt.third_rma_txn_billing_type_id,
crt.third_ship_txn_billing_type_id,
crt.third_party_flag
FROM csd_repairs cr,
csd_repair_types_vl crt
where cr.repair_type_id = crt.repair_type_id
and cr.repair_line_id = p_rep_line_id;
SELECT
revision
FROM mtl_item_revisions
where inventory_item_id = p_inv_item_id
and organization_id = p_org_id;
Select
serial_number_control_code,
comms_nl_trackable_flag
from mtl_system_items_kfv
where inventory_item_id = p_inv_item_id
and organization_id = p_org_id;
select chr_id from okc_k_lines_b
where id = p_contract_line_id;
select distinct
hp.party_site_id
from hz_party_sites_v hp,
hz_parties hz,
hz_cust_acct_sites_all hca,
hz_cust_site_uses_all hcsu
where hcsu.site_use_code = p_site_use_type
and hp.status = 'A'
and hcsu.status = 'A'
and hp.party_id = hz.party_id
and hp.party_id = p_party_id
and hca.party_site_id = hp.party_site_id
and hca.cust_account_id = p_account_id
and hcsu.cust_acct_site_id = hca.cust_acct_site_id
and hca.org_id = p_org_id
and hcsu.primary_flag = 'Y'
and rownum = 1;
SELECT lot_number
FROM csd_repairs_v
WHERE repair_line_id = p_repair_line_id;
x_prod_txn_tbl.delete;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
select party_id
into x_prod_txn_tbl(l_index).bill_to_party_id
from hz_cust_accounts
where cust_account_id = l_default_val_num;
x_prod_txn_tbl(l_index).last_update_date := sysdate ;
x_prod_txn_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
x_prod_txn_tbl(l_index).last_update_login := FND_GLOBAL.USER_ID;
select party_id
into x_prod_txn_tbl(l_index).bill_to_party_id
from hz_cust_accounts
where cust_account_id = l_default_val_num;
SELECT
quantity,
customer_product_id,
serial_number
FROM csd_repairs
where repair_line_id = p_rep_line_id;
select 'X'
into l_dummy
from csd_repairs
where repair_line_id = p_repair_line_id;
select 'X'
into l_dummy
from csd_repair_estimate
where repair_estimate_id = p_estimate_id;
select 'X'
into l_dummy
from csd_repair_estimate_lines
where repair_estimate_line_id = p_estimate_line_id;
select 'X'
into l_dummy
from fnd_lookups
where lookup_type = 'CSD_PROD_ACTION_TYPE'
and lookup_code = p_action_type;
select 'X'
into l_dummy
from fnd_lookups
where lookup_type = 'CSD_PRODUCT_ACTION_CODE'
and lookup_code = p_action_code;
select b.org_id
into l_org_id
from csd_repairs a,
cs_incidents_all_b b
where a.incident_id = b.incident_id
and a.repair_line_id = p_repair_line_id;
Select
sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
csd_repair_estimate_lines crel,
cs_estimate_details ced,
cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
and crel.estimate_detail_id = ced.estimate_detail_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and cre.repair_line_id = p_repair_line_id
and ctbt.billing_type = 'M';
Select
sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
csd_repair_estimate_lines crel,
cs_estimate_details ced,
cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
and crel.estimate_detail_id = ced.estimate_detail_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and cre.repair_line_id = p_repair_line_id
and ctbt.billing_type = 'L';
Select
sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
csd_repair_estimate_lines crel,
cs_estimate_details ced,
cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
and crel.estimate_detail_id = ced.estimate_detail_id
and ced.txn_billing_type_id = ctbt.txn_billing_type_id
and cre.repair_line_id = p_repair_line_id
and ctbt.billing_type = 'E';
Select
sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
csd_repair_estimate_lines crel,
cs_estimate_details ced
where cre.repair_estimate_id = crel.repair_estimate_id
and crel.estimate_detail_id = ced.estimate_detail_id
and cre.repair_line_id = p_repair_line_id ;
/* select b.business_process_id
into l_bus_process_id
from cs_incidents_all_b a,
cs_incident_types_b b
where a.incident_type_id = b.incident_type_id
and a.incident_id = p_incident_id;
select t.business_process_id
into l_bus_process_id
from csd_repairs r,
csd_repair_types_b t
where r.repair_line_id = p_repair_line_id
and r.repair_type_id = t.repair_type_id;
select SRC_RETURN_REQD
,NON_SRC_RETURN_REQD
from csi_ib_txn_types a,
cs_txn_billing_types b
where a.cs_transaction_type_id = b.transaction_type_id
and b.txn_billing_type_id = p_txn_billing_type_id;
select decode(bill_to_site_id, p_prod_txn_rec.invoice_to_org_id,bill_to_contact_id),
decode(ship_to_site_id,p_prod_txn_rec.ship_to_org_id,ship_to_contact_id)
into
x_charges_rec.bill_to_contact_id,
x_charges_rec.ship_to_contact_id
from cs_incidents_all_b
where incident_id = decode(p_prod_txn_rec.incident_id,FND_API.G_MISS_NUM,
(select incident_id from csd_repairs where repair_line_id = p_prod_txn_rec.repair_line_id),
p_prod_txn_rec.incident_id );
select b.line_type_id,
c.line_order_category_code
into x_line_type_id,
x_line_category_code
from cs_txn_billing_types a,
CS_TXN_BILLING_OETXN_ALL b,
cs_transaction_types_vl c
where a.txn_billing_type_id = b.txn_billing_type_id
and a.transaction_type_id = c.transaction_type_id
and a.txn_billing_type_id = p_txn_billing_type_id
and b.org_id = p_org_id;
Select count(*)
into l_rejected_quantity
from csd_repairs
where upper(approval_status) = 'R'
and repair_group_id = p_repair_group_id;
select 'X'
into l_dummy
from csd_product_transactions
where product_transaction_id = p_prod_txn_id;
SELECT
a.repair_mode,
a.repair_type_id,
b.repair_line_id,
c.quantity_required
FROM csd_repairs a,
csd_product_transactions b,
cs_estimate_details c
where a.repair_line_id = b.repair_line_id
and b.estimate_detail_id = c.estimate_detail_id
and b.product_transaction_id = p_prod_txn_id;
SELECT a.repair_mode,
a.repair_type_id,
b.repair_line_id,
c.quantity_required
INTO l_repair_mode,
l_repair_type_id,
l_repair_line_id,
l_prod_txn_qty
FROM csd_repairs a,
csd_product_transactions b,
cs_estimate_details c
where a.repair_line_id = b.repair_line_id
and b.estimate_detail_id = c.estimate_detail_id
and b.product_transaction_id = p_prod_txn_id ;
select sum(quantity_completed)
into l_qty_completed
from csd_repair_job_xref
where repair_line_id = l_repair_line_id;
Select count(*)
into l_count
from jtf_tasks_vl
where source_object_type_code = 'DR'
and source_object_id = l_repair_line_id
and task_status_id not in (7,8,9,11);
SELECT
abs(sum(b.quantity_required))
FROM csd_product_transactions a,
cs_estimate_details b
where a.estimate_detail_id = b.estimate_detail_id
and a.action_code = 'CUST_PROD'
and a.prod_txn_status <> 'CANCELLED'
and a.action_type = p_action_type
and a.repair_line_id = p_repair_line_id;
SELECT
quantity
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id;
select serial_number_control_code
into l_serial_code
from mtl_system_items
where inventory_item_id = p_inv_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;
SELECT select_id, select_name, from_table, where_clause
FROM jtf_objects_b
WHERE object_code = l_object_type_code;
l_id_column jtf_objects_b.select_id%TYPE;
l_name_column jtf_objects_b.select_name%TYPE;
select substr(p_object_type_code,1,3)
into l_code
from sys.dual;
SELECT DECODE (l_where_clause, NULL, ' ', l_where_clause || ' AND
')
INTO
l_where_clause
FROM dual;
sql_stmt := ' SELECT ' ||
l_name_column ||
' from ' ||
l_from_clause ||
' where ' ||
l_where_clause ||
l_id_column ||
' = :object_id ';
SELECT price_list_header_id
FROM csd_repair_types_b
WHERE repair_type_id = p_repair_type_id;
SELECT business_process_id
INTO l_bus_process_id
FROM csd_repair_types_b
WHERE repair_type_id = p_repair_type_id;
SELECT contract_service_id
INTO l_sr_contract_line_id
FROM cs_incidents
WHERE incident_id = p_incident_id;
SELECT 'Y' FROM QP_LIST_HEADERS_B
WHERE LIST_HEADER_ID = p_list_header_id
AND COMMENTS LIKE '%999';
SELECT 'Y' FROM QP_LIST_LINES_V
WHERE LIST_HEADER_ID =p_list_header_id
AND PRICE_BY_FORMULA_ID IS NOT NULL AND ROWNUM < 2;
SELECT MIC.CATEGORY_ID
FROM MTL_DEFAULT_CATEGORY_SETS MDCS,
mtl_item_categories MIC
WHERE MDCS.FUNCTIONAL_AREA_ID=7
AND MDCS.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
AND MIC.INVENTORY_ITEM_ID=p_item_id
and MIC.organization_id = cs_std.get_item_valdn_orgzn_Id;
Select multi_org_flag
into l_multiorg_enabled
from FND_PRODUCT_GROUPS;
SELECT gl.currency_code
INTO l_currency_code
FROM gl_sets_of_books gl, hr_operating_units hr
WHERE hr.set_of_books_id = gl.set_of_books_id
AND hr.organization_id= p_org_id;
SELECT currency_code
INTO l_pl_curr_code
FROM qp_list_headers_b
WHERE list_header_id = p_price_list_id;
Select max(ced.order_header_id)
into l_add_to_order_id
from csd_repairs dr,
cs_estimate_details ced,
oe_order_headers_all ooh,
oe_order_types_v oot,
cs_incidents_all_b sr -- swai: bug 5931926
where dr.repair_line_id = p_repair_line_id
and ced.incident_id = dr.incident_id
and ced.order_header_id is not null
and ooh.open_flag = 'Y'
and nvl(ooh.cancelled_flag,'N') = 'N'
and ooh.header_id = ced.order_header_id
and ooh.transactional_curr_code = dr.currency_code
and (ooh.cust_po_number = nvl(dr.default_po_num,ooh.cust_po_number)
or ooh.cust_po_number is null)
and oot.order_type_id = ooh.order_type_id
and oot.order_category_code in ('MIXED','RETURN')
and ced.interface_to_oe_flag = 'Y'
and ooh.sold_to_org_id = sr.account_id -- swai: bug 5931926
and sr.incident_id = dr.incident_id; -- swai: bug 5931926
Select max(ced.order_header_id)
into l_add_to_order_id
from csd_repairs dr,
cs_estimate_details ced,
oe_order_headers_all ooh,
oe_order_types_v oot,
cs_incidents_all_b sr -- swai: bug 5931926
where dr.repair_line_id = p_repair_line_id
and ced.incident_id = dr.incident_id
and ced.order_header_id is not null
and ooh.open_flag = 'Y'
and nvl(ooh.cancelled_flag,'N') = 'N'
and ooh.header_id = ced.order_header_id
and ooh.transactional_curr_code = dr.currency_code
and (ooh.cust_po_number = nvl(dr.default_po_num,ooh.cust_po_number)
or ooh.cust_po_number is null)
and oot.order_type_id = ooh.order_type_id
and oot.order_category_code in ('MIXED','ORDER')
and ced.interface_to_oe_flag = 'Y'
and ooh.sold_to_org_id = sr.account_id -- swai: bug 5931926
and sr.incident_id = dr.incident_id; -- swai: bug 5931926
SELECT negative_inv_receipt_code
INTO l_negative_inv_allowed
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT SERIAL_NUMBER_CONTROL_CODE
INTO l_SERIAL_CONTROL_CODE
from MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id AND ORGANIZATION_ID = p_org_id;
SELECT 'Y'
INTO l_exists
FROM mtl_onhand_quantities_detail
WHERE subinventory_code = p_sub_inv
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND rownum = 1;
SELECT 'Y'
INTO l_exists
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND current_subinventory_code = p_sub_inv
AND current_organization_id = p_org_id;
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_process_pvt.update_product_txn';
SELECT 'Y'
INTO l_order_hold
FROM oe_order_headers_all oh
WHERE oh.header_id = p_order_header_id
AND NVL(oh.booked_flag,'N') = 'N'
AND EXISTS
(
SELECT 'x'
FROM oe_order_holds_all oeh ,
oe_hold_sources_all ohs,
oe_hold_definitions od
WHERE oeh.header_id = oh.header_id
AND NVL(oeh.released_flag,'N') <> 'Y'
AND oeh.line_id IS NULL
AND oeh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = od.hold_id
AND od.activity_name IS NULL
);
SELECT holdexists
INTO l_order_hold
FROM (
SELECT 'Y' holdexists
FROM oe_order_headers_all oh
WHERE oh.header_id = p_order_header_id
AND EXISTS
(
SELECT 'x'
FROM oe_order_holds_all oeh ,
oe_hold_sources_all ohs,
oe_hold_definitions od
WHERE oeh.header_id = oh.header_id
AND NVL(oeh.released_flag,'N') <> 'Y'
AND oeh.line_id IS NULL
AND oeh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = od.hold_id
AND od.activity_name IS NULL
)
union all
SELECT 'Y' holdexists
FROM oe_order_headers_all oh
WHERE oh.header_id = p_order_header_id
AND EXISTS
(
SELECT 'x'
FROM oe_order_holds_all oeh ,
oe_hold_sources_all ohs,
oe_hold_definitions od
WHERE oeh.header_id = oh.header_id
AND NVL(oeh.released_flag,'N') <> 'Y'
AND oeh.line_id = p_order_line_id
AND oeh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = od.hold_id
AND od.activity_name IS NULL
) ) where rownum = 1;