[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
into G_gmsimpl_rec
from gms_implementations_all
where org_id is NULL ;
select *
into G_gmsimpl_rec
from gms_implementations_all
where org_id = g_award_rec.org_id ;
PROCEDURE insert_award_record ( X_return_status in out NOCOPY varchar2) ;
Select project_id
from PA_PROJECTS_ALL
where project_type = 'AWARD_PROJECT'
and template_flag = 'Y'
and (segment1 = 'AWD_PROJ_-999'
or segment1 = 'AWD_PROJ_'||TO_CHAR(L_org_id))
and rownum = 1;
SELECT 'X'
FROM Hz_party_sites party_site,
Hz_locations loc,
Hz_cust_acct_sites_all acct_site,
hz_cust_site_uses su
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND acct_site.cust_account_id = p_customer_id
AND Nvl(su.Status, 'A') = 'A'
AND su.Site_Use_Code IN ( 'BILL_TO', 'SHIP_TO')
AND su.primary_flag = 'Y'
AND su.Contact_Id IS NOT NULL;
SELECT acct_site.cust_acct_site_id,
su.Contact_Id
FROM hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
Hz_cust_site_uses su
Where acct_site.cust_acct_site_id = su.cust_acct_site_id
And acct_site.cust_account_id = l_Customer_Id
And acct_site.party_site_id = party_site.party_site_id
And loc.location_id = party_site.location_id
And Nvl(su.Status, 'A') = 'A'
And su.Site_Use_Code = l_usage_code
And su.primary_flag = 'Y' ;
G_stage := 'Insert_award ' ;
insert_award_record ( X_return_status ) ;
gms_award_manager_pkg.insert_award_manager_id ( g_award_rec.award_id,
g_award_rec.award_manager_id,
g_award_rec.start_date_active ) ;
L_contact_rec.last_update_date := g_award_rec.last_update_date ;
L_contact_rec.last_updated_by := g_award_rec.last_updated_by ;
L_contact_rec.last_update_login := g_award_rec.last_update_login ;
SELECT cust_account_id
FROM hz_cust_accounts
WHERE status = 'A'
AND cust_account_id = g_award_rec.funding_source_id ;
SELECT 1
FROM gms_lookups
WHERE lookup_type = p_lookup_type
and lookup_code = p_code ;
SELECT 1
FROM gms_allowability_schedules
WHERE allowability_schedule_id = g_award_rec.allowable_schedule_id ;
SELECT ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = g_award_rec.idc_schedule_id
and trunc(sysdate) between start_date_active and NVL(end_date_active, (SYSDATE+1)) ;
SELECT 1
FROM ra_terms
WHERE term_id = g_award_rec.billing_term ;
SELECT 1
FROM pa_invoice_groups inv_grp,
pa_invoice_formats inv_fmt
WHERE inv_grp.invoice_group_id = inv_fmt.invoice_group_id
and inv_fmt.invoice_format_id = x_formatID
and inv_grp.invoice_format_type = x_format ;
SELECT 1
FROM pa_organizations_lov_v
WHERE code = g_award_rec.award_organization_id ;
SELECT 1
FROM pa_billing_cycles
WHERE trunc(sysdate) between start_date_active and NVL( end_date_active, sysdate )
and billing_cycle_id = g_award_rec.billing_cycle_id ;
SELECT nvl(next_unique_identifier,0)
into l_dummy
FROM gms_unique_identifier_control
WHERE table_name = 'GMS_AWARDS'
FOR update of next_unique_identifier;
UPDATE gms_unique_identifier_control
SET next_unique_identifier = l_dummy + 1,
last_update_date = trunc(sysdate) ,
last_updated_by = 0
WHERE table_name = 'GMS_AWARDS';
select decode(NAME, g_award_rec.award_number,1,2)
from PA_PROJECTS
where ( NAME = g_award_rec.award_number
OR SEGMENT1 = g_award_rec.award_number) ;
select award_id
from gms_awards_all
where award_number = g_award_rec.award_number ;
select award_id
from gms_awards_all
where award_SHORT_NAME = g_award_rec.award_short_name ;
SELECT 1, gmsp.start_date_active, end_date_active
FROM per_assignments_f pera,
gms_personnel gmsp
WHERE pera.person_id = g_award_rec.award_manager_id
and trunc(sysdate) between pera.effective_start_date
and pera.effective_end_date
and pera.primary_flag = 'Y'
and pera.person_id = gmsp.person_id
and gmsp.award_role = 'AM'
and SYSDATE BETWEEN NVL (Start_Date_Active, SYSDATE-1)
AND NVL (End_Date_Active, SYSDATE+1)
and pera.assignment_type = 'E' ;
SELECT 1
FROM pa_implementations i,
pa_employees e,
per_assignments_f a
WHERE e.business_group_id = i.business_group_id
AND e.person_id = g_award_rec.award_manager_id
AND a.person_id = e.person_id
AND trunc(sysdate) BETWEEN a.effective_start_date AND a.effective_end_date
AND a.primary_flag = 'Y'
-- AND a.assignment_type = 'E'; -- Bug 10124847
select count(*) from pa_periods pap
where x_date between pap.start_date and pap.end_date ;
select 1
from gl_period_statuses gps
where x_date between gps.start_date
and gps.end_date
and gps.application_id = 101
and gps.set_of_books_id = (select set_of_books_id
from pa_implementations);
select count(*) from gl_period_statuses gps
where x_date between gps.start_date and gps.end_date
and gps.application_id = 101
and gps.set_of_books_id in (select set_of_books_id from pa_implementations_all);
SELECT agreement_type
FROM pa_agreement_types
WHERE agreement_type = g_award_rec.type ;
select 1 from dual where exists (
select user_id
from fnd_user
where employee_id = g_award_rec.award_manager_id);
--Shared Service Enhancement : Added ORG_ID in the pa_agreements_pkg.insert_row
-- ====================
PROCEDURE create_agreement(p_agreement_id OUT NOCOPY NUMBER ) is
L_row_id varchar2(30) ;
PA_AGREEMENTS_PKG.INSERT_ROW(
X_ROWID => L_Row_Id,
X_AGREEMENT_ID => L_Agreement_Id,
X_CUSTOMER_ID => g_award_rec.funding_source_id, --G_bill_contact_rec.customer_id, bug 3076921
X_AGREEMENT_NUM => g_award_rec.award_number,
X_AGREEMENT_TYPE => g_award_rec.type,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => g_award_rec.last_updated_by,
X_CREATION_DATE => sysdate,
X_CREATED_BY => g_award_rec.created_by,
X_LAST_UPDATE_LOGIN => g_award_rec.last_update_login,
X_OWNED_BY_PERSON_ID => g_award_rec.award_manager_id,
X_TERM_ID => g_award_rec.billing_term,
X_REVENUE_LIMIT_FLAG => nvl(g_award_rec.hard_limit_flag, 'N'), -- Bug 2464841 : Changed 'Y'to'N'
X_AMOUNT => 0,
X_DESCRIPTION => NULL,
X_EXPIRATION_DATE => g_award_rec.close_date,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_TEMPLATE_FLAG => NULL,
X_PM_AGREEMENT_REFERENCE => NULL,
X_PM_PRODUCT_CODE => NULL,
-- Bug 2464841 : Added parameters for 11.5 PA-J certification.
X_OWNING_ORGANIZATION_ID => NULL,
X_AGREEMENT_CURRENCY_CODE => pa_currency.get_currency_code,
X_INVOICE_LIMIT_FLAG => nvl(g_award_rec.invoice_limit_flag, 'N'), /*Bug 6642901*/
X_ORG_ID => g_award_rec.org_id
);
PROCEDURE insert_award_record ( X_return_status in out NOCOPY varchar2) IS
l_row_id varchar2(50) ;
select gms_awards_s.NEXTVAL
into g_award_rec.award_id
from DUAL ;
INSERT into gms_awards_all
(
AWARD_ID ,
AWARD_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
AWARD_SHORT_NAME ,
AWARD_FULL_NAME ,
FUNDING_SOURCE_ID ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
CLOSE_DATE ,
FUNDING_SOURCE_AWARD_NUMBER ,
AWARD_PURPOSE_CODE ,
STATUS ,
ALLOWABLE_SCHEDULE_ID ,
IDC_SCHEDULE_ID ,
REVENUE_DISTRIBUTION_RULE ,
BILLING_FREQUENCY ,
BILLING_DISTRIBUTION_RULE ,
BILLING_FORMAT ,
BILLING_TERM ,
AWARD_PROJECT_ID ,
AGREEMENT_ID ,
AWARD_TEMPLATE_FLAG ,
PREAWARD_DATE ,
AWARD_MANAGER_ID ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
AGENCY_SPECIFIC_FORM ,
BILL_TO_CUSTOMER_ID ,
TRANSACTION_NUMBER ,
AMOUNT_TYPE ,
BOUNDARY_CODE ,
FUND_CONTROL_LEVEL_AWARD ,
FUND_CONTROL_LEVEL_TASK ,
FUND_CONTROL_LEVEL_RES_GRP ,
FUND_CONTROL_LEVEL_RES ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
ATTRIBUTE16 ,
ATTRIBUTE17 ,
ATTRIBUTE18 ,
ATTRIBUTE19 ,
ATTRIBUTE20 ,
ATTRIBUTE21 ,
ATTRIBUTE22 ,
ATTRIBUTE23 ,
ATTRIBUTE24 ,
ATTRIBUTE25 ,
TEMPLATE_START_DATE_ACTIVE ,
TEMPLATE_END_DATE_ACTIVE ,
TYPE ,
ORG_ID ,
COST_IND_SCH_FIXED_DATE ,
LABOR_INVOICE_FORMAT_ID ,
NON_LABOR_INVOICE_FORMAT_ID ,
BILL_TO_ADDRESS_ID ,
SHIP_TO_ADDRESS_ID ,
LOC_BILL_TO_ADDRESS_ID ,
LOC_SHIP_TO_ADDRESS_ID ,
AWARD_ORGANIZATION_ID ,
HARD_LIMIT_FLAG ,
INVOICE_LIMIT_FLAG , /*Bug 6642901*/
BILLING_OFFSET ,
BILLING_CYCLE_ID ,
PROPOSAL_ID ,
BUDGET_WF_ENABLED_FLAG
)
values
(
g_award_rec.AWARD_ID ,
g_award_rec.AWARD_NUMBER ,
g_award_rec.LAST_UPDATE_DATE ,
g_award_rec.LAST_UPDATED_BY ,
g_award_rec.CREATION_DATE ,
g_award_rec.CREATED_BY ,
g_award_rec.LAST_UPDATE_LOGIN ,
g_award_rec.AWARD_SHORT_NAME ,
g_award_rec.AWARD_FULL_NAME ,
g_award_rec.FUNDING_SOURCE_ID ,
g_award_rec.START_DATE_ACTIVE ,
g_award_rec.END_DATE_ACTIVE ,
g_award_rec.CLOSE_DATE ,
g_award_rec.FUNDING_SOURCE_AWARD_NUMBER ,
g_award_rec.AWARD_PURPOSE_CODE ,
g_award_rec.STATUS ,
g_award_rec.ALLOWABLE_SCHEDULE_ID ,
g_award_rec.IDC_SCHEDULE_ID ,
g_award_rec.REVENUE_DISTRIBUTION_RULE ,
g_award_rec.BILLING_FREQUENCY ,
g_award_rec.BILLING_DISTRIBUTION_RULE ,
g_award_rec.BILLING_FORMAT ,
g_award_rec.BILLING_TERM ,
g_award_rec.AWARD_PROJECT_ID ,
g_award_rec.AGREEMENT_ID ,
g_award_rec.AWARD_TEMPLATE_FLAG ,
g_award_rec.PREAWARD_DATE ,
g_award_rec.AWARD_MANAGER_ID ,
g_award_rec.REQUEST_ID ,
g_award_rec.PROGRAM_APPLICATION_ID ,
g_award_rec.PROGRAM_ID ,
g_award_rec.PROGRAM_UPDATE_DATE ,
g_award_rec.AGENCY_SPECIFIC_FORM ,
g_award_rec.BILL_TO_CUSTOMER_ID ,
g_award_rec.TRANSACTION_NUMBER ,
g_award_rec.AMOUNT_TYPE ,
g_award_rec.BOUNDARY_CODE ,
g_award_rec.FUND_CONTROL_LEVEL_AWARD ,
g_award_rec.FUND_CONTROL_LEVEL_TASK ,
g_award_rec.FUND_CONTROL_LEVEL_RES_GRP ,
g_award_rec.FUND_CONTROL_LEVEL_RES ,
g_award_rec.ATTRIBUTE_CATEGORY ,
g_award_rec.ATTRIBUTE1 ,
g_award_rec.ATTRIBUTE2 ,
g_award_rec.ATTRIBUTE3 ,
g_award_rec.ATTRIBUTE4 ,
g_award_rec.ATTRIBUTE5 ,
g_award_rec.ATTRIBUTE6 ,
g_award_rec.ATTRIBUTE7 ,
g_award_rec.ATTRIBUTE8 ,
g_award_rec.ATTRIBUTE9 ,
g_award_rec.ATTRIBUTE10 ,
g_award_rec.ATTRIBUTE11 ,
g_award_rec.ATTRIBUTE12 ,
g_award_rec.ATTRIBUTE13 ,
g_award_rec.ATTRIBUTE14 ,
g_award_rec.ATTRIBUTE15 ,
g_award_rec.ATTRIBUTE16 ,
g_award_rec.ATTRIBUTE17 ,
g_award_rec.ATTRIBUTE18 ,
g_award_rec.ATTRIBUTE19 ,
g_award_rec.ATTRIBUTE20 ,
g_award_rec.ATTRIBUTE21 ,
g_award_rec.ATTRIBUTE22 ,
g_award_rec.ATTRIBUTE23 ,
g_award_rec.ATTRIBUTE24 ,
g_award_rec.ATTRIBUTE25 ,
g_award_rec.TEMPLATE_START_DATE_ACTIVE ,
g_award_rec.TEMPLATE_END_DATE_ACTIVE ,
g_award_rec.TYPE ,
g_award_rec.ORG_ID ,
g_award_rec.COST_IND_SCH_FIXED_DATE ,
g_award_rec.LABOR_INVOICE_FORMAT_ID ,
g_award_rec.NON_LABOR_INVOICE_FORMAT_ID ,
g_award_rec.BILL_TO_ADDRESS_ID ,
g_award_rec.SHIP_TO_ADDRESS_ID ,
g_award_rec.LOC_BILL_TO_ADDRESS_ID ,
g_award_rec.LOC_SHIP_TO_ADDRESS_ID ,
g_award_rec.AWARD_ORGANIZATION_ID ,
g_award_rec.HARD_LIMIT_FLAG ,
g_award_rec.INVOICE_LIMIT_FLAG , /*Bug 6642901*/
g_award_rec.BILLING_OFFSET ,
g_award_rec.BILLING_CYCLE_ID ,
g_award_rec.proposal_id ,
g_award_rec.BUDGET_WF_ENABLED_FLAG
);
END insert_award_record ;
Select * from pa_projects_all
where project_id = x_award_project_id;
Select 'Y' from pa_proj_elements
where project_id = x_award_project_id;
insert into pa_project_options
(project_id,
option_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
select
x_award_project_id,
option_code,
SYSDATE,
fnd_global.user_id,
SYSDATE ,
fnd_global.user_id,
fnd_global.login_id
from pa_options
where option_code not in ( 'STRUCTURES', 'STRUCTURES_SS' );
SELECT project_id
FROM pa_project_copy_overrides
WHERE project_id = x_award_project_id
AND field_name = x_field_name;
-- Update projects for additional informations.
--
Update PA_PROJECTS_ALL
set cost_ind_rate_sch_id = g_award_rec.IDC_Schedule_Id,
cost_ind_sch_fixed_date = g_award_rec.cost_ind_sch_fixed_date,
labor_invoice_format_id = g_award_rec.Labor_Invoice_Format_Id,
non_labor_invoice_format_Id = g_award_rec.Non_Labor_Invoice_Format_Id,
name = g_award_rec.award_number,
segment1 = g_award_rec.award_number,
billing_cycle_id = g_award_rec.Billing_Cycle_Id,
billing_offset = NVL(g_award_rec.Billing_Offset,0) ,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where project_id = g_award_rec.award_project_id ;
-- Update project customers.
--
update PA_PROJECT_Customers
set BILL_TO_ADDRESS_ID = NVL(g_award_rec.bill_To_Address_Id, bill_TO_ADDRESS_ID ),
SHIP_TO_ADDRESS_ID = NVL(g_award_rec.Ship_To_Address_Id, SHIP_TO_ADDRESS_ID )
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.login_id
where project_id = g_award_rec.award_project_id
and customer_id = L_Project_IN_REC.CUSTOMER_ID ;
select *
from gms_awards_all
where award_id = p_award_base_id ;
select *
from gms_awards_contacts
where award_id = p_award_base_id;
select *
from gms_default_reports
where award_id = p_award_base_id
and l_base_fund_src_id = g_award_rec.funding_source_id ;
select *
from gms_personnel
where award_id = p_award_base_id
and award_role <> 'AM';
select *
from gms_reference_numbers
where award_id = p_award_base_id;
select *
from gms_awards_terms_conditions
where award_id = p_award_base_id;
-- Bug 2244805 Added if condition and delete statement
-- Requirement: If funding source has changed from funding source in base award
-- then award contacts (bill to and ship to only) should be taken from receivables.
-- If funding source has not changed from funding source in base award, then simply
-- copy ALL contacts from the base award (not from receivables)
--
-- The bill to and ship to contacts are created (based on default contacts in receivables)
-- during create_award. Therefore, if the funding source has changed, then there is no need
-- to copy any more contacts. If on the other hand, the funding source has not changed from
-- that existing in the base award, we first delete the default contacts created from
-- receivables by create_award and then copy contacts existing in the base award.
--Bug : 3455542 : Commented by Sanjay Banerjee
--create_award procedure is alredy creating contacts, removing these contacts and re-creating
--does not make sense. Also, create_contact is not just a copy procedure, it does the validation too.
--We need to create contacts based on the funding_source given. Even if the funding source is same,
--as before, we have to query to get the latest bill_to and ship_to address_ids.
--
/*****
if (l_base_fund_src_id = g_award_rec.funding_source_id) then
delete from gms_awards_contacts
where award_id = g_award_rec.award_id;
l_awards_contacts.last_update_date := SYSDATE ;
l_awards_contacts.last_updated_by := l_rec.last_updated_by ;
l_awards_contacts.last_update_login := l_rec.last_update_login ;
l_report_rec.last_update_date := l_rec.last_update_date ;
l_report_rec.last_updated_by := l_rec.last_updated_by ;
l_report_rec.last_update_login := l_rec.last_update_login ;
l_report_rec.program_update_date := l_rec.program_update_date ;
l_personnel_rec.last_update_date := SYSDATE ;
l_personnel_rec.last_updated_by := l_rec.last_updated_by ;
l_personnel_rec.last_update_login := l_rec.last_update_login ;
l_refnum_rec.last_update_date := l_rec.last_update_date ;
l_refnum_rec.last_updated_by := l_rec.last_updated_by ;
l_refnum_rec.last_update_login := l_rec.last_update_login ;
l_termscond_rec.last_update_date := l_rec.last_update_date ;
l_termscond_rec.last_updated_by := l_rec.last_updated_by ;
l_termscond_rec.last_update_login := l_rec.last_update_login ;
SELECT 'X'
FROM gms_lookups
WHERE lookup_type = 'INSTALLMENT_TYPE'
AND lookup_code = p_installment_type ;
SELECT 'X'
FROM gms_installments
WHERE installment_num = p_installment_num
AND award_id = p_award_id ;
SELECT start_date_active , end_date_active,close_date
FROM gms_awards_all
WHERE award_id = p_award_id ;
-- If the return_status <> g_ret_sts_success then we don't proceed with the Inserts.
IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
RAISE FND_API.G_EXC_ERROR;
SELECT gms_installments_s.nextval
INTO G_installment_rec.installment_id
FROM dual;
gms_installments_pkg.insert_row(
X_ROWID => l_rowid,
X_INSTALLMENT_ID => G_installment_rec.installment_id,
X_INSTALLMENT_NUM => G_installment_rec.installment_num,
X_AWARD_ID => G_installment_rec.award_id,
X_START_DATE_ACTIVE => G_installment_rec.start_date_active,
X_END_DATE_ACTIVE => G_installment_rec.end_date_active,
X_CLOSE_DATE => G_installment_rec.close_date,
X_DIRECT_COST => G_installment_rec.direct_cost,
X_INDIRECT_COST => G_installment_rec.indirect_cost,
X_ACTIVE_FLAG => G_installment_rec.active_flag,
X_BILLABLE_FLAG => G_installment_rec.billable_flag,
X_TYPE => G_installment_rec.type,
X_ISSUE_DATE => G_installment_rec.issue_date,
X_DESCRIPTION => G_installment_rec.description,
X_ATTRIBUTE_CATEGORY =>G_installment_rec.attribute_category,
X_ATTRIBUTE1 =>G_installment_rec.attribute1,
X_ATTRIBUTE2 =>G_installment_rec.attribute2,
X_ATTRIBUTE3 =>G_installment_rec.attribute3,
X_ATTRIBUTE4 =>G_installment_rec.attribute4,
X_ATTRIBUTE5 =>G_installment_rec.attribute5,
X_ATTRIBUTE6 =>G_installment_rec.attribute6,
X_ATTRIBUTE7 =>G_installment_rec.attribute7,
X_ATTRIBUTE8 =>G_installment_rec.attribute8,
X_ATTRIBUTE9 =>G_installment_rec.attribute9,
X_ATTRIBUTE10 =>G_installment_rec.attribute10,
X_ATTRIBUTE11 =>G_installment_rec.attribute11,
X_ATTRIBUTE12 =>G_installment_rec.attribute12,
X_ATTRIBUTE13 =>G_installment_rec.attribute13,
X_ATTRIBUTE14 =>G_installment_rec.attribute14,
X_ATTRIBUTE15 =>G_installment_rec.attribute15,
X_MODE => 'R' );
SELECT 'X'
from pa_employees p ,
fnd_user u
WHERE
EXISTS (SELECT null FROM per_assignments_f a
WHERE p.person_id = a.person_id
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND a.primary_flag = 'Y'
--AND a.assignment_type = 'E') --Bug 10124847
AND a.assignment_type in ('C','E'))
AND p.person_id = u.employee_id(+)
AND p.person_id = p_person_id ;
SELECT 'X'
FROM gms_lookups g
WHERE lookup_type = 'AWARD_ROLE'
AND g.lookup_code = p_award_role
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active,trunc(sysdate)) ;
SELECT start_date_active,budget_wf_enabled_flag , award_template_flag,
award_project_id , end_date_active
FROM gms_awards_all
WHERE award_id = p_award_id ;
SELECT user_id
FROM fnd_user
WHERE employee_id = G_personnel_rec.person_id ;
SELECT start_date_active, end_date_active
FROM gms_personnel
WHERE award_id = G_personnel_rec.award_id
AND award_role = 'AM'
ORDER BY start_date_active;
SELECT gms_personnel_s.nextval
INTO G_personnel_rec.personnel_id
FROM DUAL ;
gms_personnel_pkg.insert_row
( x_rowid => L_rowid,
x_personnel_id => G_personnel_rec.personnel_id,
x_award_id => G_personnel_rec.award_id,
x_person_id => G_personnel_rec.person_id,
x_award_role => G_personnel_rec.award_role,
x_start_date_active => G_personnel_rec.start_date_active,
x_end_date_active => G_personnel_rec.end_date_active,
x_required_flag => G_personnel_rec.required_flag,
x_mode => 'R'
);
SELECT 'X'
FROM gms_awards_terms_conditions
WHERE award_id = G_term_condition_rec.award_id
AND category_id = G_term_condition_rec.category_id
AND term_id = G_term_condition_rec.term_id ;
SELECT 'X'
FROM gms_tc_categories
WHERE category_id = G_term_condition_rec.category_id ;
SELECT 'X'
FROM gms_terms_conditions tc1
WHERE tc1.category_id = G_term_condition_rec.category_id
and term_id not in (select term_id from gms_terms_conditions tc
where
(
(tc.start_date_active > l_award_start_date_active
and tc.start_date_active > l_award_end_date_active
and tc.start_date_active is not null and tc.end_date_active is not null)
or
(tc.end_date_active < l_award_start_date_active
and tc.end_date_active < l_award_end_date_active
and tc.start_date_active is not null
and tc.end_date_active is not null)
or
( tc.start_date_active is null
and tc.end_date_active < l_award_start_date_active
and tc.end_date_active is not null)
or
(tc.end_date_active is null
and tc.start_date_active > l_award_end_date_active
and tc.start_date_active is not null)
)
) ;
SELECT start_date_active,end_date_active
FROM gms_awards_all
WHERE award_id = G_term_condition_rec.award_id ;
gms_awards_tc_pkg.insert_row
( X_ROWID => l_rowid,
X_AWARD_ID => G_term_condition_rec.award_id,
X_CATEGORY_ID => G_term_condition_rec.category_id,
X_TERM_ID => G_term_condition_rec.term_id,
X_OPERAND => G_term_condition_rec.operand,
X_VALUE => G_term_condition_rec.value,
X_MODE => 'R'
) ;
SELECT 'X'
FROM gms_lookups
WHERE lookup_type = 'REFERENCE_NUMBER'
AND lookup_code = G_reference_number_rec.type ;
SELECT 'X'
FROM gms_reference_numbers
WHERE award_id = G_reference_number_rec.award_id
AND type = G_reference_number_rec.type ;
-- insert the record but the control go to exception section.
-- ==============================================================================
IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
RAISE fnd_api.g_exc_error ;
gms_reference_numbers_pkg.insert_row
( x_rowid => l_rowid,
x_award_id => G_reference_number_rec.award_id,
x_type => G_reference_number_rec.type,
x_value => G_reference_number_rec.value,
x_required_flag => G_reference_number_rec.required_flag,
x_mode => 'R'
);
SELECT award_id,award_project_id
FROM gms_awards_all
WHERE award_id = G_contact_rec.award_id;
SELECT 'X'
FROM gms_awards_all ga,
Hz_cust_account_roles acct_roles
WHERE ga.award_id = G_contact_rec.award_id
AND decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)=acct_roles.cust_account_id
AND acct_roles.cust_account_role_id = G_contact_rec.contact_id;
SELECT 'X'
FROM hz_cust_site_uses a,
Hz_cust_acct_sites b,
ar_lookups c,
gms_awards_all ga
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.cust_account_id = decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)
AND c.lookup_type = 'SITE_USE_CODE'
AND c.lookup_code = g_contact_rec.usage_code;
SELECT 'X'
FROM gms_awards_contacts
WHERE award_id = G_contact_rec.award_id
AND contact_id = G_contact_rec.contact_id -- Bug 2672027
AND customer_id = G_contact_rec.customer_id
AND usage_code = G_contact_rec.usage_code;
UPDATE pa_project_contacts
SET contact_id = DECODE(project_contact_type_code,
'BILLING', (G_contact_rec.Contact_id),
'SHIPPING',(G_contact_rec.Contact_id),
contact_id ),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE project_id = l_award_project_id
AND customer_id = G_contact_rec.customer_id;
-- Calling Table Handler to Insert the Row.
-- ========================================
G_stage := 'gms_awards_contacts_pkg.insert_row' ;
gms_awards_contacts_pkg.insert_row
( x_rowid => L_rowid,
x_award_id => G_contact_rec.award_id,
x_customer_id => G_contact_rec.customer_id,
x_contact_id => G_contact_rec.contact_id,
x_mode => 'R',
x_primary_flag => G_contact_rec.primary_flag,
x_usage_code => G_contact_rec.usage_code
);
SELECT 'X'
FROM gms_default_reports
WHERE award_id = G_report_rec.award_id
AND report_template_id = G_report_rec.report_template_id;
SELECT 'X'
FROM gms_lookups
WHERE lookup_type = 'REPORT_FREQUENCY'
AND lookup_code = G_report_rec.frequency;
SELECT 'X'
FROM hz_cust_site_uses a,
Hz_cust_acct_sites b,
ar_lookups c,
gms_awards_all d
WHERE a.cust_acct_site_id = b. cust_acct_site_id
AND b. cust_account_id = d.funding_source_id
AND d.award_id = G_report_rec.award_id
AND c.lookup_type = 'SITE_USE_CODE'
AND c.lookup_code = a.site_use_code
AND a.site_use_id = G_report_rec.site_use_id;
G_stage := 'gms_default_reports_pkg.insert_row' ;
SELECT gms_default_reports_s.nextval
INTO G_report_rec.default_report_id
FROM dual ;
-- Calling Table Handler to Insert the Row.
-- ========================================
gms_default_reports_pkg.insert_row(
X_ROWID => l_rowid,
X_DEFAULT_REPORT_ID => G_report_rec.default_report_id,
X_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
X_AWARD_ID => G_report_rec.award_id,
X_FREQUENCY => G_report_rec.frequency,
X_DUE_WITHIN_DAYS => G_report_rec.due_within_days,
X_SITE_USE_ID => G_report_rec.site_use_id,
X_COPY_NUMBER => G_report_rec.copy_number,
X_ATTRIBUTE_CATEGORY => '',
X_ATTRIBUTE1 => '',
X_ATTRIBUTE2 => '',
X_ATTRIBUTE3 => '',
X_ATTRIBUTE4 => '',
X_ATTRIBUTE5 => '',
X_ATTRIBUTE6 => '',
X_ATTRIBUTE7 => '',
X_ATTRIBUTE8 => '',
X_ATTRIBUTE9 => '',
X_ATTRIBUTE10 => '',
X_ATTRIBUTE11 => '',
X_ATTRIBUTE12 => '',
X_ATTRIBUTE13 => '',
X_ATTRIBUTE14 => '',
X_ATTRIBUTE15 => '',
X_MODE => 'R'
);
-- Calling Table Handler to Insert the Row.
-- ========================================
gms_notification_pkg.crt_default_report_events(
P_AWARD_ID => G_report_rec.award_id,
P_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
x_err_code =>l_error_code,
x_err_stage =>l_error_stage
);
SELECT 'X'
FROM gms_notifications
WHERE award_id = G_notification_rec.award_id
AND event_type = G_notification_rec.event_type
AND user_id = G_notification_rec.user_id;
SELECT 'X'
FROM gms_default_reports
WHERE award_id = G_notification_rec.award_id
AND report_template_id = x_report_template_id;
SELECT 'X'
FROM gms_reports gr,
gms_installments gi
WHERE gi.award_id = G_notification_rec.award_id
AND gi.installment_id = gr.installment_id
AND gr.report_template_id = x_report_template_id;
G_stage := 'gms_notification_pkg.insert_row' ;
gms_notification_pkg.insert_row(
X_ROWID => l_rowid,
X_AWARD_ID => G_notification_rec.award_id,
X_EVENT_TYPE => G_notification_rec.event_type,
X_USER_ID => G_notification_rec.user_id
);
SELECT *
FROM gms_awards_all
WHERE award_id = P_AWARD_ID;
SELECT *
FROM gms_installments
WHERE award_id = P_AWARD_ID
AND installment_id = P_INSTALLMENT_ID;
SELECT p.project_status_code,
pt.project_type_class_code,
pt.sponsored_flag,
p.template_flag,
p.start_date,
p.closed_date
FROM pa_projects_all p,
pa_project_types_all pt
WHERE p.project_id = P_PROJECT_ID
AND pt.project_type = p.project_type
And p.org_id = pt.org_id /* For Bug 5414832*/
AND pt.sponsored_flag='Y' ;
SELECT 'X'
FROM pa_tasks
WHERE project_id = P_project_id
AND task_id = P_task_id
AND task_id = top_task_id;
SELECT 'X'
FROM gms_installments i,
gms_summary_project_fundings f
WHERE i.award_id = p_award_id
AND f.installment_id = i.installment_id
AND project_id = p_project_id
AND ((task_id is null and p_task_id is not null) OR
(task_id is not null and p_task_id is null));
SELECT task_id,total_funding_amount
FROM gms_summary_project_fundings gspf
WHERE installment_id = P_installment_id
AND project_id = P_project_id
AND NVL(task_id,-99) = NVL(P_task_id,-99);
-- We select task id, funding amount from gms_summary_project_fundings table
-- We default the Project Funding Level to 'P' means Project Level.
-- If the cursor did not find any record, then it would be 'F' meaning First Time
-- time funding. If the select returns a Task Id then it would be Task Level
-- Funding.
-- ==============================================================================
G_stage := 'Add_funding.Getting Funding Level' ;
G_stage := 'gms_project_fundings_pkg.insert_row' ;
SELECT gms_project_fundings_s.nextval
INTO x_gms_project_funding_id
FROM dual;
GMS_PROJECT_FUNDINGS_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_GMS_PROJECT_FUNDING_ID => x_gms_project_funding_id,
X_PROJECT_FUNDING_ID => l_project_funding_id,
X_PROJECT_ID => P_project_id,
X_TASK_ID => P_task_id,
X_INSTALLMENT_ID => P_installment_id,
X_FUNDING_AMOUNT => P_amount,
X_DATE_ALLOCATED => P_funding_date,
X_MODE => 'R'
);
-- GMS_SUMMARY_PROJECT_FUNDINGS need to be updated with this amount
-- if this project and task and installment combination exists or
-- this need to be inserted.
-- ================================================================
G_stage := 'gms_summary_project_fundings.create_funding' ;
-- need to be updated with this information. The revenue budget
-- for the award project need to be re built with the updated
-- amounts.
-- =============================================================
G_stage := 'gms_multi_funding.create_award_funding' ;