ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 21C67ECE56D62CC4E0630100007F31B8Y with lc as ( select /*+ materialize*/ x.organization_id, x.inventory_item_id, max(x.cost_type) cost_type, max(x.cost_type_desc) cost_type_desc, sum(decode(x.cost_component_code,:p_cost_component1,x.component_cost)) c1, sum(decode(x.cost_component_code,:p_cost_component2,x.component_cost)) c2, sum(decode(x.cost_component_code,:p_cost_component3,x.component_cost)) c3, sum(decode(x.cost_component_code,:p_cost_component4,x.component_cost)) c4, sum(decode(x.cost_component_code,:p_cost_component5,x.component_cost)) c5, sum(decode(x.cost_component_code,:p_cost_component6,x.component_cost)) c6, sum(decode(x.cost_component_code,:p_cost_component7,x.component_cost)) c7, sum(decode(x.cost_component_code,:p_cost_component8,x.component_cost)) c8, sum(decode(x.cost_component_code,:p_cost_component9,x.component_cost)) c9, sum(decode(x.cost_component_code,:p_cost_component10,x.component_cost)) c10, sum(decode(x.cost_component_code,:p_cost_component11,x.component_cost)) c11, sum(decode(x.cost_component_code,:p_cost_component12,x.component_cost)) c12, sum(decode(x.cost_component_code,:p_cost_component13,x.component_cost)) c13, sum(decode(x.cost_component_code,:p_cost_component14,x.component_cost)) c14, sum(decode(x.cost_component_code,:p_cost_component15,x.component_cost)) c15, sum(decode(x.cost_component_code,:p_cost_component16,x.component_cost)) c16, sum(decode(x.cost_component_code,:p_cost_component17,x.component_cost)) c17, sum(decode(x.cost_component_code,:p_cost_component18,x.component_cost)) c18, sum(decode(x.cost_component_code,:p_cost_component19,x.component_cost)) c19, sum(decode(x.cost_component_code,:p_cost_component20,x.component_cost)) c20, sum(decode(x.cost_component_code,:p_cost_component21,x.component_cost)) c21, sum(decode(x.cost_component_code,:p_cost_component22,x.component_cost)) c22, sum(decode(x.cost_component_code,:p_cost_component23,x.component_cost)) c23, sum(decode(x.cost_component_code,:p_cost_component24,x.component_cost)) c24, sum(decode(x.cost_component_code,:p_cost_component25,x.component_cost)) c25, sum(decode(x.cost_component_code,:p_cost_component26,x.component_cost)) c26, sum(decode(x.cost_component_code,:p_cost_component27,x.component_cost)) c27, sum(decode(x.cost_component_code,:p_cost_component28,x.component_cost)) c28, sum(decode(x.cost_component_code,:p_cost_component29,x.component_cost)) c29, sum(decode(x.cost_component_code,:p_cost_component30,x.component_cost)) c30, sum(case when x.cost_component_code in (:p_cost_component1,:p_cost_component2,:p_cost_component3,:p_cost_component4,:p_cost_component5,:p_cost_component6,:p_cost_component7,:p_cost_component8,:p_cost_component9,:p_cost_component10,:p_cost_component11,:p_cost_component12,:p_cost_component13,:p_cost_component14,:p_cost_component15,:p_cost_component16,:p_cost_component17,:p_cost_component18,:p_cost_component19,:p_cost_component20,:p_cost_component21,:p_cost_component22,:p_cost_component23,:p_cost_component24,:p_cost_component25,:p_cost_component26,:p_cost_component27,:p_cost_component28,:p_cost_component29,:p_cost_component30) then null else x.component_cost end) other_costs, sum(x.component_cost) total_item_cost from ( select x.organization_id, x.inventory_item_id, x.cost_type, x.cost_type_desc, x.cost_component_code, round(sum(x.component_cost),9) component_cost from ( select mp.organization_id, gic.inventory_item_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_desc, ccmb.cost_cmpntcls_code cost_component_code, gid.cmptcost_amt component_cost from mtl_parameters mp, hr_organization_information hoi, cm_whse_asc cwa, gmf_fiscal_policies gfp, gmf_period_statuses gps, cm_mthd_mst cmm, gl_item_cst gic, gl_item_dtl gid, cm_cmpt_mst_b ccmb where :p_cost_type is null and 32=32 and 35=35 and 36=36 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and mp.organization_id=cwa.organization_id(+) and to_number(hoi.org_information2)=gfp.legal_entity_id and gfp.legal_entity_id=gps.legal_entity_id and gfp.cost_type_id=gps.cost_type_id and gps.cost_type_id=cmm.cost_type_id and gps.period_id=gic.period_id and gps.cost_type_id=gic.cost_type_id and nvl(cwa.cost_organization_id,mp.organization_id)=gic.organization_id and gic.delete_mark=0 and gic.itemcost_id=gid.itemcost_id and gid.cost_cmpntcls_id=ccmb.cost_cmpntcls_id union all select mp.organization_id, ccd.inventory_item_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_desc, ccmb.cost_cmpntcls_code cost_component_code, ccd.cmpnt_cost component_cost from mtl_parameters mp, hr_organization_information hoi, cm_whse_asc cwa, gmf_period_statuses gps, cm_mthd_mst cmm, cm_cmpt_dtl ccd, cm_cmpt_mst_b ccmb where :p_cost_type is not null and 32=32 and 33=33 and 35=35 and 36=36 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and mp.organization_id=cwa.organization_id(+) and to_number(hoi.org_information2)=gps.legal_entity_id and gps.cost_type_id=cmm.cost_type_id and gps.period_id=ccd.period_id and gps.cost_type_id=ccd.cost_type_id and nvl(cwa.cost_organization_id,mp.organization_id)=ccd.organization_id and ccd.delete_mark=0 and ccd.cost_cmpntcls_id=ccmb.cost_cmpntcls_id and ccmb.product_cost_ind=1 ) x group by x.organization_id, x.inventory_item_id, x.cost_type, x.cost_type_desc, x.cost_component_code ) x group by x.organization_id, x.inventory_item_id ), last_formula as ( select max(ffmv.formula_id) keep (dense_rank last order by ffmv.formula_vers) formula_id from mtl_parameters mp, fm_form_mst_vl ffmv, gmd_status_vl gsv where 32=32 and 37=37 and mp.organization_id=ffmv.owner_organization_id and ffmv.delete_mark=0 and ffmv.formula_status=gsv.status_code group by ffmv.owner_organization_id, ffmv.formula_no ) select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, :p_calendar_code calendar_code, :p_period_code period_code, &category_segments1 ffmv.formula_no formula_number, ffmv.formula_vers formula_version, gsv.status_code, gsv.meaning status_description, ffmv.formula_desc1 formula_description1, ffmv.formula_class, gfcv.formula_class_desc formula_class_description, msiv.concatenated_segments product_item_number, &category_segments2 &category_segments3 nvl(ffmv.attribute4,msiv.attribute4) label_approval, fmd.line_no, msiv2.concatenated_segments line_item_number, msiv2.description line_item_description, xxen_util.meaning(msiv2.item_type,'ITEM_TYPE',3) item_type, xxen_util.meaning(fmd.line_type,'GMD_FORMULA_ITEM_TYPE',552) formula_item_type, (select misv.inventory_item_status_code_tl from mtl_item_status_vl misv where msiv2.inventory_item_status_code=misv.inventory_item_status_code) item_status, xxen_util.meaning(msiv2.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, &p_show_more_details fmd.detail_uom detailed_uom, msiv2.primary_uom_code primary_uom, fmd.qty quantity, fmd.qty*mucv.conversion_rate primary_quantity, fmd.cost_alloc cost_allocation, lc.cost_type, lc.cost_type_desc cost_type_description, nvl(lc.c1,0) "&p_cost_component1", nvl(lc.c2,0) "&p_cost_component2", nvl(lc.c3,0) "&p_cost_component3", nvl(lc.c4,0) "&p_cost_component4", nvl(lc.c5,0) "&p_cost_component5", nvl(lc.c6,0) "&p_cost_component6", nvl(lc.c7,0) "&p_cost_component7", nvl(lc.c8,0) "&p_cost_component8", nvl(lc.c9,0) "&p_cost_component9", nvl(lc.c10,0) "&p_cost_component10", nvl(lc.c11,0) "&p_cost_component11", nvl(lc.c12,0) "&p_cost_component12", nvl(lc.c13,0) "&p_cost_component13", nvl(lc.c14,0) "&p_cost_component14", nvl(lc.c15,0) "&p_cost_component15", nvl(lc.c16,0) "&p_cost_component16", nvl(lc.c17,0) "&p_cost_component17", nvl(lc.c18,0) "&p_cost_component18", nvl(lc.c19,0) "&p_cost_component19", nvl(lc.c20,0) "&p_cost_component20", nvl(lc.c21,0) "&p_cost_component21", nvl(lc.c22,0) "&p_cost_component22", nvl(lc.c23,0) "&p_cost_component23", nvl(lc.c24,0) "&p_cost_component24", nvl(lc.c25,0) "&p_cost_component25", nvl(lc.c26,0) "&p_cost_component26", nvl(lc.c27,0) "&p_cost_component27", nvl(lc.c28,0) "&p_cost_component28", nvl(lc.c29,0) "&p_cost_component29", nvl(lc.c30,0) "&p_cost_component30", nvl(lc.other_costs,0) other_costs, nvl(lc.total_item_cost,0) total_item_cost from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, fm_form_mst_vl ffmv, &p_latest_formula_table gmd_status_vl gsv, gmd_formula_class_vl gfcv, fm_matl_dtl fmd, mtl_system_items_vl msiv, mtl_system_items_vl msiv2, mtl_uom_conversions_view mucv, &p_show_more_dtl_tables lc where 31=31 and 32=32 and 34=34 and 37=37 and 38=38 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.process_enabled_flag='Y' and mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haouv.organization_id and sysdate<nvl(haouv.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=ffmv.owner_organization_id and ffmv.delete_mark=0 and ffmv.formula_status=gsv.status_code and ffmv.formula_class=gfcv.formula_class(+) and ffmv.formula_id=fmd.formula_id and mp.organization_id=msiv.organization_id and msiv.inventory_item_id=(select max(fmd2.inventory_item_id) from fm_matl_dtl fmd2 where ffmv.formula_id=fmd2.formula_id and fmd2.line_type=1) and fmd.organization_id=msiv2.organization_id and fmd.inventory_item_id=msiv2.inventory_item_id and fmd.organization_id=mucv.organization_id and fmd.inventory_item_id=mucv.inventory_item_id and fmd.detail_uom=mucv.uom_code and fmd.organization_id=lc.organization_id(+) and fmd.inventory_item_id=lc.inventory_item_id(+) &p_latest_formula_joins &p_show_more_dtl_joins order by ledger, operating_unit, org_code, formula_number, formula_version, formula_item_type desc, product_item_number, fmd.line_no, line_item_number Rebuilt to the Blitz Report SQL standards: the component costs of all formula lines are read in one pass instead of 32 subqueries per line, Other Costs no longer stays zero unless all 30 cost component parameters are entered (the not in list compared with empty parameters), the cost type columns belong to the line item instead of the product, Show More Details takes the lot size of the latest validity rule of the latest recipe instead of listing a line once per validity rule, Contribute to Yield shows Yes or blank, item status translated. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. #,##0.00000;[Red](#,##0.00000) US CAC OPM Costed Formula Report showing OPM formulas and item costs, by OPM Cost Component Class. The report automatically displays the first thirty Cost Components, sorted by Usage Indicator (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc), then by the Cost Component Class. With the "Other Costs" column summing up any other non-displayed Cost Component Classes. For a different selection of Cost Component Classes, you may override any of the defaulted Cost Component Classes. If you have fewer than thirty Cost Components the report automatically displays "Not Available", for the succeeding Cost Component columns. And if you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end item costs. Note: The Label Approval column is from a user-defined Formula field, attribute4. Your use of these descriptive flexfields, may be different and may require you to customize this report. -- | Version Modified on Modified by Description -- | ======= =========== ============== -- | 1.0 02 Jun 2024 Douglas Volz Initial Coding based on client's sample report. -- | 1.1 03 Jun 2024 Douglas Volz UOM conversions for formula line quantities -- | 1.2 08 Jun 2024 Douglas Volz Replaced Cost Component rownum sort logic. -- | 1.3 12 Jun 2024 Douglas Volz Cleaned-up naming for Cost Component parameters, fixed item number parameter. -- | 1.4 03 Aug 2024 Douglas Volz Add OPM Cost Organizations to get correct item costs. -- | 1.5 05 Aug 2024 Douglas Volz Add inventory organization access control security. -- | 1.6 07 Aug 2024 Douglas Volz Not all formulas are assigned to classes, needs outer join. -- | 1.7 08 Aug 2024 Douglas Volz Add item status and Make/Buy columns. -- | 1.8 09 Aug 2024 Douglas Volz Add parameter "Show More Details" for Scale Type, Contribute to Yield, Std Lot Size and End Date. -- | 1.9 17 Aug 2024 Douglas Volz Add parameters "Only Show Latest Version" and "Effective Date". -- | 1.10 18 Aug 2024 Douglas Volz Restructured code. -- | 1.11 07 Sep 2024 Douglas Volz Fixed Cost Component parameters, from lexicals to bind variables. -- | 1.12 10 Sep 2024 Douglas Volz Add Std Lot Size UOM column. -- | 1.13 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. Enginatics R12 only &category_segments1 &category_segments2 &category_segments3 &p_cost_component1 &p_cost_component10 &p_cost_component11 &p_cost_component12 &p_cost_component13 &p_cost_component14 &p_cost_component15 &p_cost_component16 &p_cost_component17 &p_cost_component18 &p_cost_component19 &p_cost_component2 &p_cost_component20 &p_cost_component21 &p_cost_component22 &p_cost_component23 &p_cost_component24 &p_cost_component25 &p_cost_component26 &p_cost_component27 &p_cost_component28 &p_cost_component29 &p_cost_component3 &p_cost_component30 &p_cost_component4 &p_cost_component5 &p_cost_component6 &p_cost_component7 &p_cost_component8 &p_cost_component9 &p_latest_formula_joins &p_latest_formula_table &p_show_more_details &p_show_more_dtl_joins &p_show_more_dtl_tables 31=31 32=32 33=33 34=34 35=35 36=36 37=37 38=38 :p_calendar_code :p_cost_component1 :p_cost_component10 :p_cost_component11 :p_cost_component12 :p_cost_component13 :p_cost_component14 :p_cost_component15 :p_cost_component16 :p_cost_component17 :p_cost_component18 :p_cost_component19 :p_cost_component2 :p_cost_component20 :p_cost_component21 :p_cost_component22 :p_cost_component23 :p_cost_component24 :p_cost_component25 :p_cost_component26 :p_cost_component27 :p_cost_component28 :p_cost_component29 :p_cost_component3 :p_cost_component30 :p_cost_component4 :p_cost_component5 :p_cost_component6 :p_cost_component7 :p_cost_component8 :p_cost_component9 :p_cost_type :p_period_code 1 10 33=33 cmm.cost_mthd_code=:p_cost_type LOV Cost Type (OPM Orgs Only) 184A903C40A16B3FE0630100007F2880 select x.value, x.description from ( select cmm.cost_mthd_code value, cmm.cost_mthd_desc description from cm_mthd_mst cmm where exists (select 'x' from gmf_calendar_assignments gca, org_organization_definitions ood, mtl_parameters mp where gca.legal_entity_id = ood.legal_entity and gca.cost_type_id = cmm.cost_type_id and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id and mp.process_enabled_flag = 'Y' ) ) x group by x.value, x.description order by value US Cost Type Cost type whose component costs value the formula lines. Leave blank to use the period-end item costs of the fiscal policy cost type. 2 20 35=35 gps.calendar_code=:p_calendar_code LOV OPM Calendar Code 19508EAA42357240E0630100007FD049 select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and exists (select 'x' from gmf_calendar_assignments gca, gmf_period_statuses gps, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id ) order by value desc Y US OPM Calendar Code 3 30 36=36 gps.period_code=:p_period_code LOV OPM Period Code 19508EAA42367240E0630100007FD049 select ccd.period_code value, ccd.period_desc description from cm_cldr_dtl ccd where exists (select 1 from gmf_period_statuses gps where gps.calendar_code = :$flex$.opm_calendar_code and gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood where (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV'))) and gps.period_code = ccd.period_code and gps.calendar_code = ccd.calendar_code ) order by ccd.end_date desc Y US OPM Period Code 4 40 &p_latest_formula_table last_formula, LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Only Show Latest Version Yes reports only the latest version of each formula; No reports all versions. 5 &p_latest_formula_joins and ffmv.formula_id=last_formula.formula_id Y US Only Show Latest Version 6 50 &p_show_more_details xxen_util.meaning(fmd.scale_type,'SCALE_TYPE',552) scale_type, xxen_util.yes(fmd.contribute_yield_ind) contribute_to_yield, last_grvr.detail_uom lot_size_uom, last_grvr.std_qty standard_lot_size, fmd.ingredient_end_date, LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show More Details Yes adds the ingredient scale type, contribute to yield, end date and the lot size of the latest validity rule of the latest recipe. 7 &p_show_more_dtl_joins and fmd.formula_id=last_grvr.formula_id(+) and fmd.inventory_item_id=last_grvr.inventory_item_id(+) Y US Show More Details 8 &p_show_more_dtl_tables ( select lr.formula_id, grvr.inventory_item_id, max(grvr.detail_uom) keep (dense_rank last order by grvr.recipe_validity_rule_id) detail_uom, max(grvr.std_qty) keep (dense_rank last order by grvr.recipe_validity_rule_id) std_qty from ( select grv.formula_id, max(grv.recipe_id) keep (dense_rank last order by grv.recipe_version) recipe_id from mtl_parameters mp, gmd_recipes_vl grv, gmd_status_vl gsv where 32=32 and 37=37 and mp.organization_id=grv.owner_organization_id and grv.recipe_status=gsv.status_code group by grv.owner_organization_id, grv.formula_id ) lr, gmd_recipe_validity_rules grvr, gmd_status_vl gsv where 37=37 and lr.recipe_id=grvr.recipe_id and grvr.start_date<=:p_effective_date and (grvr.end_date is null or grvr.end_date>=:p_effective_date) and grvr.validity_rule_status=gsv.status_code group by lr.formula_id, grvr.inventory_item_id ) last_grvr, Y US Show More Details 9 60 38=38 (fmd.ingredient_end_date is null or fmd.ingredient_end_date >= :p_effective_date) Date sysdate Y US Effective Date Formula lines ending before this date and validity rules not effective on it are excluded. 10 70 37=37 gsv.status_code=:p_formula_status LOV OPM Status Code 1A5739C5DE270375E0630100007F5142 select gsv.status_code value, gsv.description description from gmd_status_vl gsv order by to_number(gsv.status_code) select gsv.status_code value, gsv.description description from gmd_status_vl gsv where gsv.status_code = 700 order by gsv.status_code US Status to Include Formula, recipe and validity rule status to report. 11 80 &category_segments1 select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name select mcsv.category_set_name from mtl_default_category_sets mdcs, mtl_category_sets_v mcsv where mdcs.functional_area_id=19 and mdcs.category_set_id=mcsv.category_set_id US Product Category Set 12 90 &category_segments2 select x.* from ( select '(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv2.organization_id=mic.organization_id and msiv2.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text from mtl_category_sets_vl mcsv, fnd_id_flex_segments_vl fifsv where mcsv.category_set_name=:category_set_name2 and mcsv.structure_id=fifsv.id_flex_num and fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.enabled_flag='Y' order by fifsv.id_flex_num, fifsv.segment_num ) x union all select '(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv2.organization_id=mic.organization_id and msiv2.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text from mtl_category_sets_vl mcsv where mcsv.category_set_name=:category_set_name2 LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name select mcsv.category_set_name from mtl_default_category_sets mdcs, mtl_category_sets_v mcsv where mdcs.functional_area_id=14 and mdcs.category_set_id=mcsv.category_set_id US Line Category Set 1 13 100 &category_segments3 select x.* from ( select '(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv2.organization_id=mic.organization_id and msiv2.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text from mtl_category_sets_vl mcsv, fnd_id_flex_segments_vl fifsv where mcsv.category_set_name=:category_set_name3 and mcsv.structure_id=fifsv.id_flex_num and fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.enabled_flag='Y' order by fifsv.id_flex_num, fifsv.segment_num ) x union all select '(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv2.organization_id=mic.organization_id and msiv2.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text from mtl_category_sets_vl mcsv where mcsv.category_set_name=:category_set_name3 LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Line Category Set 2 14 110 34=34 msiv2.concatenated_segments=:p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 15 120 32=32 mp.organization_code=:p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 select ood.organization_code value, ood.organization_name 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 (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 16 130 31=31 haouv_ou.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 17 140 31=31 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger 18 150 :p_cost_component1 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 1 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 1 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 1 ) US Cost Component 1 19 &p_cost_component1 <parameter_value> US Cost Component 1 20 160 :p_cost_component2 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 2 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 2 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 2 ) US Cost Component 2 21 &p_cost_component2 <parameter_value> US Cost Component 2 22 170 :p_cost_component3 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 3 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 3 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 3 ) US Cost Component 3 23 &p_cost_component3 <parameter_value> US Cost Component 3 24 180 :p_cost_component4 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 4 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 4 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 4 ) US Cost Component 4 25 &p_cost_component4 <parameter_value> US Cost Component 4 26 190 :p_cost_component5 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 5 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 5 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 5 ) US Cost Component 5 27 &p_cost_component5 <parameter_value> US Cost Component 5 28 200 :p_cost_component6 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 6 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 6 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 6 ) US Cost Component 6 29 &p_cost_component6 <parameter_value> US Cost Component 6 30 210 :p_cost_component7 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 7 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 7 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 7 ) US Cost Component 7 31 &p_cost_component7 <parameter_value> US Cost Component 7 32 220 :p_cost_component8 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 8 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 8 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 8 ) US Cost Component 8 33 &p_cost_component8 <parameter_value> US Cost Component 8 34 230 :p_cost_component9 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 9 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 9 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 9 ) US Cost Component 9 35 &p_cost_component9 <parameter_value> US Cost Component 9 36 240 :p_cost_component10 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 10 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 10 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 10 ) US Cost Component 10 37 &p_cost_component10 <parameter_value> US Cost Component 10 38 250 :p_cost_component11 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 11 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 11 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 11 ) US Cost Component 11 39 &p_cost_component11 <parameter_value> US Cost Component 11 40 260 :p_cost_component12 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 12 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 12 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 12 ) US Cost Component 12 41 &p_cost_component12 <parameter_value> US Cost Component 12 42 270 :p_cost_component13 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 13 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 13 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 13 ) US Cost Component 13 43 &p_cost_component13 <parameter_value> US Cost Component 13 44 280 :p_cost_component14 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 14 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 14 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 14 ) US Cost Component 14 45 &p_cost_component14 <parameter_value> US Cost Component 14 46 290 :p_cost_component15 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 15 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 15 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 15 ) US Cost Component 15 47 &p_cost_component15 <parameter_value> US Cost Component 15 48 300 :p_cost_component16 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 16 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 16 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 16 ) US Cost Component 16 49 &p_cost_component16 <parameter_value> US Cost Component 16 50 310 :p_cost_component17 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 17 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 17 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 17 ) US Cost Component 17 51 &p_cost_component17 <parameter_value> US Cost Component 17 52 320 :p_cost_component18 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 18 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 18 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 18 ) US Cost Component 18 53 &p_cost_component18 <parameter_value> US Cost Component 18 54 330 :p_cost_component19 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 19 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 19 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 19 ) US Cost Component 19 55 &p_cost_component19 <parameter_value> US Cost Component 19 56 340 :p_cost_component20 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 20 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 20 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 20 ) US Cost Component 20 57 &p_cost_component20 <parameter_value> US Cost Component 20 58 350 :p_cost_component21 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 21 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 21 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 21 ) US Cost Component 21 59 &p_cost_component21 <parameter_value> US Cost Component 21 60 360 :p_cost_component22 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 22 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 22 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 22 ) US Cost Component 22 61 &p_cost_component22 <parameter_value> US Cost Component 22 62 370 :p_cost_component23 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 23 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 23 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 23 ) US Cost Component 23 63 &p_cost_component23 <parameter_value> US Cost Component 23 64 380 :p_cost_component24 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 24 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 24 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 24 ) US Cost Component 24 65 &p_cost_component24 <parameter_value> US Cost Component 24 66 390 :p_cost_component25 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 25 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 25 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 25 ) US Cost Component 25 67 &p_cost_component25 <parameter_value> US Cost Component 25 68 400 :p_cost_component26 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 26 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 26 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 26 ) US Cost Component 26 69 &p_cost_component26 <parameter_value> US Cost Component 26 70 410 :p_cost_component27 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 27 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 27 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 27 ) US Cost Component 27 71 &p_cost_component27 <parameter_value> US Cost Component 27 72 420 :p_cost_component28 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 28 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 28 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 28 ) US Cost Component 28 73 &p_cost_component28 <parameter_value> US Cost Component 28 74 430 :p_cost_component29 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 29 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 29 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 29 ) US Cost Component 29 75 &p_cost_component29 <parameter_value> US Cost Component 29 76 440 :p_cost_component30 LOV custom select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 30 ) select ccmv.cost_component_code value, ccmv.cost_cmpntcls_desc description from -- Order the Cost Components by usage_ind (1-Material, 2-Overhead, 3-Resource, 4-Expense Alloc) then by Cost Component Code (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 30 union all select fl.meaning value, fl.meaning description from fnd_lookups fl where fl.lookup_type = 'XDP_ADAPTER_STATUS' and fl.lookup_code = 'NOT_AVAILABLE' and not exists (select 'x' from (select row_number() over (order by ccmv.usage_ind, gl.meaning, ccmv.cost_cmpntcls_code) row_number, usage_ind, gl.meaning cost_component_type, ccmv.cost_cmpntcls_code cost_component_code, ccmv.cost_cmpntcls_desc, ccmv.cost_cmpntcls_id cost_component_id from cm_cmpt_mst_vl ccmv, -- Cost Component Master gem_lookups gl where gl.lookup_type = 'COST_COMPONENT_TYPE' and gl.lookup_code = ccmv.usage_ind and ccmv.delete_mark = 0 -- Active order by usage_ind, gl.meaning, ccmv.cost_cmpntcls_code ) ccmv where ccmv.row_number = 30 ) US Cost Component 30 77 &p_cost_component30 <parameter_value> US Cost Component 30