ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E32CC702C97C49B79BA6C58BEE9153EDY select null action_, null status_, null message_, null modified_columns_, to_number(null) transaction_id, x.organization_code, x.line, x.line_description, x.assembly, x.assembly_description, x.primary_uom, x.job, x.job_description, x.bill_revision, x.transaction_type, sysdate transaction_date, to_char(null) overcompletion, x.job_quantity, x.job_complete, x.available, case when x.revision_qty_control_code=2 then x.bill_revision end revision, x.subinventory, x.locator, x.primary_uom uom, to_number(null) quantity, to_char(null) final_completion, case when x.lot_control_code=2 then x.lot_number end lot_number, to_date(null) lot_expiration_date, to_char(null) from_serial_number, to_char(null) to_serial_number, to_char(null) reason, cast(null as varchar2(240)) reference, to_char(null) completion_attribute_category, to_char(null) completion_attribute1, to_char(null) completion_attribute2, to_char(null) completion_attribute3, to_char(null) completion_attribute4, to_char(null) completion_attribute5, to_char(null) completion_attribute6, to_char(null) completion_attribute7, to_char(null) completion_attribute8, to_char(null) completion_attribute9, to_char(null) completion_attribute10, to_char(null) completion_attribute11, to_char(null) completion_attribute12, to_char(null) completion_attribute13, to_char(null) completion_attribute14, to_char(null) completion_attribute15, row_number() over (order by x.organization_code,x.job,x.line,x.assembly,x.transaction_action_id) upload_row from ( select mp.organization_code, to_char(null) line, to_char(null) line_description, msiv.concatenated_segments assembly, msiv.description assembly_description, msiv.primary_uom_code primary_uom, msiv.revision_qty_control_code, msiv.lot_control_code, we.wip_entity_name job, we.description job_description, wdj.bom_revision bill_revision, mtt.transaction_type_name transaction_type, mtt.transaction_action_id, wdj.start_quantity job_quantity, wdj.quantity_completed job_complete, xxen_wip_upload.completion_available(wdj.organization_id,wdj.wip_entity_id,null,mtt.transaction_action_id) available, wdj.completion_subinventory subinventory, milk.concatenated_segments locator, wdj.lot_number from mtl_parameters mp, wip_discrete_jobs wdj, wip_entities we, mtl_system_items_vl msiv, mtl_transaction_types mtt, mtl_item_locations_kfv milk where 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) and mp.organization_id=wdj.organization_id and wdj.status_type in (3,4) and wdj.serialization_start_op is null and wdj.wip_entity_id=we.wip_entity_id and we.entity_type=1 and wdj.primary_item_id=msiv.inventory_item_id and wdj.organization_id=msiv.organization_id and mtt.transaction_type_id in (44,17) and wdj.completion_locator_id=milk.inventory_location_id(+) and wdj.organization_id=milk.organization_id(+) union all select mp.organization_code, wl.line_code line, wl.description line_description, msiv.concatenated_segments assembly, msiv.description assembly_description, msiv.primary_uom_code primary_uom, msiv.revision_qty_control_code, msiv.lot_control_code, to_char(null) job, to_char(null) job_description, (select max(wrs.bom_revision) keep (dense_rank first order by wrs.first_unit_start_date) from wip_repetitive_schedules wrs where wri.wip_entity_id=wrs.wip_entity_id and wri.line_id=wrs.line_id and wrs.status_type in (3,4)) bill_revision, mtt.transaction_type_name transaction_type, mtt.transaction_action_id, to_number(null) job_quantity, to_number(null) job_complete, xxen_wip_upload.completion_available(wri.organization_id,wri.wip_entity_id,wri.line_id,mtt.transaction_action_id) available, wri.completion_subinventory subinventory, milk.concatenated_segments locator, to_char(null) lot_number from mtl_parameters mp, wip_repetitive_items wri, wip_lines wl, mtl_system_items_vl msiv, mtl_transaction_types mtt, mtl_item_locations_kfv milk where 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) and mp.organization_id=wri.organization_id and (wri.wip_entity_id,wri.line_id) in (select wrs.wip_entity_id, wrs.line_id from wip_repetitive_schedules wrs where wrs.status_type in (3,4)) and wri.line_id=wl.line_id and wri.primary_item_id=msiv.inventory_item_id and wri.organization_id=msiv.organization_id and mtt.transaction_type_id in (44,17) and wri.completion_locator_id=milk.inventory_location_id(+) and wri.organization_id=milk.organization_id(+) ) x where x.available>0 and 1=1 New upload: assembly completions and returns of discrete jobs and repetitive schedules as in the Completion Transactions form (WIPTXCMP), each row inserted into mtl_transactions_interface and processed by inv_txn_manager_pub.process_transactions in its own transaction, with lots, serial numbers, overcompletion and final completion. U A xxen_wip_upload.upload_completion_transaction xxen_wip_upload.validate_completion_trx(:$column$.organization_code,:$column$.line,:$column$.assembly,:$column$.job,:$column$.transaction_type,:$column$.transaction_date,:$column$.overcompletion,:$column$.final_completion,:$column$.subinventory,:$column$.uom,:$column$.quantity,:$column$.lot_number,:$column$.from_serial_number) US WIP Completion Upload Creates assembly completions and returns as in the Completion Transactions form, for discrete jobs with and without routing and for the repetitive schedules of a line and assembly, into a completion subinventory and locator, with lot and serial numbers and overcompletions. Each row is inserted into the inventory transaction interface and processed immediately by the transaction manager, with backflush of the assembly pull and operation pull components, as for a completion entered in the form. Upload Mode Create opens an empty sheet. Create, Update downloads the jobs and schedules of the selected Transaction Type: for completions the quantity at To move of the last operation, or without routing the quantity not completed yet, as Available; for returns the completed quantity. Enter Quantity on the rows to transact, rows left unchanged are not uploaded. An existing transaction is never updated; correct a completion with a return. Quantity above Available needs Overcompletion, within the overcompletion tolerance, and follows the Allow Overcompletion parameter. A blank Final Completion takes the form's default. Each row carries one lot and one serial number range; transact several lots as several rows. Not supported: serialized jobs, sales order reservations and kanban replenishment, e-records and e-signatures, and backflush lot and serial entry. A completion fails when a backflush component is serial controlled, or lot controlled with a lot that cannot be derived. To enter descriptive flexfield values, use the All Columns template, which shows the Completion Attribute segment prompts. Enginatics R12 only Upload 1=1 :allow_overcompletion 1 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 Create: an empty sheet for new transactions. Create, Update: the jobs and schedules with a quantity to complete or to return, as new transaction rows; an existing transaction is not updated. 2 20 1=1 x.organization_code=:organization_code LOV custom select oav.organization_code value, oav.organization_name description from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id and oav.organization_id in (select wp.organization_id from wip_parameters wp) 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 Organization of the downloaded jobs and schedules and default organization of new rows. 3 30 1=1 x.line=:line_code LOV custom select wl.line_code value, wl.description from mtl_parameters mp, wip_lines wl where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=wl.organization_id and nvl(wl.disable_date,sysdate+1)>sysdate and wl.line_id in (select wri.line_id from wip_repetitive_items wri) order by value US Line Restricts the download to the repetitive schedules of the production line. 4 40 1=1 x.assembly=:assembly LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Assembly Restricts the download to the jobs and repetitive schedules of the assembly. 5 50 1=1 x.job=:job LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and ( :$flex$.organization_code is null and ood.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) or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US Job Restricts the download to the job. 6 60 1=1 x.transaction_type=:transaction_type LOV custom select mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt where mtt.transaction_type_id in (44,17) order by mtt.transaction_type_id desc select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id=44 US Transaction Type Completion downloads the quantity to complete, Completion Return the completed quantity. 7 70 :allow_overcompletion LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Allow Overcompletion No refuses rows with Overcompletion, as the Completion Transactions form does without the Allow Overcompletions function; to apply it to users or responsibilities, lock No on a template copy shared as restricted with them. A78D1B46206248B9835B0C3AE70EFF23 All Columns Every column of the upload, including the job quantities and the transaction descriptive flexfield. ENGINATICS 4 ASSEMBLY 5 ASSEMBLY_DESCRIPTION 15 AVAILABLE 9 BILL_REVISION 29 COMPLETION_ATTRIBUTE1 38 COMPLETION_ATTRIBUTE10 39 COMPLETION_ATTRIBUTE11 40 COMPLETION_ATTRIBUTE12 41 COMPLETION_ATTRIBUTE13 42 COMPLETION_ATTRIBUTE14 43 COMPLETION_ATTRIBUTE15 30 COMPLETION_ATTRIBUTE2 31 COMPLETION_ATTRIBUTE3 32 COMPLETION_ATTRIBUTE4 33 COMPLETION_ATTRIBUTE5 34 COMPLETION_ATTRIBUTE6 35 COMPLETION_ATTRIBUTE7 36 COMPLETION_ATTRIBUTE8 37 COMPLETION_ATTRIBUTE9 28 COMPLETION_ATTRIBUTE_CATEGORY 21 FINAL_COMPLETION 24 FROM_SERIAL_NUMBER 7 JOB 14 JOB_COMPLETE 8 JOB_DESCRIPTION 13 JOB_QUANTITY 2 LINE 3 LINE_DESCRIPTION 18 LOCATOR 23 LOT_EXPIRATION_DATE 22 LOT_NUMBER 1 ORGANIZATION_CODE 12 OVERCOMPLETION 6 PRIMARY_UOM 20 QUANTITY 26 REASON 27 REFERENCE 16 REVISION 17 SUBINVENTORY 25 TO_SERIAL_NUMBER 11 TRANSACTION_DATE 10 TRANSACTION_TYPE Y 19 UOM Upload Mode C S Site 46B5C5F161454BB4B8FB7702AAA7D9E1 Completion Assembly completions with the main fields of the Completion Transactions form, without the flexfields. Upload Mode Create opens an empty sheet, Create, Update downloads the jobs and schedules with a quantity to complete. ENGINATICS 3 ASSEMBLY 4 ASSEMBLY_DESCRIPTION 10 AVAILABLE 16 FINAL_COMPLETION 18 FROM_SERIAL_NUMBER 5 JOB 6 JOB_DESCRIPTION 2 LINE 13 LOCATOR 17 LOT_NUMBER 1 ORGANIZATION_CODE 9 OVERCOMPLETION 15 QUANTITY 20 REASON 21 REFERENCE 11 REVISION 12 SUBINVENTORY 19 TO_SERIAL_NUMBER 8 TRANSACTION_DATE 7 TRANSACTION_TYPE Y 14 UOM Upload Mode C S Site 4BAE82F441E94923AC5ED11B774D6C95 Return Assembly returns: Create, Update downloads the completed jobs and schedules with their completed quantity as return rows. ENGINATICS 3 ASSEMBLY 4 ASSEMBLY_DESCRIPTION 9 AVAILABLE 16 FROM_SERIAL_NUMBER 5 JOB 6 JOB_DESCRIPTION 2 LINE 12 LOCATOR 15 LOT_NUMBER 1 ORGANIZATION_CODE 14 QUANTITY 18 REASON 19 REFERENCE 10 REVISION 11 SUBINVENTORY 17 TO_SERIAL_NUMBER 8 TRANSACTION_DATE 7 TRANSACTION_TYPE Y 13 UOM Transaction Type WIP Completion Return Upload Mode C,U S Site 46B5C5F161454BB4B8FB7702AAA7D9E1 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 TRANSACTION_ID Number number Y 6 ORGANIZATION_CODE LOV custom varchar2 select oav.organization_id id, oav.organization_code value, oav.organization_name description from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id and oav.organization_id in (select wp.organization_id from wip_parameters wp) order by value select oav.organization_code from org_access_view oav where oav.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id and oav.organization_id in (select wp.organization_id from wip_parameters wp) Y Y 7 LINE LOV custom varchar2 select wl.line_id id, wl.line_code value, wl.description from mtl_parameters mp, wip_lines wl where mp.organization_code=:$column$.organization_code and mp.organization_id=wl.organization_id and nvl(wl.disable_date,sysdate+1)>sysdate and wl.line_id in (select wri.line_id from wip_repetitive_items wri) order by value 8 LINE_DESCRIPTION Char varchar2 select wl.description from mtl_parameters mp, wip_lines wl where mp.organization_code=:$column$.organization_code and mp.organization_id=wl.organization_id and wl.line_code=:$column$.line Y 9 ASSEMBLY LOV custom varchar2 select msiv.inventory_item_id id, msiv.concatenated_segments value, msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.mtl_transactions_enabled_flag='Y' and ( :$column$.line is not null and msiv.inventory_item_id in (select wri.primary_item_id from wip_lines wl, wip_repetitive_items wri where mp.organization_id=wl.organization_id and wl.line_code=:$column$.line and wl.line_id=wri.line_id) or :$column$.line is null and msiv.inventory_item_id in ( select wdj.primary_item_id from wip_discrete_jobs wdj where mp.organization_id=wdj.organization_id and wdj.status_type in (3,4) union all select we.primary_item_id from wip_repetitive_schedules wrs, wip_entities we where mp.organization_id=wrs.organization_id and wrs.status_type in (3,4) and wrs.wip_entity_id=we.wip_entity_id ) ) order by value select msiv.concatenated_segments from mtl_parameters mp, wip_entities we, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.primary_item_id=msiv.inventory_item_id and we.organization_id=msiv.organization_id 10 ASSEMBLY_DESCRIPTION Char varchar2 select msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly Y 11 PRIMARY_UOM Char varchar2 select msiv.primary_uom_code from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly Y 12 JOB LOV custom varchar2 select we.wip_entity_id id, we.wip_entity_name value, we.description from mtl_parameters mp, wip_discrete_jobs wdj, wip_entities we where mp.organization_code=:$column$.organization_code and mp.organization_id=wdj.organization_id and wdj.status_type in (3,4) and wdj.serialization_start_op is null and wdj.primary_item_id is not null and wdj.wip_entity_id=we.wip_entity_id and we.entity_type=1 order by value 13 JOB_DESCRIPTION Char varchar2 select we.description from mtl_parameters mp, wip_entities we where mp.organization_code=:$column$.organization_code and mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job Y 14 BILL_REVISION Char varchar2 select coalesce( (select wdj.bom_revision from wip_entities we, wip_discrete_jobs wdj where mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id), ( select max(wrs.bom_revision) keep (dense_rank first order by wrs.first_unit_start_date) from mtl_system_items_vl msiv, wip_entities we, wip_lines wl, wip_repetitive_schedules wrs where :$column$.job is null and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.organization_id=we.organization_id and msiv.inventory_item_id=we.primary_item_id and we.entity_type=2 and mp.organization_id=wl.organization_id and wl.line_code=:$column$.line and we.wip_entity_id=wrs.wip_entity_id and wl.line_id=wrs.line_id and wrs.status_type in (3,4) ) ) bill_revision from mtl_parameters mp where mp.organization_code=:$column$.organization_code Y 15 TRANSACTION_TYPE LOV custom varchar2 select mtt.transaction_type_id id, mtt.transaction_type_name value, mtt.description from mtl_transaction_types mtt where mtt.transaction_source_type_id=5 and ( mtt.user_defined_flag='N' and mtt.transaction_type_id in (44,17) or mtt.user_defined_flag='Y' and mtt.transaction_action_id in (31,32) ) and nvl(mtt.disable_date,sysdate+1)>sysdate order by value select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_type_id=44 Y 16 TRANSACTION_DATE DateTime date sysdate Y 17 OVERCOMPLETION LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where nvl(:$flex$.Allow_Overcompletion,'Y')='Y' and :$column$.transaction_type in (select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_action_id=31 and mtt.transaction_source_type_id=5) Quantity above Available is completed as overcompletion; offered for completions when Allow Overcompletion is Yes. 18 JOB_QUANTITY Number number select wdj.start_quantity from mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj where mp.organization_code=:$column$.organization_code and mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id Y 19 JOB_COMPLETE Number number select wdj.quantity_completed from mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj where mp.organization_code=:$column$.organization_code and mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id Y 20 AVAILABLE Number number xxen_wip_upload.completion_available_dsp(:$column$.organization_code,:$column$.job,:$column$.line,:$column$.assembly,:$column$.transaction_type,:$column$.uom) Completions: the quantity at To move of the last operation, without routing the quantity not completed yet. Returns: the completed quantity. Y 21 REVISION LOV custom varchar2 select mir.revision value, mir.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_item_revisions_vl mir where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.revision_qty_control_code=2 and msiv.inventory_item_id=mir.inventory_item_id and msiv.organization_id=mir.organization_id order by value select :$column$.bill_revision from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.revision_qty_control_code=2 22 SUBINVENTORY LOV custom varchar2 select msi.secondary_inventory_name value, msi.description from mtl_parameters mp, mtl_secondary_inventories msi where mp.organization_code=:$column$.organization_code and mp.organization_id=msi.organization_id and msi.quantity_tracked=1 and nvl(msi.disable_date,sysdate+1)>sysdate and ( msi.secondary_inventory_name in (select misi.secondary_inventory from mtl_system_items_vl msiv, mtl_item_sub_inventories misi where mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.inventory_item_id=misi.inventory_item_id and msiv.organization_id=misi.organization_id) or mp.organization_id not in (select msiv.organization_id from mtl_system_items_vl msiv where mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.restrict_subinventories_code=1) ) order by value select coalesce( (select wdj.completion_subinventory from wip_entities we, wip_discrete_jobs wdj where mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id), ( select wri.completion_subinventory from mtl_system_items_vl msiv, wip_lines wl, wip_repetitive_items wri where :$column$.job is null and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and mp.organization_id=wl.organization_id and wl.line_code=:$column$.line and wl.line_id=wri.line_id and msiv.inventory_item_id=wri.primary_item_id and msiv.organization_id=wri.organization_id ) ) subinventory from mtl_parameters mp where mp.organization_code=:$column$.organization_code Y 23 LOCATOR LOV custom varchar2 select milk.inventory_location_id id, milk.concatenated_segments value, milk.description from mtl_parameters mp, mtl_item_locations_kfv milk where mp.organization_code=:$column$.organization_code and mp.organization_id=milk.organization_id and milk.subinventory_code=:$column$.subinventory and nvl(milk.disable_date,sysdate+1)>sysdate order by value select milk.concatenated_segments from mtl_parameters mp, mtl_item_locations_kfv milk where mp.organization_code=:$column$.organization_code and mp.organization_id=milk.organization_id and milk.subinventory_code=:$column$.subinventory and milk.inventory_location_id=coalesce( (select wdj.completion_locator_id from wip_entities we, wip_discrete_jobs wdj where mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id), ( select wri.completion_locator_id from mtl_system_items_vl msiv, wip_lines wl, wip_repetitive_items wri where :$column$.job is null and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and mp.organization_id=wl.organization_id and wl.line_code=:$column$.line and wl.line_id=wri.line_id and msiv.inventory_item_id=wri.primary_item_id and msiv.organization_id=wri.organization_id ) ) 24 UOM LOV custom varchar2 select miuv.uom_code value, miuv.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_item_uoms_view miuv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.organization_id=miuv.organization_id and msiv.inventory_item_id=miuv.inventory_item_id order by value select msiv.primary_uom_code from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly Y 25 QUANTITY Number number Y 26 FINAL_COMPLETION LOV custom varchar2 select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and :$column$.transaction_type in (select mtt.transaction_type_name from mtl_transaction_types mtt where mtt.transaction_action_id=31 and mtt.transaction_source_type_id=5) order by flvv.lookup_code desc Blank takes the default of the Completion Transactions form: No, or the computed value where the WIP parameters compute final completions. 27 LOT_NUMBER LOV custom varchar2 Y select mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.assembly and msiv.lot_control_code=2 and msiv.inventory_item_id=mln.inventory_item_id and msiv.organization_id=mln.organization_id order by value select wdj.lot_number from mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj, mtl_system_items_b msib where mp.organization_code=:$column$.organization_code and mp.organization_id=we.organization_id and we.wip_entity_name=:$column$.job and we.wip_entity_id=wdj.wip_entity_id and wdj.primary_item_id=msib.inventory_item_id and wdj.organization_id=msib.organization_id and msib.lot_control_code=2 A completion creates a lot that does not exist yet. 28 LOT_EXPIRATION_DATE Date date Expiration date of a new lot; blank derives it from the item shelf life. 29 FROM_SERIAL_NUMBER Char varchar2 30 TO_SERIAL_NUMBER Char varchar2 Blank completes or returns the From Serial Number only. 31 REASON LOV varchar2 INV Transaction Reason (active) F9578FED2EBB45508370CFB87C1140A8 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 32 REFERENCE Char varchar2 240 33 COMPLETION_ATTRIBUTE_CATEGORY LOV custom varchar2 select fdfcv.descriptive_flex_context_code id, fdfcv.descriptive_flex_context_name value, fdfcv.description from fnd_descr_flex_contexts_vl fdfcv where fdfcv.application_id=401 and fdfcv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfcv.enabled_flag='Y' and fdfcv.descriptive_flex_context_code<>'Global Data Elements' order by value 34 COMPLETION_ATTRIBUTE1 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE1', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE1' 35 COMPLETION_ATTRIBUTE2 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE2', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE2' 36 COMPLETION_ATTRIBUTE3 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE3', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE3' 37 COMPLETION_ATTRIBUTE4 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE4', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE4' 38 COMPLETION_ATTRIBUTE5 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE5', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE5' 39 COMPLETION_ATTRIBUTE6 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE6', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE6' 40 COMPLETION_ATTRIBUTE7 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE7', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE7' 41 COMPLETION_ATTRIBUTE8 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE8', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE8' 42 COMPLETION_ATTRIBUTE9 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE9', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE9' 43 COMPLETION_ATTRIBUTE10 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE10', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE10' 44 COMPLETION_ATTRIBUTE11 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE11', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE11' 45 COMPLETION_ATTRIBUTE12 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE12', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE12' 46 COMPLETION_ATTRIBUTE13 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE13', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE13' 47 COMPLETION_ATTRIBUTE14 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE14', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE14' 48 COMPLETION_ATTRIBUTE15 LOV custom varchar2 select substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1) value, substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1,lengthb(lov.column_value)) description from table( xxen_util.dff_attribute_lov( p_application_id=>401, p_descriptive_flexfield_name=>'MTL_MATERIAL_TRANSACTIONS', p_context_name=>:$column$.completion_attribute_category, p_column_name=>'ATTRIBUTE15', p_attribute1=>:$column$.completion_attribute1, p_attribute2=>:$column$.completion_attribute2, p_attribute3=>:$column$.completion_attribute3, p_attribute4=>:$column$.completion_attribute4, p_attribute5=>:$column$.completion_attribute5, p_attribute6=>:$column$.completion_attribute6, p_attribute7=>:$column$.completion_attribute7, p_attribute8=>:$column$.completion_attribute8, p_attribute9=>:$column$.completion_attribute9, p_attribute10=>:$column$.completion_attribute10, p_attribute11=>:$column$.completion_attribute11, p_attribute12=>:$column$.completion_attribute12, p_attribute13=>:$column$.completion_attribute13, p_attribute14=>:$column$.completion_attribute14, p_attribute15=>:$column$.completion_attribute15 ) ) lov order by value select distinct '('||decode(fdfc.descriptive_flex_context_code,'Global Data Elements','Global',fdfc.descriptive_flex_context_code)||') '||fdfcuv.form_left_prompt from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv where fdfv.application_id=401 and fdfv.descriptive_flexfield_name='MTL_MATERIAL_TRANSACTIONS' and fdfv.application_id=fdfc.application_id and fdfv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfc.enabled_flag='Y' and fdfc.application_id=fdfcuv.application_id and fdfc.descriptive_flexfield_name=fdfcuv.descriptive_flexfield_name and fdfc.descriptive_flex_context_code=fdfcuv.descriptive_flex_context_code and fdfcuv.enabled_flag='Y' and fdfcuv.application_column_name='ATTRIBUTE15' 49 UPLOAD_ROW Number number Y U 1 p_transaction_id in Number Y TRANSACTION_ID U 2 p_organization_id in Number ORGANIZATION_CODE U 3 p_line_id in Number Y LINE U 4 p_assembly_id in Number Y ASSEMBLY U 5 p_wip_entity_id in Number Y JOB U 6 p_transaction_type_id in Number Y TRANSACTION_TYPE U 7 p_transaction_date in Date Y TRANSACTION_DATE U 8 p_overcompletion in Char Y OVERCOMPLETION U 9 p_revision in Char Y REVISION U 10 p_subinventory in Char Y SUBINVENTORY U 11 p_locator_id in Number Y LOCATOR U 12 p_transaction_uom in Char Y UOM U 13 p_transaction_quantity in Number Y QUANTITY U 14 p_final_completion in Char Y FINAL_COMPLETION U 15 p_lot_number in Char Y LOT_NUMBER U 16 p_lot_expiration_date in Date Y LOT_EXPIRATION_DATE U 17 p_fm_serial_number in Char Y FROM_SERIAL_NUMBER U 18 p_to_serial_number in Char Y TO_SERIAL_NUMBER U 19 p_reason_id in Number Y REASON U 20 p_reference in Char Y REFERENCE U 21 p_attribute_category in Char Y COMPLETION_ATTRIBUTE_CATEGORY U 22 p_attribute1 in Char Y COMPLETION_ATTRIBUTE1 U 23 p_attribute2 in Char Y COMPLETION_ATTRIBUTE2 U 24 p_attribute3 in Char Y COMPLETION_ATTRIBUTE3 U 25 p_attribute4 in Char Y COMPLETION_ATTRIBUTE4 U 26 p_attribute5 in Char Y COMPLETION_ATTRIBUTE5 U 27 p_attribute6 in Char Y COMPLETION_ATTRIBUTE6 U 28 p_attribute7 in Char Y COMPLETION_ATTRIBUTE7 U 29 p_attribute8 in Char Y COMPLETION_ATTRIBUTE8 U 30 p_attribute9 in Char Y COMPLETION_ATTRIBUTE9 U 31 p_attribute10 in Char Y COMPLETION_ATTRIBUTE10 U 32 p_attribute11 in Char Y COMPLETION_ATTRIBUTE11 U 33 p_attribute12 in Char Y COMPLETION_ATTRIBUTE12 U 34 p_attribute13 in Char Y COMPLETION_ATTRIBUTE13 U 35 p_attribute14 in Char Y COMPLETION_ATTRIBUTE14 U 36 p_attribute15 in Char Y COMPLETION_ATTRIBUTE15 U 37 p_transaction_id_out out Number TRANSACTION_ID U 38 status_ out Char STATUS_CODE_ U 39 message_ out Char MESSAGE_ Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.transaction_id, nvl(mp.organization_code,xu.organization_code) organization_code, nvl(wl.line_code,xu.line) line, nvl(wl.description,xu.line_description) line_description, nvl(msiv.concatenated_segments,xu.assembly) assembly, nvl(msiv.description,xu.assembly_description) assembly_description, nvl(msiv.primary_uom_code,xu.primary_uom) primary_uom, nvl(case when mmt.repetitive_line_id is null then we.wip_entity_name end,xu.job) job, nvl(case when mmt.repetitive_line_id is null then we.description end,xu.job_description) job_description, nvl(wdj.bom_revision,xu.bill_revision) bill_revision, nvl(mtt.transaction_type_name,xu.transaction_type) transaction_type, nvl(mmt.transaction_date,xu.transaction_date) transaction_date, xxen_util.yes(case when mmt.overcompletion_transaction_qty>0 then 'Y' end) overcompletion, nvl(wdj.start_quantity,xu.job_quantity) job_quantity, nvl(wdj.quantity_completed,xu.job_complete) job_complete, xu.available, mmt.revision, nvl(mmt.subinventory_code,xu.subinventory) subinventory, milk.concatenated_segments locator, nvl(mmt.transaction_uom,xu.uom) uom, nvl(abs(mmt.transaction_quantity),xu.quantity) quantity, xxen_util.meaning(mmt.final_completion_flag,'YES_NO',0) final_completion, mtln.lot_number, (select mln.expiration_date from mtl_lot_numbers mln where mtln.inventory_item_id=mln.inventory_item_id and mtln.organization_id=mln.organization_id and mtln.lot_number=mln.lot_number) lot_expiration_date, (select min(mut.serial_number) from mtl_unit_transactions mut where mut.transaction_id in (mmt.transaction_id,mtln.serial_transaction_id)) from_serial_number, (select max(mut.serial_number) from mtl_unit_transactions mut where mut.transaction_id in (mmt.transaction_id,mtln.serial_transaction_id)) to_serial_number, mtr.reason_name reason, mmt.transaction_reference reference, xxen_util.display_flexfield_context(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category) completion_attribute_category, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE1',null,mmt.attribute1) completion_attribute1, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE2',null,mmt.attribute2) completion_attribute2, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE3',null,mmt.attribute3) completion_attribute3, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE4',null,mmt.attribute4) completion_attribute4, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE5',null,mmt.attribute5) completion_attribute5, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE6',null,mmt.attribute6) completion_attribute6, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE7',null,mmt.attribute7) completion_attribute7, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE8',null,mmt.attribute8) completion_attribute8, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE9',null,mmt.attribute9) completion_attribute9, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE10',null,mmt.attribute10) completion_attribute10, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE11',null,mmt.attribute11) completion_attribute11, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE12',null,mmt.attribute12) completion_attribute12, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE13',null,mmt.attribute13) completion_attribute13, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE14',null,mmt.attribute14) completion_attribute14, xxen_util.display_flexfield_value(401,'MTL_MATERIAL_TRANSACTIONS',mmt.attribute_category,'ATTRIBUTE15',null,mmt.attribute15) completion_attribute15, xu.upload_row from xxen_wip_completion_upl_4916_u xu, mtl_material_transactions mmt, mtl_parameters mp, mtl_transaction_types mtt, wip_entities we, wip_discrete_jobs wdj, wip_lines wl, mtl_system_items_vl msiv, mtl_item_locations_kfv milk, mtl_transaction_reasons mtr, mtl_transaction_lot_numbers mtln where xu.status_code_=xxen_upload.status_success and xu.transaction_id=mmt.transaction_id(+) and mmt.organization_id=mp.organization_id(+) and mmt.transaction_type_id=mtt.transaction_type_id(+) and mmt.transaction_source_id=we.wip_entity_id(+) and mmt.transaction_source_id=wdj.wip_entity_id(+) and mmt.repetitive_line_id=wl.line_id(+) and mmt.inventory_item_id=msiv.inventory_item_id(+) and mmt.organization_id=msiv.organization_id(+) and mmt.locator_id=milk.inventory_location_id(+) and mmt.organization_id=milk.organization_id(+) and mmt.reason_id=mtr.reason_id(+) and mmt.transaction_id=mtln.transaction_id(+)