CAC Internal Order Shipment Margin

select txns_sum.from_ledger From_Ledger,
-- ========================================================
-- Section I
-- Output the Report Columns
-- ========================================================
 txns_sum.from_operating_unit From_Operating_Unit,
 txns_sum.to_ledger To_Ledger,
 txns_sum.to_operating_unit To_Operating_Unit,
 txns_sum.ship_from_org Ship_From_Org,
 txns_sum.ship_to_org Ship_To_Org,
 txns_sum.item_number Item_Number,
 txns_sum.item_description Item_Description,
 fcl.meaning Item_Type,
&category_columns
 txns_sum.customer Customer,
 txns_sum.customer_number Customer_Number,
 txns_sum.so_order_number  Sales_Order_Number,
 txns_sum.so_line_number Sales_Order_Line,
 ottt.name Order_Type,
 (select rct.trx_number
  from ra_customer_trx_all rct
  where rct.customer_trx_id = txns_sum.ar_invoice_id) AR_Invoice_Number,
 decode(txns_sum.ar_invoice_line_number, 0, null, to_char(txns_sum.ar_invoice_line_number)) AR_Line_Number,
 -- End revision for version 1.25
 decode(txns_sum.pr_number, 0, null, txns_sum.pr_number)  Requisition_Number,
 qlh_tl.name Price_List,
 mtt.transaction_type_name Transaction_Type,
 txns_sum.transaction_id Transaction_Number,
 txns_sum.transaction_date Ship_Date,
 txns_sum.lot_number Lot_Number,
 txns_sum.transaction_cost From_Org_Txn_Cost,
 txns_sum.unit_price From_Org_Unit_Price,
 txns_sum.uom_code UOM_Code,
 txns_sum.cogs_primary_quantity Ship_Quantity,
 txns_sum.quantity_invoiced Invoice_Quantity,
 txns_sum.cogs_primary_quantity - txns_sum.quantity_invoiced Quantity_Diff,
 txns_sum.from_curr_code From_Curr_Code,
 txns_sum.cogs_amount From_Org_COGS_Amount,
 txns_sum.revenue_amount From_Org_Sales_Amount,
 txns_sum.cogs_amount - txns_sum.revenue_amount From_Org_Margin_Amount,
 -- ======================================================== 
 -- Revision for version 1.10, fix the Margin Percentage
 -- Margin / Sales_Amount
 -- If the Sales Price is Zero and the Unit_Cost is Zero the Margin Percent is 0
 -- If the Sales_Amount is Zero the Margin Percent is -100 %
 -- Else do the normal calculation
 -- ======================================================== 
 case 
    -- Unit_Price is zero and Unit_Cost is zero set the margin percentage to zero
    when nvl(txns_sum.unit_price,0) = 0 and txns_sum.transaction_cost = 0 then 0
    -- Unit_Price is zero and the Unit_Cost is not zero the Margin Percent is 100 %
    when nvl(txns_sum.unit_price,0) = 0 and txns_sum.transaction_cost <> 0 then 100
    -- Else do the calculation for Margin Percentage and round the percentage
    else round((txns_sum.transaction_cost - nvl(txns_sum.unit_price,0))/ nvl(txns_sum.unit_price,0),3) * 100
        end From_Org_Margin_Percent,
 txns_sum.transaction_cost - txns_sum.unit_price From_Org_Unit_Margin,
 txns_sum.from_org_pii_cost From_Org_PII_Item_Cost,
 txns_sum.transaction_cost - txns_sum.unit_price + txns_sum.from_org_pii_cost From_Org_Net_Unit_Margin,
 txns_sum.cogs_amount - txns_sum.revenue_amount +
  round((txns_sum.from_org_pii_cost * txns_sum.cogs_primary_quantity),2) From_Org_Net_Margin_Amount,
 txns_sum.to_curr_code To_Org_Curr_Code,
 nvl(gdr.conversion_date,:conversion_date) Curr_Conv_Date,
 round(nvl(gdr.conversion_rate,1),6) Curr_Conv_Rate,
 round((txns_sum.cogs_amount - txns_sum.revenue_amount + round((txns_sum.from_org_pii_cost * txns_sum.cogs_primary_quantity),2))
   * nvl(gdr.conversion_rate,1),2) Conv_Net_Margin_Amount,
 txns_sum.to_org_pii_cost To_Org_PII_Item_Cost,
 round(txns_sum.cogs_primary_quantity * txns_sum.to_org_pii_cost,2) To_Org_PII_Amount,
 -- Conv Margin Amount + To_Org_PII_Amount
 round((txns_sum.cogs_amount - txns_sum.revenue_amount + round((txns_sum.from_org_pii_cost * txns_sum.cogs_primary_quantity),2))
   * nvl(gdr.conversion_rate,1),2) + round(txns_sum.cogs_primary_quantity * txns_sum.to_org_pii_cost,2) Conv_Net_Margin_Less_PII
from
(select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.conversion_date=:conversion_date and gdct.user_conversion_type=:user_conversion_type and gdct.conversion_type=gdr.conversion_type) gdr,
 -- ========================================================
 -- Section III: Condense the 2 union all statements into one line
 --              for each Transaction Id, reports 1 - 2.
 -- ========================================================
 (select txns.from_ledger_id,
  txns.from_ledger,
  txns.from_operating_unit_id,
  txns.from_operating_unit,
  txns.to_ledger_id,
  txns.to_ledger,
  txns.to_operating_unit_id,
  txns.to_operating_unit,
  txns.ship_from_org_id,
  txns.ship_from_org,
  txns.ship_to_org_id,
  txns.ship_to_org,
  txns.from_curr_code,
  txns.to_curr_code,
  txns.inventory_item_id,
  txns.item_number,
  txns.item_description,
  txns.item_type,
  txns.customer,
  txns.customer_number,
  txns.so_order_number,
  txns.so_header_id,
  txns.so_line_number,
  txns.so_line_id,
  txns.line_type_id,
  sum(txns.pr_number) pr_number,
  txns.price_list_id,
  txns.transaction_type_id,
  txns.transaction_id,
  txns.transaction_date,
  txns.lot_number,
  round(sum(txns.transaction_cost),5) transaction_cost,
  -- From Org profit in inventory costs
  nvl((select sum(cicd.item_cost)
       from cst_item_cost_details cicd,
    cst_cost_types cct,
    bom_resources br
       where cicd.inventory_item_id = txns.inventory_item_id
       and cicd.organization_id   = txns.ship_from_org_id
       and br.resource_id         = cicd.resource_id
       and 5=5                    -- p_pii_resource_code
       and cct.cost_type_id       = cicd.cost_type_id
       and 6=6                    -- p_pii_cost_type
     ),0) from_org_pii_cost,
  -- To Org profit in inventory costs
  nvl((select sum(cicd.item_cost)
       from cst_item_cost_details cicd,
    cst_cost_types cct,
    bom_resources br
       where cicd.inventory_item_id = txns.inventory_item_id
       and cicd.organization_id   = txns.ship_to_org_id
       and br.resource_id         = cicd.resource_id
       and 5=5                    -- p_pii_resource_code
       and cct.cost_type_id       = cicd.cost_type_id
       and 6=6                    -- p_pii_cost_type
     ),0) to_org_pii_cost,
  txns.currency_code,
  txns.uom_code,
  round(sum(txns.primary_quantity),3) cogs_primary_quantity,
  round(sum(txns.COGS_amount),2) cogs_amount,
  sum(txns.ar_invoice_id) ar_invoice_id,
  sum(txns.ar_invoice_line_number) ar_invoice_line_number, 
  round(sum(txns.unit_price),5) unit_price,
  round(sum(txns.quantity_invoiced),3) quantity_invoiced,
  round(sum(txns.revenue_amount),3) revenue_amount 
 from 
 (
  -- ======================================================== 
  -- Section IV:  Get the material, payables and revenue transactions
  --              which represent the Internal Order activity.
  --              Assume IR/ISO shipments may or may not use custom billing.
  --              Section III has 7 union all reports.
  -- ======================================================== 
  -- Report 1: First get the COGS for IR/ISO Intransit Shipments
  -- where title passes upon shipment (FOB_Point = 1, Shipment)
  -- ========================================================
  select 'Rept1 IR-ISO FOB Ship' rept_section,
  gl_from_org.ledger_id from_ledger_id,
  haou_from_org.organization_id from_operating_unit_id,
  gl_to_org.ledger_id to_ledger_id,
  haou_to_org.organization_id to_operating_unit_id,
  mp_from.organization_id ship_from_org_id,
  mp_to.organization_id ship_to_org_id,
  gl_from_org.currency_code from_curr_code,
  gl_to_org.currency_code to_curr_code,
  nvl(gl_from_org.short_name, gl_from_org.name) from_ledger,
  nvl(gl_to_org.short_name, gl_to_org.name) to_ledger,
  haou_from_org.name from_operating_unit,
  haou_to_org.name to_operating_unit,
  mp_from.organization_code ship_from_org,
  mp_to.organization_code ship_to_org,
  msiv.inventory_item_id,
  msiv.concatenated_segments item_number,
  msiv.description item_description,
  msiv.item_type,
  nvl(InterCo.customer, hz.party_name) customer,
  nvl(InterCo.customer_number, hca.account_number) customer_number,
  iso.order_number so_order_number,
  iso.header_id so_header_id,
  iso_line.line_number so_line_number,
  iso_line.line_id so_line_id,
  iso_line.line_type_id,
  to_number(decode(iso.order_source_id, 10, iso.orig_sys_document_ref, -9999)) pr_number,
  iso.price_list_id price_list_id,
  mmt.transaction_type_id,
  mmt.transaction_id transaction_id,
  mmt.transaction_date transaction_date,
  mtln.lot_number lot_number,
  -- Revision for version 1.7
  mmt.actual_cost transaction_cost,
  gl_from_org.currency_code currency_code,
  -- Revision for version 1.27
  muomv.uom_code,
  decode(mmt.transaction_type_id,
   61, nvl(mtln.primary_quantity,mmt.primary_quantity),
   95, nvl(mtln.primary_quantity,mmt.primary_quantity),
   nvl(mtln.primary_quantity,mmt.primary_quantity) * -1
        ) primary_quantity,
  -- Revision for version 1.25
  -- Calculate From Org COGS
  -- Quantity X Unit_Cost
  round(decode(mmt.transaction_type_id,
    61, nvl(mtln.primary_quantity,mmt.primary_quantity),
    95, nvl(mtln.primary_quantity,mmt.primary_quantity),
    nvl(mtln.primary_quantity,mmt.primary_quantity) * -1
    -- End revision for version 1.15
       ) * nvl(mmt.actual_cost,0)
     ,2) COGS_amount,
  0 ar_invoice_id,
  0 ar_invoice_line_number,
  0 unit_price,
  0 quantity_invoiced,
  0 revenue_amount
  from mtl_system_items_vl msiv,
  -- Revision for version 1.27
  mtl_units_of_measure_vl muomv,
  mtl_material_transactions mmt,
  mtl_transaction_lot_numbers mtln,
  oe_order_headers_all iso,  
  oe_order_lines_all iso_line,
  po_requisition_headers_all prh,   
  po_requisition_lines_all prl,
  mtl_parameters mp_from,   -- from org
  mtl_parameters mp_to,  -- to org
  -- Revision for version 1.15, comment out user information 
  -- fnd_user fu_iso,
  -- fnd_user fu_iso2,
  hz_cust_accounts_all hca,
  hz_cust_acct_sites_all hcs,
  hz_cust_site_uses_all hsu,
  hz_parties hz,
  -- From Information
  hr_organization_information hoi_from_org,
  hr_all_organization_units_vl haou_from_org, -- operating unit 
  gl_ledgers gl_from_org,
  -- To Information
  hr_organization_information hoi_to_org,
  hr_all_organization_units_vl haou_to_org, -- operating unit 
  gl_ledgers gl_to_org,
  -- Inline select for Customer and Operating_Unit information
  (select HOU_to.name to_ou,
   HOU_from.name from_ou,
   hou_to.organization_id to_ou_id,
   hou_from.organization_id from_ou_id,
   rc.customer_name customer,
   rc.customer_number customer_number,
   qlh_tl.name price_list,
   nvl(hsu.price_list_id, hca.price_list_id) price_list_id
   from hz_cust_site_uses_all hsu,           -- joins to intercompany relationships
   mtl_intercompany_parameters mip,     -- intercompany ship (from) and sell to relationships
   hz_cust_acct_sites_all hcs,
   hz_cust_accounts hca,
   qp_list_headers_tl qlh_tl,
   (select cust_account_id customer_id , 
    party.party_name customer_name , 
    cust_acct.account_number customer_number,
    cust_acct.price_list_id price_list_id 
    from hz_parties party, 
    hz_cust_accounts cust_acct 
    where cust_acct.party_id = party.party_id
   ) rc, 
   hr_all_organization_units_vl hou_to,
   hr_all_organization_units_vl hou_from
   where mip.customer_site_id            = hsu.site_use_id            -- internal customer information
   and hca.cust_account_id             = hcs.cust_account_id
   and hcs.cust_acct_site_id           = hsu.cust_acct_site_id
   and rc.customer_id                  = mip.customer_id
   and qlh_tl.list_header_id           = nvl(hsu.price_list_id, hca.price_list_id)
   and qlh_tl.language                 = userenv('lang')
   and hou_to.organization_id          = mip.sell_organization_id
   and hou_from.organization_id        = mip.ship_organization_id  
   -- Fix for version 1.4, the customer type is not set correctly for internal orgs
   -- This condition was preventing the internal To Orgs from being selected
   -- and hca.customer_type                = 'I'  -- Internal Customers
  ) InterCo
  -- ======================================================== 
  -- join the organization_id and item to the internal SO
  -- ======================================================== 
  where msiv.organization_id            = iso_line.ship_from_org_id 
  and msiv.inventory_item_id          = iso_line.inventory_item_id 
  and muomv.uom_code                  = msiv.primary_uom_code
  and mp_from.organization_id         = msiv.organization_id
  and mp_to.organization_id           = prl.destination_organization_id
  -- ======================================================== 
  -- use this condition to limit this sql for internal requisitions
  -- ======================================================== 
  and iso_line.order_source_id        = 10 -- internal requisitions
  and iso_line.line_category_code in ('ORDER')
  -- ======================================================== 
  -- Use these joins for order header to line and matl transactions
  -- mmt.trx_source_line_id is populated for internal req shipments
  -- ======================================================== 
  and iso.header_id                   = iso_line.header_id
  and mmt.trx_source_line_id          = iso_line.line_id
  and mmt.transaction_id              = mtln.transaction_id (+)
  -- ======================================================== 
  -- Only report intransit-related transactions
  -- ======================================================== 
  -- Revision for version 1.17
  -- and mmt.transaction_action_id in (12, 21)
  -- Only want Intransit Shipments when the FOB_Point = 1 (Shipment)
  -- as this represents the Debit to COGS for the Intransit Shipment.
  and mmt.transaction_action_id       = 21
  and mmt.fob_point                   = 1
  -- ======================================================== 
  -- Material Transaction date joins
  -- ======================================================== 
  and 7=7    -- p_trx_date_from, p_trx_date_to
  -- ======================================================== 
  -- use these conditions to join to purchase reqs
  -- ======================================================== 
  and prh.type_lookup_code            = 'INTERNAL'
  and iso.source_document_id          = prh.requisition_header_id
  and prl.requisition_header_id       = prh.requisition_header_id
  and prl.requisition_line_id         = iso_line.source_document_line_id
  -- Revision for version 1.15, comment out user information 
  -- and fu_iso.user_id                  = prh.created_by
  -- and fu_iso2.user_id                 = prh.last_updated_by
  -- ========================================================
  -- added for customer ship-to-information; replaces use of ra_customers
  -- d.volz 6-Oct-08
  -- ========================================================
  and iso_line.sold_to_org_id         = hca.cust_account_id
  and iso_line.Ship_to_org_id         = hsu.site_use_id
  and hcs.cust_acct_site_id           = hsu.cust_acct_site_id
  and hca.cust_account_id             = hcs.cust_account_id
  and hca.party_id                    = hz.party_id
  -- ========================================================
  -- From organization information
  -- ========================================================
  and hoi_from_org.org_information_context = 'Accounting Information'
  and hoi_from_org.organization_id    = mp_from.organization_id     -- ship from org for sales and COGS
  and InterCo.from_ou_id (+)          = to_number(hoi_from_org.org_information3) -- this gets the operating unit id
  and haou_from_org.organization_id   = to_number(hoi_from_org.org_information3) -- this gets the operating unit id
  and gl_from_org.ledger_id           = to_number(hoi_from_org.org_information1) 
  and 8=8                             -- p_from_org_ledger
  and gl_from_org.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
  -- ========================================================
  -- To organization information
  -- ========================================================
  and hoi_to_org.org_information_context = 'Accounting Information'
  and hoi_to_org.organization_id      = mp_to.organization_id     -- ship to org for sales and COGS
  and InterCo.to_ou_id                = to_number(hoi_to_org.org_information3) -- this gets the operating unit id
  and haou_to_org.organization_id     = to_number(hoi_to_org.org_information3) -- this gets the operating unit id
  and gl_to_org.ledger_id             = to_number(hoi_to_org.org_information1)
  and 9=9                             -- p_to_org_ledger
  and gl_to_org.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
  -- End revision for version 1.14
  -- Only report entries which cross operating units
  -- Doing this screens out transfers to the consignment organizations
  and haou_to_org.organization_id    <> haou_from_org.organization_id
  union all
  -- ======================================================== 
  -- Report 2: Get the IR/ISO COGS for IR/ISO Intransit Receipts
  -- where title passes upon receipt (FOB_Point = 2, Receipt)
  -- ========================================================
  select 'Rept2 IR-ISO FOB Receipt' rept_section,
  gl_from_org.ledger_id from_ledger_id,
  haou_from_org.organization_id from_operating_unit_id,
  gl_to_org.ledger_id to_ledger_id,
  haou_to_org.organization_id to_operating_unit_id,
  mp_from.organization_id ship_from_org_id,
  mp_to.organization_id ship_to_org_id,
  gl_from_org.currency_code from_curr_code,
  gl_to_org.currency_code to_curr_code,
  nvl(gl_from_org.short_name, gl_from_org.name) from_ledger,
  nvl(gl_to_org.short_name, gl_to_org.name) to_ledger,
  haou_from_org.name from_operating_unit,
  haou_to_org.name to_operating_unit,
  mp_from.organization_code ship_from_org,
  mp_to.organization_code ship_to_org,
  msiv.inventory_item_id,
  msiv.concatenated_segments item_number,
  msiv.description item_description,
  msiv.item_type,
  hz.party_name customer,
  hca.account_number customer_number,
  iso.order_number so_order_number,
  iso.header_id so_header_id,
  iso_line.line_number so_line_number,
  iso_line.line_id so_line_id,
  iso_line.line_type_id,
  to_number(decode (iso.order_source_id, 10, iso.orig_sys_document_ref, -9999)) pr_number,
  iso.price_list_id price_list_id,
  mmt.transaction_type_id,
  mmt.transaction_id transaction_id,
  mmt.transaction_date transaction_date,
  mtln.lot_number lot_number,
  -- Revision for version 1.7
  -- Have to use the Shipping Org's cost due to
  -- standard cost updates between the shipment
  -- and receipt transactions.
  mmt_ship.actual_cost transaction_cost,
  gl_from_org.currency_code currency_code,
  -- Revision for version 1.27
  muomv.uom_code,
  decode(mmt.transaction_type_id,
   95, nvl(mtln.primary_quantity, mmt.primary_quantity) * -1,
   nvl(mtln.primary_quantity, mmt.primary_quantity)
        ) primary_quantity,
  -- Calculate From Org COGS
  round(decode(mmt.transaction_type_id,
    95, nvl(mtln.primary_quantity, mmt.primary_quantity) * -1,
    nvl(mtln.primary_quantity, mmt.primary_quantity)
              ) * nvl(mmt_ship.actual_cost,0)
     ,5) COGS_amount,
  0 ar_invoice_id,
  0 ar_invoice_line_number,
  0 unit_price,
  0 quantity_invoiced,
  0 revenue_amount
  from mtl_system_items_vl msiv,
  -- Revision for version 1.27
  mtl_units_of_measure_vl muomv,
  mtl_material_transactions mmt,
  mtl_material_transactions mmt_ship,
  mtl_transaction_lot_numbers mtln,
  oe_order_headers_all iso,  
  oe_order_lines_all iso_line,  
  po_requisition_headers_all prh,   
  po_requisition_lines_all prl,
  mtl_parameters mp_from,   -- from org
  mtl_parameters mp_to,  -- to org
  -- Revision for version 1.15, comment out user information 
  -- fnd_user fu_iso,
  -- fnd_user fu_iso2,
  hz_cust_accounts_all hca,
  hz_cust_acct_sites_all hcs,
  hz_cust_site_uses_all hsu,
  hz_parties hz,
  -- From organization
  hr_organization_information hoi_from_org,
  hr_all_organization_units_vl haou_from_org,