VIEW: APPS.CSP_PLAN_DETAILS_V

Source

View Text - Preformatted

SELECT sum(decode(sign(round(cpd.plan_date - (trunc(cpd.creation_date)))/cfp.period_size), -1, cpd.quantity, null)) Past, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 0, decode(sign(cpd.plan_date - trunc(cpd.creation_date)),-1,null,cpd.quantity), null)) Curr, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 1, cpd.quantity, null)) C1, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 2, cpd.quantity, null)) C2, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 3, cpd.quantity, null)) C3, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 4, cpd.quantity, null)) C4, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 5, cpd.quantity, null)) C5, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 6, cpd.quantity, null)) C6, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 7, cpd.quantity, null)) C7, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 8, cpd.quantity, null)) C8, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 9, cpd.quantity, null)) C9, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 10, cpd.quantity, null)) C10, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 11, cpd.quantity, null)) C11, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 12, cpd.quantity, null)) C12, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 13, cpd.quantity, null)) C13, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 14, cpd.quantity, null)) C14, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 15, cpd.quantity, null)) C15, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 16, cpd.quantity, null)) C16, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 17, cpd.quantity, null)) C17, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 18, cpd.quantity, null)) C18, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 19, cpd.quantity, null)) C19, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 20, cpd.quantity, null)) C20, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 21, cpd.quantity, null)) C21, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 22, cpd.quantity, null)) C22, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 23, cpd.quantity, null)) C23, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 24, cpd.quantity, null)) C24, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 25, cpd.quantity, null)) C25, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 26, cpd.quantity, null)) C26, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 27, cpd.quantity, null)) C27, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 28, cpd.quantity, null)) C28, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 29, cpd.quantity, null)) C29, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 30, cpd.quantity, null)) C30, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 31, cpd.quantity, null)) C31, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 32, cpd.quantity, null)) C32, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 33, cpd.quantity, null)) C33, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 34, cpd.quantity, null)) C34, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 35, cpd.quantity, null)) C35, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 36, cpd.quantity, null)) C36, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 37, cpd.quantity, null)) C37, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 38, cpd.quantity, null)) C38, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 39, cpd.quantity, null)) C39, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 40, cpd.quantity, null)) C40, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 41, cpd.quantity, null)) C41, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 42, cpd.quantity, null)) C42, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 43, cpd.quantity, null)) C43, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 44, cpd.quantity, null)) C44, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 45, cpd.quantity, null)) C45, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 46, cpd.quantity, null)) C46, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 47, cpd.quantity, null)) C47, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 48, cpd.quantity, null)) C48, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 49, cpd.quantity, null)) C49, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 50, cpd.quantity, null)) C50, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 51, cpd.quantity, null)) C51, sum(decode(trunc((cpd.plan_date - trunc(cpd.creation_date))/cfp.period_size), 52, cpd.quantity, null)) C52, cpd.parent_type, cpd.plan_detail_type, msibk.inventory_item_id, msibk.organization_id, msibk.concatenated_Segments item_number, decode(cpd.source_organization_id, null, fl.meaning, haou.name||decode(cpd.source_number, null, null, '.')||cpd.source_number) from csp_plan_details cpd, mtl_system_items_kfv msibk, csp_planning_parameters cpp, fnd_lookups fl, hr_all_organization_units haou, csp_plan_details cfp WHERE cpd.inventory_item_id = msibk.inventory_item_id and cpd.organization_id = msibk.organization_id and cpp.organization_id = cpd.organization_id and cpp.organization_type = 'W' and cfp.organization_id = cpd.organization_id and cfp.inventory_item_id = cpd.inventory_item_id and cfp.plan_detail_type = 1 and fl.lookup_type(+) = 'CSP_PLAN_DETAIL_TYPE' and fl.lookup_code(+) = cpd.plan_detail_type and haou.organization_id(+) = cpd.source_organization_id group by cpd.plan_detail_type, cpd.parent_type, cfp.plan_Detail_type, fl.meaning, msibk.inventory_item_id, msibk.organization_id, msibk.concatenated_segments, cpd.source_organization_id, haou.name, cpd.source_number order by plan_Detail_type, parent_type

View Text - HTML Formatted

SELECT SUM(DECODE(SIGN(ROUND(CPD.PLAN_DATE - (TRUNC(CPD.CREATION_DATE)))/CFP.PERIOD_SIZE)
, -1
, CPD.QUANTITY
, NULL)) PAST
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 0
, DECODE(SIGN(CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))
, -1
, NULL
, CPD.QUANTITY)
, NULL)) CURR
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 1
, CPD.QUANTITY
, NULL)) C1
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 2
, CPD.QUANTITY
, NULL)) C2
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 3
, CPD.QUANTITY
, NULL)) C3
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 4
, CPD.QUANTITY
, NULL)) C4
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 5
, CPD.QUANTITY
, NULL)) C5
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 6
, CPD.QUANTITY
, NULL)) C6
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 7
, CPD.QUANTITY
, NULL)) C7
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 8
, CPD.QUANTITY
, NULL)) C8
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 9
, CPD.QUANTITY
, NULL)) C9
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 10
, CPD.QUANTITY
, NULL)) C10
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 11
, CPD.QUANTITY
, NULL)) C11
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 12
, CPD.QUANTITY
, NULL)) C12
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 13
, CPD.QUANTITY
, NULL)) C13
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 14
, CPD.QUANTITY
, NULL)) C14
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 15
, CPD.QUANTITY
, NULL)) C15
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 16
, CPD.QUANTITY
, NULL)) C16
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 17
, CPD.QUANTITY
, NULL)) C17
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 18
, CPD.QUANTITY
, NULL)) C18
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 19
, CPD.QUANTITY
, NULL)) C19
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 20
, CPD.QUANTITY
, NULL)) C20
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 21
, CPD.QUANTITY
, NULL)) C21
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 22
, CPD.QUANTITY
, NULL)) C22
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 23
, CPD.QUANTITY
, NULL)) C23
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 24
, CPD.QUANTITY
, NULL)) C24
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 25
, CPD.QUANTITY
, NULL)) C25
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 26
, CPD.QUANTITY
, NULL)) C26
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 27
, CPD.QUANTITY
, NULL)) C27
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 28
, CPD.QUANTITY
, NULL)) C28
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 29
, CPD.QUANTITY
, NULL)) C29
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 30
, CPD.QUANTITY
, NULL)) C30
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 31
, CPD.QUANTITY
, NULL)) C31
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 32
, CPD.QUANTITY
, NULL)) C32
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 33
, CPD.QUANTITY
, NULL)) C33
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 34
, CPD.QUANTITY
, NULL)) C34
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 35
, CPD.QUANTITY
, NULL)) C35
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 36
, CPD.QUANTITY
, NULL)) C36
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 37
, CPD.QUANTITY
, NULL)) C37
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 38
, CPD.QUANTITY
, NULL)) C38
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 39
, CPD.QUANTITY
, NULL)) C39
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 40
, CPD.QUANTITY
, NULL)) C40
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 41
, CPD.QUANTITY
, NULL)) C41
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 42
, CPD.QUANTITY
, NULL)) C42
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 43
, CPD.QUANTITY
, NULL)) C43
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 44
, CPD.QUANTITY
, NULL)) C44
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 45
, CPD.QUANTITY
, NULL)) C45
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 46
, CPD.QUANTITY
, NULL)) C46
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 47
, CPD.QUANTITY
, NULL)) C47
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 48
, CPD.QUANTITY
, NULL)) C48
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 49
, CPD.QUANTITY
, NULL)) C49
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 50
, CPD.QUANTITY
, NULL)) C50
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 51
, CPD.QUANTITY
, NULL)) C51
, SUM(DECODE(TRUNC((CPD.PLAN_DATE - TRUNC(CPD.CREATION_DATE))/CFP.PERIOD_SIZE)
, 52
, CPD.QUANTITY
, NULL)) C52
, CPD.PARENT_TYPE
, CPD.PLAN_DETAIL_TYPE
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.ORGANIZATION_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM_NUMBER
, DECODE(CPD.SOURCE_ORGANIZATION_ID
, NULL
, FL.MEANING
, HAOU.NAME||DECODE(CPD.SOURCE_NUMBER
, NULL
, NULL
, '.')||CPD.SOURCE_NUMBER)
FROM CSP_PLAN_DETAILS CPD
, MTL_SYSTEM_ITEMS_KFV MSIBK
, CSP_PLANNING_PARAMETERS CPP
, FND_LOOKUPS FL
, HR_ALL_ORGANIZATION_UNITS HAOU
, CSP_PLAN_DETAILS CFP
WHERE CPD.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND CPD.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND CPP.ORGANIZATION_ID = CPD.ORGANIZATION_ID
AND CPP.ORGANIZATION_TYPE = 'W'
AND CFP.ORGANIZATION_ID = CPD.ORGANIZATION_ID
AND CFP.INVENTORY_ITEM_ID = CPD.INVENTORY_ITEM_ID
AND CFP.PLAN_DETAIL_TYPE = 1
AND FL.LOOKUP_TYPE(+) = 'CSP_PLAN_DETAIL_TYPE'
AND FL.LOOKUP_CODE(+) = CPD.PLAN_DETAIL_TYPE
AND HAOU.ORGANIZATION_ID(+) = CPD.SOURCE_ORGANIZATION_ID GROUP BY CPD.PLAN_DETAIL_TYPE
, CPD.PARENT_TYPE
, CFP.PLAN_DETAIL_TYPE
, FL.MEANING
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.ORGANIZATION_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPD.SOURCE_ORGANIZATION_ID
, HAOU.NAME
, CPD.SOURCE_NUMBER ORDER BY PLAN_DETAIL_TYPE
, PARENT_TYPE