ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F48A560B34937C4DE0530100007FFC44Y select null action_, null status_, null message_, null request_id_, null modified_columns_, rowidtochar(prha.rowid) header_row_id, rowidtochar(prla.line_row_id) line_row_id, to_char(null) delete_this_line, to_char(null) pre_delete_template_lines, :p_upload_mode upload_mode, to_char(null) publish_template_to_icx, -- header haouv.name operating_unit, prha.express_name template_name, prha.description template_description, podt.type_name type, prha.inactive_date, xxen_util.meaning(prha.reserve_po_number,'RESERVE PO NUM',201) reserve_po_number, -- lines prla.sequence_num line_number, plt.line_type, nvl2(prla.item_id,msiv.concatenated_segments,null) item, prla.item_revision, mck.concatenated_segments category, prla.item_description line_description, muomv.unit_of_measure_tl uom, prla.suggested_quantity, prla.unit_price, prla.amount, xxen_util.meaning(prla.source_type_code,'REQUISITION SOURCE TYPE',201) source_type, po_inq_sv.get_person_name(prla.suggested_buyer_id) buyer, asu.vendor_name supplier, assa.vendor_site_code supplier_site, decode(asco.last_name,null,null, asco.last_name||', '|| asco.first_name) supplier_contact, prla.suggested_vendor_product_code supplier_item, ood.organization_code source_organization, prla.source_subinventory, decode(prla.negotiated_by_preparer_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null) negotiated, decode(prla.rfq_required_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null) rfq_required, -- xxen_util.user_name(prha.last_updated_by) template_last_updated_by, prha.last_update_date template_last_updated_on, xxen_util.user_name(prla.last_updated_by) line_last_updated_by, prla.last_update_date line_last_updated_on, xxen_util.user_name(prha.created_by) template_created_by, prha.creation_date template_created_on, xxen_util.user_name(prla.created_by) line_created_by, prla.creation_date line_created_on, -- to_number(null) upload_row from po_reqexpress_headers_all prha, (select fspa.inventory_organization_id, prla.rowid line_row_id, prla.* from po_reqexpress_lines_all prla, financials_system_params_all fspa where prla.org_id = fspa.org_id ) prla, hr_all_organization_units_vl haouv, po_line_types plt, po_document_types_all_tl podt, mtl_system_items_vl msiv, mtl_categories_kfv mck, mtl_units_of_measure_vl muomv, org_organization_definitions ood, ap_suppliers asu, ap_supplier_sites_all assa, ap_supplier_contacts asco where 1=1 and nvl(:p_publish_template_to_icx,'N')=nvl(:p_publish_template_to_icx,'N') and nvl(:p_pre_delete_template_lines,'N')=nvl(:p_pre_delete_template_lines,'N') and prha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and prha.org_id = haouv.organization_id and prha.type_lookup_code = podt.document_subtype and prha.org_id = podt.org_id and podt.document_type_code = 'REQUISITION' and podt.language = userenv ('LANG') and prha.express_name = prla.express_name (+) and prha.org_id = prla.org_id (+) and prla.line_type_id = plt.line_type_id (+) and prla.item_id = msiv.inventory_item_id (+) and prla.inventory_organization_id = msiv.organization_id (+) and prla.category_id = mck.category_id (+) and prla.unit_meas_lookup_code = muomv.unit_of_measure (+) and prla.source_organization_id = ood.organization_id (+) and prla.suggested_vendor_id = asu.vendor_id (+) and prla.suggested_vendor_site_id = assa.vendor_site_id (+) and prla.suggested_vendor_contact_id = asco.vendor_contact_id (+) and prla.suggested_vendor_site_id = asco.vendor_site_id (+) and not exists -- exclude templates copied from a Blanket PO (select null from po_reqexpress_lines_all prla2 where prla2.org_id = prha.org_id and prla2.express_name = prha.express_name and prla2.po_line_id is not null ) Convert result SQL from the legacy lexical style to the standard Error/Success/Order By style; add the hidden upload_row column; order the result by upload_row (the order rows were uploaded). Also removed a redundant column-level filter-before-display flag on the shared Buyer LOV. U A xxen_po_upload.create_update_req_template xxen_po_upload.post_req_template_process US PO Requisition Template Upload PO Requisition Template Upload ================================ The purpose of the PO Requisition Template Upload is to allow for: - the creation of new Requisition Templates (Create Mode). - the update of existing Requisition Templates (Update Mode). Update includes the update to existing template and template lines, the creation of new template lines against an existing template, and the deletion of template lines from an existing template. As far as feasible, the upload provides the user with the same functionality available in the ‘Requisition Template’ Form. (Purchasing: + Setup – Purchasing – Requisition Templates) Upload Mode ============ The upload mode determines the actions the user is allowed to perform in the upload. Create - the user may upload new Requisition Templates only. Update - the user may update existing Requisition Templates only. This includes adding new and deleting existing template lines from the template. Create and Update - the user can create new Requisition Templates and update existing Requisition Templates. Parameters ========== Upload Mode (Required=Yes Default=Create) - See above. The following optional parameters are only applicable in the 'Create and Update' and 'Update' modes, and can be used to restrict the template data downloaded for update: - Operating Unit - the operating unit(s) the templates belong to. - Template Type - the template type (Purchase Requisition, Internal Requisition). - Template Name - the template name(s) to be downloaded. - Template Name Contains - the user can restrict the download of existing templates where the Template Name contains the specified text (case insensitive). Enginatics Upload 1=1 :p_pre_delete_template_lines :p_publish_template_to_icx 1 -120 :p_pre_delete_template_lines LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Pre Delete Template Lines 2 -110 :p_publish_template_to_icx LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Publish Template to Catalog 3 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 Y US Upload Mode 4 20 1=1 haouv.name=:p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 5 30 1=1 prha.type_lookup_code=:p_template_type LOV PO Requisition Type E8261147D2435465E0530100007F7AB4 Y select podt.document_subtype id, podt.type_name value, podt.type_name description from po_document_types_all_tl podt, po_document_types_all_b podb, hr_all_organization_units_vl haouv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and podb.org_id=haouv.organization_id and podb.org_id=podt.org_id and podb.document_type_code='REQUISITION' and podb.document_type_code=podt.document_type_code and podb.document_subtype=podt.document_subtype and podt.language=userenv ('LANG') US Template Type 6 40 1=1 prha.express_name = :p_template_name LOV PO Requisition Templates F3BC24FD74AE50BEE0530100007F16E7 Y select prha.express_name value, haouv.name || ' - ' || podt.type_name || ' - ' || prha.description description from po_reqexpress_headers_all prha, po_document_types_all_b podb, po_document_types_all_tl podt, hr_all_organization_units_vl haouv where podt.document_subtype = prha.type_lookup_code and podt.org_id=prha.org_id and podt.language=userenv ('LANG') and podb.document_type_code='REQUISITION' and podb.org_id=podt.org_id and podb.document_type_code=podt.document_type_code and podb.document_subtype=podt.document_subtype and haouv.organization_id=prha.org_id and -- ( haouv.name = :$flex$.Operating_Unit or (:$flex$.Operating_Unit is null and prha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) ) and ( :$flex$.template_type is null or xxen_util.contains(:$flex$.template_type,podt.document_subtype)='Y' ) order by prha.express_name US Template Name 7 50 1=1 lower(prha.express_name) like '%' || lower(:p_template_name_like) || '%' Char US Template Name Contains 4DCB005E7887A7EFF6EE088F76741F4E Default SYSADMIN 17 BUYER 11 CATEGORY Y 26 DELETE_THIS_LINE 5 INACTIVE_DATE 9 ITEM 10 ITEM_REVISION 12 LINE_DESCRIPTION Y 7 LINE_NUMBER Y 8 LINE_TYPE Y 24 NEGOTIATED 1 OPERATING_UNIT Y 6 RESERVE_PO_NUMBER 25 RFQ_REQUIRED 22 SOURCE_ORGANIZATION 23 SOURCE_SUBINVENTORY 16 SOURCE_TYPE Y 14 SUGGESTED_QUANTITY 18 SUPPLIER 20 SUPPLIER_CONTACT 21 SUPPLIER_ITEM 19 SUPPLIER_SITE 3 TEMPLATE_DESCRIPTION 2 TEMPLATE_NAME Y 4 TYPE Y 15 UNIT_PRICE Y 13 UOM Y S Site 4DCB005E7887A7EFF6EE088F76741F4E 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 HEADER_ROW_ID Char varchar2 18 Y 7 LINE_ROW_ID Char varchar2 18 Y 8 DELETE_THIS_LINE LOV custom varchar2 Y select meaning value, 'Delete this Template Line' description from fnd_lookups where lookup_type='YES_NO' and lookup_code = 'Y' and :$column$.line_row_id is not null order by value 9 PRE_DELETE_TEMPLATE_LINES Char varchar2 :$flex$.Pre_Delete_Template_Lines Y 10 UPLOAD_MODE Char varchar2 2000 :$flex$.Upload_Mode Y 11 PUBLISH_TEMPLATE_TO_ICX Char varchar2 :$flex$.Publish_Template_to_Catalog Y 12 OPERATING_UNIT LOV varchar2 240 HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name Y Y 13 TEMPLATE_NAME Char varchar2 25 Y 14 TEMPLATE_DESCRIPTION Char varchar2 240 15 TYPE LOV varchar2 80 PO Requisition Type for upload F101F178C9E5597EE0530100007FE590 Y select podt.document_subtype id, podt.type_name value, podt.type_name description from po_document_types_all_tl podt, po_document_types_all_b podb, hr_all_organization_units_vl haouv where haouv.name = :$column$.operating_unit and podt.type_name=nvl(:$flex$.requisition_type,podt.type_name) and podb.org_id=haouv.organization_id and podb.org_id=podt.org_id and podb.document_type_code='REQUISITION' and podb.document_type_code=podt.document_type_code and podb.document_subtype=podt.document_subtype and podt.language=userenv ('LANG') order by podt.type_name Y Y 16 INACTIVE_DATE Date date 17 RESERVE_PO_NUMBER LOV custom varchar2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'RESERVE PO NUM' and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate) and nvl(flvv.end_date_active,sysdate) and rtrim(flvv.lookup_code) is not null order by flvv.meaning 18 LINE_NUMBER Number number Y 19 LINE_TYPE LOV custom varchar2 25 Y select pltvv.line_type_id id, pltvv.line_type value, pltvv.description || ' (' || pltvv.displayed_field || ')' description from po_line_types_val_v pltvv where nvl(pltvv.outside_operation_flag,'N') = 'N' and pltvv.purchase_basis <> 'TEMP LABOR' and ( ( pltvv.order_type_lookup_code = 'FIXED PRICE' and po_setup_s1.get_services_enabled_flag = 'Y' ) or ( pltvv.order_type_lookup_code <> 'FIXED PRICE' ) ) order by pltvv.line_type Y 20 ITEM LOV custom varchar2 40 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, financials_system_params_all fspa, hr_operating_units hou where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and msiv.bom_item_type not in (1,2) and (msiv.purchasing_enabled_flag='Y' or nvl(msiv.internal_order_enabled_flag,'N') = 'Y') and :$column$.line_type in (select pltvv.line_type from po_line_types_val_v pltvv where nvl(pltvv.outside_operation_flag,'N') = 'N' and pltvv.order_type_lookup_code in ('QUANTITY','RATE') ) order by value 21 ITEM_REVISION LOV custom varchar2 3 Y select mir.revision value, mir.revision description from hr_operating_units hou, financials_system_params_all fspa, mtl_system_items_vl msiv, mtl_item_revisions_b mir where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and mir.inventory_item_id=msiv.inventory_item_id and mir.organization_id=msiv.organization_id and msiv.concatenated_segments = :$column$.item order by value 22 CATEGORY LOV custom varchar2 163 Y select mcv.category_concat_segs value, mcv.category_concat_segs||'-'||mcv.description from mtl_categories_v mcv, mtl_category_sets mcs where mcv.structure_id=mcs.structure_id and mcs.category_set_id=(select mdcs.category_set_id from mtl_default_category_sets mdcs where mdcs.functional_area_id=2) and ( (:$column$.item is null and exists (select pltvv.category_id from po_line_types_val_v pltvv where pltvv.line_type = :$column$.line_type and nvl(pltvv.category_id,mcv.category_id) = mcv.category_id ) ) or mcv.category_id in (select mic.category_id from mtl_item_categories mic, mtl_system_items_vl msiv, financials_system_params_all fspa, hr_operating_units hou where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and mic.inventory_item_id=msiv.inventory_item_id and mic.organization_id=msiv.organization_id and mic.category_set_id=(select mdcs.category_set_id from mtl_default_category_sets mdcs where mdcs.functional_area_id=2) and msiv.concatenated_segments = :$column$.item ) ) order by value Y 23 LINE_DESCRIPTION LOV custom varchar2 240 select msiv.description value, msiv.concatenated_segments description from hr_operating_units hou, financials_system_params_all fspa, mtl_system_items_vl msiv where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and msiv.concatenated_segments = :$column$.item Y 24 UOM LOV custom varchar2 25 Y select muomv.unit_of_measure value, muomv.unit_of_measure_tl || ' (' || muomv.uom_class || ')' description from mtl_units_of_measure_vl muomv where ( ( :$column$.item is null and exists (select pltvv.unit_of_measure from po_line_types_val_v pltvv where pltvv.line_type = :$column$.line_type and nvl(pltvv.unit_of_measure,muomv.unit_of_measure) = muomv.unit_of_measure ) ) or muomv.unit_of_measure in (select msiv.primary_unit_of_measure from hr_operating_units hou, financials_system_params_all fspa, mtl_system_items_vl msiv where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and msiv.concatenated_segments = :$column$.item ) ) order by value Y 25 SUGGESTED_QUANTITY Number number 26 UNIT_PRICE LOV custom number select x.value, x.description from ( select msiv.list_price_per_unit value, msiv.list_price_per_unit description from mtl_system_items_vl msiv, financials_system_params_all fspa, hr_operating_units hou where hou.name=:$column$.operating_unit and fspa.org_id=hou.organization_id and msiv.organization_id=fspa.inventory_organization_id and msiv.concatenated_segments = :$column$.item and :$column$.line_type in (select pltvv.line_type from po_line_types_val_v pltvv where nvl(pltvv.outside_operation_flag,'N') = 'N' and pltvv.order_type_lookup_code in ('QUANTITY','RATE') ) union select nvl(pltvv.unit_price,1) value, nvl(pltvv.unit_price,1) description from po_line_types_val_v pltvv where :$column$.item is null and pltvv.line_type = :$column$.line_type and pltvv.order_type_lookup_code in ('AMOUNT') ) x Y 27 AMOUNT Number number 28 SOURCE_TYPE LOV custom varchar2 Y select plc.lookup_code id, plc.displayed_field value, plc.displayed_field description from po_lookup_codes plc where plc.lookup_type='REQUISITION SOURCE TYPE' and (:$column$.item is not null or plc.lookup_code = 'VENDOR') order by value Y 29 BUYER LOV varchar2 PO Buyer 8E2FF36EDF2F79D2E0530100007F1FF2 Y Y select 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)) and ppx.person_id in ( select paaf.person_id from per_all_assignments_f paaf where trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and (:$flex$.organization_code is null or paaf.business_group_id in (select ood.business_group_id from org_organization_definitions ood where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') ) ) order by ppx.full_name 30 SUPPLIER LOV varchar2 240 PO Requisition Vendor DD2EF3BCFE9045B3E0530100007F1BC2 Y select aps.vendor_id id, aps.vendor_name value, aps.vendor_name||'-'||aps.segment1 description from ap_suppliers aps where exists(select 1 from ap_supplier_sites_all assa,hr_all_organization_units_vl haouv where assa.org_id=haouv.organization_id and haouv.name=:$flex$.operating_unit and assa.vendor_id=aps.vendor_id and assa.purchasing_site_flag='Y') and :$column$.source_type = xxen_util.meaning('VENDOR','REQUISITION SOURCE TYPE',201) 31 SUPPLIER_SITE LOV varchar2 15 PO Requisition Vendor Sites DD2EF3BCFE9145B3E0530100007F1BC2 Y select assa.vendor_site_id id, assa.vendor_site_code value, assa.vendor_site_code description from ap_supplier_sites_all assa, ap_suppliers aps, hr_all_organization_units_vl haouv where assa.vendor_id=aps.vendor_id and aps.vendor_name=:$column$.supplier and assa.purchasing_site_flag='Y' and assa.org_id=haouv.organization_id and haouv.name=:$flex$.operating_unit order by value 32 SUPPLIER_CONTACT LOV varchar2 77 PO Requisition Vendor Contact DEDE5D48397F1326E0530100007F5A29 Y select apc.vendor_contact_id id, apc.last_name||', '||apc.first_name value, apc.last_name||', '||apc.first_name description from ap_supplier_contacts apc, ap_supplier_sites_all assa where apc.vendor_site_id=assa.vendor_site_id and assa.vendor_site_code=:$column$.supplier_site and rtrim(apc.last_name || apc.first_name) is not null order by value 33 SUPPLIER_ITEM Char varchar2 25 34 SOURCE_ORGANIZATION LOV varchar2 3 PO Requisition Source Organization E916F60CF0DF3AB6E0530100007F7147 Y select ood.organization_id id, ood.organization_code value, ood.organization_code||'-'||ood.organization_name description from org_organization_definitions ood where trunc(nvl(ood.disable_date, sysdate+1))>trunc(sysdate) and ood.inventory_enabled_flag='Y' and :$column$.source_type = xxen_util.meaning('INVENTORY','REQUISITION SOURCE TYPE',201) 35 SOURCE_SUBINVENTORY LOV varchar2 10 PO Requisition Source Subinventory EA95342AA01D35ACE0530100007FB4A2 Y select msi.secondary_inventory_name id, msi.secondary_inventory_name value, msi.secondary_inventory_name description from mtl_secondary_inventories msi, org_organization_definitions ood where msi.organization_id=ood.organization_id and ood.organization_code=:$column$.source_organization order by value 36 NEGOTIATED LOV custom varchar2 Y select meaning value, lookup_code description from fnd_lookups where lookup_type='YES_NO' and lookup_code = 'Y' order by value 37 RFQ_REQUIRED LOV custom varchar2 Y select meaning value, lookup_code description from fnd_lookups where lookup_type='YES_NO' and lookup_code = 'Y' and :$column$.source_type = xxen_util.meaning('VENDOR','REQUISITION SOURCE TYPE',201) order by value 38 TEMPLATE_LAST_UPDATED_BY Char varchar2 39 TEMPLATE_LAST_UPDATED_ON DateTime date 40 LINE_LAST_UPDATED_BY Char varchar2 41 LINE_LAST_UPDATED_ON DateTime date 42 TEMPLATE_CREATED_BY Char varchar2 43 TEMPLATE_CREATED_ON DateTime date 44 LINE_CREATED_BY Char varchar2 45 LINE_CREATED_ON DateTime date 46 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 p_delete_line_flag in Char DELETE_THIS_LINE U 3 p_operating_unit in Char OPERATING_UNIT U 4 p_template_name in Char TEMPLATE_NAME U 5 p_template_description in Char TEMPLATE_DESCRIPTION U 6 p_type in Char TYPE value U 7 p_inactive_date in Date INACTIVE_DATE U 8 p_reserve_po_number in Char RESERVE_PO_NUMBER U 9 p_sequence_num in Number LINE_NUMBER U 10 p_line_type in Char LINE_TYPE value U 11 p_item in Char ITEM U 12 p_item_revision in Char ITEM_REVISION U 13 p_category in Char CATEGORY U 14 p_line_description in Char LINE_DESCRIPTION U 15 p_uom in Char UOM U 16 p_suggested_quantity in Number SUGGESTED_QUANTITY U 17 p_unit_price in Number UNIT_PRICE U 18 p_amount in Number AMOUNT U 19 p_source_type in Char SOURCE_TYPE value U 20 p_buyer in Char BUYER U 21 p_supplier in Char SUPPLIER value U 22 p_supplier_site in Char SUPPLIER_SITE value U 23 p_supplier_contact in Char SUPPLIER_CONTACT value U 24 p_supplier_item in Char SUPPLIER_ITEM U 25 p_source_organization in Char SOURCE_ORGANIZATION value U 26 p_source_subinventory in Char SOURCE_SUBINVENTORY U 27 p_negotiated_flag in Char NEGOTIATED U 28 p_rfq_required_flag in Char RFQ_REQUIRED U 30 p_pre_delete_template_lines in Char Y PRE_DELETE_TEMPLATE_LINES U 31 p_upload_mode in Char UPLOAD_MODE U 32 p_header_row_id in/out Char HEADER_ROW_ID U 33 p_line_row_id in/out Char LINE_ROW_ID U 34 request_id_ out Number REQUEST_ID_ U 35 status_ out Char STATUS_CODE_ U 36 message_ out Char MESSAGE_ P 1 p_publish_template_to_icx in Char PUBLISH_TEMPLATE_TO_ICX P 2 request_id_ out Number REQUEST_ID_ P 3 status_ out Char STATUS_CODE_ P 4 message_ out Char MESSAGE_ Error SQL Order By upload_row, operating_unit, template_name, line_number Success SQL select xm.action_, xm.status_, xm.message_, xm.request_id_, xm.modified_columns_, xm.header_row_id, xm.line_row_id, xm.delete_this_line, xm.pre_delete_template_lines, xm.upload_mode, xm.publish_template_to_icx, -- header decode(xm.status_code_,xxen_upload.status_error,xm.operating_unit,nvl(haouv.name,xm.operating_unit)) operating_unit, decode(xm.status_code_,xxen_upload.status_error,xm.template_name,nvl(prha.express_name,xm.template_name)) template_name, decode(xm.status_code_,xxen_upload.status_error,xm.template_description,nvl(prha.description,xm.template_description)) template_description, decode(xm.status_code_,xxen_upload.status_error,xm.type,nvl(podt.type_name,xm.type)) type, decode(xm.status_code_,xxen_upload.status_error,xm.inactive_date,nvl(prha.inactive_date,xm.inactive_date)) inactive_date, decode(xm.status_code_,xxen_upload.status_error,xm.reserve_po_number,nvl(xxen_util.meaning(prha.reserve_po_number,'RESERVE PO NUM',201),xm.reserve_po_number)) reserve_po_number, -- lines decode(xm.status_code_,xxen_upload.status_error,to_number(xm.line_number),nvl(prla.sequence_num,to_number(xm.line_number))) line_number, decode(xm.status_code_,xxen_upload.status_error,xm.line_type,plt.line_type) line_type, decode(xm.status_code_,xxen_upload.status_error,xm.item,nvl2(prla.item_id,msiv.concatenated_segments,null)) item, decode(xm.status_code_,xxen_upload.status_error,xm.item_revision,prla.item_revision) item_revision, decode(xm.status_code_,xxen_upload.status_error,xm.category,mck.concatenated_segments) category, decode(xm.status_code_,xxen_upload.status_error,xm.line_description,prla.item_description) line_description, decode(xm.status_code_,xxen_upload.status_error,xm.uom,muomv.unit_of_measure_tl) uom, decode(xm.status_code_,xxen_upload.status_error,to_number(xm.suggested_quantity),prla.suggested_quantity) suggested_quantity, decode(xm.status_code_,xxen_upload.status_error,to_number(xm.unit_price),prla.unit_price) unit_price, decode(xm.status_code_,xxen_upload.status_error,to_number(xm.amount),prla.amount) amount, decode(xm.status_code_,xxen_upload.status_error,xm.source_type,xxen_util.meaning(prla.source_type_code,'REQUISITION SOURCE TYPE',201)) source_type, decode(xm.status_code_,xxen_upload.status_error,xm.buyer,po_inq_sv.get_person_name(prla.suggested_buyer_id)) buyer, decode(xm.status_code_,xxen_upload.status_error,xm.supplier,asu.vendor_name) supplier, decode(xm.status_code_,xxen_upload.status_error,xm.supplier_site,assa.vendor_site_code) supplier_site, decode(xm.status_code_,xxen_upload.status_error,xm.supplier_contact,decode(asco.last_name,null,null, asco.last_name||', '|| asco.first_name)) supplier_contact, decode(xm.status_code_,xxen_upload.status_error,xm.supplier_item,prla.suggested_vendor_product_code) supplier_item, decode(xm.status_code_,xxen_upload.status_error,xm.source_organization,ood.organization_code) source_organization, decode(xm.status_code_,xxen_upload.status_error,xm.source_subinventory,prla.source_subinventory) source_subinventory, decode(xm.status_code_,xxen_upload.status_error,xm.negotiated,decode(prla.negotiated_by_preparer_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null)) negotiated, decode(xm.status_code_,xxen_upload.status_error,xm.rfq_required,decode(prla.rfq_required_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null)) rfq_required, -- decode(xm.status_code_,xxen_upload.status_error,xm.template_last_updated_by,xxen_util.user_name(prha.last_updated_by)) template_last_updated_by, decode(xm.status_code_,xxen_upload.status_error,xm.template_last_updated_on,prha.last_update_date) template_last_updated_on, decode(xm.status_code_,xxen_upload.status_error,xm.line_last_updated_by,xxen_util.user_name(prla.last_updated_by)) line_last_updated_by, decode(xm.status_code_,xxen_upload.status_error,xm.line_last_updated_on,prla.last_update_date) line_last_updated_on, decode(xm.status_code_,xxen_upload.status_error,xm.template_created_by,xxen_util.user_name(prha.created_by)) template_created_by, decode(xm.status_code_,xxen_upload.status_error,xm.template_created_on,prha.creation_date) template_created_on, decode(xm.status_code_,xxen_upload.status_error,xm.line_created_by,xxen_util.user_name(prla.created_by)) line_created_by, decode(xm.status_code_,xxen_upload.status_error,xm.line_created_on,prla.creation_date) line_created_on, xm.upload_row from po_reqexpress_headers_all prha, (select fspa.inventory_organization_id, prla.rowid line_row_id, prla.* from po_reqexpress_lines_all prla, financials_system_params_all fspa where prla.org_id = fspa.org_id ) prla, hr_all_organization_units_vl haouv, po_line_types plt, po_document_types_all_tl podt, mtl_system_items_vl msiv, mtl_categories_kfv mck, mtl_units_of_measure_vl muomv, org_organization_definitions ood, ap_suppliers asu, ap_supplier_sites_all assa, ap_supplier_contacts asco, xxen_po_requisition_tem_3164_u xm where xm.status_code_=xxen_upload.status_success and chartorowid(xm.line_row_id) = prla.line_row_id (+) and prla.express_name = prha.express_name (+)and prla.org_id = prha.org_id (+) and prha.org_id = haouv.organization_id (+) and prla.line_type_id = plt.line_type_id (+) and prha.type_lookup_code = podt.document_subtype (+) and prha.org_id = podt.org_id (+) and podt.document_type_code (+) = 'REQUISITION' and podt.language (+) = userenv ('LANG') and prla.item_id = msiv.inventory_item_id (+) and prla.inventory_organization_id = msiv.organization_id (+) and prla.category_id = mck.category_id (+) and prla.unit_meas_lookup_code = muomv.unit_of_measure (+) and prla.source_organization_id = ood.organization_id (+) and prla.suggested_vendor_id = asu.vendor_id (+) and prla.suggested_vendor_site_id = assa.vendor_site_id (+) and prla.suggested_vendor_contact_id = asco.vendor_contact_id (+) and prla.suggested_vendor_site_id = asco.vendor_site_id (+)