MRP Supply Chain Order Reschedule

Description
Categories: BI Publisher, Manufacturing
Application: Master Scheduling/MRP
Source: Supply Chain Order Reschedule Report (XML)
Short Name: MRPRPRSS_XML
DB package: MRP_MRPRPRSC_XMLP_PKG
SELECT    null                            C_FLEXDATA,
          parm.organization_code                    C_PLANNED_ORG_CODE,
          to_char(&P_WIP_START,'DD-MON-YY')                           C_old_start_date,
          rec.order_type                         C_order_type,
          rec.inventory_item_id                  C_item_id,
          sys.description                        C_description,
          req.planner_code                       C_planner_code,
          req.buyer_name                         C_buyer_id,
          &C_PO_ORDER_NUM                        C_po_order_num,
          &C_REQ_ORDER_NUM                       C_req_order_num,
          &C_WIP_ORDER_NUM                       C_wip_order_num,
          rec.disposition_id                     C_disposition_id,
          rec.purch_line_num                     C_purch_line_num,
          vendors.vendor_name                    C_vendor_name,
          round(rec.new_order_quantity,:P_QTY_PRECISION) C_new_order_qty,
          DECODE(rec.disposition_status_type, 2,
              :P_CANCEL_TEXT, 
              NVL(TO_CHAR(rec.new_wip_start_date,'DD-MON-YY'),
              ' '))                              C_new_start_date,
          DECODE(rec.disposition_status_type, 2,
              :P_CANCEL_TEXT,
              NVL(TO_CHAR(rec.new_order_placement_date,'DD-MON-YY'),
              ' '))                              C_new_order_plcmnt_date,
          DECODE(rec.disposition_status_type, 2,
              :P_CANCEL_TEXT,
              NVL(TO_CHAR(rec.new_dock_date,'DD-MON-YY'),
              ' '))                              C_new_dock_date,
          DECODE(rec.disposition_status_type, 2,
              :P_CANCEL_TEXT,
              TO_CHAR(rec.new_schedule_date,'DD-MON-YY'))    C_new_schedule_date,
          TO_CHAR(rec.old_schedule_date,'DD-MON-YY')         C_old_schedule_date,
          TO_CHAR(rec.old_order_placement_date,'DD-MON-YY')  C_old_order_plcmnt_date,
          NVL(TO_CHAR(rec.old_dock_date,'DD-MON-YY'), ' ')   C_old_dock_date,
          round(rec.new_order_quantity
          * NVL(standard_cost,0),:P_PRECISION)   C_standard_cost
          &C_SORT_COLUMN, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'INV', 'MSTK', 101, sys.ORGANIZATION_ID, sys.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD
FROM   mtl_item_categories   ic,
          po_vendors_view       vendors,
          mrp_system_items      req,
          mtl_system_items      sys,
          mrp_recommendations   rec,
          mrp_plan_organizations_v mpo,
          mtl_parameters    parm
          &C_WIP_FROM
          &C_PO_FROM
          &C_CATEGORY_FROM
          &C_ABC_FROM
   WHERE     
      mpo.planned_organization  =  rec.organization_id  
     AND rec.compile_designator = mpo.compile_designator
     AND  rec.vendor_id = vendors.vendor_id(+) 
     AND  sys.organization_id = rec.organization_id
     AND  sys.inventory_item_id = rec.inventory_item_id
          &C_PLANNER_RANGE
          &C_BUYER_RANGE        
          &C_ABC_WHERE
          &C_ABC_RANGE
          &C_DAYS_RANGE
          &C_WIP_WHERE
          &C_PO_WHERE
          &C_CATEGORY_WHERE
     AND  &P_ITEM_RANGE
     AND  &P_CAT_RANGE
     AND  req.organization_id = rec.organization_id
     AND  req.compile_designator = rec.compile_designator
     AND  req.inventory_item_id = rec.inventory_item_id
          &C_CUTOFF_COLUMN
     AND  rec.order_type = decode (:P_ORDER_TYPE,
                                   1,1,
                                   2,2,
                                   3,3,
                                   rec.order_type)
     AND  rec.order_type in (1,2,3)
     AND  ((rec.new_schedule_date <> rec.old_schedule_date)
           OR (rec.disposition_status_type = 2)) 
     AND  ic.inventory_item_id   = req.inventory_item_id 
     AND  ic.organization_id     = req.organization_id
     AND  ic.category_set_id     = :P_CAT_SET
     AND  mpo.planned_organization  = parm.organization_id 
     AND  DECODE(:P_REPORT_MULTIORG, 1,  
                      mpo.planned_organization,  mpo.organization_id) = :P_ORG_ID 
     AND  mpo.compile_designator = :P_PLAN_NAME
ORDER BY  &C_ORDER_BY
          DECODE(rec.disposition_status_type, 2, TO_DATE('9999/12/31',
          'YYYY/MM/DD'), new_schedule_date), :P_ITEM_ORDER_BY, 
          parm.organization_code,  vendors.vendor_name, rec.new_order_quantity,
          req.buyer_name, req.planner_code, &C_PO_ORDER_NUM,
          &C_REQ_ORDER_NUM, &C_WIP_ORDER_NUM, rec.purch_line_num
Parameter NameSQL textValidation
Quantity Precision
 
Number
Currency Code
 
LOV Oracle
Category Structure
 
Number
Org id
 
Number
Cutoff Date
 
LOV Oracle
Cutoff Type
 
LOV Oracle
ABC Class
 
LOV Oracle
ABC Assignment Group
 
LOV Oracle
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
Items From
 
Buyer
 
LOV Oracle
Planner
 
LOV Oracle
In Days Filter
 
Number
Out Days Filter
 
Number
Sort
 
LOV Oracle
Order Type
 
LOV Oracle
Organization Selection
 
LOV Oracle
Plan Name
 
LOV Oracle