[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asset_name ,
asset_description1,
asset_description2,
asset_description3,
asset_desc_separator,
asset_location,
asset_category
FROM ipa_asset_naming_conventions;
SELECT pei.attribute8,
pei.attribute9,
pei.attribute10,
pei.attribute6, --crl_inventory_item
pei.attribute7, --crl_serial_number
pt.task_name,
pt.attribute10 task_attribute10,
ppr.name project_name,
pt.task_id,
ppr.project_id,
pei.expenditure_item_id
FROM pa_projects_all ppr, -- Changed to _all as part of MOAC changes.
pa_project_types ppt,
-- pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_tasks pt,
pa_tasks pt2
WHERE ppr.segment1 between x_project_num_from and x_project_num_to and
ppr.template_flag <> 'Y' and
ppr.project_status_code <> 'CLOSED' and
ppr.project_type = ppt.project_type and
ppt.cip_grouping_method_code = 'CIPGCE' and
--nvl(ppt.attribute10,'N') = 'Y' and
ppt.project_type_class_code = 'CAPITAL' and
nvl(ppr.attribute10,'Y') ='Y' and
nvl(pt2.attribute9,'Y') ='Y' and
ppt.interface_asset_cost_code = 'F'
AND pt.project_id = ppr.project_id
AND ppt.org_id = ppr.org_id -- Fix for bug : 4969694
--AND pcdl.expenditure_item_id = pei.expenditure_item_id
-- dcharlto 4/21/99 crl3.1
AND decode(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'Y',pei.expenditure_item_id,-99) = decode(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'Y',nvl(IPA_ASSET_MECH_APIS_PKG.g_expenditure_item_id,pei.expenditure_item_id), -99)
-- dcharlto 4/21/99 crl3.1
--AND decode(nvl(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N'),'Y','N',pei.revenue_distributed_flag) = 'N'
--AND pei.revenue_distributed_flag||'' = 'N'
--AND pcdl.line_type = DECODE(ppt.capital_cost_type_code,'R','R','B','D','R')
--AND pcdl.billable_flag = 'Y'
AND pei.billable_flag = 'Y'
--AND pcdl.pa_date <= x_pa_date
AND pei.expenditure_item_date <= x_pa_date
AND pei.task_id = pt.task_id
AND nvl(pei.CRL_ASSET_CREATION_STATUS_CODE,'N') <>'Y'
AND pt.top_task_id = pt2.task_id
AND ((pei.attribute8 is not null) OR (pei.attribute9 is not null)
OR (pei.attribute10 is not null) )
/* Added for Bug 3574567 */
AND (pei.revenue_distributed_flag = 'N' OR
(pei.revenue_distributed_flag = 'Y'
AND NOT EXISTS (SELECT 'This CDL was summarized before'
FROM pa_project_asset_line_details pald,
pa_project_asset_lines pal
WHERE pald.expenditure_item_id = pei.expenditure_item_id
AND pald.project_asset_line_detail_id = pal.project_asset_line_detail_id
AND pal.project_asset_id >= 1)
)
)
for update of CRL_ASSET_CREATION_STATUS_CODE NOWAIT;
select 'X'
FROM pa_project_asset_assignments ppaa
WHERE ppaa.task_id = c_task_id
AND ppaa.project_id = c_project_id
AND nvl(ppaa.attribute8,'~!@#') = nvl(c_attribute8, '~!@#')
AND nvl(ppaa.attribute9,'~!@#') = nvl(c_attribute9, '~!@#')
AND nvl(ppaa.attribute10,'~!@#') = nvl(c_attribute10, '~!@#')
/* Start Bug fix:2956569 : attribute6,7 Should be validated only when the nl_installed flag = Y*/
AND ( (NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'Y'
AND nvl(ppaa.attribute6,'~!@#') = nvl(c_attribute6, '~!@#') --crl_inventory
AND nvl(ppaa.attribute7,'~!@#') = nvl(c_attribute7, '~!@#') --serial_number
)
OR
NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'N'
);
select substr(decode(asset_naming_method_rec.asset_description1,
'ADT',task_name,
'ADP',project_name,
'ADGE1',attribute8,
'ADGE2',attribute9,
'ADGE3',attribute10)
||decode(asset_naming_method_rec.asset_description2,'None',null,asset_naming_method_rec.asset_desc_separator)||
decode(asset_naming_method_rec.asset_description2,
'ADT',task_name,
'ADP',project_name,
'ADGE1',attribute8,
'ADGE2',attribute9,
'ADGE3',attribute10)
||decode(asset_naming_method_rec.asset_description3,'None',null,asset_naming_method_rec.asset_desc_separator)||
decode(asset_naming_method_rec.asset_description3,
'ADT',task_name,
'ADP',project_name,
'ADGE1',attribute8,
'ADGE2',attribute9,
'ADGE3',attribute10)||
decode(nvl(IPA_ASSET_MECH_APIS_PKG.g_nl_installed, 'N'), 'Y',
decode(attribute6,null,null,
asset_naming_method_rec.asset_desc_separator||
attribute6|| --Inventory_item
decode(attribute7,null,null,
asset_naming_method_rec.asset_desc_separator||
attribute7)), null),1,80)
asset_description
from dual;
select category_id
from fa_categories
where upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) = upper(v_asset_category);
select location_id
from fa_locations
where upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) = upper(v_asset_location);
select bc.book_type_code
from fa_book_controls bc, fa_category_books cb, pa_implementations pi
where cb.category_id = v_category_id
and cb.book_type_code = bc.book_type_code
and bc.book_class = 'CORPORATE'
and pi.set_of_books_id = bc.set_of_books_id;
Select accounting_flex_structure
from fa_book_controls
where book_type_code = v_book_type_code;
update pa_expenditure_items_all pei
set (attribute8, attribute9, attribute10) =
(select aid.attribute8,aid.attribute9, aid.attribute10
from ap_invoice_distributions aid,
pa_cost_distribution_lines_all pcd
where pei.expenditure_item_id = pcd.expenditure_item_id
and pcd.system_reference2 = aid.invoice_id
and pcd.system_reference3 = aid.distribution_line_number
and pcd.transfer_status_code = 'V')
where pei.expenditure_item_id = l_exp_id;
select org_id
into v_org_id
from pa_implementations;
Select Book_Type_Code
Into V_Book_Type_Code
From Pa_Implementations;
x_err_stage := 'Inserting Into PA_PROJECT_ASSETS_ALL';
PA_PROJECT_ASSETS_PKG.Insert_Row(
X_Rowid =>v_Row_id
,X_Project_Asset_Id =>v_Project_Asset_ID
,X_Project_Id =>ei_rec.Project_Id
,X_Asset_Number =>null
,X_Asset_Name =>'X'
,X_Asset_Description =>v_Asset_Description
,X_Location_Id =>v_location_id
,X_Assigned_To_Person_Id =>null
,X_Date_Placed_In_Service =>null
,X_Asset_Category_Id =>v_category_id
,X_Asset_key_ccid => null --Added for 11i
,X_Book_Type_Code =>v_book_type_code
-- dcharlto
,X_Asset_Units =>nvl(IPA_ASSET_MECH_APIS_PKG.g_number_of_units,1)
-- dcharlto
,X_Depreciate_Flag =>'Y'
,X_Amortize_Flag =>'N'
,X_Cost_Adjustment_Flag => 'N'
,X_Reverse_Flag => 'N'
,X_Depreciation_Expense_Ccid =>v_deprn_expense_ccid
,X_Capitalized_Flag =>'N'
,X_Estimated_In_Service_Date =>to_date(null)
,X_Capitalized_Cost =>0
,X_Grouped_CIP_Cost =>0
,X_Last_Update_Date =>sysdate
,X_Last_Updated_By =>X_Last_Updated_By
,X_Creation_Date =>sysdate
,X_Created_By =>X_Created_By
,X_Last_Update_Login =>X_Last_Update_Login
,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 =>ei_rec.attribute8
,X_Attribute9 =>ei_rec.attribute9
,X_Attribute10 =>ei_rec.attribute10
,X_Attribute11 =>null
,X_Attribute12 =>null
,X_Attribute13 =>null
,X_Attribute14 =>null
,X_Attribute15 =>null
--Bug 3068204, added the new parameters included in PA.L
, X_Project_Asset_Type =>'ESTIMATED'
, X_Estimated_Units =>1
, X_Parent_Asset_Id =>null
, X_Estimated_Cost =>null
, X_Manufacturer_Name =>null
, X_Model_Number =>null
, X_Serial_Number =>null
, X_Tag_Number =>null
, X_Capital_Hold_Flag =>'N'
, X_Ret_Target_Asset_Id =>null
, X_ORG_ID => v_org_id -- MOAC changes
);
update pa_project_assets
set asset_name = substr(v_asset_name,1,30-length(asset_naming_method_rec.asset_desc_separator||to_char(v_project_asset_id)))
||asset_naming_method_rec.asset_desc_separator||to_char(v_project_asset_id),
org_id = v_org_id,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
where rowid = v_row_id;
x_err_stage := 'Inserting Into PA_PROJECT_ASSETS_ASSIGNMENTS';
PA_PROJ_ASSET_ASSIGN_PKG.insert_row(X_Rowid =>v_row_id2
,X_Project_Asset_Id =>v_project_asset_id
,X_Task_Id => ei_rec.task_id
,X_Project_Id => ei_rec.project_id
,X_Last_Update_Date =>sysdate
,X_Last_Updated_By =>x_Last_Updated_By
,X_Creation_Date =>sysdate
,X_Created_By =>x_Created_By
,X_Last_Update_Login =>x_Last_Update_Login);
update pa_project_asset_assignments
set attribute8 = ei_rec.attribute8
,attribute9 = ei_rec.attribute9
,attribute10 = ei_rec.attribute10
,attribute6 = decode(nvl(IPA_ASSET_MECH_APIS_PKG.g_nl_installed, 'N'), 'Y',
ei_rec.attribute6, attribute6) --crl_inventory_item
,attribute7 = decode(nvl(IPA_ASSET_MECH_APIS_PKG.g_nl_installed, 'N'), 'Y',
ei_rec.attribute7, attribute7) --crl_serial_number
where rowid = v_row_id2;
update pa_expenditure_items_all
set crl_asset_creation_rej_code = v_rejection_code,
crl_asset_creation_status_code = decode(v_rejection_code,null,'Y','R') ,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
where current of get_expenditure_items;
select 'X'
from pa_tasks pt,
pa_tasks pt2,
pa_project_types ppt,
pa_projects_all pp -- Changed to _ALL as part of MOAC changes
where pp.project_type = ppt.project_type
and ppt.cip_grouping_method_code = 'CIPGCE'
--and nvl(ppt.attribute10,'N') = 'Y'
and pp.project_id = x_project_id
and pt.task_id= x_task_id
and pt.project_id = x_project_id
and pt2.task_id = pt.top_task_id
and nvl(pp.attribute10,'Y') = 'Y'
and nvl(pt2.attribute9,'Y') = 'Y';