CAC Order Type Setup

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to display the sales order transaction types with the corresponding receivables (A/R) transaction types.

/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use ...  Report to display the sales order transaction types with the corresponding receivables (A/R) transaction types.

/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_oe_transaction_types.sql
-- |
-- | Parameters:
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- | Description:
-- | Report to display the sales order transaction types with the corresponding
-- | receivables (A/R) transaction types.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 15 Nov 2016 Douglas Volz Initial Coding
-- | 1.1 09 Jan 2017 Douglas Volz Added description and Receivables Txn Type
-- | 1.2 11 Jan 2017 Douglas Volz Added Order Category, Order Type (DFF),
-- | and Disti Plus Pricing (DFF), and add a
-- | new section for Order Types with no COGS
-- | account.
-- | 1.3 14 Mar 2017 Douglas Volz Changed apps.ra_cust_trx_types to
-- | apps.ra_cust_trx_types_all, added A/R
-- | revenue account
-- | 1.4 23 Aug 2017 Douglas Volz Add user-defined field (descriptive flex-
-- | field) for the COGS ICP account, a contra-
-- | account which is used to record the ICP
-- | portion of the COGS entry.
-- | 1.5 13 Mar 2019 Douglas Volz Added Operating Unit Parameter.
-- | 1.6 22 May 2019 Douglas Volz Added missing ORG_ID join
-- | 1.7 14 Apr 2020 Douglas Volz Added creation date to report
-- | 1.8 16 Apr 2020 Douglas Volz Moved sales ccid joins to outer query
-- | and added Ledger parameter.
-- | 1.9 28 Apr 2020 Douglas Volz Changed to multi-language views for the
-- | inventory orgs and operating units.
-- +=============================================================================+*/   more
Run CAC Order Type Setup and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger,
 haou.name Operating_Unit,
 ottt.name Order_Type_Name,
 otta.transaction_type_id Order_Type_Id,
 otta.transaction_type_code Transaction_Type_Code,
 otta.order_category_code Order_Category,
 otta.currency_code Currency_Code,
 otta.start_date_active Start_Date,
 otta.end_date_active End_Date,
 rctt.name AR_Transaction_Name,
 rctt.description AR_Transaction_Description,
 rctt.type AR_Transaction_Type,
 &segment_columns
 -- Revision for version 1.8
 &segment_columns2
 -- Revision for version 1.7
 otta.creation_date Creation_Date,
 otta.last_update_date Last_Update_Date
from oe_transaction_types_tl ottt,
 oe_transaction_types_all otta,
 gl_code_combinations gcc1, -- COGS_Accounts
 -- Revision for version 1.8
 gl_code_combinations gcc2, -- Sales_Accounts
 -- Revision for version 1.3
 -- Revision for version 1.1
 -- ra_cust_trx_types rctt,
 (select  rctt.cust_trx_type_id,
  rctt.org_id,
  rctt.name,
  rctt.description,
  rctt.type,
  rctt.gl_id_rev
  from ra_cust_trx_types_all rctt
 ) rctt,
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,  -- operating unit
 gl_ledgers gl
where   ottt.transaction_type_id    = otta.transaction_type_id
and ottt.language               = userenv('lang')
and gcc1.code_combination_id (+)= otta.cost_of_goods_sold_account
-- Revision for version 1.8
and gcc2.code_combination_id (+)= rctt.gl_id_rev
-- Revision for version 1.2
and otta.cust_trx_type_id       = rctt.cust_trx_type_id (+)
and otta.transaction_type_code <> 'LINE'
-- Revision for version 1.6
and otta.org_id                 = rctt.org_id
-- Revision for version 1.7
and hoi.org_information_context = 'Operating Unit Information'
and hoi.organization_id         = haou.organization_id -- this gets the operating unit id
and gl.ledger_id                = to_number(hoi.org_information3) -- this joins OU to GL
-- End revision for version 1.7 
and haou.organization_id        = otta.org_id -- this gets the operating unit id
and 1=1                         -- p_operating_unit, p_ledger
order by 1,2,3,4,5,7
Parameter Name SQL text Validation
Operating Unit 
haou.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
CAC Margin Analysis Account Summary GL Account Analysis COPY OF: GL Account Analysis CAC Internal Order Shipment Margin GL Account Analysis (Distributions) CAC Margin Analysis Summary CAC Open Internal ISO-IRO ONT Transaction Types and Line WF Processes CAC Deferred COGS Out-of-Balance
Blitz Report™

Blitz Report™ provides multiple benefits: