RLM Net Change

select nvl(a.start_date_time,b.start_date_time) start_date,
nvl(a.cust_production_seq_num, b.cust_production_seq_num) prod_num,
 a.header_id new_header_id1,  b.header_id old_header_id1,
a.line_id, b.line_id line_id1,  a.qty_type_code new_code, b.qty_type_code old_code,
nvl(a.ship_from_org_id, b.ship_from_org_id) ship_from_org_id, 
nvl(a.ship_to_org_id, b.ship_to_org_id) ship_to_org_id,
nvl(a.bill_to_address_id,b.bill_to_address_id) bill_to_address_id,
nvl(a.ship_to_address_id,b.ship_to_address_id) ship_to_address_id,
nvl(a.ship_to_name_ext, b.ship_to_name_ext) ship_to_name, 
nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id) intrmd_ship_to_id,
nvl(a.customer_item_id,b.customer_item_id) customer_item_id,
nvl(a.inventory_item_id, b.inventory_item_id) inventory_item_id,
nvl(a.item_detail_subtype, b.item_detail_subtype) item_detail_subtype,  fnd1.meaning detail_subtype,
nvl(a.uom_code, b.uom_code) uom_code,
nvl(a.cust_po_number, b.cust_po_number) cust_po_number,
nvl(a.industry_attribute1,b.industry_attribute1) cust_record_year,
 a.item_detail_quantity item_detail_quantity1,  
a.item_detail_type item_detail_type3,  fnd2.meaning new_detail_type,
b.item_detail_quantity, 
b.item_detail_type , fnd3.meaning old_detail_type, 
--	RLM_RLMNETCH_XMLP_PKG.cf_old_cumformula(b.qty_type_code, b.item_detail_quantity, a.qty_type_code,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) CF_old_cum, 
--	RLM_RLMNETCH_XMLP_PKG.cf_1formula(a.qty_type_code, a.item_detail_quantity, b.qty_type_code,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) CF_new_cum, 
	RLM_RLMNETCH_XMLP_PKG.c_actual_old_qtyformula(b.qty_type_code, b.item_detail_quantity,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) C_actual_old_qty, 
	RLM_RLMNETCH_XMLP_PKG.c_net_changeformula(:C_actual_new_qty, :C_actual_old_qty) C_net_change, 
	RLM_RLMNETCH_XMLP_PKG.cf_percentageformula(:C_sum_old_qty, :CF_old_cum, :C_change_cum) CF_percentage, 
	RLM_RLMNETCH_XMLP_PKG.c_actual_new_qtyformula(a.qty_type_code, a.item_detail_quantity,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) C_actual_new_qty ,
	RLM_RLMNETCH_XMLP_PKG.c_change_cumformula(:CF_new_cum, :CF_old_cum) C_change_cum
from rlm_schedule_lines_all  a, rlm_schedule_lines_all b, 
fnd_lookups fnd1,
fnd_lookups fnd2,
fnd_lookups fnd3
where (a.org_id = b.org_id and 
 a.org_id = :CP_org_id   and 
 a.header_id (+) = :P_new_header_id and
b.header_id  = :P_old_header_id and
a.customer_item_id (+) = b.customer_item_id and
a.ship_to_org_id (+) = b.ship_to_org_id and
a.ship_from_org_id (+) = b.ship_from_org_id and
a.inventory_item_id (+) = b. inventory_item_id and
nvl(a.intrmd_ship_to_id,nvl(b.intrmd_ship_to_id,0)) = nvl(b.intrmd_ship_to_id,0) and
a.item_detail_subtype (+) = b.item_detail_subtype and
a.uom_code (+) = b.uom_code and
nvl(a.cust_po_number, nvl(b.cust_po_number,0)) = nvl(b.cust_po_number,0) and 
a.item_detail_type (+)  < :P_ITEM_DETAIL_TYPE and
b.item_detail_type  < :P_ITEM_DETAIL_TYPE and
trunc(a.start_date_time (+) )  = trunc(b.start_date_time) and
fnd1.lookup_code = nvl(a.item_detail_subtype, b.item_detail_subtype) and
fnd1.lookup_type = :P_RLM_DETAIL_SUBTYPE_CODE and
 nvl(a.item_detail_type, '0') = fnd2.lookup_code   and
fnd2.lookup_type = :P_RLM_DETAIL_TYPE_CODE  and
fnd3.lookup_code = b.item_detail_type and
fnd3.lookup_type = :P_RLM_DETAIL_TYPE_CODE)
--P_where4
--P_where3
and nvl ( a.customer_item_id , b.customer_item_id )=:customer_item_id1 
 and nvl ( a.ship_from_org_id , b.ship_from_org_id )=:ship_from_org_id1 
 and nvl ( a.inventory_item_id , b.inventory_item_id )=:inventory_item_id1 
 and nvl ( a.ship_to_org_id , b.ship_to_org_id )=:ship_to_org_id1
UNION
select  
nvl(a.start_date_time,b.start_date_time) start_date,
nvl(a.cust_production_seq_num, b.cust_production_seq_num) prod_num,
a.header_id new_header_id1,  b.header_id old_header_id1,
a.line_id, b.line_id line_id1, a.qty_type_code new_code, b.qty_type_code old_code,
nvl(a.ship_from_org_id, b.ship_from_org_id) ship_from_org_id, 
nvl(a.ship_to_org_id, b.ship_to_org_id) ship_to_org_id,
nvl(a.bill_to_address_id,b.bill_to_address_id) bill_to_address_id,
nvl(a.ship_to_address_id,b.ship_to_address_id) ship_to_address_id,
nvl(a.ship_to_name_ext, b.ship_to_name_ext) ship_to_name, 
nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id) intrmd_ship_to_id,
nvl(a.customer_item_id,b.customer_item_id) customer_item_id,
nvl(a.inventory_item_id, b.inventory_item_id) inventory_item_id,
nvl(a.item_detail_subtype, b.item_detail_subtype) item_detail_subtype,  fnd1.meaning detail_subtype,
nvl(a.uom_code, b.uom_code) uom_code,
nvl(a.cust_po_number, b.cust_po_number) cust_po_number,
nvl(a.industry_attribute1,b.industry_attribute1) cust_record_year,
 a.item_detail_quantity item_detail_quantity1,  
a.item_detail_type item_detail_type3,  fnd2.meaning new_detail_type,
b.item_detail_quantity, 
b.item_detail_type , fnd3.meaning old_detail_type, 
--	RLM_RLMNETCH_XMLP_PKG.cf_old_cumformula(b.qty_type_code, b.item_detail_quantity, a.qty_type_code,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) CF_old_cum, 
--	RLM_RLMNETCH_XMLP_PKG.cf_1formula(a.qty_type_code, a.item_detail_quantity, b.qty_type_code,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) CF_new_cum, 
	RLM_RLMNETCH_XMLP_PKG.c_actual_old_qtyformula(b.qty_type_code, b.item_detail_quantity,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) C_actual_old_qty, 
	RLM_RLMNETCH_XMLP_PKG.c_net_changeformula(:C_actual_new_qty, :C_actual_old_qty) C_net_change, 
	RLM_RLMNETCH_XMLP_PKG.cf_percentageformula(:C_sum_old_qty, :CF_old_cum, :C_change_cum) CF_percentage, 
	RLM_RLMNETCH_XMLP_PKG.c_actual_new_qtyformula(a.qty_type_code, a.item_detail_quantity,nvl(a.industry_attribute1,b.industry_attribute1),nvl(a.intrmd_ship_to_id,b.intrmd_ship_to_id),nvl(a.customer_item_id,b.customer_item_id),nvl(a.ship_from_org_id, b.ship_from_org_id),nvl(a.bill_to_address_id,b.bill_to_address_id),nvl(a.ship_to_address_id,b.ship_to_address_id),nvl(a.inventory_item_id, b.inventory_item_id),nvl(a.cust_po_number, b.cust_po_number),nvl(a.ship_to_org_id, b.ship_to_org_id)) C_actual_new_qty ,
	RLM_RLMNETCH_XMLP_PKG.c_change_cumformula(:CF_new_cum, :CF_old_cum) C_change_cum
from rlm_schedule_lines_all  a, rlm_schedule_lines_all b, 
fnd_lookups fnd1,
fnd_lookups fnd2,
fnd_lookups fnd3
where (a.org_id = b.org_id and 
 a.org_id = :CP_org_id   and 
a.header_id =  :P_new_header_id and
b.header_id (+) = :P_old_header_id and
b.customer_item_id (+) = a.customer_item_id and
b.ship_to_org_id (+) = a.ship_to_org_id and
b.ship_from_org_id (+) =a.ship_from_org_id and
b.inventory_item_id (+) = a. inventory_item_id and
nvl(b.intrmd_ship_to_id, nvl(a.intrmd_ship_to_id,0)) = nvl(a.intrmd_ship_to_id,0) and
b.item_detail_subtype (+) = a.item_detail_subtype and
b.uom_code (+) = a.uom_code and
nvl(b.cust_po_number, nvl(a.cust_po_number, 0)) = nvl(a.cust_po_number,0) and 
b.item_detail_type (+)  < :P_ITEM_DETAIL_TYPE and
a.item_detail_type  < :P_ITEM_DETAIL_TYPE and
trunc(b.start_date_time (+) )  = trunc(a.start_date_time) and
fnd1.lookup_code = nvl(b.item_detail_subtype, a.item_detail_subtype) and
fnd1.lookup_type = :P_RLM_DETAIL_SUBTYPE_CODE and 
a.item_detail_type = fnd2.lookup_code   and
fnd2.lookup_type = :P_RLM_DETAIL_TYPE_CODE  and
nvl(b.item_detail_type,'0') = fnd3.lookup_code  and
fnd3.lookup_type = :P_RLM_DETAIL_TYPE_CODE)
--P_where5
--P_where3 
 and nvl ( a.customer_item_id , b.customer_item_id )=:customer_item_id1 
 and nvl ( a.ship_from_org_id , b.ship_from_org_id )=:ship_from_org_id1 
 and nvl ( a.inventory_item_id , b.inventory_item_id )=:inventory_item_id1 
 and nvl ( a.ship_to_org_id , b.ship_to_org_id )=:ship_to_org_id1
 ORDER BY 13 ASC,
  9 ASC,
  10 ASC,
  14 ASC,
  15 ASC,
  16 ASC