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,
	to_char(gdr.conversion_date,'DD-Mon-YYYY') Curr_Conv_Date,
	round(gdr.conversion_rate,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))
		 * gdr.conversion_rate,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))
		 * gdr.conversion_rate,2) + round(txns_sum.cogs_primary_quantity * txns_sum.to_org_pii_cost,2) Conv_Net_Margin_Less_PII
from	-- ========================================================
	-- Section II:  Get all currency conversion rates based
	--		on the standard cost budget rate type
	-- ========================================================
	(select	gdr.from_currency,
		gdr.to_currency,
		gdct.user_conversion_type,
		gdr.conversion_date,
		round(gdr.conversion_rate,6) conversion_rate
	 from	gl_daily_rates gdr,
		gl_daily_conversion_types gdct
	 where	gdr.conversion_type        = gdct.conversion_type
	 and	1=1                        -- p_curr_conv_date
	 and	4=4                        -- p_std_cost_curr_conv_type
	 union all
	 select	gl.currency_code,              -- from_currency
		gl.currency_code,              -- to_currency
		gdct.user_conversion_type,     -- conversion_type
		:p_curr_conv_date,             -- conversion_date, p_curr_conv_date
		1 conversion_rate              -- conversion_rate
	 from	gl_ledgers gl,
		gl_daily_conversion_types gdct
	 where	4=4                            -- p_std_cost_curr_conv_type
	 group by
		gl.currency_code,
		gl.currency_code,
		gdct.user_conversion_type,
		:p_curr_conv_date,             -- p_curr_conv_date
		1
	) gdr, -- Standard Cost Budgeted Currency Rate
	-- ========================================================
	-- 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
	 -- ========================================================
	 -- 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
	 -- 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