[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_id
FROM pa_projects_basic_v
WHERE project_number=ltrim(rtrim(P_project_num))
AND project_type_class_code = 'CONTRACT'
AND template_flag <> 'Y'
AND pa_project_stus_utils.Is_Project_In_Purge_Status(project_status_code) <>'Y'
AND nvl(cc_prvdr_flag,'N') <> 'Y';
the event being inserted is at project level.it returns 'y' oterwise*/
FUNCTION CHECK_FUNDING(
P_project_id IN NUMBER
,P_TASK_ID IN NUMBER) RETURN VARCHAR2 IS
l_funding_level VARCHAR2(1);
SELECT project_level_funding_flag
FROM PA_PROJECTS
WHERE project_id = P_project_id;
SELECT TASK_ID
FROM pa_tasks_top_v
WHERE project_id =P_project_id
AND task_number =ltrim(rtrim(P_task_num));
SELECT event_num
FROM pa_events
WHERE project_id=P_project_id
AND task_id IS NULL
AND event_num=P_event_num;
SELECT event_num
FROM pa_events
WHERE project_id=P_project_id
AND task_id =P_task_id
AND event_num=P_event_num;
SELECT event_type_classification
FROM pa_event_types_lov_v
WHERE event_type=P_event_type;
SELECT event_type_classification
FROM pa_event_types_lov_v
WHERE event_type=P_event_type
AND event_type_classification = 'MANUAL';
SELECT organization_id
FROM pa_organizations_event_v
WHERE name=P_event_org_name
AND TRUNC(SYSDATE) BETWEEN date_from AND nvl(date_to, TRUNC(SYSDATE));
SELECT 1
FROM fnd_currencies /* Changed vl into base for bug 4403197*/
WHERE nvl(enabled_flag, 'Y') = 'Y'
AND trunc(sysdate)
BETWEEN DECODE(TRUNC(start_date_active), null, TRUNC(SYSDATE), trunc(start_date_active))
AND decode (trunc(end_date_active), null, trunc(sysdate), trunc(end_date_active))
AND currency_code=p_bill_trans_curr;
SELECT conversion_type -- Added for bug 3009307
FROM pa_conversion_types_v
WHERE user_conversion_type = P_fund_rate_type;
SELECT conversion_type
FROM pa_conversion_types_v
WHERE conversion_type <>'User'
AND (pa_multi_currency.is_user_rate_type_allowed(
p_bill_trans_currency_code,
p_project_currency_code,
decode(p_proj_level_rt_dt_cod, 'PA_INVOICE_DATE',
nvl(p_project_rate_date, p_event_date),
'FIXED_DATE', p_project_rate_date))= 'N')
AND user_conversion_type=P_proj_rate_type
UNION ALL
-- Commented for bug 3009307 SELECT 1
SELECT conversion_type
FROM pa_conversion_types_v
WHERE pa_multi_currency.is_user_rate_type_allowed(
p_bill_trans_currency_code,
p_project_currency_code,
decode(p_proj_level_rt_dt_cod, 'PA_INVOICE_DATE',
nvl(p_project_rate_date, p_event_date),
'FIXED_DATE', p_project_rate_date))= 'Y'
AND user_conversion_type=P_proj_rate_type;
SELECT conversion_type
FROM pa_conversion_types_v
WHERE conversion_type <>'User'
AND (pa_multi_currency.is_user_rate_type_allowed(
p_bill_trans_currency_code,
p_proj_func_currency_code,
decode(p_proj_level_func_rt_dt_cod, 'PA_INVOICE_DATE',
nvl(p_proj_func_rate_date, p_event_date),
'FIXED_DATE', p_proj_func_rate_date))= 'N')
AND user_conversion_type=P_pfc_rate_type
UNION ALL
-- Commented for bug 3009307 SELECT 1
SELECT conversion_type
FROM pa_conversion_types_v
WHERE pa_multi_currency.is_user_rate_type_allowed(
p_bill_trans_currency_code,
p_proj_func_currency_code,
decode(p_proj_level_func_rt_dt_cod,
'PA_INVOICE_DATE', nvl(p_proj_func_rate_date, p_event_date),
'FIXED_DATE', p_proj_func_rate_date))= 'Y'
AND user_conversion_type=P_pfc_rate_type;
SELECT REVENUE_DISTRIBUTED_FLAG,BILLED_FLAG
FROM PA_EVENTS
WHERE EVENT_ID=P_event_id;
SELECT SUM(NVL(AMOUNT,0))
FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV,PA_DRAFT_INVOICES_ALL DIA
WHERE DI.PROJECT_ID=EV.PROJECT_ID
AND nvl(DI.TASK_ID,-1) =nvl(EV.TASK_ID,-1)
AND DI.EVENT_NUM = EV.EVENT_NUM
AND DI.PROJECT_ID=DIA.PROJECT_ID
AND DI.DRAFT_INVOICE_NUM=DIA.DRAFT_INVOICE_NUM
AND NVL(DIA.WRITE_OFF_FLAG,'N')<>'Y'
AND EV.EVENT_ID = P_event_id;
SELECT 1
INTO dummy
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV
WHERE DI.project_id=EV.project_id
AND nvl(DI.TASK_ID,-1) =nvl(EV.TASK_ID,-1)
AND DI.EVENT_NUM = EV.EVENT_NUM
AND EV.EVENT_ID = P_event_id);
No update will be allowed */
IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'Y' THEN
RETURN('N');
Only update of bill_trans_rev_amount will be allowed */
IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'N' THEN
RETURN('I');
Only update of bill_trans_bill_amount and bill_hold_flag will be allowed */
IF l_invoiced_flag = 'N' AND nvl(l_rev_flag, 'N') = 'Y' THEN
RETURN('R');
SELECT HOU.organization_id
FROM PA_IMPLEMENTATIONS I,HR_ORGANIZATION_UNITS HOU
WHERE HOU.BUSINESS_GROUP_ID=I.BUSINESS_GROUP_ID
AND HOU.NAME=P_inv_org_name;
SELECT 1
FROM mtl_item_flexfields
WHERE item_id=P_inv_item_id
AND trunc(sysdate)
BETWEEN decode(trunc(start_date_active), null, trunc(sysdate), trunc(start_date_active))
AND decode (trunc(end_date_active), null, trunc(sysdate), trunc(end_date_active));
SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
FROM pa_summary_project_fundings
WHERE project_id= P_project_id
AND task_id IS NULL;
SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
FROM pa_summary_project_fundings
WHERE project_id= P_project_id
AND task_id=P_task_id;
SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
FROM PA_EVENTS_V
WHERE event_type_classification='WRITE OFF'
AND NVL(revenue_distributed_flag,'N')='N'
AND event_date IS NOT NULL
AND NVL(event_id,-1)<>NVL(P_event_id,-2)
AND project_id= P_project_id; */
SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
FROM PA_EVENTS_V
WHERE event_type_classification='WRITE OFF'
AND NVL(REVENUE_DISTRIBUTED_FLAG,'N')='N'
AND event_date IS NOT NULL
AND project_id= P_project_id
AND NVL(event_id,-1)<>NVL(P_event_id,-2)
AND task_id=P_task_id;*/
SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
FROM PA_EVENTS EV,PA_EVENT_TYPES EVT
WHERE EVT.event_type_classification='WRITE OFF'
AND EVT.event_type=EV.event_type
AND NVL(EV.revenue_distributed_flag,'N')='N'
AND EV.completion_date IS NOT NULL
AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
AND EV.project_id= P_project_id; */
SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
FROM PA_EVENTS EV
WHERE EV.revenue_distributed_flag ='N'
AND EV.completion_date IS NOT NULL
AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
AND exists (select 1 from PA_EVENT_TYPES EVT
where EVT.event_type_classification='WRITE OFF'
and EVT.event_type=EV.event_type )
AND EV.project_id= P_project_id;
SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
FROM PA_EVENTS EV ,PA_EVENT_TYPES EVT
WHERE EVT.event_type_classification='WRITE OFF'
AND EVT.event_type=EV.event_type
AND NVL(EV.revenue_distributed_flag,'N')='N'
AND EV.completion_date IS NOT NULL
AND EV.project_id= P_project_id
AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
AND EV.task_id=P_task_id;
IF P_task_id IS NULL THEN /*project level event is being inserted So check for only project funding*/
OPEN proj_rev_bill_amount;
/*Task level event is being inserted .So we have to check both
project as well as task level funding*/
OPEN proj_rev_bill_amount;
/*l_projfunc_rev_amt contains the revenue amount of the event being inserted in projfunc curency*/
IF (l_projfunc_rev_amt <= (l_accrued_amount-l_billed_amount-l_sum_revenue)) THEN
RETURN('Y');
SELECT AG.AGREEMENT_ID
FROM pa_projects_all p,
pa_agreements_all ag,
hz_cust_accounts cust,
Pa_summary_project_fundings fun
WHERE p.project_id = P_project_id
AND nvl(p.date_eff_funds_consumption, 'N') = 'Y'
AND fun.project_id = p.project_id
AND ag.agreement_id = fun.agreement_id
And nvl(fun.task_id, nvl(P_task_id,-999)) = nvl(P_task_id,-999)
AND cust.account_number = P_customer_number
AND ag.customer_id = cust.cust_account_id
AND ag.agreement_num = P_agreement_number
AND ag.agreement_type = P_agreement_type
AND fun.TOTAL_BASELINED_AMOUNT >0;
SELECT start_date, expiration_date
FROM pa_agreements_all
WHERE agreement_id = P_agreement_id;