Wednesday, June 08, 2016

Purchasing Plan Report

SELECT DISTINCT
  msi.organization_code
, msi.organization_id
,msi.inventory_item_id
, sup.plan_id
,sup.sr_instance_id
, msi.item_name
, msi.abc_class mfg_code
, msi.buyer_name
, msi.description
, MIC.CATEGORY_NAME
,SUP.NEW_ORDER_PLACEMENT_DATE
,SUP.NEW_SCHEDULE_DATE
--,SUM(MD.USING_REQUIREMENT_QUANTITY)
,SUM(SUP.NEW_ORDER_QUANTITY )
, (MSI.PREPROCESSING_LEAD_TIME)
,(MSI.FULL_LEAD_TIME)
, (MSI.MINIMUM_ORDER_QUANTITY)
,(MSI.FIXED_LOT_MULTIPLIER)
,msi.uom_code uom
FROM
  MSC_SUPPLIES SUP
, msc_system_items msi
, msc_item_categories mic
, MSC_CATEGORY_SETS MCS
--, mtl_system_items_b@ASCPTEST_TO_PREPROD mtl
, MSC_PLANS MP
--, msc_demands md
WHERE
SUP.ORDER_TYPE             =5
AND
--SUP.SR_INSTANCE_ID       =MD.SR_INSTANCE_ID
--AND SUP.ORGANIZATION_ID      =MD.ORGANIZATION_ID
--AND SUP.PLAN_ID              =MD.PLAN_ID
--AND md.demand_type           =1
mic.sr_instance_id       = sup.sr_instance_id
AND mic.organization_id      = sup.organization_id
AND mic.inventory_item_id    = sup.inventory_item_id
AND msi.plan_id              = NVL (mp.base_plan_id, mp.plan_id)
AND sup.sr_instance_id       = msi.sr_instance_id
AND sup.ORGANIZATION_ID      = msi.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID    = MSI.INVENTORY_ITEM_ID
--AND msi.organization_id      =mtl.organization_id(+)
--AND msi.sr_inventory_item_id =mtl.inventory_item_id(+)
  /*--AND msi.organization_code   IN ('EBS:AWH','EBS:JWH','AWEER','CPW')*/
AND MIC.CATEGORY_SET_ID   =MCS.CATEGORY_SET_ID
AND mcs.CATEGORY_SET_NAME ='Purchasing'
AND mp.plan_id            = sup.plan_id
AND sup.plan_id           =NVL(:p_plan_id,sup.plan_id)
AND SUP.INVENTORY_ITEM_ID =NVL(:P_ITEM_ID,SUP.INVENTORY_ITEM_ID )
AND MIC.SR_CATEGORY_ID    =NVL(:P_CATEGORY,MIC.SR_CATEGORY_ID)
--and  (sup.new_schedule_date  ) between nvl(:CP_FROM_DUE,sup.new_schedule_date)  and nvl(:CP_TO_DUE,sup.new_schedule_date)
and  (sup.NEW_ORDER_PLACEMENT_DATE  ) between nvl(:CP_FROM_ORDER,sup.NEW_ORDER_PLACEMENT_DATE)  and nvl(:CP_TO_ORDER,sup.NEW_ORDER_PLACEMENT_DATE)
--AND trunc(SUP.NEW_ORDER_PLACEMENT_DATE) BETWEEN TRUNC(NVL(:P_ORDER_FROM,SUP.NEW_ORDER_PLACEMENT_DATE)) AND TRUNC(NVL(:P_ORDER_TO,SUP.NEW_ORDER_PLACEMENT_DATE))
--AND trunc(SUP.NEW_SCHEDULE_DATE) BETWEEN TRUNC(NVL(:P_DUE_FROM,SUP.NEW_SCHEDULE_DATE)) AND TRUNC(NVL(:P_DUE_TO,SUP.NEW_SCHEDULE_DATE))
&CP_ORG_CODE
GROUP BY
  msi.organization_code
, msi.organization_id
,msi.inventory_item_id
, sup.plan_id
, msi.item_name
, msi.abc_class
, msi.buyer_name
, msi.description
, MIC.CATEGORY_NAME
,SUP.NEW_ORDER_PLACEMENT_DATE
,SUP.NEW_SCHEDULE_DATE
, MSI.PREPROCESSING_LEAD_TIME
,MSI.FULL_LEAD_TIME
, MSI.MINIMUM_ORDER_QUANTITY
,MSI.FIXED_LOT_MULTIPLIER
--,mtl.primary_unit_of_measure
,SUP.SR_INSTANCE_ID
,msi.uom_code
order by msi.organization_code desc, SUP.NEW_ORDER_PLACEMENT_DATE asc, msi.item_name asc
;