VIEW: APPS.POA_EDW_CUSTOM_MEASURE_FCV

Source

View Text - Preformatted

SELECT inc.seq_id seq_id, 1 view_id, pms.evaluation_score_id || '-' || TO_CHAR(gpm.accounting_date, 'dd-mm-yyyy') || '-' || eli.instance_code cstm_msr_pk, eli.instance_code instance_fk, pme.custom_measure_code || '-' || 'CUSTOM MEASURE' || '-' || 'PO' custom_measure_fk, pms.criteria_code || '-' || 'SATISFACTION CRITERIA' || '-' || 'PO' criteria_code_fk, EDW_TIME_PKG.cal_day_fk(gpm.accounting_date, fsp.set_of_books_id, eli.instance_code) eval_date_fk, DECODE(pme.supplier_site_id, NULL, EDW_TRD_PARTNER_PKG.supplier_fk (pme.supplier_id, eli.instance_code), EDW_TRD_PARTNER_PKG.supplier_site_fk (pme.supplier_site_id, pme.org_id, eli.instance_code)) supplier_site_fk, EDW_ORGANIZATION_PKG.operating_unit_fk (pme.oper_unit_id, eli.instance_code) operating_unit_fk, DECODE(pme.item_id, NULL, DECODE(pme.category_id, NULL, 'NA_EDW', EDW_ITEMS_PKG.category_fk (2, 2, pme.category_id, eli.instance_code)), EDW_ITEMS_PKG.item_org_fk(pme.item_id, DECODE(pme.item_id, NULL, pme.org_id, fsp.inventory_organization_id), NULL, to_number(NULL), eli.instance_code)) item_fk, pms.score score, pms.weight weight, (pms.score-pms.min_score)/(pms.max_score-pms.min_score)*pms.weight weighted_score, pms.min_score min_score, pms.max_score max_score, GREATEST(pms.last_update_date, fsp.last_update_date, pme.last_update_date, gpm.last_update_date) last_update_date, fu.user_name user_name, pme.comments eval_comments, pms.comments score_comments, NULL operation_code, NULL DUNS_FK, NULL UNSPSC_FK, NULL SIC_CODE_FK, NULL user_attribute1, NULL user_attribute2, NULL user_attribute3, NULL user_attribute4, NULL user_attribute5, NULL user_attribute6, NULL user_attribute7, NULL user_attribute8, NULL user_attribute9, NULL user_attribute10, NULL user_attribute11, NULL user_attribute12, NULL user_attribute13, NULL user_attribute14, NULL user_attribute15, NULL user_fk1, NULL user_fk2, NULL user_fk3, NULL user_fk4, NULL user_fk5, to_number(NULL) user_measure1, to_number(NULL) user_measure2, to_number(NULL) user_measure3, to_number(NULL) user_measure4, to_number(NULL) user_measure5, pme.evaluation_id evaluation_id FROM poa_edw_cstm_msr_inc inc, poa_cm_eval_scores pms, poa_cm_evaluation pme, gl_date_period_map gpm, fnd_user fu, gl_sets_of_books gsob, financials_system_params_all fsp, edw_local_instance eli WHERE inc.primary_key = pms.evaluation_score_id AND pms.evaluation_id = pme.evaluation_id AND NVL(fsp.org_id, -999) = NVL(pme.org_id, -999) AND gsob.set_of_books_id = fsp.set_of_books_id AND gpm.period_set_name = gsob.period_set_name AND gpm.period_type = pme.period_type AND gpm.period_name = pme.period_name AND fu.user_id (+)= pme.evaluated_by

View Text - HTML Formatted

SELECT INC.SEQ_ID SEQ_ID
, 1 VIEW_ID
, PMS.EVALUATION_SCORE_ID || '-' || TO_CHAR(GPM.ACCOUNTING_DATE
, 'DD-MM-YYYY') || '-' || ELI.INSTANCE_CODE CSTM_MSR_PK
, ELI.INSTANCE_CODE INSTANCE_FK
, PME.CUSTOM_MEASURE_CODE || '-' || 'CUSTOM MEASURE' || '-' || 'PO' CUSTOM_MEASURE_FK
, PMS.CRITERIA_CODE || '-' || 'SATISFACTION CRITERIA' || '-' || 'PO' CRITERIA_CODE_FK
, EDW_TIME_PKG.CAL_DAY_FK(GPM.ACCOUNTING_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE) EVAL_DATE_FK
, DECODE(PME.SUPPLIER_SITE_ID
, NULL
, EDW_TRD_PARTNER_PKG.SUPPLIER_FK (PME.SUPPLIER_ID
, ELI.INSTANCE_CODE)
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (PME.SUPPLIER_SITE_ID
, PME.ORG_ID
, ELI.INSTANCE_CODE)) SUPPLIER_SITE_FK
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK (PME.OPER_UNIT_ID
, ELI.INSTANCE_CODE) OPERATING_UNIT_FK
, DECODE(PME.ITEM_ID
, NULL
, DECODE(PME.CATEGORY_ID
, NULL
, 'NA_EDW'
, EDW_ITEMS_PKG.CATEGORY_FK (2
, 2
, PME.CATEGORY_ID
, ELI.INSTANCE_CODE))
, EDW_ITEMS_PKG.ITEM_ORG_FK(PME.ITEM_ID
, DECODE(PME.ITEM_ID
, NULL
, PME.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, NULL
, TO_NUMBER(NULL)
, ELI.INSTANCE_CODE)) ITEM_FK
, PMS.SCORE SCORE
, PMS.WEIGHT WEIGHT
, (PMS.SCORE-PMS.MIN_SCORE)/(PMS.MAX_SCORE-PMS.MIN_SCORE)*PMS.WEIGHT WEIGHTED_SCORE
, PMS.MIN_SCORE MIN_SCORE
, PMS.MAX_SCORE MAX_SCORE
, GREATEST(PMS.LAST_UPDATE_DATE
, FSP.LAST_UPDATE_DATE
, PME.LAST_UPDATE_DATE
, GPM.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, FU.USER_NAME USER_NAME
, PME.COMMENTS EVAL_COMMENTS
, PMS.COMMENTS SCORE_COMMENTS
, NULL OPERATION_CODE
, NULL DUNS_FK
, NULL UNSPSC_FK
, NULL SIC_CODE_FK
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, PME.EVALUATION_ID EVALUATION_ID
FROM POA_EDW_CSTM_MSR_INC INC
, POA_CM_EVAL_SCORES PMS
, POA_CM_EVALUATION PME
, GL_DATE_PERIOD_MAP GPM
, FND_USER FU
, GL_SETS_OF_BOOKS GSOB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, EDW_LOCAL_INSTANCE ELI
WHERE INC.PRIMARY_KEY = PMS.EVALUATION_SCORE_ID
AND PMS.EVALUATION_ID = PME.EVALUATION_ID
AND NVL(FSP.ORG_ID
, -999) = NVL(PME.ORG_ID
, -999)
AND GSOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND GPM.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME
AND GPM.PERIOD_TYPE = PME.PERIOD_TYPE
AND GPM.PERIOD_NAME = PME.PERIOD_NAME
AND FU.USER_ID (+)= PME.EVALUATED_BY