VIEW: APPS.CSP_PLAN_SEARCH_V

Source

View Text - Preformatted

SELECT msibk.inventory_item_id, msibk.organization_id, msibk.concatenated_segments, msibk.description, msibk.planner_code, maav.abc_class_id, maav.abc_class_name, plan_order_type_code, plan_order_type, nvl(sum(excess_qty), 0) Excess_Qty, nvl(sum(repair_qty), 0) Repair_qty, nvl(sum(newbuy_qty), 0) New_Buy_Qty, nvl(round(sum(Excess_qty) * cic.item_cost, 2), 0) Excess_Value, nvl(round(sum(repair_qty) * cic.item_cost, 2), 0) Repair_Value, nvl(round(sum(newbuy_qty) * cic.item_cost, 2), 0) New_Buy_Value, trunc(min(plan_date)) plan_date from ( select cpd.inventory_item_id, cpd.organization_id, cpd.plan_Detail_type, min(cpd.plan_date) plan_date, sum(decode(fl.lookup_code, 200, decode(plan_Detail_type, 4110, quantity, null), 300, decode(plan_Detail_type, 8130, quantity, null), 900, decode(plan_detail_type, 8630, quantity, null), 100, decode(plan_detail_type, 4110, decode(sign(trunc(cpd.plan_date) - nvl(cpl.excess_lead_time, 0) - trunc(sysdate)), -1 , quantity, null)), null)) excess_qty, sum(decode(fl.lookup_code, 200, decode(plan_Detail_type, 4210, quantity, null), 300, decode(plan_Detail_type, 8120, quantity, null), 400, decode(plan_detail_type, 8220, quantity, null), 900,decode(plan_detail_type, 8620, quantity, null),100, decode(plan_Detail_type, 4210, decode(sign(trunc(cpd.plan_date) - nvl(cpl.repair_lead_time, 0) - trunc(sysdate)), -1, quantity, null) ), null)) repair_qty, sum(decode(fl.lookup_code, 200, decode(plan_Detail_type, 4310, quantity, null), 300, decode(plan_Detail_type, 8110, quantity, null), 400, decode(plan_detail_type, 8210, quantity, null), 500, decode(plan_detail_type, 8310, quantity, null), 600, decode(plan_detail_type, 8410, quantity, null), 700, decode(plan_detail_type, 8420, quantity, null), 800, decode(plan_detail_type, 8510, quantity, null), 900, decode(plan_detail_type, 8610, quantity, null), 100, decode(plan_detail_type, 4310, decode(sign(trunc(cpd.plan_date) - nvl(cpl.newbuy_lead_time, 0) - trunc(sysdate)), -1, quantity, null)), null)) newbuy_qty, fl.lookup_code plan_order_type_code, fl.meaning plan_order_type from csp_plan_Details cpd, csp_plan_leadtimes cpl, fnd_lookups fl WHERE fl.lookup_code = decode(cpd.plan_detail_type, 4110, decode(sign(trunc(cpd.plan_date) - nvl(cpl.excess_lead_time, 0) - trunc(sysdate)), -1, 100, 200), 4210, decode(sign(trunc(cpd.plan_date) - nvl(cpl.repair_lead_time,0) - trunc(sysdate)), -1, 100, 200), 4310, decode(sign(trunc(cpd.plan_date) - nvl(cpl.newbuy_lead_time, 0) - trunc(sysdate)), -1, 100, 200), 8110, 300, 8120, 300, 8130, 300, 8210, 400, 8220, 400, 8310, 500, 8410, 600, 8420, 700, 8510, 800, 8610, 900, 8620, 900, 8630, 900) and fl.lookup_type (+) = 'CSP_PLAN_TYPE' and cpl.organization_id(+) = cpd.organization_id and cpl.inventory_item_id(+) = cpd.inventory_item_id and cpd.plan_detail_type in (4110, 4210, 4310, 8110, 8120, 8130, 8210, 8220, 8310, 8410, 8420, 8510, 8610, 8620, 8630) group by cpd.organization_id, cpd.inventory_item_id, cpd.plan_detail_type, fl.meaning, fl.lookup_code) a, cst_item_costs_for_gl_view cic, mtl_abc_assignments_v maav, mtl_system_items_kfv msibk where cic.inventory_item_id(+) = msibk.inventory_item_id and cic.organization_id(+) = msibk.organization_id and maav.organization_id(+) = msibk.organization_id and maav.inventory_item_id(+) = msibk.inventory_item_id and msibk.inventory_item_id = a.inventory_item_id (+) and msibk.organization_id = a.organization_id (+) group by msibk.organization_id, msibk.inventory_item_id, msibk.concatenated_segments, msibk.description, msibk.planner_code, abc_class_id , abc_class_name, cic.item_cost, plan_order_type_code, plan_order_type

View Text - HTML Formatted

SELECT MSIBK.INVENTORY_ITEM_ID
, MSIBK.ORGANIZATION_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.PLANNER_CODE
, MAAV.ABC_CLASS_ID
, MAAV.ABC_CLASS_NAME
, PLAN_ORDER_TYPE_CODE
, PLAN_ORDER_TYPE
, NVL(SUM(EXCESS_QTY)
, 0) EXCESS_QTY
, NVL(SUM(REPAIR_QTY)
, 0) REPAIR_QTY
, NVL(SUM(NEWBUY_QTY)
, 0) NEW_BUY_QTY
, NVL(ROUND(SUM(EXCESS_QTY) * CIC.ITEM_COST
, 2)
, 0) EXCESS_VALUE
, NVL(ROUND(SUM(REPAIR_QTY) * CIC.ITEM_COST
, 2)
, 0) REPAIR_VALUE
, NVL(ROUND(SUM(NEWBUY_QTY) * CIC.ITEM_COST
, 2)
, 0) NEW_BUY_VALUE
, TRUNC(MIN(PLAN_DATE)) PLAN_DATE
FROM ( SELECT CPD.INVENTORY_ITEM_ID
, CPD.ORGANIZATION_ID
, CPD.PLAN_DETAIL_TYPE
, MIN(CPD.PLAN_DATE) PLAN_DATE
, SUM(DECODE(FL.LOOKUP_CODE
, 200
, DECODE(PLAN_DETAIL_TYPE
, 4110
, QUANTITY
, NULL)
, 300
, DECODE(PLAN_DETAIL_TYPE
, 8130
, QUANTITY
, NULL)
, 900
, DECODE(PLAN_DETAIL_TYPE
, 8630
, QUANTITY
, NULL)
, 100
, DECODE(PLAN_DETAIL_TYPE
, 4110
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.EXCESS_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, QUANTITY
, NULL))
, NULL)) EXCESS_QTY
, SUM(DECODE(FL.LOOKUP_CODE
, 200
, DECODE(PLAN_DETAIL_TYPE
, 4210
, QUANTITY
, NULL)
, 300
, DECODE(PLAN_DETAIL_TYPE
, 8120
, QUANTITY
, NULL)
, 400
, DECODE(PLAN_DETAIL_TYPE
, 8220
, QUANTITY
, NULL)
, 900
, DECODE(PLAN_DETAIL_TYPE
, 8620
, QUANTITY
, NULL)
, 100
, DECODE(PLAN_DETAIL_TYPE
, 4210
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.REPAIR_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, QUANTITY
, NULL) )
, NULL)) REPAIR_QTY
, SUM(DECODE(FL.LOOKUP_CODE
, 200
, DECODE(PLAN_DETAIL_TYPE
, 4310
, QUANTITY
, NULL)
, 300
, DECODE(PLAN_DETAIL_TYPE
, 8110
, QUANTITY
, NULL)
, 400
, DECODE(PLAN_DETAIL_TYPE
, 8210
, QUANTITY
, NULL)
, 500
, DECODE(PLAN_DETAIL_TYPE
, 8310
, QUANTITY
, NULL)
, 600
, DECODE(PLAN_DETAIL_TYPE
, 8410
, QUANTITY
, NULL)
, 700
, DECODE(PLAN_DETAIL_TYPE
, 8420
, QUANTITY
, NULL)
, 800
, DECODE(PLAN_DETAIL_TYPE
, 8510
, QUANTITY
, NULL)
, 900
, DECODE(PLAN_DETAIL_TYPE
, 8610
, QUANTITY
, NULL)
, 100
, DECODE(PLAN_DETAIL_TYPE
, 4310
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.NEWBUY_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, QUANTITY
, NULL))
, NULL)) NEWBUY_QTY
, FL.LOOKUP_CODE PLAN_ORDER_TYPE_CODE
, FL.MEANING PLAN_ORDER_TYPE
FROM CSP_PLAN_DETAILS CPD
, CSP_PLAN_LEADTIMES CPL
, FND_LOOKUPS FL
WHERE FL.LOOKUP_CODE = DECODE(CPD.PLAN_DETAIL_TYPE
, 4110
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.EXCESS_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, 100
, 200)
, 4210
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.REPAIR_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, 100
, 200)
, 4310
, DECODE(SIGN(TRUNC(CPD.PLAN_DATE) - NVL(CPL.NEWBUY_LEAD_TIME
, 0) - TRUNC(SYSDATE))
, -1
, 100
, 200)
, 8110
, 300
, 8120
, 300
, 8130
, 300
, 8210
, 400
, 8220
, 400
, 8310
, 500
, 8410
, 600
, 8420
, 700
, 8510
, 800
, 8610
, 900
, 8620
, 900
, 8630
, 900)
AND FL.LOOKUP_TYPE (+) = 'CSP_PLAN_TYPE'
AND CPL.ORGANIZATION_ID(+) = CPD.ORGANIZATION_ID
AND CPL.INVENTORY_ITEM_ID(+) = CPD.INVENTORY_ITEM_ID
AND CPD.PLAN_DETAIL_TYPE IN (4110
, 4210
, 4310
, 8110
, 8120
, 8130
, 8210
, 8220
, 8310
, 8410
, 8420
, 8510
, 8610
, 8620
, 8630) GROUP BY CPD.ORGANIZATION_ID
, CPD.INVENTORY_ITEM_ID
, CPD.PLAN_DETAIL_TYPE
, FL.MEANING
, FL.LOOKUP_CODE) A
, CST_ITEM_COSTS_FOR_GL_VIEW CIC
, MTL_ABC_ASSIGNMENTS_V MAAV
, MTL_SYSTEM_ITEMS_KFV MSIBK
WHERE CIC.INVENTORY_ITEM_ID(+) = MSIBK.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID(+) = MSIBK.ORGANIZATION_ID
AND MAAV.ORGANIZATION_ID(+) = MSIBK.ORGANIZATION_ID
AND MAAV.INVENTORY_ITEM_ID(+) = MSIBK.INVENTORY_ITEM_ID
AND MSIBK.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID (+)
AND MSIBK.ORGANIZATION_ID = A.ORGANIZATION_ID (+) GROUP BY MSIBK.ORGANIZATION_ID
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.PLANNER_CODE
, ABC_CLASS_ID
, ABC_CLASS_NAME
, CIC.ITEM_COST
, PLAN_ORDER_TYPE_CODE
, PLAN_ORDER_TYPE