ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5E7B2C91A0D34F8EB61C4A2D9F03E7B5Y select haouv.name operating_unit, obha.order_number agreement_number, obha.sales_document_name agreement_name, ottt.name agreement_type, xxen_util.meaning(obha.flow_status_code,'FLOW_STATUS',660) agreement_status, hp.party_name customer, hca.account_number customer_number, obha.cust_po_number customer_po, jrrev.resource_name salesperson, obha.transactional_curr_code currency, (select qlhv.name from qp_list_headers_vl qlhv where obha.price_list_id=qlhv.list_header_id) price_list, (select qlhv.name from qp_list_headers_vl qlhv where obhe.new_modifier_list_id=qlhv.list_header_id) modifier_list, obha.version_number agreement_version, obhe.start_date_active agreement_start_date, obhe.end_date_active agreement_end_date, xxen_util.yes(obhe.on_hold_flag) on_hold, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.blanket_min_amount end agreement_min_amount, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.blanket_max_amount end agreement_max_amount, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.released_amount end agreement_released_amount, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.fulfilled_amount end agreement_fulfilled_amount, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.returned_amount end agreement_returned_amount, case when row_number() over (partition by obha.header_id order by obla.line_number)=1 then obhe.blanket_max_amount+nvl(obhe.returned_amount,0)-nvl(obhe.released_amount,0) end agreement_remaining_amount, obla.line_number, xxen_util.meaning(obla.item_identifier_type,'ITEM_IDENTIFIER_TYPE',660) item_identifier_type, nvl(msiv.concatenated_segments,obla.ordered_item) item, msiv.description item_description, obla.order_quantity_uom uom, obla.unit_list_price list_price, oble.start_date_active line_start_date, oble.end_date_active line_end_date, oble.blanket_min_quantity line_min_quantity, oble.blanket_max_quantity line_max_quantity, oble.released_quantity line_released_quantity, oble.fulfilled_quantity line_fulfilled_quantity, oble.returned_quantity line_returned_quantity, oble.blanket_line_min_amount line_min_amount, oble.blanket_line_max_amount line_max_amount, oble.released_amount line_released_amount, oble.fulfilled_amount line_fulfilled_amount, oble.returned_amount line_returned_amount, oble.blanket_max_quantity+nvl(oble.returned_quantity,0)-nvl(oble.released_quantity,0) line_remaining_quantity, oble.blanket_line_max_amount+nvl(oble.returned_amount,0)-nvl(oble.released_amount,0) line_remaining_amount, round(100*nvl(oble.released_quantity,0)/nullif(oble.blanket_max_quantity,0),2) line_released_quantity_percent, round(100*nvl(oble.released_amount,0)/nullif(oble.blanket_line_max_amount,0),2) line_released_amount_percent, oble.min_release_quantity, oble.max_release_quantity, oble.min_release_amount, oble.max_release_amount, xxen_util.yes(oble.override_blanket_controls_flag) override_blanket_controls, (select count(distinct oola.header_id) from oe_order_lines_all oola where obha.order_number=oola.blanket_number and obla.line_number=oola.blanket_line_number and oola.cancelled_flag='N') releases, &dff_columns xxen_util.user_name(obha.created_by) created_by, xxen_util.client_time(obha.creation_date) creation_date, xxen_util.user_name(obla.last_updated_by) line_last_updated_by, xxen_util.client_time(obla.last_update_date) line_last_update_date, obha.header_id, obla.line_id from hr_all_organization_units_vl haouv, oe_blanket_headers_all obha, oe_blanket_headers_ext obhe, oe_blanket_lines_all obla, oe_blanket_lines_ext oble, oe_transaction_types_tl ottt, hz_cust_accounts hca, hz_parties hp, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, mtl_system_items_vl msiv where 1=1 and obha.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 haouv.organization_id=obha.org_id and obha.order_number=obhe.order_number and obha.header_id=obla.header_id and obla.line_id=oble.line_id and obha.order_type_id=ottt.transaction_type_id(+) and ottt.language(+)=userenv('lang') and obha.sold_to_org_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and obha.salesrep_id=jrs.salesrep_id(+) and obha.org_id=jrs.org_id(+) and jrs.resource_id=jrrev.resource_id(+) and decode(obla.item_identifier_type,'CAT',to_number(null),obla.inventory_item_id)=msiv.inventory_item_id(+) and nvl(obla.ship_from_org_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',obla.org_id))=msiv.organization_id(+) order by haouv.name, obha.order_number, obla.line_number New report US ONT Blanket Sales Agreements Blanket sales agreements, one row per agreement line, with the minimum and maximum quantity and amount, and the quantity and amount released, fulfilled and returned by the sales order lines that reference the agreement. Remaining is the maximum plus the returned less the released quantity or amount, the limit that Order Management validates new releases against. Agreement level amounts are shown on the first line of each agreement only, so that they can be summed. Releases counts the sales orders with a not cancelled line released against the agreement line. Use ONT Orders and Lines with the Sales Agreement Number parameter for the release order lines. Enginatics &dff_columns 1=1 1 10 1=1 haouv.name=: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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 2 20 1=1 obha.order_number=:agreement_number LOV custom Y select obha.order_number value, obha.sales_document_name||' '||hca.account_name||' ('||xxen_util.meaning(obha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_blanket_headers_all obha, hz_cust_accounts hca where (:$flex$.operating_unit is null or obha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and obha.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 obha.sold_to_org_id=hca.cust_account_id(+) order by obha.order_number desc US Agreement Number 3 30 1=1 ottt.name=:agreement_type LOV custom Y select ottt.name value, null description from oe_transaction_types_all otta, oe_transaction_types_tl ottt where (:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and otta.sales_document_type_code='B' and otta.transaction_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ottt.name US Agreement Type 4 40 1=1 obha.flow_status_code=xxen_util.lookup_code(:status,'FLOW_STATUS',660) LOV custom Y select xxen_util.meaning(x.flow_status_code,'FLOW_STATUS',660) value, null description from (select distinct obha.flow_status_code from oe_blanket_headers_all obha) x order by 1 US Status 5 50 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer Name 6 60 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Account Number 7 70 1=1 jrrev.resource_name=:salesperson LOV JTF Salesperson (active) B16963CD8D7755B1E0530100007F44CB Y select distinct jrrev.resource_name value, jrrev.source_city||nvl2(jrrev.source_country,', '||jrrev.source_country,null) description from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where (:$flex$.operating_unit is null or jrs.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between jrs.start_date_active and nvl(jrs.end_date_active,sysdate) and sysdate between jrrev.start_date_active and nvl(jrrev.end_date_active,sysdate) and jrs.resource_id=jrrev.resource_id order by jrrev.resource_name US Salesperson 8 80 1=1 msiv.concatenated_segments like :item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y 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 Item 9 90 1=1 nvl(oble.end_date_active,obhe.end_date_active)>=:end_date_from Date US End Date From Line end date, or the agreement end date for lines without one 10 100 1=1 nvl(oble.end_date_active,obhe.end_date_active)<:end_date_to+1 Date last_day(:$flex$.end_date_from) US End Date To Line end date, or the agreement end date for lines without one 11 110 1=1 nvl(oble.end_date_active,obhe.end_date_active)>=trunc(sysdate) and nvl(oble.end_date_active,obhe.end_date_active)<trunc(sysdate)+:expiring_within_days+1 Number US Expiring Within Days Lines whose end date, or the agreement end date, falls between today and the given number of days from today 12 120 1=1 obha.open_flag='Y' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Only 13 130 &dff_columns select xxen_util.dff_columns(p_table_name=>'oe_blanket_headers_all',p_table_alias=>'obha',p_column_name_prefix=>'Agreement: ',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'oe_blanket_lines_all',p_table_alias=>'obla',p_column_name_prefix=>'Line: ',p_display_mode=>:dff_display) sql_text from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D Y select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 6D8DE2F5ABB05784928BBED0ED23148D Expiring Sales Agreements Active blanket sales agreement lines that end within the next 90 days, with the quantity and amount still remaining. ENGINATICS 9 AGREEMENT_END_DATE 1 AGREEMENT_FULFILLED_AMOUNT AGREEMENT_MAX_AMOUNT AGREEMENT_MIN_AMOUNT 3 AGREEMENT_NAME 2 AGREEMENT_NUMBER 3 AGREEMENT_RELEASED_AMOUNT AGREEMENT_REMAINING_AMOUNT AGREEMENT_RETURNED_AMOUNT AGREEMENT_START_DATE 4 AGREEMENT_STATUS AGREEMENT_TYPE AGREEMENT_VERSION CREATED_BY CREATION_DATE 8 CURRENCY 5 CUSTOMER 6 CUSTOMER_NUMBER CUSTOMER_PO HEADER_ID 11 ITEM 12 ITEM_DESCRIPTION ITEM_IDENTIFIER_TYPE 13 LINE_END_DATE 2 LINE_FULFILLED_AMOUNT LINE_FULFILLED_QUANTITY LINE_ID LINE_LAST_UPDATED_BY LINE_LAST_UPDATE_DATE 17 LINE_MAX_AMOUNT 14 LINE_MAX_QUANTITY LINE_MIN_AMOUNT LINE_MIN_QUANTITY 10 LINE_NUMBER 4 18 LINE_RELEASED_AMOUNT LINE_RELEASED_AMOUNT_PERCENT 15 LINE_RELEASED_QUANTITY LINE_RELEASED_QUANTITY_PERCENT 19 LINE_REMAINING_AMOUNT 16 LINE_REMAINING_QUANTITY LINE_RETURNED_AMOUNT LINE_RETURNED_QUANTITY LINE_START_DATE LIST_PRICE MAX_RELEASE_AMOUNT MAX_RELEASE_QUANTITY MIN_RELEASE_AMOUNT MIN_RELEASE_QUANTITY MODIFIER_LIST ON_HOLD 1 OPERATING_UNIT OVERRIDE_BLANKET_CONTROLS PRICE_LIST 20 RELEASES 7 SALESPERSON UOM Expiring Within Days 90 Status Active S Site FF2A8D86C07C5C6F92B62D7FB3A512CD Sales Agreement Consumption Released, fulfilled and remaining quantity and amount per active blanket sales agreement line against its maximum. ENGINATICS 7 AGREEMENT_END_DATE AGREEMENT_FULFILLED_AMOUNT AGREEMENT_MAX_AMOUNT AGREEMENT_MIN_AMOUNT 3 AGREEMENT_NAME 2 AGREEMENT_NUMBER 2 AGREEMENT_RELEASED_AMOUNT AGREEMENT_REMAINING_AMOUNT AGREEMENT_RETURNED_AMOUNT 6 AGREEMENT_START_DATE AGREEMENT_STATUS AGREEMENT_TYPE AGREEMENT_VERSION CREATED_BY CREATION_DATE 5 CURRENCY 4 CUSTOMER CUSTOMER_NUMBER CUSTOMER_PO HEADER_ID 9 ITEM 10 ITEM_DESCRIPTION ITEM_IDENTIFIER_TYPE LINE_END_DATE 18 LINE_FULFILLED_AMOUNT LINE_FULFILLED_QUANTITY LINE_ID LINE_LAST_UPDATED_BY LINE_LAST_UPDATE_DATE 16 LINE_MAX_AMOUNT 12 LINE_MAX_QUANTITY LINE_MIN_AMOUNT LINE_MIN_QUANTITY 8 LINE_NUMBER 3 17 LINE_RELEASED_AMOUNT 20 LINE_RELEASED_AMOUNT_PERCENT 13 LINE_RELEASED_QUANTITY 15 LINE_RELEASED_QUANTITY_PERCENT 19 LINE_REMAINING_AMOUNT 14 LINE_REMAINING_QUANTITY LINE_RETURNED_AMOUNT LINE_RETURNED_QUANTITY LINE_START_DATE LIST_PRICE MAX_RELEASE_AMOUNT MAX_RELEASE_QUANTITY MIN_RELEASE_AMOUNT MIN_RELEASE_QUANTITY MODIFIER_LIST ON_HOLD 1 OPERATING_UNIT 1 OVERRIDE_BLANKET_CONTROLS PRICE_LIST 21 RELEASES SALESPERSON 11 UOM Status Active S Site