ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 9A4C2F1B7E3D48A6B0C5D9E2F7A13B4CY 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, we.wip_entity_name job, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) job_type, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, msiv_a.concatenated_segments assembly, msiv_a.description assembly_description, wro.operation_seq_num, bd.department_code department, msiv.concatenated_segments component, msiv.description component_description, xxen_util.meaning(wro.wip_supply_type,'WIP_SUPPLY',700) supply_type, wro.date_required, wro.required_quantity, wro.quantity_issued, wro.required_quantity-wro.quantity_issued open_quantity, to_char(null) transaction_type, to_number(null) quantity, (select muomv.unit_of_measure from mtl_units_of_measure_vl muomv where msiv.primary_uom_code=muomv.uom_code) uom, wro.supply_subinventory subinventory, milk.concatenated_segments locator, to_char(null) revision, sysdate transaction_date, to_char(null) reason, cast(null as varchar2(240)) transaction_reference, to_char(null) lot_number, to_date(null) lot_expiration_date, to_number(null) lot_quantity, to_char(null) from_serial_number, to_char(null) to_serial_number, to_char(null) source_project, to_char(null) source_task, to_char(null) expenditure_type, to_char(null) expenditure_org, row_number() over (order by we.wip_entity_name, wro.operation_seq_num, msiv.concatenated_segments) upload_row from org_organization_definitions ood, wip_entities we, wip_discrete_jobs wdj, wip_requirement_operations wro, mtl_system_items_vl msiv, mtl_system_items_vl msiv_a, bom_departments bd, mtl_item_locations_kfv milk where 1=1 and we.organization_id=ood.organization_id and we.entity_type in (1,5,6) and wdj.wip_entity_id=we.wip_entity_id and wdj.organization_id=we.organization_id and wro.wip_entity_id=we.wip_entity_id and wro.organization_id=we.organization_id and wro.repetitive_schedule_id is null and msiv.inventory_item_id=wro.inventory_item_id and msiv.organization_id=wro.organization_id and msiv_a.inventory_item_id(+)=wdj.primary_item_id and msiv_a.organization_id(+)=wdj.organization_id and bd.department_id(+)=wro.department_id and milk.inventory_location_id(+)=wro.supply_locator_id and milk.organization_id(+)=wro.organization_id New upload: issue and return components to WIP discrete, lot based and maintenance work orders. Requirement driven download from wip_requirement_operations, processed through mtl_transactions_interface by xxen_inv_upload.upload_inv_trx and the inventory transaction manager. U A xxen_inv_upload.upload_inv_trx xxen_inv_upload.process_transaction xxen_inv_upload.validate_wip_material_trx( p_organization=>:$column$.organization, p_job=>:$column$.job, p_operation_seq_num=>:$column$.operation_seq_num, p_department=>:$column$.department, p_component=>:$column$.component, p_transaction_type=>:$column$.transaction_type, p_quantity=>:$column$.quantity, p_uom=>:$column$.uom, p_subinventory=>:$column$.subinventory, p_locator=>:$column$.locator, p_revision=>:$column$.revision, p_transaction_date=>:$column$.transaction_date, p_lot_number=>:$column$.lot_number, p_lot_quantity=>:$column$.lot_quantity, p_from_serial_number=>:$column$.from_serial_number, p_to_serial_number=>:$column$.to_serial_number ) US WIP Material Transaction Upload Issues components to, and returns components from, WIP work orders. The download returns one row per component requirement of the selected work orders, with the operation, department, supply subinventory and locator already filled in, and the required, issued and still open quantities shown. Enter a Transaction Type and a Quantity on the rows you want to transact and leave the rest blank. Quantity is always entered as a positive number; the Transaction Type decides the direction. Discrete jobs, lot based jobs and maintenance work orders are supported. Repetitive schedules are not. Rows are staged in the inventory transaction interface and processed by the inventory transaction manager, so the resulting transactions are identical to those the WIP Material Transactions form creates and relieve the work order requirement in the same way. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 ood.organization_code=:organization_code LOV custom Y select mp.organization_code value, haouv.name description from mtl_parameters mp, hr_all_organization_units_vl haouv where mp.organization_id=haouv.organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by value select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 3 30 1=1 we.wip_entity_name=:job LOV custom Y select we.wip_entity_name value, msiv.concatenated_segments||decode(msiv.concatenated_segments,null,null,' - ')||xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) description from wip_entities we, wip_discrete_jobs wdj, mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$flex$.Organization_Code and we.organization_id=mp.organization_id and we.entity_type in (1,5,6) and wdj.wip_entity_id=we.wip_entity_id and wdj.organization_id=we.organization_id and msiv.inventory_item_id(+)=wdj.primary_item_id and msiv.organization_id(+)=wdj.organization_id order by value US Job 4 40 1=1 wdj.status_type=:job_status LOV custom Y select ml.lookup_code id, ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' and ml.lookup_code in (1,3,4,6) order by value select ml.meaning from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' and ml.lookup_code=3 US Job Status 5 50 1=1 msiv_a.concatenated_segments=:assembly LOV custom Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where mp.organization_code=:$flex$.Organization_Code and msiv.organization_id=mp.organization_id and msiv.build_in_wip_flag='Y' order by value US Assembly 6 60 1=1 msiv.concatenated_segments=:component LOV custom Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where mp.organization_code=:$flex$.Organization_Code and msiv.organization_id=mp.organization_id order by value US Component 7 70 1=1 bd.department_code=:department LOV custom Y select bd.department_code value, bd.description from bom_departments bd, mtl_parameters mp where mp.organization_code=:$flex$.Organization_Code and bd.organization_id=mp.organization_id order by value US Department 8 80 1=1 wro.date_required>=:date_required_from Date US Date Required From 9 90 1=1 wro.date_required<:date_required_to+1 Date :$flex$.Date_Required_From US Date Required To 10 100 1=1 wro.required_quantity>wro.quantity_issued LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual select xxen_util.meaning('Y','YES_NO',0) from dual US Open Requirements Only 785CEB21C76E47519C3AC8D03A7C023B Default All columns. ENGINATICS 5 ASSEMBLY 6 ASSEMBLY_DESCRIPTION 9 COMPONENT 10 COMPONENT_DESCRIPTION 12 DATE_REQUIRED 8 DEPARTMENT 33 EXPENDITURE_ORG 32 EXPENDITURE_TYPE 28 FROM_SERIAL_NUMBER 2 JOB 4 JOB_STATUS 3 JOB_TYPE Y 20 LOCATOR 26 LOT_EXPIRATION_DATE 25 LOT_NUMBER 27 LOT_QUANTITY 15 OPEN_QUANTITY 7 OPERATION_SEQ_NUM 1 ORGANIZATION 17 QUANTITY 14 QUANTITY_ISSUED 23 REASON 13 REQUIRED_QUANTITY 21 REVISION 30 SOURCE_PROJECT 31 SOURCE_TASK 19 SUBINVENTORY 11 SUPPLY_TYPE 29 TO_SERIAL_NUMBER 22 TRANSACTION_DATE 24 TRANSACTION_REFERENCE 16 TRANSACTION_TYPE 18 UOM S Site 785CEB21C76E47519C3AC8D03A7C023B 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 Y select mp.organization_id id, mp.organization_code value, haouv.name description from mtl_parameters mp, hr_all_organization_units_vl haouv where mp.organization_id=haouv.organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by value Y Y 8 JOB LOV custom varchar2 Y Y select we.wip_entity_id id, we.wip_entity_name value, msiv.concatenated_segments||decode(msiv.concatenated_segments,null,null,' - ')||xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) description from wip_entities we, wip_discrete_jobs wdj, mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization and we.organization_id=mp.organization_id and we.entity_type in (1,5,6) and wdj.wip_entity_id=we.wip_entity_id and wdj.organization_id=we.organization_id and wdj.status_type in (3,4,6) and msiv.inventory_item_id(+)=wdj.primary_item_id and msiv.organization_id(+)=wdj.organization_id order by value Y 9 JOB_TYPE Char varchar2 Y 10 JOB_STATUS Char varchar2 Y 11 ASSEMBLY Char varchar2 Y 12 ASSEMBLY_DESCRIPTION Char varchar2 Y 13 OPERATION_SEQ_NUM LOV custom number Y select distinct wro.operation_seq_num id, wro.operation_seq_num value, bd.department_code description from wip_requirement_operations wro, wip_entities we, mtl_parameters mp, bom_departments bd where mp.organization_code=:$column$.organization and we.organization_id=mp.organization_id and we.wip_entity_name=:$column$.job and wro.wip_entity_id=we.wip_entity_id and wro.organization_id=we.organization_id and bd.department_id(+)=wro.department_id order by value Operation sequence the component is issued to. Required for work orders with a routing. 14 DEPARTMENT LOV custom varchar2 Y select bd.department_id id, bd.department_code value, bd.description from bom_departments bd, mtl_parameters mp where mp.organization_code=:$column$.organization and bd.organization_id=mp.organization_id order by value select bd.department_code from wip_requirement_operations wro, wip_entities we, mtl_parameters mp, bom_departments bd where mp.organization_code=:$column$.organization and we.organization_id=mp.organization_id and we.wip_entity_name=:$column$.job and wro.wip_entity_id=we.wip_entity_id and wro.organization_id=we.organization_id and wro.operation_seq_num=:$column$.operation_seq_num and bd.department_id=wro.department_id and rownum=1 15 COMPONENT LOV custom varchar2 Y Y select msiv.inventory_item_id id, msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where mp.organization_code=:$column$.organization and msiv.organization_id=mp.organization_id and msiv.mtl_transactions_enabled_flag='Y' and msiv.bom_item_type=4 order by value Component to issue or return. Any transactable component item may be entered; one that is not on the work order bill is allowed and is flagged in the result, matching the WIP Material Transactions form. Y 16 COMPONENT_DESCRIPTION Char varchar2 Y 17 SUPPLY_TYPE Char varchar2 Y 18 DATE_REQUIRED Date date Y 19 REQUIRED_QUANTITY Number number Y 20 QUANTITY_ISSUED Number number Y 21 OPEN_QUANTITY Number number Y 22 TRANSACTION_TYPE LOV custom varchar2 Y select mttv.transaction_type_id id, mttv.transaction_type_name value, mttv.description from mtl_trx_types_view mttv where mttv.transaction_source_type_id=5 and mttv.transaction_action_id in (1,27) order by value select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id=35 Y 23 QUANTITY Number number Quantity to issue or return, in the transaction unit of measure. Always enter a positive quantity - the Transaction Type decides the direction. Y 24 UOM LOV custom varchar2 Y select muomv.uom_code id, muomv.unit_of_measure value, muomv.uom_class description from mtl_units_of_measure_vl muomv, mtl_system_items_vl msiv, mtl_parameters mp where mp.organization_code=:$column$.organization and msiv.organization_id=mp.organization_id and msiv.concatenated_segments=:$column$.component and (muomv.uom_code=msiv.primary_uom_code or exists (select null from mtl_uom_conversions muc where muc.uom_code=muomv.uom_code and muc.uom_class=(select muomv2.uom_class from mtl_units_of_measure_vl muomv2 where muomv2.uom_code=msiv.primary_uom_code) and muc.inventory_item_id in (0,msiv.inventory_item_id) and nvl(muc.disable_date,sysdate+1)>sysdate)) order by value Y 25 SUBINVENTORY LOV custom varchar2 Y select msi.secondary_inventory_name value, msi.description from mtl_secondary_inventories msi, mtl_parameters mp where mp.organization_code=:$column$.organization and msi.organization_id=mp.organization_id and nvl(msi.disable_date,sysdate+1)>sysdate order by value Y 26 LOCATOR LOV custom varchar2 Y select milk.inventory_location_id id, milk.concatenated_segments value, milk.description from mtl_item_locations_kfv milk, mtl_parameters mp where mp.organization_code=:$column$.organization and milk.organization_id=mp.organization_id and milk.subinventory_code=:$column$.subinventory and nvl(milk.disable_date,sysdate+1)>sysdate order by value 27 REVISION LOV custom varchar2 Y select mir.revision value, xxen_util.client_time(mir.effectivity_date) description from mtl_item_revisions mir, mtl_system_items_vl msiv, mtl_parameters mp where mp.organization_code=:$column$.organization and msiv.organization_id=mp.organization_id and msiv.concatenated_segments=:$column$.component and mir.inventory_item_id=msiv.inventory_item_id and mir.organization_id=msiv.organization_id order by value 28 TRANSACTION_DATE Date date Y 29 REASON LOV custom varchar2 Y select mtr.reason_id id, mtr.reason_name value, mtr.description from mtl_transaction_reasons mtr where nvl(mtr.disable_date,sysdate+1)>sysdate order by value 30 TRANSACTION_REFERENCE Char varchar2 31 LOT_NUMBER Char varchar2 32 LOT_EXPIRATION_DATE Date date 33 LOT_QUANTITY Number number 34 FROM_SERIAL_NUMBER Char varchar2 35 TO_SERIAL_NUMBER Char varchar2 36 SOURCE_PROJECT LOV custom varchar2 Y select ppev.project_id id, ppev.project_number value, ppev.project_name description from pa_projects_expend_v ppev, pjm_project_parameters ppp, mtl_parameters mp where mp.organization_code=:$column$.organization and ppp.organization_id=mp.organization_id and ppp.project_id=ppev.project_id order by value 37 SOURCE_TASK LOV custom varchar2 Y select ptev.task_id id, ptev.task_number value, ptev.task_name description from pa_tasks_expend_v ptev, pa_projects_expend_v ppev where ppev.project_number=:$column$.source_project and ptev.project_id=ppev.project_id and ptev.chargeable_flag='Y' order by value 38 EXPENDITURE_TYPE LOV custom varchar2 Y select pet.expenditure_type value, pet.description from pa_expenditure_types pet where nvl(pet.end_date_active,sysdate+1)>sysdate order by value 39 EXPENDITURE_ORG LOV custom varchar2 Y select haouv.organization_id id, haouv.name value, haouv.type description from hr_all_organization_units_vl haouv where nvl(haouv.date_to,sysdate+1)>sysdate order by value 40 UPLOAD_ROW Number number Y 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 U 6 p_validation_required in Number Y U 7 p_inventory_item_id in Number Y COMPONENT 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 U 29 p_transfer_organization in Number Y U 30 p_transfer_locator in Number Y U 31 p_shipment_number in Char Y U 32 p_transportation_cost in Number Y U 33 p_transportation_account in Number Y U 34 p_transfer_cost in Number Y U 35 p_freight_code in Char Y U 36 p_containers in Number Y U 37 p_waybill_airbill in Char Y U 38 p_expected_arrival_date in Date Y 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 U 51 p_attribute1 in Char Y U 52 p_attribute2 in Char Y U 53 p_attribute3 in Char Y U 54 p_attribute4 in Char Y U 55 p_attribute5 in Char Y U 56 p_attribute6 in Char Y U 57 p_attribute7 in Char Y U 58 p_attribute8 in Char Y U 59 p_attribute9 in Char Y U 60 p_attribute10 in Char Y U 61 p_attribute11 in Char Y U 62 p_attribute12 in Char Y U 63 p_attribute13 in Char Y U 64 p_attribute14 in Char Y U 65 p_attribute15 in Char Y 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 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 JOB 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 U 138 p_distribution_account_id in Number Y U 139 p_dst_segment1 in Char Y U 140 p_dst_segment2 in Char Y U 141 p_dst_segment3 in Char Y U 142 p_dst_segment4 in Char Y U 143 p_dst_segment5 in Char Y U 144 p_dst_segment6 in Char Y U 145 p_dst_segment7 in Char Y U 146 p_dst_segment8 in Char Y U 147 p_dst_segment9 in Char Y U 148 p_dst_segment10 in Char Y U 149 p_dst_segment11 in Char Y U 150 p_dst_segment12 in Char Y U 151 p_dst_segment13 in Char Y U 152 p_dst_segment14 in Char Y U 153 p_dst_segment15 in Char Y 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 DEPARTMENT 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 OPERATION_SEQ_NUM 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 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 10 status_ out Char STATUS_CODE_ P 20 message_ out Char MESSAGE_ P 30 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_line_id, xu.source_header_id, xu.organization, xu.job, xu.job_type, xu.job_status, xu.assembly, xu.assembly_description, xu.operation_seq_num, xu.department, xu.component, xu.component_description, xu.supply_type, xu.date_required, xu.required_quantity, xu.quantity_issued, xu.open_quantity, xu.transaction_type, xu.quantity, xu.uom, xu.subinventory, xu.locator, xu.revision, xu.transaction_date, xu.reason, xu.transaction_reference, xu.lot_number, xu.lot_expiration_date, xu.lot_quantity, xu.from_serial_number, xu.to_serial_number, xu.source_project, xu.source_task, xu.expenditure_type, xu.expenditure_org, xu.upload_row from xxen_wip_material_trans_5414_u xu where nvl(xu.status_code_,xxen_upload.status_error)<>xxen_upload.status_success Order By upload_row, job, operation_seq_num, component Success SQL select xu.action_, xu.status_, case when mmt.transaction_id is not null and nvl(wro.required_quantity,0)=0 then rtrim('Component is not on the work order bill. '||xu.message_) else xu.message_ end message_, xu.modified_columns_, xu.source_line_id, xu.source_header_id, nvl(mp.organization_code,xu.organization) organization, nvl(we.wip_entity_name,xu.job) job, nvl(xxen_util.meaning(we.entity_type,'WIP_ENTITY',700),xu.job_type) job_type, nvl(xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700),xu.job_status) job_status, nvl(msiv_a.concatenated_segments,xu.assembly) assembly, nvl(msiv_a.description,xu.assembly_description) assembly_description, nvl(mmt.operation_seq_num,xu.operation_seq_num) operation_seq_num, nvl(bd.department_code,xu.department) department, nvl(msiv.concatenated_segments,xu.component) component, nvl(msiv.description,xu.component_description) component_description, nvl(xxen_util.meaning(wro.wip_supply_type,'WIP_SUPPLY',700),xu.supply_type) supply_type, nvl(wro.date_required,xu.date_required) date_required, nvl(wro.required_quantity,xu.required_quantity) required_quantity, nvl(wro.quantity_issued,xu.quantity_issued) quantity_issued, nvl(wro.required_quantity-wro.quantity_issued,xu.open_quantity) open_quantity, nvl(mttv.transaction_type_name,xu.transaction_type) transaction_type, nvl(abs(mmt.transaction_quantity),xu.quantity) quantity, nvl((select muomv.unit_of_measure from mtl_units_of_measure_vl muomv where mmt.transaction_uom=muomv.uom_code),xu.uom) uom, nvl(mmt.subinventory_code,xu.subinventory) subinventory, nvl(milk.concatenated_segments,xu.locator) locator, nvl(mmt.revision,xu.revision) revision, nvl(mmt.transaction_date,xu.transaction_date) transaction_date, nvl(mtr.reason_name,xu.reason) reason, nvl(mmt.transaction_reference,xu.transaction_reference) transaction_reference, nvl(mtln.lot_number,xu.lot_number) lot_number, xu.lot_expiration_date, nvl(abs(mtln.transaction_quantity),xu.lot_quantity) lot_quantity, xu.from_serial_number, xu.to_serial_number, nvl((select ppa.segment1 from pa_projects_all ppa where mmt.source_project_id=ppa.project_id),xu.source_project) source_project, nvl((select pt.task_number from pa_tasks pt where mmt.source_task_id=pt.task_id),xu.source_task) source_task, nvl(mmt.expenditure_type,xu.expenditure_type) expenditure_type, nvl((select haouv.name from hr_all_organization_units_vl haouv where mmt.pa_expenditure_org_id=haouv.organization_id),xu.expenditure_org) expenditure_org, xu.upload_row from xxen_wip_material_trans_5414_u xu, mtl_material_transactions mmt, mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj, wip_requirement_operations wro, mtl_system_items_vl msiv, mtl_system_items_vl msiv_a, bom_departments bd, mtl_item_locations_kfv milk, mtl_trx_types_view mttv, mtl_transaction_reasons mtr, mtl_transaction_lot_numbers mtln where xu.status_code_=xxen_upload.status_success and xu.source_line_id=mmt.source_line_id(+) and mmt.source_code(+)='XXEN_INV_TRX_UPLOAD' and mmt.organization_id=mp.organization_id(+) and mmt.transaction_source_id=we.wip_entity_id(+) and mmt.organization_id=we.organization_id(+) and mmt.transaction_source_id=wdj.wip_entity_id(+) and mmt.organization_id=wdj.organization_id(+) and mmt.transaction_source_id=wro.wip_entity_id(+) and mmt.organization_id=wro.organization_id(+) and mmt.inventory_item_id=wro.inventory_item_id(+) and mmt.operation_seq_num=wro.operation_seq_num(+) and mmt.inventory_item_id=msiv.inventory_item_id(+) and mmt.organization_id=msiv.organization_id(+) and wdj.primary_item_id=msiv_a.inventory_item_id(+) and wdj.organization_id=msiv_a.organization_id(+) and mmt.department_id=bd.department_id(+) and mmt.organization_id=bd.organization_id(+) and mmt.locator_id=milk.inventory_location_id(+) and mmt.organization_id=milk.organization_id(+) and mmt.transaction_type_id=mttv.transaction_type_id(+) and mmt.reason_id=mtr.reason_id(+) and mmt.transaction_id=mtln.transaction_id(+)