[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 PVS.vendor_site_id,PVS.country,PVS.state,PVS.city,PVS.zip,nvl(HL.language,userenv('LANG')) lang
from AP_SUPPLIER_SITES_ALL PVS ,HZ_LOCATIONS HL
WHERE PVS.LOCATION_ID = HL.LOCATION_ID(+);
select PVS.vendor_site_id,PVS.country,PVS.state,PVS.city,PVS.zip,nvl(HL.language,userenv('LANG')) lang
from AP_SUPPLIER_SITES_ALL PVS ,HZ_LOCATIONS HL
where PVS.last_update_date >= p_date
AND PVS.LOCATION_ID = HL.LOCATION_ID(+);
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);
MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Val inserted into MRP_REGION_SITES for vendor_site_id: '|| l_vendor_site_tab(i) );
wo_last_update DATE := NULL;
SELECT SYSDATE,
FND_GLOBAL.USER_ID
INTO v_current_date,
v_current_user
FROM DUAL;
'select WIP_ENTITY_ID, ORGANIZATION_ID ' ||
' from AHL_WORKORDERS_SN ' ||
' where ITEM_ALTERNATES_EXIST = ''Y''';
L_ALT_ITEM_TBL.DELETE; -- need to delete the table L_ALT_ITEM_TBL
INSERT INTO MRP_WO_SUB_COMP
(WIP_ENTITY_ID,
ORGANIZATION_ID,
PRIMARY_COMPONENT_ID,
ALTERNATE_COMPONENT_ID,
OP_SEQ_NUM,
RATIO,
RANK,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES
(l_wip_entity_id_tab(i),
l_wip_entity_id_tab1(i),
L_ALT_ITEM_TBL(k).PRIMARY_ITEM_ID,
L_ALT_ITEM_TBL(k).ALTERNATE_ITEM_ID,
L_ALT_ITEM_TBL(k).OPERATION_SEQUENCE,
1,
L_ALT_ITEM_TBL(k).PRIORITY,
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 zone,parent_region_id
into p_zone,p_region_id
from (
select wrl.region_id, z.parent_region_id ,a.zone, wrl.location_id,l.location_id,
wrl.region_type
from
wsh_region_locations wrl,
wsh_zone_regions z,
wsh_regions_v a ,
HZ_PARTY_SITES l
where wrl.region_id = z.region_id
and z.parent_region_id = a.region_id
and a.region_type = 10
and wrl.location_id = l.location_id
and l.party_site_id = p_location_id
order by l.location_id,wrl.region_type desc, a.zone_level asc)
where rownum =1 ;
SELECT
mib.Instance_id ITEM_INSTANCE_ID,
mib.instance_number,
mib.inventory_item_id ,
mib.quantity QUANTITY,
mib.install_date INSTALL_DATE,
mib.active_end_date EXPIRATION_DATE,
nvl(mib.install_location_id,mib.location_id) loc_id,
nvl(mib.install_location_type_code,mib.location_type_code) LOC_TYPE_CODE
FROM
CSI_ITEM_INSTANCES mib
WHERE
mib.accounting_class_code ='CUST_PROD'
and mib.instance_usage_code = 'OUT_OF_ENTERPRISE'
and mib.install_date is not null
and( mib.active_end_date is null or mib.active_end_date >=orig_st_date)
and mib.owner_party_source_table = 'HZ_PARTIES'
and nvl(mib.install_location_type_code,mib.location_type_code) = 'HZ_PARTY_SITES';
lv_grace_stmt := 'select GRACE_PERIOD,GRACE_DURATION from oks_k_headers_b where chr_id = ' || x_output_contracts(i).contract_id;
insert into MSC_IB_CONTRACTS_TEMP(
item_instance_id,
inventory_item_id ,
install_date,
expiration_date,
quantity,
zone,
region_id,
location_id,
contract_id,
contract_number,
contract_num_modifier,
sts_code,
service_line_id,
service_name,
service_start_date,
service_end_date,
coterm_date ,
last_update_date,
last_updated_by,
creation_date,
created_by
)
values (
t.item_instance_id,
t.inventory_item_id,
trunc(t.install_date),
t.expiration_date,
t.quantity,
lv_zone,
lv_region_id,
t.loc_id,
x_output_contracts(i).contract_id,
x_output_contracts(i).contract_number,
x_output_contracts(i).contract_number_modifier,
x_output_contracts(i).sts_code,
x_output_contracts(i).service_line_id,
SUBSTRB(x_output_contracts(i).service_name,1,240),
trunc(x_output_contracts(i).service_start_date),
x_output_contracts(i).service_end_date,
x_output_contracts(i).date_terminated,
systimestamp,
-1,
systimestamp,
-1);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Completed inserting Data into MSC_IB_CONTRACTS_TEMP');
Delete from msc_sr_zn_aggr_ibuc where sample_date = orig_st_date;
insert into msc_sr_zn_aggr_ibuc
(sr_inventory_item_id,
sample_date,
zone,
region_id,
quantity,
last_update_date,
last_updated_by,
creation_date,
created_by)
select
inventory_item_id,
v_curr_date,
zone,
region_id,
sum(qty),
systimestamp,
-1,
systimestamp,
-1
from ( select distinct item_instance_id, inventory_item_id, quantity qty,zone,region_id
from msc_ib_contracts_temp mict
where mict.install_date <= v_curr_date
and nvl(mict.coterm_date,mict.service_end_date) >= v_curr_date
and mict.service_start_date <=v_curr_date
and nvl(mict.expiration_date,(SYSTIMESTAMP + 365000)) >= v_curr_date
)
group by inventory_item_id,zone,region_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Completed inserting data into MSC_SR_ZN_AGGR_IBUC');
'select inventory_item_id,item_instance_id,location_id from '
|| MSC_UTIL.G_MSC_SCHEMA ||'.MSC_IB_CONTRACTS_TEMP'
|| ' where zone is null group by inventory_item_id,item_instance_id,location_id order by inventory_item_id ' ;
select LONG_TEXT
into lbuffer
from FND_DOCUMENTS_LONG_TEXT
where MEDIA_ID=pmedia_id;
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 ;
' INSERT INTO MSC_ST_LONG_TEXT '
||'( MEDIA_ID,'
||' LONG_TEXT,'
||' SR_INSTANCE_ID,'
||' REFRESH_ID,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY,'
||' LAST_UPDATE_LOGIN)'
||' SELECT'
||' MLT.MEDIA_ID,'
||' TO_LOB(MLT.LONG_TEXT),'
||' -999,'
||' -99,'
||' MLT.LAST_UPDATE_DATE,'
||' MLT.LAST_UPDATED_BY,'
||' MLT.CREATION_DATE,'
||' MLT.CREATED_BY,'
||' MLT.LAST_UPDATE_LOGIN'
||' FROM MSC_AP_LONG_TEXT_V MLT';
select op.concatenated_segments
into p_op_code
from ahl_operations_b_kfv op, ahl_workorders awo, ahl_workorder_operations awop
where awo.workorder_id = awop.workorder_id
and awop.operation_id = op.operation_id
and awo.wip_entity_id = p_wip_entity_id
and awop.operation_sequence_num = p_op_seq_num;
select to_char(actual_start_date,'DD-MON-YYYY')||'#'||to_char(actual_end_date,'DD-MON-YYYY')
into p_actual_dates
from ( select actual_start_date,actual_end_date
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and transaction_type=1
order by transaction_id desc) where rownum=1;
select to_char(awop.actual_start_date,'DD-MON-YYYY')||'#'||to_char(awop.actual_end_date,'DD-MON-YYYY')
into p_actual_dates
from ahl_workorders aw, ahl_workorder_operations awop
where aw.workorder_id = awop.workorder_id
and aw.wip_entity_id = p_wip_entity_id
and awop.operation_sequence_num = p_op_seq_num;