ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C3F1A2B0D4E54C6A9B1E7F0A3D5C2B41Y select haouv.name operating_unit, ooha.order_number, ottt.name order_type, xxen_util.client_time(ooha.ordered_date) ordered_date, hp.party_name customer, hca.account_number customer_number, xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) order_status, ooha.transactional_curr_code currency, rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line, xxen_util.meaning(oola.line_category_code,'ORDER_CATEGORY',660) line_category, xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status, msiv.concatenated_segments item, msiv.description item_description, oola.ordered_quantity quantity, oola.order_quantity_uom uom, oola.unit_list_price list_price, oola.unit_selling_price, xxen_util.meaning(opa.list_line_type_code,'LIST_LINE_TYPE_CODE',661) adjustment_type, xxen_util.meaning(opa.modifier_level_code,'MODIFIER_LEVEL_CODE',661) modifier_level, qlhv.name modifier, xxen_util.meaning(qlhv.list_type_code,'LIST_TYPE_CODE',661) modifier_type, opa.list_line_no modifier_line, (select qpp.name from qp_pricing_phases qpp where opa.pricing_phase_id=qpp.pricing_phase_id) pricing_phase, coalesce(xxen_util.meaning(opa.charge_type_code,'FREIGHT_CHARGES_TYPE',661),xxen_util.meaning(opa.charge_type_code,'FREIGHT_COST_TYPE',665)) charge_type, xxen_util.meaning(opa.credit_or_charge_flag,'CREDIT_OR_CHARGE_FLAG',660) credit_or_charge, xxen_util.meaning(opa.arithmetic_operator,'ARITHMETIC_OPERATOR',661) operator, opa.operand, case when opa.line_id is not null then opa.adjusted_amount end unit_adjustment, case when opa.line_id is null then case when opa.list_line_type_code='FREIGHT_CHARGE' and opa.arithmetic_operator='LUMPSUM' then decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand end when opa.list_line_type_code='FREIGHT_CHARGE' then decode(opa.credit_or_charge_flag,'C',-1,1)*decode(opa.arithmetic_operator,'LUMPSUM',case when oola.ordered_quantity>0 then opa.operand end,oola.ordered_quantity*opa.adjusted_amount) else decode(oola.line_category_code,'RETURN',-1,1)*oola.ordered_quantity*opa.adjusted_amount end extended_amount, xxen_util.yes(opa.automatic_flag) automatic, xxen_util.yes(opa.update_allowed) override_allowed, xxen_util.yes(opa.updated_flag) overridden, xxen_util.yes(opa.applied_flag) applied, xxen_util.yes(opa.accrual_flag) accrual, xxen_util.yes(opa.invoiced_flag) invoiced, xxen_util.meaning(opa.change_reason_code,'CHANGE_CODE',660) change_reason, opa.change_reason_text change_comments, &dff_columns xxen_util.user_name(opa.created_by) created_by, xxen_util.client_time(opa.creation_date) creation_date, xxen_util.user_name(opa.last_updated_by) last_updated_by, xxen_util.client_time(opa.last_update_date) last_update_date, ooha.header_id, oola.line_id, opa.price_adjustment_id from hr_all_organization_units_vl haouv, oe_order_headers_all ooha, oe_price_adjustments opa, oe_order_lines_all oola, oe_transaction_types_tl ottt, hz_cust_accounts hca, hz_parties hp, mtl_system_items_vl msiv, qp_list_headers_vl qlhv where 1=1 and ooha.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 opa.list_line_type_code in (select flvv.lookup_code from fnd_lookup_values_vl flvv where flvv.lookup_type='LIST_LINE_TYPE_CODE' and flvv.view_application_id=661 and flvv.security_group_id=0) and haouv.organization_id=ooha.org_id and ooha.header_id=opa.header_id and opa.line_id=oola.line_id(+) and ooha.order_type_id=ottt.transaction_type_id(+) and ottt.language(+)=userenv('lang') and ooha.sold_to_org_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and oola.inventory_item_id=msiv.inventory_item_id(+) and oola.ship_from_org_id=msiv.organization_id(+) and opa.list_header_id=qlhv.list_header_id(+) order by haouv.name, ooha.order_number, oola.line_number nulls first, oola.shipment_number, oola.option_number, oola.component_number, oola.service_number, opa.pricing_group_sequence, opa.price_adjustment_id New report US ONT Price Adjustments and Charges Price adjustments applied to sales orders: discounts, surcharges, price breaks, promotional goods and freight and special charges, one row per adjustment of an order header or line. Extended Amount is the unit adjustment times the ordered quantity, a lumpsum charge once per line. Discounts and credits are negative, and line amounts of return lines carry the reverse sign, as in ONT Orders and Lines. Order level modifiers are stored once per order without a line. The line selling prices include their effect, but Oracle stores no amount for it, so Unit Adjustment and Extended Amount are blank on these rows, except for a lumpsum order level charge. Tax records and shipping cost records are not listed. 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 ooha.ordered_date>=:ordered_date_from Date US Ordered Date From 3 30 1=1 ooha.ordered_date<:ordered_date_to+1 Date last_day(:$flex$.ordered_date_from) US Ordered Date To 4 40 1=1 ooha.order_number=:order_number LOV ONT Order Number 8E2FF36EDEE779D2E0530100007F1FF2 Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and ooha.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 ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc US Order Number 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 ottt.name=:order_type LOV ONT Order Type 8E2FF36EDEE879D2E0530100007F1FF2 Y select ottt.name value, xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) 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.transaction_type_code='ORDER' and otta.transaction_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by decode(otta.order_category_code,'ORDER',1,'MIXED',2,3), ottt.name US Order Type 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 opa.list_line_type_code=xxen_util.lookup_code(:adjustment_type,'LIST_LINE_TYPE_CODE',661) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='LIST_LINE_TYPE_CODE' and flvv.view_application_id=661 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code<>'PLL' order by flvv.meaning US Adjustment Type 10 100 1=1 qlhv.name=:modifier LOV custom Y select qlhv.name value, xxen_util.meaning(qlhv.list_type_code,'LIST_TYPE_CODE',661)||': '||qlhv.description description from qp_list_headers_vl qlhv where qlhv.list_type_code in ('CHARGES','DEL','DLT','PRO','SLT') order by qlhv.name US Modifier 11 110 1=1 opa.charge_type_code in (select qcl.lookup_code from qp_charge_lookup qcl where qcl.meaning=:charge_type) LOV custom Y select distinct qcl.meaning value, null description from qp_charge_lookup qcl order by qcl.meaning US Charge Type 12 120 1=1 opa.change_reason_code=xxen_util.lookup_code(:change_reason,'CHANGE_CODE',660) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='CHANGE_CODE' and flvv.view_application_id=660 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Change Reason 13 130 1=1 (opa.automatic_flag='N' or opa.updated_flag='Y') LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Manual or Overridden Only Adjustments applied manually or with an overridden operand 14 140 1=1 opa.applied_flag='Y' 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 Y Y US Applied No lists the adjustments that the pricing engine returned but that are not applied to the order 15 1=1 nvl(opa.applied_flag,'N')='N' N US Applied 16 150 &dff_columns select xxen_util.dff_columns(p_table_name=>'oe_price_adjustments',p_table_alias=>'opa',p_column_name_prefix=>'Adjustment: ',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 6F78470BD30F518FA5589124018DFBF3 Discounts by Modifier Discounts, surcharges and price breaks per modifier and modifier line, with the extended amounts summed by modifier. ENGINATICS ACCRUAL 5 ADJUSTMENT_TYPE APPLIED AUTOMATIC CHANGE_COMMENTS CHANGE_REASON CHARGE_TYPE CREATED_BY CREATION_DATE CREDIT_OR_CHARGE 10 CURRENCY 9 CUSTOMER CUSTOMER_NUMBER 19 EXTENDED_AMOUNT HEADER_ID INVOICED 12 ITEM ITEM_DESCRIPTION LAST_UPDATED_BY LAST_UPDATE_DATE 11 LINE LINE_CATEGORY LINE_ID LINE_STATUS 14 LIST_PRICE 2 MODIFIER 2 6 MODIFIER_LEVEL 4 MODIFIER_LINE 3 3 MODIFIER_TYPE 17 OPERAND 1 OPERATING_UNIT 1 16 OPERATOR 8 ORDERED_DATE 7 ORDER_NUMBER 4 ORDER_STATUS ORDER_TYPE OVERRIDDEN OVERRIDE_ALLOWED PRICE_ADJUSTMENT_ID PRICING_PHASE 13 QUANTITY 18 UNIT_ADJUSTMENT 15 UNIT_SELLING_PRICE UOM CURRENCY COLUMN 1 EXTENDED_AMOUNT VALUE 1 SUM MODIFIER ROW 1 MODIFIER_LINE ROW 2 Adjustment Type <multiple_values>Discount;Surcharge;Price Break Header S Site CC869EAD89D85B48B8F512E29A2A39CB Freight and Special Charges Freight and special charges per order and charge type, with the charges summed by charge type and order. ENGINATICS ACCRUAL ADJUSTMENT_TYPE APPLIED 17 AUTOMATIC CHANGE_COMMENTS CHANGE_REASON 11 CHARGE_TYPE CREATED_BY CREATION_DATE 13 CREDIT_OR_CHARGE 6 CURRENCY 4 CUSTOMER 5 CUSTOMER_NUMBER 16 EXTENDED_AMOUNT HEADER_ID 19 INVOICED 8 ITEM ITEM_DESCRIPTION LAST_UPDATED_BY LAST_UPDATE_DATE 7 LINE 3 LINE_CATEGORY LINE_ID LINE_STATUS LIST_PRICE 12 MODIFIER MODIFIER_LEVEL MODIFIER_LINE MODIFIER_TYPE 15 OPERAND 1 OPERATING_UNIT 1 14 OPERATOR 3 ORDERED_DATE 2 ORDER_NUMBER 2 ORDER_STATUS ORDER_TYPE 18 OVERRIDDEN OVERRIDE_ALLOWED PRICE_ADJUSTMENT_ID PRICING_PHASE 9 QUANTITY UNIT_ADJUSTMENT UNIT_SELLING_PRICE 10 UOM CHARGE_TYPE ROW 1 CURRENCY COLUMN 1 EXTENDED_AMOUNT VALUE 1 SUM ORDER_NUMBER ROW 2 Adjustment Type Freight/Special Charge S Site CBF725B5FDD759A696BFC2B032089C90 Manual Price Overrides Manually applied adjustments and adjustments with an overridden operand, with the change reason and the user who made the change. ENGINATICS ACCRUAL 11 ADJUSTMENT_TYPE APPLIED 18 AUTOMATIC 21 CHANGE_COMMENTS 20 CHANGE_REASON CHARGE_TYPE CREATED_BY CREATION_DATE CREDIT_OR_CHARGE 5 CURRENCY 4 CUSTOMER CUSTOMER_NUMBER 17 EXTENDED_AMOUNT HEADER_ID INVOICED 7 ITEM ITEM_DESCRIPTION 22 LAST_UPDATED_BY 23 LAST_UPDATE_DATE 6 LINE 3 LINE_CATEGORY LINE_ID LINE_STATUS 9 LIST_PRICE 12 MODIFIER MODIFIER_LEVEL 13 MODIFIER_LINE MODIFIER_TYPE 15 OPERAND 1 OPERATING_UNIT 1 14 OPERATOR 3 ORDERED_DATE 2 ORDER_NUMBER 2 ORDER_STATUS ORDER_TYPE 19 OVERRIDDEN OVERRIDE_ALLOWED PRICE_ADJUSTMENT_ID PRICING_PHASE 8 QUANTITY 16 UNIT_ADJUSTMENT 10 UNIT_SELLING_PRICE UOM Manual or Overridden Only Y S Site