[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(
p_SALESFORCE_ID in NUMBER,
p_SALES_GROUP_ID in NUMBER,
p_PERIOD_NAME in VARCHAR2,
p_CURRENCY_CODE in VARCHAR2,
p_ALLOCATED_BUDGET_AMOUNT in NUMBER,
p_ACTUAL_REVENUE_AMOUNT in NUMBER,
p_CREATED_BY in NUMBER,
p_CREATION_DATE in DATE,
p_LAST_UPDATED_BY in NUMBER,
p_LAST_UPDATE_DATE in DATE,
p_LAST_UPDATE_LOGIN in NUMBER,
p_REQUEST_ID in NUMBER,
p_PROGRAM_APPLICATION_ID in NUMBER,
p_PROGRAM_ID in NUMBER,
p_PROGRAM_UPDATE_DATE in DATE,
p_SECURITY_GROUP_ID in NUMBER,
p_forecast_category_id in NUMBER,
p_credit_type_id in NUMBER)
IS
BEGIN
INSERT INTO AS_FORECAST_ACTUALS(
FORECAST_ACTUAL_ID,
SALESFORCE_ID,
SALES_GROUP_ID,
PERIOD_NAME,
CURRENCY_CODE,
ALLOCATED_BUDGET_AMOUNT,
ACTUAL_REVENUE_AMOUNT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
FORECAST_CATEGORY_ID,
FORECAST_CREDIT_TYPE_ID
) VALUES (
AS_FORECAST_ACTUALS_S.NEXTVAL,
decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, 0, NULL, p_SALESFORCE_ID),
decode( p_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SALES_GROUP_ID),
decode( p_PERIOD_NAME, FND_API.G_MISS_CHAR, NULL, p_PERIOD_NAME),
decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
round(decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, 0, NULL, 0, p_ALLOCATED_BUDGET_AMOUNT),4),
round(decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, 0, NULL, 0, p_ACTUAL_REVENUE_AMOUNT),4),
decode( p_CREATED_BY, FND_API.G_MISS_NUM, -1, p_CREATED_BY),
decode( p_CREATION_DATE, FND_API.G_MISS_DATE, SYSDATE, p_CREATION_DATE),
decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, -1, p_LAST_UPDATED_BY),
decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, SYSDATE, p_LAST_UPDATE_DATE),
decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, -1, p_LAST_UPDATE_LOGIN),
decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
decode( p_forecast_category_id, FND_API.G_MISS_NUM, NULL,p_forecast_category_id),
decode( p_credit_type_id, FND_API.G_MISS_NUM, NULL,p_credit_type_id));
End Insert_Row;
PROCEDURE Update_Row(
p_FORECAST_ACTUAL_ID in NUMBER,
p_CURRENCY_CODE in VARCHAR2,
p_ALLOCATED_BUDGET_AMOUNT in NUMBER,
p_ACTUAL_REVENUE_AMOUNT in NUMBER,
p_LAST_UPDATED_BY in NUMBER,
p_LAST_UPDATE_DATE in DATE,
p_LAST_UPDATE_LOGIN in NUMBER,
p_REQUEST_ID in NUMBER,
p_PROGRAM_APPLICATION_ID in NUMBER,
p_PROGRAM_ID in NUMBER,
p_PROGRAM_UPDATE_DATE in DATE,
p_SECURITY_GROUP_ID in NUMBER
)
IS
BEGIN
Update AS_FORECAST_ACTUALS
SET
CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, p_CURRENCY_CODE),
ALLOCATED_BUDGET_AMOUNT = round(decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, ALLOCATED_BUDGET_AMOUNT, NULL, 0, p_ALLOCATED_BUDGET_AMOUNT),4),
ACTUAL_REVENUE_AMOUNT = round(decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, ACTUAL_REVENUE_AMOUNT, NULL, 0, p_ACTUAL_REVENUE_AMOUNT),4),
LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, SYSDATE, p_LAST_UPDATE_DATE),
LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID)
where FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
END Update_Row;
PROCEDURE Delete_Row(
p_FORECAST_ACTUAL_ID in NUMBER)
IS
BEGIN
DELETE FROM AS_FORECAST_ACTUALS
WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
END Delete_Row;
p_LAST_UPDATED_BY in NUMBER,
p_LAST_UPDATE_DATE in DATE,
p_LAST_UPDATE_LOGIN in NUMBER,
p_REQUEST_ID in NUMBER,
p_PROGRAM_APPLICATION_ID in NUMBER,
p_PROGRAM_ID in NUMBER,
p_PROGRAM_UPDATE_DATE in DATE,
p_SECURITY_GROUP_ID in NUMBER)
IS
CURSOR C IS
SELECT *
FROM AS_FORECAST_ACTUALS
WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID
FOR UPDATE of FORECAST_ACTUAL_ID NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
AND ( p_LAST_UPDATED_BY IS NULL )))
AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
AND ( p_LAST_UPDATE_DATE IS NULL )))
AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
AND ( p_LAST_UPDATE_LOGIN IS NULL )))
AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
OR ( ( Recinfo.REQUEST_ID IS NULL )
AND ( p_REQUEST_ID IS NULL )))
AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
OR ( ( Recinfo.PROGRAM_ID IS NULL )
AND ( p_PROGRAM_ID IS NULL )))
AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
AND ( ( Recinfo.SALESFORCE_ID = p_SALESFORCE_ID)
OR ( ( Recinfo.SALESFORCE_ID IS NULL )
AND ( p_SALESFORCE_ID IS NULL )))
AND ( ( Recinfo.SALES_GROUP_ID = p_SALES_GROUP_ID)
OR ( ( Recinfo.SALES_GROUP_ID IS NULL )
AND ( p_SALES_GROUP_ID IS NULL )))
AND ( ( Recinfo.PERIOD_NAME = p_PERIOD_NAME)
OR ( ( Recinfo.PERIOD_NAME IS NULL )
AND ( p_PERIOD_NAME IS NULL )))
AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
OR ( ( Recinfo.CURRENCY_CODE IS NULL )
AND ( p_CURRENCY_CODE IS NULL )))
AND ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT = p_ALLOCATED_BUDGET_AMOUNT)
OR ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT IS NULL )
AND ( p_ALLOCATED_BUDGET_AMOUNT IS NULL )))
AND ( ( Recinfo.ACTUAL_REVENUE_AMOUNT = p_ACTUAL_REVENUE_AMOUNT)
OR ( ( Recinfo.ACTUAL_REVENUE_AMOUNT IS NULL )
AND ( p_ACTUAL_REVENUE_AMOUNT IS NULL )))
AND ( ( Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
OR ( ( Recinfo.SECURITY_GROUP_ID IS NULL )
AND ( p_SECURITY_GROUP_ID IS NULL )))
) then
return;
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATE_DATE IN DATE,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_REQUEST_ID IN NUMBER,
p_PROGRAM_APPLICATION_ID IN NUMBER,
p_PROGRAM_ID IN NUMBER,
p_PROGRAM_UPDATE_DATE IN DATE,
p_SECURITY_GROUP_ID IN NUMBER,
p_filehandle IN UTL_FILE.FILE_TYPE,
p_forecast_category_name IN VARCHAR2,
p_credit_type_name IN VARCHAR2)
IS
-- Define the local variables
l_forecast_actual_id NUMBER := 0;
SELECT forecast_actual_id
INTO l_forecast_actual_id
FROM AS_FORECAST_ACTUALS
WHERE SALESFORCE_ID = l_salesforce_id
AND SALES_GROUP_ID = l_sales_group_id
AND FORECAST_CATEGORY_ID = l_forecast_category_id
AND FORECAST_CREDIT_TYPE_ID = l_credit_type_id
AND PERIOD_NAME = p_period_name;
SELECT forecast_actual_id
INTO l_forecast_actual_id
FROM AS_FORECAST_ACTUALS
WHERE SALES_GROUP_ID = l_sales_group_id
AND FORECAST_CATEGORY_ID = l_forecast_category_id
AND FORECAST_CREDIT_TYPE_ID = l_credit_type_id
AND PERIOD_NAME = p_period_name
AND SALESFORCE_ID IS NULL;
INSERT_ROW(
l_salesforce_id
, l_sales_group_id
, p_period_name
, p_currency_code
, p_allocated_budget_amount
, p_actual_revenue_amount
, p_created_by
, SYSDATE
, p_last_updated_by
, SYSDATE
, p_last_update_login
, p_request_id
, p_program_application_id
, p_program_id
, SYSDATE
, p_security_group_id
, l_forecast_category_id
, l_credit_type_id);
ELSE -- update existing row
UPDATE_ROW(
l_forecast_actual_id
, p_currency_code
, p_allocated_budget_amount
, p_actual_revenue_amount
, p_last_updated_by
, SYSDATE
, p_last_update_login
, p_request_id
, p_program_application_id
, p_program_id
, SYSDATE
, p_security_group_id);
select trim(value)
into l_logdir
from v$parameter
where name = 'utl_file_dir';
SELECT period_name, start_date, end_date
INTO l_period_name, x_start_date, x_end_date
FROM gl_periods
WHERE period_name = p_period_name
AND period_set_name = p_period_set_name;
SELECT currency_code
INTO l_currency_code
FROM fnd_currencies
WHERE currency_code = p_currency_code;
SELECT sales_credit_type_id
INTO x_credit_type_id
FROM aso_i_sales_credit_types_v
WHERE
enabled_flag = 'Y'
AND UPPER(name) = p_name
AND rownum = 1;
SELECT forecast_category_id
INTO x_forecast_category_id
FROM as_forecast_categories
WHERE
UPPER(forecast_category_name) = p_name
AND ((start_date_active <= p_end_date) OR (start_date_active IS NULL))
AND ((end_date_active >= p_start_date) OR (end_date_active IS NULL))
AND rownum = 1 ;
SELECT jgb.group_id
INTO x_sales_group_id
FROM
jtf_rs_groups_b jgb
, jtf_rs_group_usages jgu
WHERE
jgb.group_number = p_sales_group_number
AND ((jgb.start_date_active <= p_end_date) OR (jgb.start_date_active IS NULL))
AND ((jgb.end_date_active >= p_start_date) OR (jgb.end_date_active IS NULL))
AND jgu.usage = 'SALES'
AND jgu.group_id = jgb.group_id
AND rownum = 1;
SELECT res.resource_id
INTO x_salesforce_id
FROM
jtf_rs_group_members mem
, jtf_rs_resource_extns res
, jtf_rs_role_relations rrel
, jtf_rs_roles_b roleb
WHERE
res.resource_number = p_salesforce_number
AND roleb.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = roleb.role_id
AND (rrel.start_date_active <= p_end_date
OR rrel.start_date_active IS NULL)
AND (rrel.end_date_active >= p_start_date
OR rrel.end_date_active IS NULL)
AND rrel.delete_flag <> 'Y'
AND (roleb.member_flag = 'Y'
OR (NVL(roleb.member_flag,'N') ='N' and roleb.manager_flag='Y'))
AND mem.group_id = p_sales_group_id
AND mem.resource_id = res.resource_id
AND mem.group_member_id = rrel.role_resource_id
AND mem.delete_flag <> 'Y'
AND rownum = 1 ;
, p_LAST_UPDATED_BY IN NUMBER
, p_LAST_UPDATE_LOGIN IN NUMBER
, p_PROGRAM_APPLICATION_ID IN NUMBER)
IS
l_lob_loc BLOB;
SELECT DBMS_LOB.GETLENGTH(file_data), file_data, file_name
INTO l_lob_len, l_lob_loc, l_file_name
FROM FND_LOBS
WHERE file_id = p_file_id;
,p_LAST_UPDATED_BY
,sysdate
,p_LAST_UPDATE_LOGIN
,null
,p_PROGRAM_APPLICATION_ID
,null
,sysdate
,null
,l_file_handle
,l_forecast_category_name
,l_credit_type_name);
PROCEDURE Delete_lob(p_file_id IN NUMBER
,p_filehandle IN UTL_FILE.FILE_TYPE) IS
l_doc_id NUMBER := 0;
SELECT document_id
FROM Fnd_Documents_tl
WHERE Media_Id = p_file_id;
FND_DOCUMENTS_PKG.DELETE_ROW( l_doc_id, l_datatype, NULL);
DELETE FND_LOBS WHERE FILE_ID = p_file_id;
/*Lob is deleted*/
/*Creating the log lob by accumlating the temporary lob
p_log_string- String that needs to go in blob.
p_file_id-Blob ID to which the temp log needs to be copied at the end.
p_op_type-takes in 'C' or 'W'. C- to copy the temp blob to internal blob.
W- to write to the temp blob
p_exists- boolean value determines whether to create a new temporary blob.(True creates a new one)
*/
PROCEDURE Create_Loglob( p_log_string IN VARCHAR2
,p_file_id IN NUMBER
,p_op_type IN VARCHAR2
,p_exists IN BOOLEAN)
IS
/*The following variables are for temporary blob */
dest_lob_loc BLOB;
SELECT file_data, dbms_lob.getlength(file_data)
INTO dest_lob_loc, l_temp_amt_var1
FROM FND_LOBS
WHERE file_id = p_file_id
FOR Update;