Product: | GMD - Process Manufacturing Product Development |
---|---|
Description: | Item Effectivity view |
Implementation/DBA Data: | APPS.FM_FORM_EFF_VW3_OLD |
SELECT DISTINCT GR.RECIPE_ID
, GRV.RECIPE_VALIDITY_RULE_ID FMEFF_ID
, GRV.VALIDITY_RULE_STATUS
, GS.STATUS_TYPE
, DECODE(GRV.ORGN_CODE
, NULL
, GRV.LAB_TYPE
, GRV.ORGN_CODE) ORGN_CODE
, GRV.ITEM_ID
, GRV.RECIPE_USE FORMULA_USE
, NVL(GRV.END_DATE
, TO_DATE(FND_PROFILE.VALUE_WNPS('SY$EFF_MAX_DATE')
, 'YYYY/MM/DD HH24:MI:SS')) END_DATE
, GRV.START_DATE
, GRV.INV_MIN_QTY
, GRV.INV_MAX_QTY
, GRV.MIN_QTY
, GRV.MAX_QTY
, GRV.STD_QTY
, GRV.ITEM_UM
, GRV.PREFERENCE
, GR.ROUTING_ID
, GR.FORMULA_ID
, GRC.CUSTOMER_ID CUST_ID
, GRV.CREATION_DATE
, GRV.LAST_UPDATE_DATE
, GRV.CREATED_BY
, GRV.LAST_UPDATED_BY
, GRV.DELETE_MARK
, GRV.TEXT_CODE
, 0 TRANS_CNT
, GRV.LAST_UPDATE_LOGIN
FROM GMD_RECIPES_B GR
, GMD_RECIPE_VALIDITY_RULES GRV
, GMD_STATUS_B GS
, GMD_RECIPE_CUSTOMERS GRC
WHERE (GRV.VALIDITY_RULE_STATUS = '700' OR GRV.VALIDITY_RULE_STATUS = '900')
AND GR.RECIPE_ID = GRV.RECIPE_ID
AND GRV.VALIDITY_RULE_STATUS = GS.STATUS_CODE
AND GR.RECIPE_ID = GRC.RECIPE_ID(+)