VIEW: APPS.ISC_MSC_PURCHASING_V

Source

View Text - Preformatted

SELECT sum(pur.purchasing_cost) PURCHASING_COST, pur.plan_id PLAN_ID, pl.owning_org_id OWNING_ORG_ID, pur.sr_instance_id||'-'||pur.organization_id ORGANIZATION_ID, NVL(to_char(ic.category_set_id),'NA') CATEGORY_SET_ID, NVL(ic.category_set_id||ic.category_name,'NA') CATEGORY_ID, pur.inventory_item_id ITEM_ID, period.ID PERIOD_ID, qtr.ID QUARTER_ID, year.ID YEAR_ID, pur.supplier_id SUPPLIER_ID FROM isc_msc_purchasing_base_v pur, msc_item_categories ic, isc_msc_bis_plans_v pl, isc_msc_bis_periods_v period, isc_msc_bis_quarters_v qtr, isc_msc_bis_years_v year WHERE pur.new_schedule_date between period.start_date and period.end_date AND pl.id = pur.plan_id AND ic.inventory_item_id(+) = pur.inventory_item_id AND ic.organization_id(+) = pur.organization_id AND ic.sr_instance_id(+) = pur.sr_instance_id AND period.organization_id = pl.owning_org_id AND qtr.organization_id = period.organization_id AND qtr.period_year = period.period_year AND period.start_date between qtr.start_date and qtr.end_date AND year.organization_id = period.organization_id AND year.period_year = period.period_year AND pur.purchasing_cost <> 0 GROUP BY pur.plan_id, pl.owning_org_id,pur.sr_instance_id, pur.organization_id,ic.category_set_id,ic.category_name,pur.inventory_item_id,period.id, qtr.id,year.id,pur.supplier_id

View Text - HTML Formatted

SELECT SUM(PUR.PURCHASING_COST) PURCHASING_COST
, PUR.PLAN_ID PLAN_ID
, PL.OWNING_ORG_ID OWNING_ORG_ID
, PUR.SR_INSTANCE_ID||'-'||PUR.ORGANIZATION_ID ORGANIZATION_ID
, NVL(TO_CHAR(IC.CATEGORY_SET_ID)
, 'NA') CATEGORY_SET_ID
, NVL(IC.CATEGORY_SET_ID||IC.CATEGORY_NAME
, 'NA') CATEGORY_ID
, PUR.INVENTORY_ITEM_ID ITEM_ID
, PERIOD.ID PERIOD_ID
, QTR.ID QUARTER_ID
, YEAR.ID YEAR_ID
, PUR.SUPPLIER_ID SUPPLIER_ID
FROM ISC_MSC_PURCHASING_BASE_V PUR
, MSC_ITEM_CATEGORIES IC
, ISC_MSC_BIS_PLANS_V PL
, ISC_MSC_BIS_PERIODS_V PERIOD
, ISC_MSC_BIS_QUARTERS_V QTR
, ISC_MSC_BIS_YEARS_V YEAR
WHERE PUR.NEW_SCHEDULE_DATE BETWEEN PERIOD.START_DATE
AND PERIOD.END_DATE
AND PL.ID = PUR.PLAN_ID
AND IC.INVENTORY_ITEM_ID(+) = PUR.INVENTORY_ITEM_ID
AND IC.ORGANIZATION_ID(+) = PUR.ORGANIZATION_ID
AND IC.SR_INSTANCE_ID(+) = PUR.SR_INSTANCE_ID
AND PERIOD.ORGANIZATION_ID = PL.OWNING_ORG_ID
AND QTR.ORGANIZATION_ID = PERIOD.ORGANIZATION_ID
AND QTR.PERIOD_YEAR = PERIOD.PERIOD_YEAR
AND PERIOD.START_DATE BETWEEN QTR.START_DATE
AND QTR.END_DATE
AND YEAR.ORGANIZATION_ID = PERIOD.ORGANIZATION_ID
AND YEAR.PERIOD_YEAR = PERIOD.PERIOD_YEAR
AND PUR.PURCHASING_COST <> 0 GROUP BY PUR.PLAN_ID
, PL.OWNING_ORG_ID
, PUR.SR_INSTANCE_ID
, PUR.ORGANIZATION_ID
, IC.CATEGORY_SET_ID
, IC.CATEGORY_NAME
, PUR.INVENTORY_ITEM_ID
, PERIOD.ID
, QTR.ID
, YEAR.ID
, PUR.SUPPLIER_ID