ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN FE97565E9C63BB9CE0530100007FF679Y select null action_, null status_, null message_, null request_id_, null modified_columns_, to_char(pe.event_id) row_id, haouv.name operating_unit, pp.segment1 project_number, pp.name project_name, pt.task_number, pt.task_name, paa.agreement_num agreement_number, paa.agreement_type, pcv.customer_name, pcv.customer_number, xxen_util.meaning(pe.pm_product_code,'PM_PRODUCT_CODE',275) product_source, pe.pm_event_reference event_reference, pe.event_type, pet.event_type_classification, pe.completion_date event_date, pe.event_num event_number, haouv2.name organization, pe.description, pe.bill_trans_currency_code currency, pe.bill_trans_bill_amount bill_amount, pe.bill_trans_rev_amount revenue_amount, xxen_util.meaning( pe.bill_hold_flag,'YES_NO',0) bill_hold_flag, xxen_util.meaning( pe.revenue_hold_flag,'YES_NO',0) revenue_hold_flag, xxen_util.meaning( pe.adjusting_revenue_flag,'YES_NO',0) adjusting_revenue_flag, pe.bill_group, -- pp.projfunc_currency_code proj_func_currency, pct1.user_conversion_type proj_func_rate_type, pe.projfunc_rate_date proj_func_rate_date, pe.projfunc_exchange_rate proj_func_exchange_rate, pp.project_currency_code project_currency, pct2.user_conversion_type project_rate_type, pe.project_rate_date, pe.project_exchange_rate, pct3.user_conversion_type funding_rate_type, pe.funding_rate_date, pe.funding_exchange_rate, -- pe.quantity_billed bill_quantity, (select mp.organization_code from mtl_parameters mp where mp.organization_id=pe.inventory_org_id ) inventory_organization, (select msiv.concatenated_segments from mtl_system_items_vl msiv where msiv.organization_id=pe.inventory_org_id and msiv.inventory_item_id=pe.inventory_item_id ) inventory_item, pe.uom_code uom, pe.unit_price, -- pe.reference1, pe.reference2, pe.reference3, pe.reference4, pe.reference5, pe.reference6, pe.reference7, pe.reference8, pe.reference9, pe.reference10, -- pe.attribute_category, pe.attribute1, pe.attribute2, pe.attribute3, pe.attribute4, pe.attribute5, pe.attribute6, pe.attribute7, pe.attribute8, pe.attribute9, pe.attribute10, -- pe.event_id, -- null delivery_event, (select ppe.name from pa_proj_elements ppe where ppe.proj_element_id=pe.deliverable_id and ppe.object_type='PA_DELIVERABLES' ) deliverable, (select ppe.name from pa_proj_elements ppe where ppe.proj_element_id=pe.action_id and ppe.object_type='PA_ACTIONS' ) action, -- 'PA_EVENTS_DESC_FLEX' desc_flexfield_name, to_number(null) upload_row from pa_events pe, pa_projects pp, pa_tasks pt, hr_all_organization_units_vl haouv, -- ou pa_all_organizations pao, hr_all_organization_units_vl haouv2, -- organization pa_event_types pet, pa_agreements paa, pa_customers_v pcv, pa_conversion_types_v pct1, pa_conversion_types_v pct2, pa_conversion_types_v pct3 where :p_pm_product_code=:p_pm_product_code and nvl(:p_operating_unit,'Y')=nvl(:p_operating_unit,'Y') and nvl(:p_validate_dff,'Y')=nvl(:p_validate_dff,'Y') and 1=1 and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and pe.project_id=pp.project_id and pp.org_id=haouv.organization_id and pe.task_id=pt.task_id(+) and pe.organization_id=pao.organization_id and pao.org_id=haouv.organization_id and pao.pa_org_use_type='EXPENDITURES' and pao.organization_id=haouv2.organization_id and pe.event_type=pet.event_type and pe.agreement_id=paa.agreement_id(+) and paa.customer_id=pcv.customer_id(+) and pe.projfunc_rate_type=pct1.conversion_type(+) and pe.project_rate_type=pct2.conversion_type(+) and pe.funding_rate_type=pct3.conversion_type(+) Convert result SQL from legacy lexical style to standard Error/Success/Order By; add hidden upload_row; reformat main SQL to coding standards (padding, operators, (+) adjacency, drop redundant aliases); reuse shared HR Operating Unit LOV; standardize Upload Mode parameter. U A Y xxen_pa_upload.upload_project_event US PA Event Upload PA Event Upload =================== This upload can be used to create new Project Billing and Revenue Events against the Projects belonging to the Operating Units accessible to the current responsibility. If the Operating Unit parameter is specified, then the upload will be restricted to Projects within the specified Operating Unit only. Enginatics Upload 1=1 :p_operating_unit :p_pm_product_code :p_validate_dff 1 -5 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 :p_pm_product_code LOV custom Y select pl.meaning value, pl.description from pa_lookups pl where pl.lookup_type = 'PM_PRODUCT_CODE' order by value Y US Product Source 3 30 :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name US Operating Unit 4 1=1 haouv.name=:p_operating_unit US Operating Unit 5 40 1=1 :event_date=:event_date Date trunc(sysdate) US Default Event Date 6 50 :p_validate_dff LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Validate DFF Attributes If set to No, the DFF Attribute Validation logic will be bypassed. If you bypass the attribute DFF validaton, any DFF attribute values specified in the upload excel will not be uploaded. 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 ROW_ID Char varchar2 40 Y 7 OPERATING_UNIT LOV varchar2 240 HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name :$flex$.operating_unit Y Y 8 PROJECT_NUMBER LOV custom varchar2 25 Y select ppuv.project_number value, ppuv.project_name description from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and pa_project_stus_utils.is_project_in_purge_status(ppuv.project_status_code) != 'Y' and haouv.name = :$column$.operating_unit order by value Y 9 PROJECT_NAME LOV custom varchar2 30 Y select ppuv.project_name value, ppuv.project_number description from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and pa_project_stus_utils.is_project_in_purge_status(ppuv.project_status_code) != 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit order by value select ppuv.project_name from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and pa_project_stus_utils.is_project_in_purge_status(ppuv.project_status_code) != 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit Y Y 10 TASK_NUMBER LOV custom varchar2 25 Y select pt.task_number value, pt.task_name description from pa_tasks pt, pa_projects pp, hr_all_organization_units_vl haouv where haouv.organization_id = pp.org_id and pt.project_id = pp.project_id and pt.top_task_id = pt.task_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number order by value 11 TASK_NAME LOV custom varchar2 20 Y select pt.task_name value, pt.task_number description from pa_tasks pt, pa_projects pp, hr_all_organization_units_vl haouv where haouv.organization_id = pp.org_id and pt.project_id = pp.project_id and pt.top_task_id = pt.task_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and pt.task_number = :$column$.task_number order by value select pt.task_name from pa_tasks pt, pa_projects pp, hr_all_organization_units_vl haouv where haouv.organization_id = pp.org_id and pt.project_id = pp.project_id and pt.top_task_id = pt.task_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and pt.task_number = :$column$.task_number Y 12 AGREEMENT_NUMBER LOV custom varchar2 50 Y select distinct paa.agreement_num value, paa.agreement_type || ' (' || paa.start_date || ' - ' || paa.expiration_date || ') ' || pcv.customer_name description from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) and pp.date_eff_funds_consumption = 'Y' --federal order by value 13 AGREEMENT_TYPE LOV custom varchar2 30 Y select distinct paa.agreement_type value, null description from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and paa.agreement_num = :$column$.agreement_number and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) order by value select distinct paa.agreement_type from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and paa.agreement_num = :$column$.agreement_number and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) Y 14 CUSTOMER_NAME LOV custom varchar2 360 Y select distinct pcv.customer_name value, pcv.customer_number description from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) order by value select distinct pcv.customer_name from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) Y 15 CUSTOMER_NUMBER LOV custom varchar2 30 Y select distinct pcv.customer_number value, pcv.customer_name description from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) order by value select distinct pcv.customer_number from hr_all_organization_units_vl haouv, pa_projects pp, pa_tasks pt, pa_agreements_all paa, pa_summary_project_fundings pspf, pa_customers_v pcv where haouv.organization_id = pp.org_id and pt.project_id (+) = pp.project_id and pspf.project_id = pp.project_id and paa.agreement_id = pspf.agreement_id and pspf.total_baselined_amount > 0 and pcv.customer_id = paa.customer_id and haouv.name = :$column$.operating_unit and pp.segment1 = :$column$.project_number and paa.agreement_num = :$column$.agreement_number and pt.task_number (+) = nvl(:$column$.task_number,'~!~') and nvl(pspf.task_id,nvl(pt.task_id,-999)) = nvl(pt.task_id,-999) Y 16 PRODUCT_SOURCE LOV custom varchar2 Y select pl.meaning value, pl.description from pa_lookups pl where pl.lookup_type = 'PM_PRODUCT_CODE' and pl.meaning = :$flex$.product_source order by value select pl.lookup_code id, pl.meaning value from pa_lookups pl where pl.lookup_type = 'PM_PRODUCT_CODE' :$flex$.product_source Y Y 17 EVENT_REFERENCE Char varchar2 25 Y 18 EVENT_TYPE LOV custom varchar2 30 Y select pet.event_type value, pl.meaning description from pa_event_types pet, pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.lookup_code = pet.event_type_classification and pl.lookup_code not in ('REALIZED_GAINS', 'REALIZED_LOSSES') and (trunc(sysdate) between pet.start_date_active and nvl(pet.end_date_active,trunc(sysdate))) and ( (pet.event_type_classification != 'AUTOMATIC' and nvl(xxen_util.lookup_code(:$column$.adjusting_revenue_flag,'YES_NO',0),'N') = 'N') or (pet.event_type_classification = 'MANUAL' and nvl(xxen_util.lookup_code(:$column$.adjusting_revenue_flag,'YES_NO',0),'N') = 'Y') ) order by value Y 19 EVENT_TYPE_CLASSIFICATION LOV custom varchar2 30 Y select pl.meaning value, null description from pa_event_types pet, pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.lookup_code = pet.event_type_classification and pet.event_type = :$column$.event_type order by value select pl.meaning value from pa_event_types pet, pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.lookup_code = pet.event_type_classification and pet.event_type = :$column$.event_type Y 20 EVENT_DATE Date date :$flex$.default_event_date Y 21 EVENT_NUMBER Number number 22 ORGANIZATION LOV custom varchar2 240 Y select haouv2.name value, null description from pa_all_organizations pao, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv2 where pao.organization_id = haouv2.organization_id and pao.org_id = haouv.organization_id and pao.pa_org_use_type = 'EXPENDITURES' and pao.inactive_date is null and haouv.name = :$column$.operating_unit order by value Y 23 DESCRIPTION Char varchar2 240 select pet.description from pa_event_types pet, pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.lookup_code = pet.event_type_classification and pet.event_type = :$column$.event_type Y 24 CURRENCY LOV custom varchar2 15 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' and exists ( select null from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and pa_project_stus_utils.is_project_in_purge_status(ppuv.project_status_code) != 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and (fcv.currency_code = ppuv.projfunc_currency_code or ppuv.multi_currency_billing_flag = 'Y') ) order by fcv.currency_code select ppuv.projfunc_currency_code from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit Y 25 BILL_AMOUNT Number number select 0 value from pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.meaning = :$column$.event_type_classification and pl.lookup_code in ('WRITE ON','WRITE OFF') Y 26 REVENUE_AMOUNT Number number select 0 value from pa_lookups pl where pl.lookup_type = 'EVENT TYPE CLASSIFICATION' and pl.meaning = :$column$.event_type_classification and pl.lookup_code in ('INVOICE REDUCTION','DEFERRED REVENUE', 'SCHEDULED PAYMENTS') Y 27 BILL_HOLD_FLAG LOV custom varchar2 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='BILLING HOLD' and flvv.view_application_id=275 select x.id, x.value from ( select flvv.lookup_code id, flvv.meaning value from fnd_lookup_values_vl flvv where flvv.lookup_type='BILLING HOLD' and flvv.view_application_id=275 ) x xxen_util.meaning('N','YES_NO',0) 28 REVENUE_HOLD_FLAG LOV custom varchar2 Y select fl.meaning value, fl.description from fnd_lookups fl where fl.lookup_type='YES_NO' order by value desc select x.id, x.value from ( select flvv.lookup_code id, flvv.meaning value from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 ) x xxen_util.meaning('N','YES_NO',0) 29 ADJUSTING_REVENUE_FLAG LOV custom varchar2 Y select fl.meaning value, fl.description from fnd_lookups fl where fl.lookup_type='YES_NO' and fl.lookup_code='Y' select x.id, x.value from (select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value from dual ) x 30 BILL_GROUP Char varchar2 40 31 PROJ_FUNC_CURRENCY LOV custom varchar2 15 Y select ppuv.projfunc_currency_code value, null description from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit order by value select ppuv.projfunc_currency_code from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit Y 32 PROJ_FUNC_RATE_TYPE LOV custom varchar2 30 Y select pctv.user_conversion_type value, null description from pa_conversion_types_v pctv, pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.projfunc_currency_code and (pctv.conversion_type !='User' or pa_multi_currency.is_user_rate_type_allowed (:$column$.currency ,ppuv.projfunc_currency_code ,decode(ppuv.projfunc_bil_rate_date_code,'PA_INVOICE_DATE',nvl(:$column$.proj_func_rate_date,:$column$.event_date),'FIXED_DATE',:$column$.proj_func_rate_date) )= 'Y' ) order by value select pctv.conversion_type id, pctv.user_conversion_type value from pa_conversion_types_v pctv select pctv.user_conversion_type from pa_conversion_types_v pctv, pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and pctv.conversion_type = projfunc_bil_rate_type and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.projfunc_currency_code 33 PROJ_FUNC_RATE_DATE Date date select nvl(ppuv.projfunc_bil_rate_date,:$column$.event_date) from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.projfunc_currency_code 34 PROJ_FUNC_EXCHANGE_RATE Number number 35 PROJECT_CURRENCY LOV custom varchar2 15 Y select ppuv.project_currency_code value, null description from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit order by value select ppuv.project_currency_code from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit Y 36 PROJECT_RATE_TYPE LOV custom varchar2 30 Y select pctv.user_conversion_type value, null description from pa_conversion_types_v pctv, pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.project_currency_code and (pctv.conversion_type !='User' or pa_multi_currency.is_user_rate_type_allowed (:$column$.currency ,ppuv.project_currency_code ,decode(ppuv.project_bil_rate_date_code,'PA_INVOICE_DATE',nvl(:$column$.project_rate_date,:$column$.event_date),'FIXED_DATE',:$column$.project_rate_date) )= 'Y' ) order by value select pctv.conversion_type id, pctv.user_conversion_type value from pa_conversion_types_v pctv select pctv.user_conversion_type from pa_conversion_types_v pctv, pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and pctv.conversion_type = project_bil_rate_type and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.project_currency_code 37 PROJECT_RATE_DATE Date date select nvl(ppuv.project_bil_rate_date,:$column$.event_date) from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit and :$column$.currency != ppuv.project_currency_code 38 PROJECT_EXCHANGE_RATE Number number 39 FUNDING_RATE_TYPE LOV custom varchar2 30 Y select pctv.user_conversion_type value, null description from pa_conversion_types_v pctv order by value select pctv.conversion_type id, pctv.user_conversion_type value from pa_conversion_types_v pctv select ppuv.funding_rate_type from pa_projects_update_v ppuv, hr_all_organization_units_vl haouv where haouv.organization_id = ppuv.org_id and ppuv.template_flag !='Y' and ppuv.project_type = 'CONTRACT' and ppuv.cc_prvdr_flag !='Y' and ppuv.multi_currency_billing_flag = 'Y' and ppuv.project_number = :$column$.project_number and haouv.name = :$column$.operating_unit 40 FUNDING_RATE_DATE Date date 41 FUNDING_EXCHANGE_RATE Number number 42 BILL_QUANTITY Number number 43 INVENTORY_ORGANIZATION LOV custom varchar2 3 Y select ood.organization_name value, ood.organization_code description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name = :$column$.operating_unit) and nvl(ood.disable_date,sysdate)>=sysdate order by value 44 INVENTORY_ITEM LOV custom varchar2 40 Y Y select msiv.concatenated_segments value, msiv.description from org_organization_definitions ood, mtl_system_items_vl msiv where ood.organization_name=:$column$.inventory_organization and msiv.organization_id=ood.organization_id order by msiv.concatenated_segments select distinct msiv.inventory_item_id id, msiv.concatenated_segments value from mtl_system_items_vl msiv 45 UOM LOV custom varchar2 3 Y Y select uom_code value, unit_of_measure_tl description from mtl_units_of_measure_vl order by value select msiv.primary_uom_code from org_organization_definitions ood, mtl_system_items_vl msiv where ood.organization_name=:$column$.inventory_organization and msiv.organization_id=ood.organization_id and msiv.concatenated_segments = :$column$.inventory_item 46 UNIT_PRICE Number number select msiv.list_price_per_unit from org_organization_definitions ood, mtl_system_items_vl msiv where ood.organization_name=:$column$.inventory_organization and msiv.organization_id=ood.organization_id and msiv.concatenated_segments = :$column$.inventory_item 47 REFERENCE1 Char varchar2 240 48 REFERENCE2 Char varchar2 240 49 REFERENCE3 Char varchar2 240 50 REFERENCE4 Char varchar2 240 51 REFERENCE5 Char varchar2 240 52 REFERENCE6 Char varchar2 240 53 REFERENCE7 Char varchar2 240 54 REFERENCE8 Char varchar2 240 55 REFERENCE9 Char varchar2 240 56 REFERENCE10 Char varchar2 240 57 ATTRIBUTE_CATEGORY Char varchar2 30 58 ATTRIBUTE1 Char varchar2 150 59 ATTRIBUTE2 Char varchar2 150 60 ATTRIBUTE3 Char varchar2 150 61 ATTRIBUTE4 Char varchar2 150 62 ATTRIBUTE5 Char varchar2 150 63 ATTRIBUTE6 Char varchar2 150 64 ATTRIBUTE7 Char varchar2 150 65 ATTRIBUTE8 Char varchar2 150 66 ATTRIBUTE9 Char varchar2 150 67 ATTRIBUTE10 Char varchar2 150 68 EVENT_ID Number number Y 69 DELIVERY_EVENT LOV custom varchar2 select fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' and fl.lookup_code = 'Y' order by value select 'D' id, fl.meaning value from fnd_lookups fl where fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' Y 70 DELIVERABLE LOV custom varchar2 240 Y select ppe.name value, ppe.description from pa_proj_elements ppe, hr_all_organization_units haou where ppe.object_type = 'PA_DELIVERABLES' and haou.organization_id (+) = ppe.carrying_out_organization_id and nvl(haou.name,:$column$.operating_unit) = :$column$.operating_unit order by value select ppe.proj_element_id id, ppe.name value from pa_proj_elements ppe where ppe.object_type = 'PA_DELIVERABLES' Y 71 ACTION LOV custom varchar2 240 Y select ppe.name value, ppe.description from pa_proj_elements ppe, hr_all_organization_units haou where ppe.object_type = 'PA_ACTIONS' and haou.organization_id (+) = ppe.carrying_out_organization_id and nvl(haou.name,:$column$.operating_unit) = :$column$.operating_unit order by value select ppe.proj_element_id id, ppe.name value from pa_proj_elements ppe where ppe.object_type = 'PA_ACTIONS' Y 72 DESC_FLEXFIELD_NAME Char varchar2 select 'PA_EVENTS_DESC_FLEX' from dual where :$flex$.validate_dff_attributes = xxen_util.meaning('Y','YES_NO',0) Y 73 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 request_id_ out Number REQUEST_ID_ U 5 p_row_id in/out Char ROW_ID U 6 p_operating_unit in Char OPERATING_UNIT U 7 p_project_number in Char PROJECT_NUMBER U 8 p_task_number in Char Y TASK_NUMBER U 9 p_customer_number in Char Y CUSTOMER_NUMBER U 10 p_agreement_type in Char Y AGREEMENT_TYPE U 11 p_agreement_number in Char Y AGREEMENT_NUMBER U 12 p_pm_product_code in Char PRODUCT_SOURCE U 13 p_pm_event_reference in Char EVENT_REFERENCE U 14 p_event_number in Number Y EVENT_NUMBER U 15 p_event_type in Char EVENT_TYPE U 16 p_description in Char Y DESCRIPTION U 17 p_completion_date in Date EVENT_DATE U 18 p_uom_code in Char Y UOM U 19 p_quantity_billed in Number Y BILL_QUANTITY U 20 p_unit_price in Number Y UNIT_PRICE U 21 p_bill_trans_currency_code in Char Y CURRENCY U 22 p_bill_trans_bill_amount in Number Y BILL_AMOUNT U 23 p_bill_trans_rev_amount in Number Y REVENUE_AMOUNT U 24 p_organization_name in Char ORGANIZATION U 25 p_inventory_org_name in Char Y INVENTORY_ORGANIZATION U 26 p_inventory_item_id in Number Y INVENTORY_ITEM U 27 p_bill_group in Char Y BILL_GROUP U 28 p_bill_hold_flag in Char Y BILL_HOLD_FLAG U 29 p_revenue_hold_flag in Char Y REVENUE_HOLD_FLAG U 30 p_adjusting_revenue_flag in Char Y ADJUSTING_REVENUE_FLAG U 31 p_project_rate_type in Char Y PROJECT_RATE_TYPE U 32 p_project_rate_date in Date Y PROJECT_RATE_DATE U 33 p_project_exchange_rate in Number Y PROJECT_EXCHANGE_RATE U 34 p_projfunc_rate_type in Char Y PROJ_FUNC_RATE_TYPE U 35 p_projfunc_rate_date in Date Y PROJ_FUNC_RATE_DATE U 36 p_projfunc_exchange_rate in Number Y PROJ_FUNC_EXCHANGE_RATE U 37 p_funding_rate_type in Char Y FUNDING_RATE_TYPE U 38 p_funding_rate_date in Date Y FUNDING_RATE_DATE U 39 p_funding_exchange_rate in Number Y FUNDING_EXCHANGE_RATE U 40 p_event_id in Number Y EVENT_ID U 41 p_deliverable_id in Number Y DELIVERABLE U 42 p_action_id in Number Y ACTION U 43 p_context in Char Y DELIVERY_EVENT U 44 p_record_version_number in Number Y U 45 p_reference1 in Char Y REFERENCE1 U 46 p_reference2 in Char Y REFERENCE2 U 47 p_reference3 in Char Y REFERENCE3 U 48 p_reference4 in Char Y REFERENCE4 U 49 p_reference5 in Char Y REFERENCE5 U 50 p_reference6 in Char Y REFERENCE6 U 51 p_reference7 in Char Y REFERENCE7 U 52 p_reference8 in Char Y REFERENCE8 U 53 p_reference9 in Char Y REFERENCE9 U 54 p_reference10 in Char Y REFERENCE10 U 55 p_desc_flex_name in Char Y DESC_FLEXFIELD_NAME U 56 p_attribute_category in Char Y ATTRIBUTE_CATEGORY U 57 p_attribute1 in Char Y ATTRIBUTE1 U 58 p_attribute2 in Char Y ATTRIBUTE2 U 59 p_attribute3 in Char Y ATTRIBUTE3 U 60 p_attribute4 in Char Y ATTRIBUTE4 U 61 p_attribute5 in Char Y ATTRIBUTE5 U 62 p_attribute6 in Char Y ATTRIBUTE6 U 63 p_attribute7 in Char Y ATTRIBUTE7 U 64 p_attribute8 in Char Y ATTRIBUTE8 U 65 p_attribute9 in Char Y ATTRIBUTE9 U 66 p_attribute10 in Char Y ATTRIBUTE10 Error SQL Order By upload_row, organization, project_number, task_number, event_date, event_type, event_number Success SQL select xm.action_, xm.status_, xm.message_, xm.request_id_, xm.modified_columns_, to_char(pe.event_id) row_id, haouv.name operating_unit, pp.segment1 project_number, pp.name project_name, pt.task_number, pt.task_name, paa.agreement_num agreement_number, paa.agreement_type, pcv.customer_name, pcv.customer_number, xxen_util.meaning(pe.pm_product_code,'PM_PRODUCT_CODE',275) product_source, pe.pm_event_reference event_reference, pe.event_type, pet.event_type_classification, pe.completion_date event_date, pe.event_num event_number, haouv2.name organization, pe.description, pe.bill_trans_currency_code currency, pe.bill_trans_bill_amount bill_amount, pe.bill_trans_rev_amount revenue_amount, xxen_util.meaning(pe.bill_hold_flag,'YES_NO',0) bill_hold_flag, xxen_util.meaning(pe.revenue_hold_flag,'YES_NO',0) revenue_hold_flag, xxen_util.meaning(pe.adjusting_revenue_flag,'YES_NO',0) adjusting_revenue_flag, pe.bill_group, -- pp.projfunc_currency_code proj_func_currency, pct1.user_conversion_type proj_func_rate_type, pe.projfunc_rate_date proj_func_rate_date, pe.projfunc_exchange_rate proj_func_exchange_rate, pp.project_currency_code project_currency, pct2.user_conversion_type project_rate_type, pe.project_rate_date, pe.project_exchange_rate, pct3.user_conversion_type funding_rate_type, pe.funding_rate_date, pe.funding_exchange_rate, -- pe.quantity_billed bill_quantity, (select mp.organization_code from mtl_parameters mp where mp.organization_id=pe.inventory_org_id ) inventory_organization, (select msiv.concatenated_segments from mtl_system_items_vl msiv where msiv.organization_id=pe.inventory_org_id and msiv.inventory_item_id=pe.inventory_item_id ) inventory_item, pe.uom_code uom, pe.unit_price, -- pe.reference1, pe.reference2, pe.reference3, pe.reference4, pe.reference5, pe.reference6, pe.reference7, pe.reference8, pe.reference9, pe.reference10, -- pe.attribute_category, pe.attribute1, pe.attribute2, pe.attribute3, pe.attribute4, pe.attribute5, pe.attribute6, pe.attribute7, pe.attribute8, pe.attribute9, pe.attribute10, -- pe.event_id, null delivery_event, (select ppe.name from pa_proj_elements ppe where ppe.proj_element_id=pe.deliverable_id and ppe.object_type='PA_DELIVERABLES' ) deliverable, (select ppe.name from pa_proj_elements ppe where ppe.proj_element_id=pe.action_id and ppe.object_type='PA_ACTIONS' ) action, -- xm.desc_flexfield_name, xm.upload_row from xxen_pa_event_upload_9030_u xm, pa_events pe, pa_projects pp, pa_tasks pt, hr_all_organization_units_vl haouv, pa_all_organizations pao, hr_all_organization_units_vl haouv2, pa_event_types pet, pa_agreements paa, pa_customers_v pcv, pa_conversion_types_v pct1, pa_conversion_types_v pct2, pa_conversion_types_v pct3 where xm.status_code_=xxen_upload.status_success and pe.event_id=to_number(xm.row_id) and pe.project_id=pp.project_id and pp.org_id=haouv.organization_id and pe.task_id=pt.task_id(+) and pe.organization_id=pao.organization_id and pao.pa_org_use_type='EXPENDITURES' and pao.org_id=haouv.organization_id and pao.organization_id=haouv2.organization_id and pe.event_type=pet.event_type and pe.agreement_id=paa.agreement_id(+) and paa.customer_id=pcv.customer_id(+) and pe.projfunc_rate_type=pct1.conversion_type(+) and pe.project_rate_type=pct2.conversion_type(+) and pe.funding_rate_type=pct3.conversion_type(+)