RLM Net Change

Description
Categories: BI Publisher, Manufacturing
Application: Release Management
Source: Net Change Report (XML)
Short Name: RLMNTCHG_XML
DB package: RLM_RLMNETCH_XMLP_PKG
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
Parameter Name SQL text Validation
Include Percent Change more than
 
Number
Include Lines with no change?(Y/N)
 
LOV Oracle
Include Other Details?(Y/N)
 
LOV Oracle
Include Authorization Quantity?(Y/N)
 
LOV Oracle
Prod. Seq. End
 
Prod. Seq. Start
 
Customer Item End
 
LOV Oracle
Customer Item Start
 
LOV Oracle
Ship To
 
LOV Oracle
Ship From
 
LOV Oracle
Old Schedule
 
LOV Oracle
New Schedule
 
LOV Oracle
Schedule Type
 
LOV Oracle
Customer
 
LOV Oracle
Report Title