[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT APPLICATION_ID
INTO lv_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_NAME = p_application_name;
SELECT USER_ID
INTO lv_user_id
FROM FND_USER
WHERE USER_NAME = p_user_name;
SELECT default_abc_assignment_group
FROM MRP_PARAMETERS
WHERE ORGANIZATION_ID = ORG_ID;
SELECT NVL(cst.tl_resource,0)
+ NVL(cst.tl_overhead,0)
+ NVL(cst.tl_material_overhead,0)
+ NVL(cst.tl_outside_processing,0)
FROM cst_item_costs cst,
cst_cost_types cct
WHERE cct.costing_method_type = p_primary_cost_method
AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
AND cst.cost_type_id = cct.cost_type_id
AND cst.inventory_item_id = p_item_id
AND cst.organization_id = p_org_id;
SELECT NVL(cst.tl_resource,0)
+ NVL(cst.tl_overhead,0)
+ NVL(cst.tl_material_overhead,0)
+ NVL(cst.tl_outside_processing,0)
FROM cst_cost_types cct,
cst_item_costs cst
WHERE cst.cost_type_id = cct.default_cost_type_id
AND cct.costing_method_type = p_primary_cost_method
AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
AND cst.inventory_item_id = p_item_id
AND cst.organization_id = p_org_id;
SELECT NVL(cst.item_cost,0)
FROM cst_item_costs cst,
cst_cost_types cct
WHERE cct.costing_method_type = p_primary_cost_method
AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
AND cst.cost_type_id = cct.cost_type_id
AND cst.inventory_item_id = p_item_id
AND cst.organization_id = p_org_id;
SELECT NVL(cst.item_cost,0)
FROM cst_cost_types cct,
cst_item_costs cst
WHERE cst.cost_type_id = cct.default_cost_type_id
AND cct.costing_method_type = p_primary_cost_method
AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
AND cst.inventory_item_id = p_item_id
AND cst.organization_id = p_org_id;
SELECT round(list_price,NVL(spl.rounding_factor,2))
from oe_price_list_lines sopl,
oe_price_lists spl
where spl.price_list_id = arg_price_list_id
and sopl.price_list_id = spl.price_list_id
and sopl.inventory_item_id = arg_item_id
and nvl(sopl.unit_code,' ') = nvl(arg_uom_code,' ')
and sysdate between nvl(sopl.start_date_active, sysdate-1)
and nvl(sopl.end_date_active, sysdate+1);
select round(operand,-1*(nvl(qplh.rounding_factor,2)))
from qp_list_headers_b qplh,
qp_list_lines qpl,
qp_pricing_attributes qpa
where qplh.list_header_id = p_price_list_id
and qpl.list_header_id + 0 = qplh.list_header_id
and qpl.list_line_id = qpa.list_line_id
and qpa.product_attribute_context = 'ITEM'
and qpa.product_attribute = 'PRICING_ATTRIBUTE1'
and qpa.product_attr_value = p_item_id
and ( qpa.product_uom_code = p_uom_code
OR ( qpa.product_uom_code IS NULL
AND p_uom_code IS NULL))
and ( qpl.start_date_active <= sysdate
OR qpl.start_date_active IS NULL)
and ( qpl.end_date_active >= sysdate
OR qpl.end_date_active IS NULL);
SELECT ROUND(QPL.OPERAND,-1 * (NVL(QPLH.ROUNDING_FACTOR,2)))
FROM
QP_PRICING_ATTRIBUTES QPA ,
QP_LIST_LINES QPL,
QP_LIST_HEADERS_B QPLH
WHERE QPA.PRICING_PHASE_ID = 1
AND QPA.QUALIFICATION_IND = 4
AND QPA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = p_item_id
AND QPA.LIST_HEADER_ID = v_price_list_id
AND QPL.LIST_LINE_ID = QPA.LIST_LINE_ID
AND QPL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
AND QPA.PRODUCT_UOM_CODE = p_uom_code
AND (QPL.START_DATE_ACTIVE <= SYSDATE OR QPL.START_DATE_ACTIVE IS NULL )
AND (QPL.END_DATE_ACTIVE >= SYSDATE OR QPL.END_DATE_ACTIVE IS NULL) ;
SELECT ROUND(QPL.OPERAND,-1 * (NVL(QPLH.ROUNDING_FACTOR,2)))
FROM QP_PRICING_ATTRIBUTES QPA ,
QP_LIST_LINES QPL,
QP_LIST_HEADERS_B QPLH
WHERE QPA.PRICING_PHASE_ID = 1
AND QPA.QUALIFICATION_IND = 4
AND QPA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = p_item_id
AND QPA.LIST_HEADER_ID = v_price_list_id
AND QPL.LIST_LINE_ID = QPA.LIST_LINE_ID
AND QPL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
AND QPA.PRODUCT_UOM_CODE IS NULL
AND (QPL.START_DATE_ACTIVE <= SYSDATE OR QPL.START_DATE_ACTIVE IS NULL )
AND (QPL.END_DATE_ACTIVE >= SYSDATE OR QPL.END_DATE_ACTIVE IS NULL) ;
SELECT DOCUMENT_HEADER_ID , DOCUMENT_LINE_ID
FROM po_asl_documents
WHERE asl_id = p_asl_id
AND using_organization_id = -1
AND DOCUMENT_TYPE_CODE = 'BLANKET'; --??
SELECT NVL(pll.unit_price,0) unit_price
FROM po_lines_all pll
WHERE po_line_id = c_line_id
and po_header_id = c_header_id
and item_id = p_item_id
and (pll.cancel_date is null OR
pll.cancel_date >= SYSDATE)
and (pll.closed_date is null OR
pll.closed_date >= SYSDATE)
and (pll.expiration_date is null OR
pll.expiration_date >= SYSDATE);
v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
||' FROM MRP_SN_OPR_SEQS bos, '
||' MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) '
||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
v_sql_stmt2 := ' SELECT nvl(bos.reverse_cumulative_yield,1) '
||' FROM MRP_SN_OPR_SEQS bos, '
||' MRP_SN_OPR_NETWORKS bon '
||' WHERE bon.from_op_seq_id = :curr_op_seq_id '
||' AND bon.to_op_seq_id = bos.operation_sequence_id '
||' AND bon.transition_type = 1 ';
SELECT nvl(wsc.split,100)
INTO v_split
FROM wsm_Co_products wsc
WHERE wsc.bill_sequence_id is not null
AND wsc.split > 0
AND wsc.bill_Sequence_id = p_bill_seq_id;
v_sql_stmt1 := ' Select wdj.status_type, decode(wdj.status_type, 1, (select nvl(bos.reverse_cumulative_yield,1) from MRP_SN_LJ_OPRS bos where bos.wip_entity_id = wdj.wip_entity_id and upper(bos.Network_start_end) = ''S''),1) from '
||' MRP_SN_DSCR_JOBS wdj '
||' Where wdj.wip_entity_id = :p_wip_entity_id ';
v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
||' FROM MRP_SN_LJ_OPRS bos, '
||' MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) '
||' AND bos.wip_entity_id = wo.wip_entity_id '
||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
v_sql_stmt2 := ' SELECT nvl(bos.reverse_cumulative_yield,1) '
||' FROM MRP_SN_LJ_OPRS bos, '
||' MRP_SN_LJ_OPR_NWK bon '
||' WHERE bon.from_op_seq_id = :curr_op_seq_id '
||' AND bon.wip_entity_id = :p_wip_entity_id '
||' AND bon.wip_entity_id = bos.wip_entity_id '
||' AND bon.to_op_seq_id = bos.operation_sequence_id '
||' AND bon.transition_type = 1 ';
SELECT nvl(wsc.split,100)
INTO v_split
FROM wsm_Co_products wsc
WHERE wsc.bill_sequence_id is not null
AND wsc.split > 0
AND wsc.bill_Sequence_id = p_bill_seq_id;
SELECT nvl(wdj.common_routing_sequence_id,wdj.routing_reference_id)
into v_routing_seq_id
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_org_id;
select reverse_cumulative_yield
into lv_rev_cum
from bom_operation_sequences
where operation_sequence_id = v_operation_sequence_id;
select wsc.split
into v_split
from wsm_Co_products wsc
where wsc.bill_sequence_id is not null
and wsc.split > 0
and wsc.bill_Sequence_id = p_bill_seq_id;
SELECT bos.reverse_cumulative_yield
FROM wip_operations wo,
bom_operation_sequences bos
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_org_id
AND (wo.quantity_in_queue <> 0 or
wo.quantity_running <> 0 or
wo.quantity_waiting_to_move <> 0)
AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id;
SELECT nvl(wdj.common_routing_sequence_id,wdj.routing_reference_id)
into v_routing_seq_id
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_org_id;
select reverse_cumulative_yield
into lv_rev_cum
from bom_operation_sequences
where operation_sequence_id = v_operation_sequence_id;
SELECT conversion_rate
FROM mtl_uom_conversions muc
WHERE muc.uom_code = user_uom_code
AND muc.inventory_item_id = 0
AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE
AND EXISTS (SELECT 1 from mtl_units_of_measure_tl b
WHERE b.uom_code = prf_uom_code
AND b.uom_class = muc.uom_class);
SELECT conversion_rate
INTO lv_day_conversion_rate
FROM mtl_uom_conversions muc
WHERE muc.uom_code = lv_day_uom_code
AND muc.inventory_item_id = 0
AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE;
SELECT nvl(conversion_rate,1/24)
INTO lv_hr_conversion_rate
FROM mtl_uom_conversions muc
WHERE muc.uom_code = lv_hr_uom_code
AND muc.inventory_item_id = 0
AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE;
select uom_code
into lv_uom_code
from mtl_units_of_measure
where uom_class = v_yield_uom_class
and base_uom_flag = 'Y';
SELECT NVL(SUM(decode(basis_type,
BASIS_RESOURCE_VALUE, res_cost * NVL(rate_or_amount,0),
NVL(rate_or_amount,0)
)
),0)
INTO v_overhead
FROM cst_department_overheads
WHERE organization_id = org_id
AND department_id = dept_id
AND cost_type_id = CST_FROZEN
AND basis_type in (BASIS_RESOURCE_VALUE, BASIS_RESOURCE_UNITS)
AND overhead_id IN (SELECT overhead_id
FROM cst_resource_overheads res
WHERE res.organization_id = org_id
AND res.resource_id = res_id
AND cost_type_id = CST_FROZEN);
select nvl(fpi.patch_level, 'Not Available')
into lv_patch_level
from fnd_application_vl fav, fnd_product_installations fpi
where fav.application_id = fpi.application_id and
fpi.APPLICATION_ID in (702);
SELECT count(1) into lv_ahl_exists FROM all_tab_columns
WHERE (OWNER, TABLE_NAME, COLUMN_NAME) in ((l_applsys_schema_ahl, 'AHL_SCHEDULE_MATERIALS', 'COMPLETED_QUANTITY'));
v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
||' FROM MRP_SN_OPR_SEQS bos, '
||' MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) '
||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
v_sql_stmt1 := ' SELECT wo1.Wsm_Op_Seq_Num '
||' FROM MRP_SN_WOPRS wo, '
||' MRP_SN_WOPRS wo1 '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) '
||' AND wo1.wip_entity_id = wo.wip_entity_id '
||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = wo1.operation_sequence_id'
||' AND rownum=1';
v_sql_stmt := ' Select wdj.status_type from '
||' MRP_SN_DSCR_JOBS wdj '
||' Where wdj.wip_entity_id = :p_wip_entity_id ';
v_sql_stmt := ' SELECT wo.Operation_Seq_Num '
||' FROM MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id ';
v_sql_stmt := ' SELECT wo.Operation_Seq_Num '
||' FROM MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) ' ;
v_sql_stmt := ' SELECT nvl(wo.wsm_op_seq_num,50000) '
||' FROM MRP_SN_WOPRS wo '
||' WHERE wo.organization_id = :p_org_id '
||' AND wo.wip_entity_id = :p_wip_entity_id '
||' AND (wo.quantity_in_queue <> 0 OR '
||' wo.quantity_running <> 0 OR '
||' wo.quantity_waiting_to_move <> 0) ' ;
v_temp_sql_stmt := ' SELECT ROUTING_SEQUENCE_ID '
||' FROM MRP_SN_OPR_RTNS '
||' WHERE ASSEMBLY_ITEM_ID = :p_primary_item_id '
||' AND ORGANIZATION_ID = :p_org_id '
||' AND nvl(ALTERNATE_ROUTING_DESIGNATOR,''-1'') = :p_alt_ROUTING_DESIG '
||' AND COMMON_ROUTING_SEQUENCE_ID = :p_common_rout_seq_id ';
' select need_by_date '
||' from ( '
||' SELECT revision_num, '
||' need_by_date, '
||' quantity, '
||' RANK() OVER (PARTITION BY LINE_LOCATION_ID, PO_HEADER_ID,PO_LINE_ID '
||' order by revision_num) as seqnumber '
||' FROM po_line_locations_archive_all '
||' where PO_HEADER_ID = :p_po_header_id '
||' and po_line_id = :p_po_line_id '
||' and line_location_id = :p_po_line_location_id '
||' ) '
||' where seqnumber = 1 ';
' select quantity '
||' from ( '
||' SELECT revision_num, '
||' need_by_date, '
||' quantity, '
||' RANK() OVER (PARTITION BY LINE_LOCATION_ID, PO_HEADER_ID,PO_LINE_ID '
||' order by revision_num) as seqnumber '
||' FROM po_line_locations_archive_all '
||' where PO_HEADER_ID = :p_po_header_id '
||' and po_line_id = :p_po_line_id '
||' and line_location_id = :p_po_line_location_id '
||' ) '
||' where seqnumber = 1 ';
SELECT NVL(FND_PROFILE.VALUE('MSC_COST_TYPE'),
DECODE(cost_org.primary_cost_method,1,1,2,cost_org.AVG_RATES_COST_TYPE_ID,1))
FROM mtl_parameters org, mtl_parameters cost_org
WHERE org.cost_organization_id = cost_org.organization_id
and org.organization_id = p_org_id;
SELECT NVL(FND_PROFILE.VALUE('MSC_COST_TYPE'),1)
FROM dual;
FUNCTION MAP_REGION_TO_SITE (p_last_update_date in DATE)
RETURN NUMBER
IS
Cursor regions_update is
select max(LAST_UPDATE_DATE)
from WSH_REGIONS;
Cursor po_vendors_update(p_date DATE) is
select vendor_site_id
from PO_VENDOR_SITES_ALL
where last_update_date >= p_date and creation_date < p_date;
select vendor_site_id,country,state,city,zip
from PO_VENDOR_SITES_ALL;
select vendor_site_id,country,state,city,zip
from PO_VENDOR_SITES_ALL
where last_update_date >= p_date;
region_last_update DATE := NULL;
SELECT SYSDATE,
FND_GLOBAL.USER_ID
INTO v_current_date,
v_current_user
FROM DUAL;
OPEN regions_update;
FETCH regions_update into region_last_update;
CLOSE regions_update;
IF region_last_update is NULL THEN
Return(1);
IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN
-- delete mrp_region_sites completely.
DELETE FROM MRP_REGION_SITES;
OPEN po_vendors_update (p_last_update_date);
FETCH po_vendors_update BULK COLLECT
INTO l_vendor_site_tab1;
CLOSE po_vendors_update;
DELETE FROM MRP_REGION_SITES
where vendor_site_id =l_vendor_site_tab1(j);
MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in DELETEING Region to Sites');
OPEN new_vendor_sites(p_last_update_date);
INSERT INTO MRP_REGION_SITES(region_id,vendor_site_id, region_type, zone_level, last_update_date, last_updated_by, creation_date, created_by)
VALUES (l_region_id_tab(k), l_vendor_site_tab(i),l_region_type_tab(k),l_zone_level_tab(k), v_current_date, v_current_user, v_current_date, v_current_user);
select set_name into l_set_name
from oe_sets
where set_id=p_SHIP_SET_ID;
select set_name into l_set_name
from oe_sets
where set_id=p_ARRIVAL_SET_ID;
SELECT
SITE_USES_ALL.site_use_id SR_TP_SITE_ID
INTO
lv_cmro_ship_to
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_CUST_ACCOUNTS CUST_ACCT,
HR_ORGANIZATION_INFORMATION O,
HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND OTL.LANGUAGE = userenv('LANG')
AND SITE_USES_ALL.ORG_ID is NOT NULL
AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
SELECT
SITE_USES_ALL.site_use_id SR_TP_SITE_ID
INTO
lv_cmro_bill_to
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_CUST_ACCOUNTS CUST_ACCT,
HR_ORGANIZATION_INFORMATION O,
HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND OTL.LANGUAGE = userenv('LANG')
AND SITE_USES_ALL.ORG_ID is NOT NULL
AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
select nvl(fpi.patch_level, 'Not Available')
into lv_patch_level
from fnd_application_vl fav, fnd_product_installations fpi
where fav.application_id = fpi.application_id and
fpi.APPLICATION_ID in (665);
SELECT USER_ID
INTO l_user_id
FROM FND_USER
WHERE USER_NAME = p_user_name;
select 1
into lv_result
from fnd_responsibility resp
, FND_USER_RESP_GROUPS user_resp
where resp.responsibility_id = user_resp.responsibility_id
and resp.application_id = user_resp.responsibility_application_id
and resp.responsibility_key = 'APS_COLLECTIONS'
and user_resp.user_id = pUSERID
and rownum =1 ;
select 1
into lv_result
from fnd_responsibility resp,
FND_USER_RESP_GROUPS user_resp
where resp.responsibility_id = user_resp.responsibility_id
and resp.application_id = user_resp.responsibility_application_id
and resp.responsibility_key = 'APS_RELEASE'
and user_resp.user_id = pUSERID
and rownum =1 ;
select resp.responsibility_id, resp.application_id
into lv_resp_id, lv_application_id
from fnd_responsibility resp,
FND_USER_RESP_GROUPS user_resp
where resp.responsibility_id = user_resp.responsibility_id
and resp.application_id = user_resp.responsibility_application_id
and resp.responsibility_key = 'APS_COLLECTIONS'
and user_resp.user_id = pUSERID
and rownum =1 ;
select resp.responsibility_id, resp.application_id
into lv_resp_id, lv_application_id
from fnd_responsibility resp,
FND_USER_RESP_GROUPS user_resp
where resp.responsibility_id = user_resp.responsibility_id
and resp.application_id = user_resp.responsibility_application_id
and resp.responsibility_key = 'APS_RELEASE'
and user_resp.user_id = pUSERID
and rownum =1 ;