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