ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 36C589E450DC444EE0633D7D37A2E3E5Y select null action_, null status_, null message_, null modified_columns_, to_number(null) source_line_id, to_number(null) source_header_id, ood.organization_code organization, msiv.concatenated_segments item, msiv.description, moqd.revision, moqd.subinventory_code subinventory, moqd.locator_code locator, moqd.on_hand_quantity onhand_quantity, msiv.primary_uom_code uom, sysdate transaction_date, to_char(null) transaction_type, to_char(null) transaction_source, to_number(null) quantity, to_char(null) account_alias, to_char(null) distribution_account, to_char(null) gl_segment1, to_char(null) gl_segment2, to_char(null) gl_segment3, to_char(null) gl_segment4, to_char(null) gl_segment5, to_char(null) gl_segment6, to_char(null) gl_segment7, to_char(null) gl_segment8, to_char(null) gl_segment9, to_char(null) gl_segment10, to_char(null) gl_segment11, to_char(null) gl_segment12, to_char(null) gl_segment13, to_char(null) gl_segment14, to_char(null) gl_segment15, to_char(null) lpn, to_char(null) from_serial_number, to_char(null) to_serial_number, cast(null as varchar2(240)) transaction_reference, to_char(null) reason, to_number(null) transaction_cost, to_char(null) lot_number, to_date(null) lot_expiration_date, to_number(null) lot_quantity, to_char(null) to_org, to_char(null) to_subinv, to_char(null) to_locator, cast(null as varchar2(30)) shipment_number, to_date(null) expected_receipt_date, cast(null as varchar2(30)) freight_code, to_number(null) containers, cast(null as varchar2(30)) waybill_airbill, to_number(null) transportation_cost, to_char(null) transportation_account, to_number(null) transfer_cost, to_char(null) location, to_char(null) source_project, to_char(null) source_task, to_char(null) expenditure_type, to_char(null) expenditure_org, cast(null as varchar2(30)) attribute_category, cast(null as varchar2(150)) inv_trx_attribute1, cast(null as varchar2(150)) inv_trx_attribute2, cast(null as varchar2(150)) inv_trx_attribute3, cast(null as varchar2(150)) inv_trx_attribute4, cast(null as varchar2(150)) inv_trx_attribute5, cast(null as varchar2(150)) inv_trx_attribute6, cast(null as varchar2(150)) inv_trx_attribute7, cast(null as varchar2(150)) inv_trx_attribute8, cast(null as varchar2(150)) inv_trx_attribute9, cast(null as varchar2(150)) inv_trx_attribute10, cast(null as varchar2(150)) inv_trx_attribute11, cast(null as varchar2(150)) inv_trx_attribute12, cast(null as varchar2(150)) inv_trx_attribute13, cast(null as varchar2(150)) inv_trx_attribute14, cast(null as varchar2(150)) inv_trx_attribute15, 0 upload_row from org_organization_definitions ood, mtl_system_items_vl msiv, ( select moqd1.organization_id, moqd1.inventory_item_id, moqd1.subinventory_code, moqd1.revision, milk.concatenated_segments locator_code, sum(primary_transaction_quantity) on_hand_quantity from mtl_onhand_quantities_detail moqd1, mtl_item_locations_kfv milk, wms_license_plate_numbers wlpn where moqd1.locator_id=milk.inventory_location_id(+) and moqd1.lpn_id=wlpn.lpn_id(+) group by moqd1.organization_id, moqd1.inventory_item_id, moqd1.subinventory_code, moqd1.revision, milk.concatenated_segments ) moqd where 1=1 and msiv.organization_id=ood.organization_id and msiv.mtl_transactions_enabled_flag = 'Y' and msiv.organization_id=moqd.organization_id(+) and msiv.inventory_item_id=moqd.inventory_item_id(+) Added the inter-organization intransit shipment columns Shipment Number, Expected Receipt Date, Freight Code, Containers, Waybill Airbill, Transportation Cost, Transportation Account and Transfer Cost. The Transaction Cost column now populates the transaction cost of the material transaction, the freight cost of an inter-organization transfer goes into the new Transportation Cost column. U XXEN_INV_UPLOAD :Upload_Mode = xxen_upload.action_create or ( :Item is not null or :Item_Like is not null or :Item_Description is not null or :Item_Type is not null or :BOM_Item_Type is not null or :Contract_Item_Type is not null or :Item_Status is not null or :Make_or_Buy is not null or :Buyer is not null or :Planner is not null or :Inventory_Planning_Method is not null or :Category is not null or :Cross_Reference_Type is not null or :Cross_Reference is not null or :Lot_Control is not null or :Serial_Number_Control is not null or :Locator_Control is not null or :Revision_Control is not null ) A xxen_inv_upload.upload_inv_trx xxen_inv_upload.process_transaction xxen_inv_upload.validate_material_transaction( p_organization=>:$column$.organization, p_item=>:$column$.item, p_subinventory_code=>:$column$.subinventory, p_revision=>:$column$.revision, p_locator=>:$column$.locator, p_transaction_date=>:$column$.transaction_date, p_transaction_type=>:$column$.transaction_type, p_quantity=>:$column$.quantity, p_from_serial_number=>:$column$.from_serial_number, p_to_serial_number=>:$column$.to_serial_number, p_lot_number=>:$column$.lot_number, p_lot_quantity=>:$column$.lot_quantity, p_source_project=>:$column$.source_project, p_source_task=>:$column$.source_task, p_expenditure_type=>:$column$.expenditure_type, p_expenditure_org=>:$column$.expenditure_org, p_transfer_org=>:$column$.to_org, p_transfer_subinventory=>:$column$.to_subinv, p_transfer_locator=>:$column$.to_locator, p_transaction_source=>:$column$.transaction_source, p_distribution_account=>:$column$.distribution_account ) US INV Transaction Upload INV Transaction Upload ====================== This upload can be used to create Inventory Material Transactions. The following upload modes are supported: Create – Generates an empty upload file. Transaction details must be entered manually. The Create mode always opens a blank Excel template, regardless of any parameter settings. Create, Update – Based on the selected parameters, this mode can generate a pre-populated file with details such as item, primary unit of measure, and transaction date (defaulted to today’s date). It also displays the current on-hand quantity for each item, allowing users to transact against existing stock. Additionally, users can create new transactions by entering details manually Note : -- For "Account alias issue" transaction Type - Transaction Source needs to be selected for this Transaction Type, Don't select Account Alias. -- For "Account alias receipt" transaction Type - Transaction Source needs to be selected for this Transaction Type, Don't select Account Alias. -- For "Account issue" transaction Type - Distribution Account needs to be entered for this Transaction Type (or an Account Alias to default it), Don't select Transaction Source. -- For "Account receipt" transaction Type - Distribution Account needs to be entered for this Transaction Type (or an Account Alias to default it), Don't select Transaction Source. -- For "Intransit Shipment" transaction Type - Shipment Number is mandatory. Expected Receipt Date, Freight Code, Containers, Waybill Airbill, Transportation Cost, Transportation Account and Transfer Cost hold the remaining shipment details. Please enter one or more item restriction criteria for download Enginatics Upload 1=1 1 -10 1=1 :ledger=:ledger Char xxen_util.default_ledger US Ledger 2 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 3 20 1=1 ood.organization_code=:organization_code LOV INV Organization Code (accessible) FA6954EC34B240D9A30C047DDF5BD14D select mp.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code value select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 4 30 1=1 (msiv.organization_id, msiv.inventory_item_id) in ( select mic.organization_id, mic.inventory_item_id from mtl_category_sets_v mcsv, mtl_item_categories mic where mcsv.category_set_name=:p_category_set_name and mcsv.category_set_id=mic.category_set_id ) LOV INV Category Set (restricted) 8E2FF36EDF2D79D2E0530100007F1FF2 select mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where mcsv.category_set_id in (select /*+ index(mic mtl_item_categories_n1) */ mic.category_set_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) order by mcsv.category_set_name select category_set_name from mtl_default_sets_view where functional_area_id = 1 US Category Set Parameter is only applicable for Upload Mode 'Create, Update' 5 40 1=1 (msiv.organization_id, msiv.inventory_item_id) in ( select mic.organization_id, mic.inventory_item_id from mtl_category_sets_v mcsv, mtl_categories_kfv mck, mtl_item_categories mic where mck.concatenated_segments=:p_category and mcsv.category_set_name=:p_category_set_name and mcsv.category_set_id=mic.category_set_id and mcsv.structure_id=mck.structure_id and mck.category_id=mic.category_id ) LOV INV Category (restricted) 8E2FF36EDF2E79D2E0530100007F1FF2 select mck.concatenated_segments value, mct.description from mtl_category_sets_v mcsv, mtl_categories_kfv mck, mtl_categories_tl mct where mcsv.category_set_name=:$flex$.category_set and mcsv.structure_id=mck.structure_id and (mcsv.category_set_id, mck.category_id) in (select mic.category_set_id, mic.category_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) and mck.category_id=mct.category_id and mct.language=userenv('lang') order by mck.concatenated_segments US Category Parameter is only applicable for Upload Mode 'Create, Update' 6 50 1=1 msiv.concatenated_segments=:item LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) order by msiv.concatenated_segments US Item 7 60 1=1 msiv.concatenated_segments like :item_like Char US Item Like 8 70 1=1 upper(msiv.description) like upper(:item_descr) LOV INV Item Description (master) B8DD9131FE72F277E0530100007F06F8 Y select msiv.description value, msiv.concatenated_segments description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) order by msiv.concatenated_segments US Item Description 9 80 1=1 msiv.item_type=xxen_util.lookup_code(:item_type,'ITEM_TYPE',3) LOV INV Item Type 8E2FF36EDE8579D2E0530100007F1FF2 select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.enabled_flag='Y' and flvv.lookup_type='ITEM_TYPE' and flvv.view_application_id=3 and flvv.security_group_id=0 order by flvv.lookup_code US Item Type 10 90 1=1 msiv.inventory_item_status_code in (select mis.inventory_item_status_code from mtl_item_status mis where mis.inventory_item_status_code_tl=:item_status) LOV INV Item Status 8E2FF36EDE9C79D2E0530100007F1FF2 select misv.inventory_item_status_code_tl value, misv.description from mtl_item_status_vl misv order by misv.inventory_item_status_code_tl US Item Status 11 100 1=1 msiv.bom_item_type=:bom_item_type LOV Oracle BOM_TYPE select to_char(lookup_code) id, meaning value, null description from fnd_lookup_values where lookup_type='BOM_ITEM_TYPE' and language=userenv('LANG') order by value,description US BOM Item Type 12 110 1=1 msiv.contract_item_type_code=xxen_util.lookup_code(:contract_item_type,'OKB_CONTRACT_ITEM_TYPE',0) LOV OKB Contract Item Type A37DDA2FDFA645B098AE74A338B3465B select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='OKB_CONTRACT_ITEM_TYPE' and flvv.view_application_id=0 and flvv.security_group_id=0 order by flvv.meaning US Contract Item Type 13 120 1=1 msiv.planning_make_buy_code=xxen_util.lookup_code(:make_or_buy,'MTL_PLANNING_MAKE_BUY',700) LOV MFG Make or Buy B0B5FC7B97ED64BCE0530100007F8BA8 select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_PLANNING_MAKE_BUY' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Make or Buy 14 130 1=1 msiv.buyer_id=:buyer_id LOV PO Buyer (with null option) 864B3B275E0142F19AFF0E822EEB13DC select x.* from ( select ppx.person_id id, ppx.full_name value, ppx.employee_number description from per_people_x ppx where ppx.current_employee_flag='Y' and ppx.person_id in (select pa.agent_id from po_agents pa where sysdate between nvl(pa.start_date_active,sysdate) and nvl(pa.end_date_active,sysdate)) order by ppx.full_name ) x union all select null id, '<null>' value, null description from dual select ppx.full_name from fnd_user fu, per_people_x ppx where 1=2 and fu.user_id=fnd_global.user_id and fu.employee_id=ppx.person_id and ppx.current_employee_flag='Y' and ppx.person_id in (select pa.agent_id from po_agents pa) US Buyer 15 1=1 msiv.buyer_id is null <null> US Buyer 16 140 1=1 msiv.planner_code=:planner_code LOV INV Planner Code 8E2FF36EDEC079D2E0530100007F1FF2 select x.* from ( select mp.planner_code value, mp.description from mtl_planners mp where nvl(mp.disable_date,sysdate+1)>=sysdate and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') order by mp.planner_code ) x union all select '<null>' value, null description from dual select mp.planner_code from fnd_user fu, per_people_x ppx, mtl_planners mp where 1=2 and fu.user_id=fnd_global.user_id and fu.employee_id=mp.employee_id and nvl(mp.disable_date,sysdate+1)>=sysdate and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Planner 17 1=1 msiv.planner_code is null <null> US Planner 18 150 1=1 msiv.inventory_planning_code=xxen_util.lookup_code(:inventory_planning_method,'MTL_MATERIAL_PLANNING',700) LOV INV Inventory Planning Method 1983BB2806901D4BE0630100007F8065 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_MATERIAL_PLANNING' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Inventory Planning Method 19 160 1=1 msiv.inventory_item_id in ( select mcrb.inventory_item_id from mtl_cross_references_b mcrb where mcrb.cross_reference_type=:cross_reference_type and sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate) ) LOV INV Cross Reference Type 8E2FF36EDEE479D2E0530100007F1FF2 select mcrt.cross_reference_type value, mcrt.description from mtl_cross_reference_types mcrt where sysdate<nvl(mcrt.disable_date,sysdate+1) order by mcrt.cross_reference_type US Cross Reference Type 20 170 1=1 msiv.inventory_item_id in ( select mcrb.inventory_item_id from mtl_cross_references_b mcrb where mcrb.cross_reference=:cross_reference and mcrb.cross_reference_type=:cross_reference_type and sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate) ) LOV INV Cross Reference 8E2FF36EDF0079D2E0530100007F1FF2 select mcrv.cross_reference value, mcrv.description from mtl_cross_references_vl mcrv where (mcrv.organization_id is null or :$flex$.organization_code is null or mcrv.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and (:$flex$.cross_reference_type is null or mcrv.cross_reference_type=:$flex$.cross_reference_type) order by mcrv.cross_reference US Cross Reference 21 180 1=1 msiv.location_control_code=xxen_util.lookup_code(:locator_control,'MTL_LOCATION_CONTROL',700) LOV custom select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_LOCATION_CONTROL' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Locator Control 22 190 1=1 msiv.serial_number_control_code=xxen_util.lookup_code(:serial_number_control,'MTL_SERIAL_NUMBER',700) LOV INV Serial Number Control 1D969404970048ED85134CBA0C3410E0 select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_SERIAL_NUMBER' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Serial Number Control 23 200 1=1 msiv.lot_control_code=xxen_util.lookup_code(:lot_control,'MTL_LOT_CONTROL',700) LOV custom select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_LOT_CONTROL' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Lot Control 24 210 1=1 msiv.revision_qty_control_code=xxen_util.lookup_code(:revision_control,'MTL_ENG_QUANTITY',700) LOV custom select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_ENG_QUANTITY' and flvv.view_application_id=700 and flvv.security_group_id=0 order by flvv.meaning US Revision Control 93E8E876C2ED89F5EF17DF780A8E274C Default ANDY.HAACK 12 ACCOUNT_ALIAS 44 CONTAINERS 13 DISTRIBUTION_ACCOUNT 42 EXPECTED_RECEIPT_DATE 53 EXPENDITURE_ORG 52 EXPENDITURE_TYPE 43 FREIGHT_CODE 30 FROM_SERIAL_NUMBER 14 GL_SEGMENT1 23 GL_SEGMENT10 24 GL_SEGMENT11 25 GL_SEGMENT12 26 GL_SEGMENT13 27 GL_SEGMENT14 28 GL_SEGMENT15 15 GL_SEGMENT2 16 GL_SEGMENT3 17 GL_SEGMENT4 18 GL_SEGMENT5 19 GL_SEGMENT6 20 GL_SEGMENT7 21 GL_SEGMENT8 22 GL_SEGMENT9 2 ITEM 49 LOCATION 5 LOCATOR 36 LOT_EXPIRATION_DATE 35 LOT_NUMBER 37 LOT_QUANTITY 29 LPN 6 ONHAND_QUANTITY 1 ORGANIZATION 11 QUANTITY 33 REASON 3 REVISION Y 41 SHIPMENT_NUMBER 50 SOURCE_PROJECT 51 SOURCE_TASK 4 SUBINVENTORY 40 TO_LOCATOR 38 TO_ORG 31 TO_SERIAL_NUMBER 39 TO_SUBINV 34 TRANSACTION_COST 8 TRANSACTION_DATE 32 TRANSACTION_REFERENCE 10 TRANSACTION_SOURCE 9 TRANSACTION_TYPE 48 TRANSFER_COST 47 TRANSPORTATION_ACCOUNT 46 TRANSPORTATION_COST 7 UOM 45 WAYBILL_AIRBILL S Site FA6599E27A594A77A157EF119128E2B5 Project related transactions ANDY.HAACK 11 DISTRIBUTION_ACCOUNT 22 EXPENDITURE_ORG Y 21 EXPENDITURE_TYPE Y 13 FROM_SERIAL_NUMBER 2 ITEM 5 LOCATOR 17 LOT_EXPIRATION_DATE 16 LOT_NUMBER 18 LOT_QUANTITY 12 LPN 6 ONHAND_QUANTITY 1 ORGANIZATION 10 QUANTITY 3 REVISION Y 19 SOURCE_PROJECT Y 20 SOURCE_TASK Y 4 SUBINVENTORY 14 TO_SERIAL_NUMBER 8 TRANSACTION_DATE 15 TRANSACTION_REFERENCE 9 TRANSACTION_TYPE 7 UOM S Site E482183EC0114B1AD7CCB797AC6A322A Serial controlled template ANDY.HAACK 11 DISTRIBUTION_ACCOUNT 25 EXPENDITURE_ORG 24 EXPENDITURE_TYPE 13 FROM_SERIAL_NUMBER 2 ITEM 5 LOCATOR 17 LOT_EXPIRATION_DATE 16 LOT_NUMBER 18 LOT_QUANTITY 12 LPN 6 ONHAND_QUANTITY 1 ORGANIZATION 10 QUANTITY 3 REVISION Y 22 SOURCE_PROJECT 23 SOURCE_TASK 4 SUBINVENTORY 21 TO_LOCATOR 19 TO_ORG 14 TO_SERIAL_NUMBER 20 TO_SUBINV 8 TRANSACTION_DATE 15 TRANSACTION_REFERENCE 9 TRANSACTION_TYPE 7 UOM Item Status Active Serial Number Control Predefined serial numbers Upload Mode C,U S Site 93E8E876C2ED89F5EF17DF780A8E274C 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 SOURCE_LINE_ID Number number Y 6 SOURCE_HEADER_ID Number number Y 7 ORGANIZATION LOV custom varchar2 3 select ood.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code Y 8 ITEM LOV custom varchar2 40 select msiv.inventory_item_id id, msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization and mp.organization_id=msiv.organization_id and msiv.mtl_transactions_enabled_flag='Y' and msiv.enabled_flag='Y' and msiv.inventory_item_flag='Y' order by msiv.concatenated_segments Y 9 DESCRIPTION Char varchar2 240 select msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item 10 REVISION LOV custom varchar2 3 select mirv.revision value, mirv.description from mtl_parameters mp, mtl_item_revisions_vl mirv, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and msiv.revision_qty_control_code=2 and msiv.inventory_item_id=mirv.inventory_item_id and msiv.organization_id=mirv.organization_id and mirv.implementation_date is not null 11 SUBINVENTORY LOV custom varchar2 10 select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where mp.organization_code=:$column$.organization and mp.organization_id=msi.organization_id and nvl(msi.disable_date,:$column$.transaction_date+1)>:$column$.transaction_date order by value Y 12 LOCATOR LOV custom varchar2 204 select milk.inventory_location_id id, milk.concatenated_segments value, ood.organization_code||': '||milk.subinventory_code ||': '||ood.organization_name description from mtl_item_locations_kfv milk, org_organization_definitions ood where milk.organization_id=ood.organization_id and milk.enabled_flag='Y' and nvl(milk.disable_date,:$column$.transaction_date+ 1)>:$column$.transaction_date and ood.organization_code=:$column$.organization and milk.subinventory_code=:$column$.subinventory order by milk.concatenated_segments 13 ONHAND_QUANTITY Number number select nvl(sum(primary_transaction_quantity),0) on_hand_quantity from mtl_onhand_quantities_detail moqd, mtl_item_locations_kfv milk, org_organization_definitions ood, mtl_system_items_vl msiv where moqd.locator_id=milk.inventory_location_id(+) and ood.organization_id=msiv.organization_id and moqd.organization_id=msiv.organization_id and moqd.inventory_item_id=msiv.inventory_item_id and nvl2(:$column$.subinventory,:$column$.subinventory,moqd.subinventory_code)=moqd.subinventory_code and nvl(milk.concatenated_segments,-1)=nvl(:$column$.locator,-1) and nvl(moqd.revision,'@@@')= nvl(:$column$.revision,'@@@') and msiv.concatenated_segments=:$column$.item and ood.organization_code=:$column$.organization 14 UOM LOV custom varchar2 3 select mtuv.uom_code value, '('||mtuv.uom_class||') '||mtuv.unit_of_measure_tl||' - '||mtuv.description description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_item_uoms_view mtuv where mp.organization_code=:$column$.organization and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and msiv.organization_id=mtuv.organization_id and msiv.inventory_item_id=mtuv.inventory_item_id order by value Y 15 TRANSACTION_DATE DateTime date sysdate Y 16 TRANSACTION_TYPE LOV custom varchar2 select mttv.transaction_type_id id, mttv.transaction_type_name value, decode(mttv.type_class,1,'(Project Related) ')||mttv.description description from mtl_trx_types_view mttv where ((mttv.transaction_source_type_id in (3,6,13) or mttv.transaction_source_type_id>=100) and mttv.transaction_action_id in (1,27) and mttv.transaction_type_id not in (66,67,68,95,96) and (nvl(mttv.type_class,2)<>1 or exists (select null from mtl_parameters mp where mp.organization_code=:$column$.organization and mp.pm_cost_collection_enabled=1))) or (mttv.transaction_source_type_id>=13 and mttv.transaction_action_id=2 and mttv.transaction_type_id not in (66,67,68)) or ((mttv.transaction_source_type_id=13 or mttv.transaction_source_type_id>=100) and mttv.transaction_type_id not in (66,67,68) and mttv.transaction_action_id in (3,21)) order by value Y 17 TRANSACTION_SOURCE LOV custom varchar2 select mgd.disposition_id id, mgd.segment1 value, mgd.description from mtl_parameters mp, mtl_generic_dispositions mgd where :$column$.transaction_type like '%Account alias%' and mp.organization_code=:$column$.organization and mp.organization_id=mgd.organization_id and mgd.enabled_flag='Y' and mgd.distribution_account in (select gcc.code_combination_id from gl_code_combinations gcc where nvl(gcc.end_date_active,:$column$.transaction_date+1)>:$column$.transaction_date and gcc.enabled_flag='Y') 18 QUANTITY Number number Y 19 ACCOUNT_ALIAS LOV custom varchar2 select fsfa.alias_name id, fsfa.alias_name value, fsfa.concatenated_segments || ' (' || flvv.description || ')' description from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, fnd_shorthand_flex_aliases fsfa, gl_code_combinations_kfv gcck, fnd_lookup_values_vl flvv where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and fsfa.application_id=101 and fsfa.id_flex_code='GL#' and fsfa.id_flex_num=gl.chart_of_accounts_id and fsfa.enabled_flag='Y' and trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=fsfa.concatenated_segments and gcck.enabled_flag='Y' and gcck.summary_flag='N' and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and flvv.lookup_type='ACCOUNT TYPE' and flvv.view_application_id=101 and flvv.lookup_code=gcck.gl_account_type order by value 20 DISTRIBUTION_ACCOUNT LOV custom varchar2 select gcck.code_combination_id id, gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description from mtl_parameters mp, hr_organization_information hoi, gl_code_combinations_kfv gcck, gl_ledgers gl, fnd_lookup_values_vl flvv where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and decode(xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602),'B',gcck.detail_budgeting_allowed,gcck.detail_posting_allowed) = 'Y' and flvv.lookup_type = 'ACCOUNT TYPE' and flvv.view_application_id = 101 and flvv.lookup_code = gcck.gl_account_type and (:$column$.account_alias is null or gcck.concatenated_segments = (select fsfa.concatenated_segments from fnd_shorthand_flex_aliases fsfa where fsfa.application_id = 101 and fsfa.id_flex_code = 'GL#' and fsfa.id_flex_num = gl.chart_of_accounts_id and fsfa.enabled_flag = 'Y' and trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and fsfa.alias_name = :$column$.account_alias ) ) order by value select gcck.concatenated_segments value from mtl_parameters mp, hr_organization_information hoi, gl_code_combinations_kfv gcck, gl_ledgers gl, fnd_lookup_values_vl flvv where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and decode(xxen_util.lookup_code(:$flex$.journal_type,'XLA_BALANCE_TYPE',602),'B',gcck.detail_budgeting_allowed,gcck.detail_posting_allowed) = 'Y' and flvv.lookup_type = 'ACCOUNT TYPE' and flvv.view_application_id = 101 and flvv.lookup_code = gcck.gl_account_type and gcck.concatenated_segments = (select fsfa.concatenated_segments from fnd_shorthand_flex_aliases fsfa where fsfa.application_id = 101 and fsfa.id_flex_code = 'GL#' and fsfa.id_flex_num = gl.chart_of_accounts_id and fsfa.enabled_flag = 'Y' and trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and fsfa.alias_name = :$column$.account_alias union select gcck.concatenated_segments from mtl_generic_dispositions mgd, org_organization_definitions ood, gl_code_combinations_kfv gcck where mgd.organization_id=ood.organization_id and mgd.enabled_flag='Y' and mgd.segment1 = :$column$.transaction_source and gcck.code_combination_id = mgd.distribution_account and ood.organization_code=:$column$.organization ) order by value 21 GL_SEGMENT1 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT1', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment1 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment1 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 22 GL_SEGMENT2 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT2', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment2 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment2 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 23 GL_SEGMENT3 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT3', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment3 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment3 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 24 GL_SEGMENT4 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT4', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment4 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment4 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 25 GL_SEGMENT5 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT5', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment5 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment5 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 26 GL_SEGMENT6 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT6', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment6 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment6 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 27 GL_SEGMENT7 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT7', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment7 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment7 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 28 GL_SEGMENT8 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT8', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment8 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment8 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 29 GL_SEGMENT9 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT9', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment9 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment9 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 30 GL_SEGMENT10 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT10', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment10 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment10 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 31 GL_SEGMENT11 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT11', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment11 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment11 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 32 GL_SEGMENT12 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT12', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment12 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment12 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 33 GL_SEGMENT13 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT13', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment13 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment13 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 34 GL_SEGMENT14 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT14', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment14 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment14 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 35 GL_SEGMENT15 LOV custom varchar2 select distinct substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1) id, substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_unnest*/ gl.chart_of_accounts_id id_flex_num from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>'SEGMENT15', p_segment1=>:$column$.gl_segment1, p_segment2=>:$column$.gl_segment2, p_segment3=>:$column$.gl_segment3, p_segment4=>:$column$.gl_segment4, p_segment5=>:$column$.gl_segment5, p_segment6=>:$column$.gl_segment6, p_segment7=>:$column$.gl_segment7, p_segment8=>:$column$.gl_segment8, p_segment9=>:$column$.gl_segment9, p_segment10=>:$column$.gl_segment10, p_segment11=>:$column$.gl_segment11, p_segment12=>:$column$.gl_segment12, p_segment13=>:$column$.gl_segment13, p_segment14=>:$column$.gl_segment14, p_segment15=>:$column$.gl_segment15 )) l where ( :$column$.distribution_account is null or substrb(l.column_value,1,instrb(l.column_value,'~',1,1)-1)= (select gcck.segment15 from gl_code_combinations_kfv gcck where gcck.concatenated_segments=:$column$.distribution_account and gcck.chart_of_accounts_id=s.id_flex_num) ) order by 2 select gcck.segment15 from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gcck.chart_of_accounts_id=gl.chart_of_accounts_id and gcck.concatenated_segments=:$column$.distribution_account 36 LPN LOV custom varchar2 select wlpn.lpn_id id, wlpn.license_plate_number value, wlpn.license_plate_number||' ('||mp.organization_code||')' description from mtl_parameters mp, wms_license_plate_numbers wlpn where mp.organization_code=:$column$.organization and mp.organization_id=wlpn.organization_id order by mp.organization_code, wlpn.license_plate_number 37 FROM_SERIAL_NUMBER LOV custom varchar2 Y select msn.serial_number value, msn.serial_number||'('||ml.meaning||')' description from mtl_transaction_types mtt, mtl_parameters mp, mtl_system_items_vl msiv, mtl_serial_numbers msn, mfg_lookups ml, mtl_item_locations_kfv milk where mtt.transaction_type_name=:$column$.transaction_type and mp.organization_code=:$column$.organization and msiv.concatenated_segments=:$column$.item and mp.organization_id=msiv.organization_id and msiv.organization_id=msn.current_organization_id and msiv.inventory_item_id=msn.inventory_item_id and nvl(msn.group_mark_id,-1)=-1 and ml.lookup_type='SERIAL_NUM_STATUS' and msn.current_status=ml.lookup_code and msn.current_locator_id=milk.inventory_location_id(+) and ( ( mtt.transaction_action_id in (1,2,3) and msn.current_status=3 and nvl(msn.current_subinventory_code,'@@@')=nvl(:$column$.subinventory,'@@@') and nvl(milk.concatenated_segments,-1)=nvl(:$column$.locator,-1) and nvl(msn.revision,'@@@')= nvl(:$column$.revision,'@@@') ) or ( mtt.transaction_action_id=27 and msn.current_status in (1,4) ) ) order by value 38 TO_SERIAL_NUMBER LOV custom varchar2 Y select msn.serial_number value, msn.serial_number||'('||ml.meaning||')' description from mtl_transaction_types mtt, mtl_parameters mp, mtl_system_items_vl msiv, mtl_serial_numbers msn, mfg_lookups ml, mtl_item_locations_kfv milk where mtt.transaction_type_name=:$column$.transaction_type and mp.organization_code=:$column$.organization and msiv.concatenated_segments=:$column$.item and mp.organization_id=msiv.organization_id and msiv.organization_id=msn.current_organization_id and msiv.inventory_item_id=msn.inventory_item_id and nvl(msn.group_mark_id,-1)=-1 and ml.lookup_type='SERIAL_NUM_STATUS' and msn.current_status=ml.lookup_code and msn.current_locator_id=milk.inventory_location_id(+) and ( ( mtt.transaction_action_id in (1,2,3) and msn.current_status=3 and nvl(msn.current_subinventory_code,'@@@')=nvl(:$column$.subinventory,'@@@') and nvl(milk.concatenated_segments,-1)=nvl(:$column$.locator,-1) and nvl(msn.revision,'@@@')= nvl(:$column$.revision,'@@@') ) or ( mtt.transaction_action_id=27 and msn.current_status in (1,4) ) ) order by value :$column$.from_serial_number 39 TRANSACTION_REFERENCE Char varchar2 240 40 REASON LOV custom varchar2 select mtr.reason_id id, mtr.reason_name value, mtr.description from mtl_transaction_reasons mtr where nvl(disable_date, :$column$.transaction_date+ 1) > :$column$.transaction_date order by value 41 TRANSACTION_COST Number number 42 LOT_NUMBER LOV custom varchar2 Y select mln.lot_number value, mln.description from mtl_lot_numbers mln, org_organization_definitions ood, mtl_system_items_vl msiv where mln.organization_id=msiv.organization_id and mln.inventory_item_id=msiv.inventory_item_id and msiv.organization_id=ood.organization_id and nvl(disable_flag, 2) = 2 and ood.organization_code=:$column$.organization and msiv.concatenated_segments=:$column$.item and inv_material_status_grp.is_status_applicable (null, null, null, null, null,null, null, null, null, lot_number,null, 'O') = 'Y' order by value 43 LOT_EXPIRATION_DATE Date date 44 LOT_QUANTITY Number number select :$column$.quantity from dual where :$column$.lot_number is not null 45 TO_ORG LOV custom varchar2 select ood.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and mp.organization_code <>:$column$.organization order by mp.organization_code 46 TO_SUBINV LOV custom varchar2 select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where mp.organization_code=nvl(:$column$.to_org,:$column$.organization) and nvl(msi.disable_date, :$column$.transaction_date + 1) > :$column$.transaction_date and mp.organization_id=msi.organization_id order by value 47 TO_LOCATOR LOV custom varchar2 select milk.inventory_location_id id, milk.concatenated_segments value, ood.organization_code||': '||milk.subinventory_code ||': '||ood.organization_name description from mtl_item_locations_kfv milk, org_organization_definitions ood where milk.organization_id=ood.organization_id and milk.enabled_flag='Y' and nvl(milk.disable_date, :$column$.transaction_date + 1) > :$column$.transaction_date and ood.organization_code=nvl(:$column$.to_org,:$column$.organization) and milk.subinventory_code=:$column$.to_subinv order by milk.concatenated_segments 48 SHIPMENT_NUMBER Char varchar2 30 49 EXPECTED_RECEIPT_DATE Date date 50 FREIGHT_CODE LOV custom varchar2 30 select oefvv.freight_code value, oefvv.description from mtl_parameters mp_from, mtl_parameters mp_to, mtl_interorg_parameters mip, org_enabled_freight_val_v oefvv where mp_from.organization_code=:$column$.organization and mp_to.organization_code=:$column$.to_org and mp_from.organization_id=mip.from_organization_id and mp_to.organization_id=mip.to_organization_id and case when mip.intransit_type=2 and mip.fob_point=1 then mip.to_organization_id else mip.from_organization_id end=oefvv.organization_id order by oefvv.freight_code 51 CONTAINERS Number number 52 WAYBILL_AIRBILL Char varchar2 30 53 TRANSPORTATION_COST Number number 54 TRANSPORTATION_ACCOUNT LOV custom varchar2 select gcck.code_combination_id id, gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) description from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl, gl_code_combinations_kfv gcck where mp.organization_code=:$column$.organization and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.org_information1=gl.ledger_id and gl.chart_of_accounts_id=gcck.chart_of_accounts_id and gcck.enabled_flag='Y' and gcck.summary_flag='N' and gcck.detail_posting_allowed='Y' and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) order by value select gcck.concatenated_segments from mtl_parameters mp_from, mtl_parameters mp_to, mtl_interorg_parameters mip, org_enabled_freight_val_v oefvv, gl_code_combinations_kfv gcck where mp_from.organization_code=:$column$.organization and mp_to.organization_code=:$column$.to_org and mp_from.organization_id=mip.from_organization_id and mp_to.organization_id=mip.to_organization_id and case when mip.intransit_type=2 and mip.fob_point=1 then mip.to_organization_id else mip.from_organization_id end=oefvv.organization_id and oefvv.freight_code=:$column$.freight_code and oefvv.distribution_account=gcck.code_combination_id 55 TRANSFER_COST Number number 56 LOCATION LOV custom varchar2 select hla.location_id id, hla.location_code value, hla.address_line_1 ||' ' || hla.address_line_2 ||' ' || hla.address_line_3||' '|| hla.town_or_city||' ' || hla.country || '( '|| hla.description || ') ' description from hr_locations_all hla where nvl(hla.inactive_date, :$column$.transaction_date + 1 ) > :$column$.transaction_date order by value 57 SOURCE_PROJECT LOV custom varchar2 select ppev.project_id id, ppev.project_number value, ppev.project_name || '('||ppev.project_status_code ||') - '|| ppev.project_description description from pa_projects_expend_v ppev where nvl(start_date, :$column$.transaction_date) <= :$column$.transaction_date and nvl ( trunc(completion_date + 1), trunc(:$column$.transaction_date + 1) ) > :$column$.transaction_date order by value 58 SOURCE_TASK LOV custom varchar2 select ptev.task_id id, ptev.task_number value, ptev.task_name || '('||ppa.segment1 ||') - '|| ptev.task_description description from pa_projects_all ppa, pa_tasks_expend_v ptev where ppa.project_id=ptev.project_id and ppa.segment1=:$column$.source_project and nvl(ptev.start_date, :$column$.transaction_date ) <= :$column$.transaction_date and nvl ( trunc(ptev.completion_date + 1), trunc(:$column$.transaction_date + 1) ) > :$column$.transaction_date order by ptev.task_number 59 EXPENDITURE_TYPE LOV custom varchar2 select pet.expenditure_type value, pet.description ||'('|| pet.expenditure_category ||')' description, pet.start_date_active from pa_expenditure_types pet, pa_expend_typ_sys_links psys where pet.expenditure_type = psys.expenditure_type and psys.system_linkage_function = 'INV' and :$column$.transaction_date between trunc(pet.start_date_active) and nvl(trunc(pet.end_date_active),:$column$.transaction_date + 1) and :$column$.transaction_date between trunc(psys.start_date_active) and nvl(trunc(psys.end_date_active),:$column$.transaction_date + 1) 60 EXPENDITURE_ORG LOV custom varchar2 select distinct poev.organization_id id, poev.name value, null description from pa_organizations_expend_v poev where poev.date_from <= :$column$.transaction_date and nvl(poev.date_to,:$column$.transaction_date ) <= :$column$.transaction_date and poev.active_flag = 'Y' order by value 61 ATTRIBUTE_CATEGORY Char varchar2 30 62 INV_TRX_ATTRIBUTE1 Char varchar2 150 63 INV_TRX_ATTRIBUTE2 Char varchar2 150 64 INV_TRX_ATTRIBUTE3 Char varchar2 150 65 INV_TRX_ATTRIBUTE4 Char varchar2 150 66 INV_TRX_ATTRIBUTE5 Char varchar2 150 67 INV_TRX_ATTRIBUTE6 Char varchar2 150 68 INV_TRX_ATTRIBUTE7 Char varchar2 150 69 INV_TRX_ATTRIBUTE8 Char varchar2 150 70 INV_TRX_ATTRIBUTE9 Char varchar2 150 71 INV_TRX_ATTRIBUTE10 Char varchar2 150 72 INV_TRX_ATTRIBUTE11 Char varchar2 150 73 INV_TRX_ATTRIBUTE12 Char varchar2 150 74 INV_TRX_ATTRIBUTE13 Char varchar2 150 75 INV_TRX_ATTRIBUTE14 Char varchar2 150 76 INV_TRX_ATTRIBUTE15 Char varchar2 150 77 UPLOAD_ROW Number number U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_source_line_id out Number SOURCE_LINE_ID U 4 p_source_header_id out Number SOURCE_HEADER_ID U 5 p_material_expense_account in Number Y DISTRIBUTION_ACCOUNT U 6 p_validation_required in Number Y U 7 p_inventory_item_id in Number Y ITEM U 8 p_revision in Char Y REVISION U 9 p_transaction_batch_id in Number Y U 10 p_transaction_batch_seq in Number Y U 11 p_transfer_organization_type in Number Y U 12 p_owning_organization_id in Number Y U 13 p_owning_tp_type in Number Y U 14 p_xfr_owning_organization_id in Number Y U 15 p_transfer_owning_tp_type in Number Y U 16 p_planning_organization_id in Number Y U 17 p_planning_tp_type in Number Y U 18 p_xfr_planning_organization_id in Number Y U 19 p_transfer_planning_tp_type in Number Y U 20 p_secondary_uom_code in Char Y U 21 p_secondary_transaction_qty in Number Y U 22 p_transaction_group_id in Number Y U 23 p_transaction_group_seq in Number Y U 24 p_representative_lot_number in Char Y U 25 p_encumbrance_account in Number Y U 26 p_encumbrance_amount in Number Y U 27 p_vendor_lot_number in Char Y U 28 p_transfer_subinventory in Char Y TO_SUBINV U 29 p_transfer_organization in Number Y TO_ORG U 30 p_transfer_locator in Number Y TO_LOCATOR U 31 p_shipment_number in Char Y SHIPMENT_NUMBER U 32 p_transportation_cost in Number Y TRANSPORTATION_COST U 33 p_transportation_account in Number Y TRANSPORTATION_ACCOUNT U 34 p_transfer_cost in Number Y TRANSFER_COST U 35 p_freight_code in Char Y FREIGHT_CODE U 36 p_containers in Number Y CONTAINERS U 37 p_waybill_airbill in Char Y WAYBILL_AIRBILL U 38 p_expected_arrival_date in Date Y EXPECTED_RECEIPT_DATE U 39 p_new_average_cost in Number Y U 40 p_value_change in Number Y U 41 p_percentage_change in Number Y U 42 p_demand_source_header_id in Number Y U 43 p_demand_source_line in Char Y U 44 p_demand_source_delivery in Char Y U 45 p_negative_req_flag in Number Y U 46 p_error_explanation in Char Y U 47 p_shippable_flag in Char Y U 48 p_error_code in Char Y U 49 p_required_flag in Char Y U 50 p_attribute_category in Char Y ATTRIBUTE_CATEGORY U 51 p_attribute1 in Char Y INV_TRX_ATTRIBUTE1 U 52 p_attribute2 in Char Y INV_TRX_ATTRIBUTE2 U 53 p_attribute3 in Char Y INV_TRX_ATTRIBUTE3 U 54 p_attribute4 in Char Y INV_TRX_ATTRIBUTE4 U 55 p_attribute5 in Char Y INV_TRX_ATTRIBUTE5 U 56 p_attribute6 in Char Y INV_TRX_ATTRIBUTE6 U 57 p_attribute7 in Char Y INV_TRX_ATTRIBUTE7 U 58 p_attribute8 in Char Y INV_TRX_ATTRIBUTE8 U 59 p_attribute9 in Char Y INV_TRX_ATTRIBUTE9 U 60 p_attribute10 in Char Y INV_TRX_ATTRIBUTE10 U 61 p_attribute11 in Char Y INV_TRX_ATTRIBUTE11 U 62 p_attribute12 in Char Y INV_TRX_ATTRIBUTE12 U 63 p_attribute13 in Char Y INV_TRX_ATTRIBUTE13 U 64 p_attribute14 in Char Y INV_TRX_ATTRIBUTE14 U 65 p_attribute15 in Char Y INV_TRX_ATTRIBUTE15 U 66 p_requisition_distribution_id in Number Y U 67 p_movement_id in Number Y U 68 p_reservation_quantity in Number Y U 69 p_shipped_quantity in Number Y U 70 p_inventory_item in Char Y U 71 p_locator_name in Char Y U 72 p_task_id in Number Y U 73 p_to_task_id in Number Y U 74 p_source_task_id in Number Y SOURCE_TASK U 75 p_project_id in Number Y U 76 p_to_project_id in Number Y U 77 p_source_project_id in Number Y SOURCE_PROJECT U 78 p_pa_expenditure_org_id in Number Y EXPENDITURE_ORG U 79 p_expenditure_type in Char Y EXPENDITURE_TYPE U 80 p_final_completion_flag in Char Y U 81 p_transfer_percentage in Number Y U 82 p_transaction_sequence_id in Number Y U 83 p_material_account in Number Y U 84 p_material_overhead_account in Number Y U 85 p_resource_account in Number Y U 86 p_outside_processing_account in Number Y U 87 p_overhead_account in Number Y U 88 p_bom_revision in Char Y U 89 p_routing_revision in Char Y U 90 p_bom_revision_date in Date Y U 91 p_routing_revision_date in Date Y U 92 p_alternate_bom_designator in Char Y U 93 p_alternate_routing_designator in Char Y U 94 p_accounting_class in Char Y U 95 p_demand_class in Char Y U 96 p_parent_id in Number Y U 97 p_substitution_type_id in Number Y U 98 p_substitution_item_id in Number Y U 99 p_schedule_group in Number Y U 100 p_build_sequence in Number Y U 101 p_schedule_number in Char Y U 102 p_scheduled_flag in Number Y U 103 p_flow_schedule in Char Y U 104 p_cost_group_id in Number Y U 105 p_kanban_card_id in Number Y U 106 p_qa_collection_id in Number Y U 107 p_overcompletion_trx_qty in Number Y U 108 p_overcompletion_primary_qty in Number Y U 109 p_overcompletion_trx in Number Y U 110 p_end_item_unit_number in Char Y U 111 p_scheduled_payback_date in Date Y U 112 p_org_cost_group_id in Number Y U 113 p_cost_type_id in Number Y U 114 p_source_lot_number in Char Y U 115 p_transfer_cost_group_id in Number Y U 116 p_lpn_id in Number Y LPN U 117 p_transfer_lpn_id in Number Y U 118 p_content_lpn_id in Number Y U 119 p_xml_document_id in Char Y U 120 p_organization_type in Number Y U 121 p_organization_id in Number Y ORGANIZATION U 122 p_transaction_quantity in Number Y QUANTITY U 123 p_primary_quantity in Number Y U 124 p_transaction_uom in Char Y UOM U 125 p_transaction_date in Date Y TRANSACTION_DATE U 126 p_acct_period_id in Number Y U 127 p_subinventory_code in Char Y SUBINVENTORY U 128 p_locator_id in Number Y LOCATOR U 129 p_transaction_source_id in Number Y TRANSACTION_SOURCE U 130 p_transaction_source_name in Char Y U 131 p_transaction_source_type_id in Number Y U 132 p_transaction_action_id in Number Y U 133 p_transaction_type_id in Number Y TRANSACTION_TYPE U 134 p_transaction_type in Char Y U 135 p_reason_id in Number Y REASON U 136 p_transaction_reference in Char Y TRANSACTION_REFERENCE U 137 p_transaction_cost in Number Y TRANSACTION_COST U 138 p_distribution_account_id in Number Y DISTRIBUTION_ACCOUNT U 139 p_dst_segment1 in Char Y GL_SEGMENT1 U 140 p_dst_segment2 in Char Y GL_SEGMENT2 U 141 p_dst_segment3 in Char Y GL_SEGMENT3 U 142 p_dst_segment4 in Char Y GL_SEGMENT4 U 143 p_dst_segment5 in Char Y GL_SEGMENT5 U 144 p_dst_segment6 in Char Y GL_SEGMENT6 U 145 p_dst_segment7 in Char Y GL_SEGMENT7 U 146 p_dst_segment8 in Char Y GL_SEGMENT8 U 147 p_dst_segment9 in Char Y GL_SEGMENT9 U 148 p_dst_segment10 in Char Y GL_SEGMENT10 U 149 p_dst_segment11 in Char Y GL_SEGMENT11 U 150 p_dst_segment12 in Char Y GL_SEGMENT12 U 151 p_dst_segment13 in Char Y GL_SEGMENT13 U 152 p_dst_segment14 in Char Y GL_SEGMENT14 U 153 p_dst_segment15 in Char Y GL_SEGMENT15 U 154 p_requisition_line_id in Number Y U 155 p_currency_code in Char Y U 156 p_currency_conversion_date in Date Y U 157 p_currency_conversion_type in Char Y U 158 p_currency_conversion_rate in Number Y U 159 p_ussgl_transaction_code in Char Y U 160 p_wip_entity_type in Number Y U 161 p_schedule_id in Number Y U 162 p_employee_code in Char Y U 163 p_department_id in Number Y U 164 p_schedule_update_code in Number Y U 165 p_setup_teardown_code in Number Y U 166 p_primary_switch in Number Y U 167 p_mrp_code in Number Y U 168 p_operation_seq_num in Number Y U 169 p_repetitive_line_id in Number Y U 170 p_picking_line_id in Number Y U 171 p_trx_source_line_id in Number Y U 172 p_trx_source_delivery_id in Number Y U 173 p_demand_id in Number Y U 174 p_customer_ship_id in Number Y U 175 p_line_item_num in Number Y U 176 p_receiving_document in Char Y U 177 p_rcv_transaction_id in Number Y U 178 p_ship_to_location_id in Number Y LOCATION U 179 p_rebuild_item_id in Number Y U 180 p_rebuild_serial_number in Char Y U 181 p_rebuild_activity_id in Number Y U 182 p_rebuild_job_name in Char Y U 183 p_move_transaction_id in Number Y U 184 p_completion_transaction_id in Number Y U 185 p_wip_supply_type in Number Y U 186 p_relieve_reservations_flag in Char Y U 187 p_relieve_high_level_rsv_flag in Char Y U 188 p_transfer_price in Number Y U 189 p_lot_number in Char Y LOT_NUMBER U 190 p_lot_expiration_date in Date Y LOT_EXPIRATION_DATE U 191 p_lot_trx_quantity in Number Y LOT_QUANTITY U 192 p_fm_serial_number in Char Y FROM_SERIAL_NUMBER U 193 p_to_serial_number in Char Y TO_SERIAL_NUMBER P 1 status_ out Char STATUS_CODE_ P 2 message_ out Char MESSAGE_ P 3 p_source_header_id in Number SOURCE_HEADER_ID Error SQL select xu.action_, xxen_upload.status_meaning(xxen_upload.status_error) status_, nvl((select nvl2(mti.error_code,mti.error_code||': '||mti.error_explanation,'Record was not processed but no error was returned') from mtl_transactions_interface mti where mti.source_code='XXEN_INV_TRX_UPLOAD' and xu.source_header_id=mti.source_header_id and xu.source_line_id=mti.source_line_id and rownum=1),xu.message_) message_, xu.modified_columns_, xu.source_header_id, xu.source_line_id, xu.organization, xu.item, xu.description, xu.revision, xu.subinventory, xu.locator, xu.onhand_quantity, xu.uom, xu.transaction_date, xu.transaction_type, xu.transaction_source, xu.quantity, xu.account_alias, xu.distribution_account, xu.gl_segment1, xu.gl_segment2, xu.gl_segment3, xu.gl_segment4, xu.gl_segment5, xu.gl_segment6, xu.gl_segment7, xu.gl_segment8, xu.gl_segment9, xu.gl_segment10, xu.gl_segment11, xu.gl_segment12, xu.gl_segment13, xu.gl_segment14, xu.gl_segment15, xu.lpn, xu.from_serial_number, xu.to_serial_number, xu.transaction_reference, xu.reason, xu.transaction_cost, xu.lot_number, xu.lot_expiration_date, xu.lot_quantity, xu.to_org, xu.to_subinv, xu.to_locator, xu.shipment_number, xu.expected_receipt_date, xu.freight_code, xu.containers, xu.waybill_airbill, xu.transportation_cost, xu.transportation_account, xu.transfer_cost, xu.location, xu.source_project, xu.source_task, xu.expenditure_type, xu.expenditure_org, xu.attribute_category, xu.inv_trx_attribute1, xu.inv_trx_attribute2, xu.inv_trx_attribute3, xu.inv_trx_attribute4, xu.inv_trx_attribute5, xu.inv_trx_attribute6, xu.inv_trx_attribute7, xu.inv_trx_attribute8, xu.inv_trx_attribute9, xu.inv_trx_attribute10, xu.inv_trx_attribute11, xu.inv_trx_attribute12, xu.inv_trx_attribute13, xu.inv_trx_attribute14, xu.inv_trx_attribute15, xu.upload_row from xxen_inv_transaction_up_3284_u xu where nvl(xu.status_code_,'E')<>'S' or (xu.source_line_id, xu.source_header_id) in (select mti.source_line_id, mti.source_header_id from mtl_transactions_interface mti where mti.source_code='XXEN_INV_TRX_UPLOAD') Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.source_header_id, xu.source_line_id, mp.organization_code, msiv.concatenated_segments, msiv.description, mmt.revision, mmt.subinventory_code, milk.concatenated_segments, ( select sum(moqd.primary_transaction_quantity) from mtl_onhand_quantities_detail moqd where mmt.organization_id=moqd.organization_id and mmt.inventory_item_id=moqd.inventory_item_id and mmt.subinventory_code=moqd.subinventory_code and nvl(mmt.locator_id,-999)=nvl(moqd.locator_id,-999) and nvl(mmt.revision,'@@@')=nvl(moqd.revision,'@@@') ) on_hand_quantity, mmt.transaction_uom, mmt.transaction_date, mttv.transaction_type_name, mgd.segment1, mmt.transaction_quantity, fsfa.alias_name, gcck.concatenated_segments value, gcck.segment1, gcck.segment2, gcck.segment3, gcck.segment4, gcck.segment5, gcck.segment6, gcck.segment7, gcck.segment8, gcck.segment9, gcck.segment10, gcck.segment11, gcck.segment12, gcck.segment13, gcck.segment14, gcck.segment15, wlpn.license_plate_number, (select msn.serial_number from mtl_serial_numbers msn where msn.last_transaction_id=mmt.transaction_id and msn.serial_number=xu.from_serial_number and rownum=1), (select msn.serial_number from mtl_serial_numbers msn where msn.last_transaction_id=mmt.transaction_id and msn.serial_number=xu.to_serial_number and rownum=1), mmt.transaction_reference, mtr.reason_name, mmt.transaction_cost, mtln.lot_number, xu.lot_expiration_date, mtln.transaction_quantity, mp1.organization_code, mmt.transfer_subinventory, milk1.concatenated_segments, mmt.shipment_number, xu.expected_receipt_date, mmt.freight_code, mmt.number_of_containers, mmt.waybill_airbill, mmt.transportation_cost, gcck_t.concatenated_segments, mmt.transfer_cost, hla.location_code, ppev.project_number, ptev.task_number, mmt.expenditure_type, poev.name, mmt.attribute_category, mmt.attribute1, mmt.attribute2, mmt.attribute3, mmt.attribute4, mmt.attribute5, mmt.attribute6, mmt.attribute7, mmt.attribute8, mmt.attribute9, mmt.attribute10, mmt.attribute11, mmt.attribute12, mmt.attribute13, mmt.attribute14, mmt.attribute15, xu.upload_row from xxen_inv_transaction_up_3284_u xu, mtl_parameters mp, mtl_system_items_vl msiv, mtl_material_transactions mmt, mtl_item_locations_kfv milk, mtl_trx_types_view mttv, mtl_generic_dispositions mgd, gl_code_combinations_kfv gcck, fnd_shorthand_flex_aliases fsfa, gl_code_combinations_kfv gcck_t, wms_license_plate_numbers wlpn, mtl_transaction_lot_numbers mtln, mtl_item_locations_kfv milk1, mtl_parameters mp1, hr_locations_all hla, mtl_transaction_reasons mtr, pa_projects_expend_v ppev, pa_tasks_expend_v ptev, pa_organizations_expend_v poev where xu.status_code_=xxen_upload.status_success and xu.organization=mp.organization_code and mp.organization_id=msiv.organization_id and xu.item=msiv.concatenated_segments and mp.organization_id=mmt.organization_id and msiv.inventory_item_id=mmt.inventory_item_id and xu.transaction_date=mmt.transaction_date and xu.source_line_id=mmt.source_line_id and mmt.source_code='XXEN_INV_TRX_UPLOAD' and mmt.transaction_type_id=mttv.transaction_type_id and mmt.locator_id=milk.inventory_location_id(+) and mmt.organization_id=milk.organization_id(+) and mmt.transaction_source_id=mgd.disposition_id(+) and mmt.organization_id=mgd.organization_id(+) and mmt.distribution_account_id=gcck.code_combination_id(+) and gcck.concatenated_segments=fsfa.concatenated_segments(+) and gcck.chart_of_accounts_id=fsfa.id_flex_num(+) and fsfa.application_id(+)=101 and fsfa.id_flex_code(+)='GL#' and mmt.transportation_dist_account=gcck_t.code_combination_id(+) and mmt.lpn_id=wlpn.lpn_id(+) and mmt.transaction_id=mtln.transaction_id(+) and mmt.transfer_locator_id=milk1.inventory_location_id(+) and mmt.transfer_organization_id=milk1.organization_id(+) and mmt.transfer_organization_id=mp1.organization_id(+) and mmt.ship_to_location_id=hla.location_id(+) and mmt.reason_id=mtr.reason_id(+) and mmt.source_project_id=ppev.project_id(+) and mmt.source_task_id=ptev.task_id(+) and mmt.pa_expenditure_org_id=poev.organization_id(+)