[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select LookUp_Code,Meaning
From Fnd_Lookups
Where Lookup_Type = ptype;
Select LookUp_Code
From Fnd_Lookups
Where Lookup_Type = ptype;
select count(distinct nvl(oh.order_number,0))
from oe_order_lines_all ol , oe_order_headers_all oh
WHERE oh.header_id = l_header_id and
oh.header_id = ol.header_id and
nvl(OH.cancelled_flag,'N') = 'N' and
nvl(oh.booked_flag,'N')= 'Y' and
ol.inventory_item_id = l_inventory_item_id and
nvl(ol.cancelled_flag,'N')= 'N' and
nvl(ol.booked_flag,'N')= 'Y' and
link_to_line_id is NULL ;
select i.index_tablespace Into pTabSpace
from fnd_product_installations i, fnd_application a, fnd_oracle_userid u
where a.application_short_name = 'IBE'
and a.application_id = i.application_id
and u.oracle_id = i.oracle_id;
PROCEDURE updateLog(pMvlogid IN Number,
pStatus IN Number,
pLogTime IN Date,
pCurrencyCode IN Varchar2,
pErrorCode IN Varchar2 default null,
pErrorMessage IN Varchar2 default null )
IS
Begin
IF (l_debug = 'Y') THEN
printDebugLog('updateLog(+)');
Update IBE_ECR_MVLOG
Set Refresh_Status = pStatus,
Refresh_Duration = ((pLogTime- Creation_Date)*24*60*60),
Currency_Code = pCurrencyCode,
Error_Code = pErrorCode,
Error_Message = pErrorMessage
Where MvLog_id = pMvlogid;
printDebugLog('updateLog(-)');
End updateLog;
PROCEDURE updateLog(pName IN Varchar2,
pMode IN Varchar2,
pFromStatus IN Number,
pToStatus IN Number,
pErrorCode IN Varchar2 default null,
pErrorMessage IN Varchar2 default null )
IS
BEGIN
IF (l_debug = 'Y') THEN
printDebugLog('updateLog(+)');
Update IBE_ECR_MVLOG
Set Refresh_Status = pToStatus,
Error_Code = nvl(pErrorCode,Error_Code),
Error_Message = nvl(pErrorMessage,Error_Message)
Where Refresh_Status = pFromStatus
And Mview_Name = pName
And Refresh_Mode = pMode;
printDebugLog('updateLog(-)');
End updateLog;
Insert into IBE_ECR_MVLOG
(mvlog_id,object_version_number,Created_by,Creation_Date,
last_updated_by,last_update_date,last_update_login,
Program_application_id,Request_id,Program_id,Program_update_Date,Refresh_Mode,
mview_Name, Refresh_Status, Begin_Date, End_Date,Fact_Source,Error_Code, Error_Message,Refresh_Duration,
CONVERSION_TYPE, DAY_BIN_OFFSET,FORCE_REFRESH_FLAG, QUARTER_BEGIN_FLAG, PERIOD_SET_NAME)
Values (ibe_ecr_mvlog_s1.nextval,0,
FND_GLOBAL.user_id,pLogTime,
FND_GLOBAL.user_id,sysdate,
FND_GLOBAL.Conc_Login_ID,
fnd_global.prog_appl_id, fnd_global.conc_request_id,
fnd_global.conc_program_id,Sysdate,pmode,
pMviewName, pStatus, pBeginDate, pEndDate,
g_data_source, pErrorCode, pErrorMessage,0,
g_ConversionType, g_dayoffset, g_ForceRefreshFlag, g_QuarterBeginFlag, g_periodSetName)
RETURNING mvlog_id into plogid;
Select owner,index_name
From all_indexes
Where table_owner = p_owner
And table_name = p_name;
select application_short_name
into l_application_short_name
from fnd_application
where application_id = 671;
EXECUTE IMMEDIATE 'Delete From '||pName;
EXECUTE IMMEDIATE 'Delete From '||pName;
Delete from IBE_ECR_QUOTES_FACT where quote_date between pFromDate and pToDate;
EXECUTE IMMEDIATE 'Delete From '||pName||' Where fact_date between :1 and :2'
USING pFromDate,pToDate;
Select Start_Date,End_Date
From GL_Periods
Where period_set_name = p_periodsetname
and period_type = p_periodType
and adjustment_period_flag = 'N'
and p_Date between Start_date and End_date;
Select trunc(NEXT_DAY(pDate-7,l_StartDay))
into pStartDate
From Dual;
Select trunc(NEXT_DAY(pDate-7*14,l_StartDay))
into pStartDate
From Dual;
Select nvl(max(to_number(lookup_code)),20) into pRows
From fnd_lookups
Where lookup_type = l_type;
Select min(End_Date) Into pFromDate
From ibe_ecr_mvlog
Where refresh_mode = pMode
And mview_name = pfactName
And refresh_status = 1;
Select min(Begin_Date) Into pFromDate
From ibe_ecr_mvlog
Where refresh_mode = pMode
And mview_name = pfactName
And refresh_status = -1;
Select End_Date Into pFromDate
From ibe_ecr_mvlog
Where refresh_mode = 'COMPLETE'
And mview_name = pfactName
And refresh_status = 1;
SELECT DISTINCT Transactional_Curr_Code,
Ordered_date
FROM (
SELECT DISTINCT
OH.Transactional_Curr_Code,
trunc(oh.ordered_date) ordered_date
FROM
Oe_order_Sources OS,
oe_order_headers_all oh,
fnd_lookups fndlkp
WHERE
nvl(oh.booked_flag,'N') = 'Y'
AND nvl(oh.cancelled_flag,'N') = 'N'
AND fndlkp.lookup_type = 'IBE_ECR_ORDER_SOURCE'
AND fndlkp.lookup_code = upper(OS.Name)
AND OH.Source_Document_Type_ID = OS.order_source_id
AND oh.booked_date BETWEEN l_start_date AND l_end_date
UNION ALL
SELECT DISTINCT
a.currency_code Transactional_Curr_Code,
to_date(pToDate,'yyyy/mm/dd hh24:mi:ss') ordered_date
FROM
aso_quote_headers_all a,
fnd_lookups fndlkp
WHERE
a.quote_header_id = (SELECT max(quote_header_id) quote_header_id
FROM aso_quote_headers_all
WHERE quote_number = a.quote_number)
AND ( (
fndlkp.lookup_code = upper(a.quote_source_code)
AND a.resource_id IS NULL )
OR a.resource_id IS NOT NULL)
AND fndlkp.lookup_type = 'IBE_ECR_ORDER_SOURCE'
AND a.order_id IS NULL
AND a.creation_date BETWEEN l_start_date and l_end_date
AND a.total_quote_price <= pAmount
);
Select Begin_Date, End_Date
Into pStartDate, pEndDate
From IBE_ECR_MVLOG
Where Refresh_Status = pStatus
And Mview_Name = pFactname
And Refresh_Mode = pMode;
PROCEDURE insertOrderHeaderFact( p_currency_code IN Varchar2, pFromDate IN Date, pToDate IN Date) IS
l_booked_flag varchar2(1) := 'Y';
printDebugLog('insertOrderHeaderFact(+)');
Insert /*+ append */ Into IBE_ECR_ORDER_HEADERS_FACT (fact_date, object_version_number, created_by,creation_date,
last_updated_by, last_update_date, last_update_login, resource_id, org_id, currency_code, header_id, ordered_date,
order_number,Transactional_Curr_Code,Conversion_Rate, sold_to_org_id, invoice_to_org_id, agreement_id, salesrep_id,
functional_amount, reported_amount, customer_class_code, party_id ) SELECT Trunc(OH.booked_date) Fact_Date,
0 Object_Version_Number,
FND_GLOBAL.user_id Created_By,
sysdate Creation_Date,
FND_GLOBAL.user_id Last_Updated_By,
sysdate Last_Updation_Date,
FND_GLOBAL.user_id Last_update_login,
null Resource_id,
oh.Org_id,
p_currency_code Currency_Code,
oh.Header_id Header_id,
oh.Ordered_date,
oh.Order_number,
oh.Transactional_Curr_Code,
oh.Conversion_Rate,
OH.Sold_To_Org_ID,
oh.Invoice_To_Org_ID,
OH.Agreement_ID,
OH.SalesRep_ID,
Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0))*
(nvl(OL.Unit_Selling_Price,0)*nvl(Oh.conversion_rate,1))) Functional_Amount,
Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0))*
gl_currency_api.convert_amount_sql(OH.Transactional_Curr_Code,p_currency_code,trunc(oh.ordered_date),
g_conversionType,nvl(OL.Unit_Selling_Price,0))) Reported_Amount,
hca.customer_class_code, hca.party_id
FROM Oe_order_Sources OS,
Oe_order_headers_all OH,
Oe_order_lines_all OL,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
fnd_lookups fndlkp
WHERE fndlkp.lookup_type = l_fact_source
AND fndlkp.lookup_code = upper(OS.Name)
AND OH.Source_Document_Type_ID = OS.order_source_id
AND OH.Header_id = OL.Header_id
AND nvl(OH.Booked_flag,'N') = l_booked_flag
AND nvl(OH.cancelled_flag,'N') = l_cancelled_flag
AND OH.booked_date between pFromDate and pToDate
AND oh.invoice_To_org_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.Cust_account_id
GROUP BY Trunc(OH.booked_date), OH.Org_ID,
oh.header_id, oh.ordered_date,
oh.order_number, oh.Transactional_Curr_Code,
oh.Conversion_Rate,OH.Invoice_To_Org_ID,
OH.Sold_To_Org_ID, OH.Agreement_ID, OH.SalesRep_ID,
hca.customer_class_code,hca.party_id;
printDebugLog('insertOrderHeaderFact(-)');
End insertOrderHeaderFact;
PROCEDURE insertOrderLineFact( p_currency_code IN Varchar2, pFromDate IN Date, pToDate IN Date) IS
l_booked_flag varchar2(1) := 'Y';
printDebugLog('insertOrderLineFact(+)');
Insert /*+ append */ into IBE_ECR_ORDERS_FACT (fact_date, object_version_number, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, resource_id, org_id, msite_id, currency_code, section_id,
organization_id, inventory_item_id, uom_code, sold_to_org_id, invoice_to_org_id, agreement_id, salesrep_id,
num_times_ordered, sale_quantity, functional_amount, reported_amount) SELECT ift.fact_date Fact_Date,
0 Object_Version_Number,
FND_GLOBAL.user_id Created_By,
sysdate Creation_Date,
FND_GLOBAL.user_id Last_Updated_By,
sysdate Last_Update_Date,
FND_GLOBAL.user_id Last_update_login,
null Resource_id,
ift.Org_id,
1 MSite_id,
p_currency_code Currency_Code,
1 Section_id,
osp.Master_Organization_id ORGANIZATION_ID,
ol.Inventory_Item_ID,
ol.Pricing_quantity_uom UOM_Code,
ol.Sold_To_Org_ID,
ol.Invoice_To_Org_ID,
ift.Agreement_ID,
ift.SalesRep_ID,
Count(ift.order_number) NUM_TIMES_ORDERED,
Sum(decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.Pricing_Quantity,0)) AS Sale_Quantity,
return_functional_amount(ol.inventory_item_id,osp.Master_Organization_id,ift.header_id,ol.item_type_code,OL.line_category_code,
OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate) Functional_Amount,
gl_currency_api.convert_amount_sql( ift.Transactional_Curr_Code,g_CurrencyCode ,
trunc(ift.ordered_date),g_ConversionType,
return_functional_amount(ol.inventory_item_id,osp.Master_Organization_id,ift.header_id,
ol.item_type_code,OL.line_category_code,
OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate) ) Reported_Amount
FROM IBE_ECR_ORDER_HEADERS_FACT ift,
OE_ORDER_LINES_ALL OL,
OE_SYSTEM_PARAMETERS_ALL OSP
WHERE ift.Header_id = OL.Header_id
AND ift.fact_date between pFromDate and pToDate
AND nvl(OL.cancelled_flag,'N') = l_cancelled_flag
AND OL.link_to_line_id is NULL
AND OL.Org_ID = OSP.Org_ID
GROUP BY ift.fact_date, ift.Org_ID, OSP.Master_Organization_id, OL.Inventory_Item_ID, OL.Pricing_quantity_uom, OL.Invoice_To_Org_ID, OL.Sold_To_Org_ID,ift.Agreement_ID, ift.SalesRep_ID ,ift.header_id
,ol.item_type_code,OL.line_category_code,OL.ordered_Quantity,OL.Unit_Selling_Price,ift.conversion_rate,
ift.Transactional_Curr_Code,ift.ordered_date;
printDebugLog('insertOrderLineFact(-)');
End insertOrderLineFact;
PROCEDURE insertQuotesFact(pFromDate IN Date, pToDate IN Date) IS
l_fact_source varchar2(240) := 'IBE_ECR_ORDER_SOURCE';
printDebugLog('insertQuotesFact(+)');
Insert /*+ append */ into IBE_ECR_QUOTES_FACT (quote_date, object_version_number, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, msite_id, org_id, currency_code, section_id, organization_id,
inventory_item_id, uom_code, cust_account_id, employee_person_id, added_to_cart_frequency, quote_quantity,
item_ordered_frequency, order_quantity)
SELECT
Trunc(QH.Creation_Date) Quote_Date,
0 Object_Version_Number,
FND_GLOBAL.user_id Created_By,
sysdate Creation_Date,
FND_GLOBAL.user_id Last_Updated_By,
sysdate Last_Updation_Date,
FND_GLOBAL.user_id Last_update_login,
1 Msite_ID,
QH.Org_id,
QH.Currency_Code,
1 Section_ID,
QL.ORGANIZATION_ID,
QL.Inventory_item_id,
QL.UOM_Code,
QH.Cust_Account_ID,
QH.Employee_Person_ID,
Count(*) Added_To_Cart_Frequency,
Sum(nvl(QL.Quantity,0)) AS Quote_Quantity,
-- Count(decode(nvl(OH.Booked_Flag,'N'),'N',Null,QH.order_id)) Item_Ordered_Frequency,
SUM(get_item_count(ql.inventory_item_id,nvl(oh.header_id,0))) Item_Ordered_Frequency,
Sum(decode(nvl(OH.Booked_Flag,'N'),'N',0, nvl(QL.Quantity,0))) AS Order_Quantity
FROM Aso_quote_headers_all QH,
Aso_quote_lines_all QL,
OE_Order_Headers_ALL OH,
Fnd_lookups fndlkp
WHERE fndlkp.lookup_type = l_fact_source
AND upper(Qh.Quote_Source_Code) = fndlkp.lookup_code
AND QH.Creation_Date between pFromDate and pToDate
AND Qh.quote_header_id = Ql.quote_header_id
and ql.item_type_code <> 'CFG'
AND Qh.order_id = OH.Header_ID (+)
AND Qh.max_version_flag = 'Y'
GROUP BY Trunc(QH.Creation_Date), QH.Org_id ,QH.Currency_Code,QL.ORGANIZATION_ID, QL.Inventory_item_id, QL.UOM_Code, QH.Currency_Code, QH.Cust_Account_ID,QH.Employee_Person_ID;
printDebugLog('insertQuotesFact(-)');
End insertQuotesFact;
PROCEDURE insertBinTOPORD(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
Begin
null;
End insertBinTOPORD;
PROCEDURE insertBinTOPORD(pCurrencyCode IN Varchar2, pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
Begin
null;
End insertBinTOPORD;
PROCEDURE insertBinTOPPRD(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date, pDataFromOM IN Boolean default FALSE) IS
Begin
null;
End insertBinTOPPRD;
PROCEDURE insertBinTOPPRD(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
Begin
null;
End insertBinTOPPRD;
PROCEDURE insertBinTOPCUST(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
Begin
null;
End insertBinTOPCUST;
PROCEDURE insertBinTOPCUST(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
Begin
null;
End insertBinTOPCUST;
PROCEDURE insertBinSUMM(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date,pDataFromOM IN Boolean default FALSE) IS
Begin
null;
End insertBinSUMM;
PROCEDURE insertBinSUMM(pCurrencyCode IN Varchar2,pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date) IS
Begin
null;
End insertBinSUMM;
PROCEDURE insertBinSUMMCart(pLookupCode IN Varchar2, pStartDate IN Date, pEndDate IN Date)
IS
Begin
null;
End insertBinSUMMCart;
Select Mvlog_id, Begin_Date, End_Date Into l_mvlog_id,l_begindate,l_enddate
From IBE_ECR_MVLOG
Where Refresh_Status = l_mvlog_Status
And Mview_Name = pObjName
And Refresh_Mode = pMode;
Update IBE_ECR_MVLOG
Set Program_ID = fnd_global.conc_program_id,
Request_id = fnd_global.conc_request_id,
Program_update_Date = SysDate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
Program_application_id = fnd_global.prog_appl_id
Where mvlog_id = l_mvlog_id;
PROCEDURE insertBinFact(pRefreshDate IN Date) IS
Begin
null;
END insertBinFact;
PROCEDURE insertBinFact(pCurrencyCode IN Varchar2,pRefreshDate IN Date) IS
Begin
null;
END insertBinFact;
updateLog(pfactName,pMode,1,2,null,null);
updateLog(pfactName,pMode,-1,2,null,null);
updateLog(pfactName,'INCREMENT',1,2,null,null);
updateLog(pfactName,'INCREMENT',-1,2,null,null);
insertOrderHeaderFact(g_CurrencyCode,l_factStartDate,l_factEndDate);
Delete From IBE_ECR_ORDER_HEADERS_FACT where functional_amount > g_maxamount_ceil;
insertOrderLineFact(g_CurrencyCode,l_factStartDate, l_factEndDate);
insertQuotesFact(l_factStartDate, l_factEndDate);
updateLog(l_mvLogID,1,sysdate,l_FactCurrencyCode,null,null);
updateLog(l_mvLogID,-1,sysdate,null,SQLCODE,SQLERRM);
Update IBE_ECR_MVLOG Set Refresh_status = -1 Where Refresh_Status = 0;
Select 'drop materialized view '||owner||'.'||mview_name sqlstmt
From all_mviews
Where owner = pOwner
And mview_name = pName;
ElsIf pOperation = 'INSERT' Then
l_Stmt := 'insert /*+append */ into '||pMviewName||' ';
Select 'Y',Begin_Date,End_Date,Conversion_Type,Currency_code,Fact_Source,
day_bin_offset,Force_Refresh_Flag,Quarter_Begin_Flag,Period_Set_Name
INTO pStatus,pBeginDate,pEndDate,pConversionType,pCurrencyCode,pFactSource,
pDayOffset, pForceRefresh, pQuarterBegin,pPeriodSetName
From IBE_ECR_MVLOG
WHERE MView_Name = pName
ANd Refresh_Status = l_mvlogStatus
And Refresh_Mode = pMode;
select user into l_username from dual;
updateLog(pMViewName,'COMPLETE',1,2,null,null);
updateLog(pMViewName,'COMPLETE',-1,2,null,null);
registerMview('INSERT',pMViewName);
updateLog(l_mvLogID,1,sysdate,g_currencyCode,null,null);
updateLog(l_mvLogID,-1,sysdate,null,SQLCODE,SQLERRM);
updateLog(l_mvLogID,-1,sysdate,null,'IBE_ECR_NO_FACT_DATA',x_NoFactMessage);
Delete from IBE_ECR_MVLOG where Refresh_Status = l_mvlogStatus ;
select Sum((decode(OL.line_category_code,'RETURN',-1,1)* nvl(OL.ordered_Quantity,0))*
(nvl(OL.Unit_Selling_Price,0))) into l_return_amount
from oe_order_lines_all ol
where header_id = p_order_header_id
and exists
( select line_id from oe_order_lines_all x
where x.inventory_item_id = p_inventory_item_id
and ol.top_model_line_id = x.top_model_line_id
) ;
select decode(p_item_type_code,'MODEL',
return_amount(p_inventory_item_id,p_organization_id,p_order_header_id),
Sum((decode(p_line_category_code,'RETURN',-1,1)* nvl(p_ordered_Quantity,0))*
(nvl(p_Unit_Selling_Price,0)*nvl(p_conversion_rate,1))))
into l_return_amount
from dual ;
select a.concat_ids, b.msite_root_section_id
from ibe_dsp_msite_sct_sects a,
ibe_msites_b b
where a.child_section_id = section_id
and a.mini_site_id = 1
and b.msite_id = l_store_id
and b.site_type = 'I'; -- Changed as per the Bug # 4394901
SELECT display_name
FROM ibe_dsp_sections_tl
WHERE section_id = l_concat_section_id
AND language = userenv('LANG');