VIEW: APPS.PMI_REP_PROD_STATUS_V

Source

View Text - Preformatted

SELECT org.co_code Company_ID, org.orgn_code Organization_id, glcldr.YEAR_ID YEAR_ID, glcldr.QUARTER_ID QUARTER_ID, glcldr.PERIOD_ID PERIOD_ID, count(distinct(decode(btch.batch_status,1,btch.batch_id,null))) pnd_batches, sum(decode(btch.batch_status,1,ipnd.trans_qty * -1 * pmi_common_pkg.PMICO_GET_COST(bdtl.item_id, ipnd.whse_code,fiscalplcy.GL_COST_MTHD,plan_cmplt_date),0)) pnd_value, count(distinct(decode(btch.batch_status,2,btch.batch_id,null))) wip_batches, sum(decode(btch.batch_status,2,ipnd.trans_qty * -1 * pmi_common_pkg.PMICO_GET_COST(bdtl.item_id, ipnd.whse_code,fiscalplcy.GL_COST_MTHD,plan_cmplt_date),0)) wip_value, count(distinct(decode(btch.batch_status,3,btch.batch_id,4,btch.batch_id,null))) cmp_batches, sum(decode(btch.batch_status,3,ipnd.trans_qty * -1 * pmi_common_pkg.PMICO_GET_COST(bdtl.item_id, ipnd.whse_code,fiscalplcy.GL_COST_MTHD,plan_cmplt_date), 4,ipnd.trans_qty * -1 * pmi_common_pkg.PMICO_GET_COST(bdtl.item_id, ipnd.whse_code,fiscalplcy.GL_COST_MTHD,plan_cmplt_date),0)) cmp_value from GME_BATCH_HEADER btch, GME_MATERIAL_DETAILS bdtl, sy_orgn_mst org, gl_plcy_mst fiscalplcy, ic_tran_pnd ipnd, PMI_GL_TIME_V glcldr WHERE btch.plant_code = org.orgn_code AND org.co_code = fiscalplcy.co_code AND btch.batch_id = bdtl.batch_id AND glcldr.set_of_books_name = fiscalplcy.SET_OF_BOOKS_NAME AND btch.batch_status in (1,2,3,4) AND btch.batch_type = 0 AND btch.batch_status > 0 AND bdtl.line_type = -1 AND ipnd.trans_qty <> 0 AND ipnd.doc_type = 'PROD' AND ipnd.doc_id = btch.batch_id AND ipnd.line_id = bdtl.material_detail_id AND trunc(decode(batch_status,1, plan_cmplt_date,2, actual_start_date, actual_cmplt_date)) between glcldr.PERIOD_start_date and glcldr.PERIOD_end_date AND 'TRUE' = PMI_SECURITY_PKG.show_record(org.orgn_code) group by org.co_code , org.orgn_code , glcldr.YEAR_ID , glcldr.QUARTER_ID , glcldr.PERIOD_ID

View Text - HTML Formatted

SELECT ORG.CO_CODE COMPANY_ID
, ORG.ORGN_CODE ORGANIZATION_ID
, GLCLDR.YEAR_ID YEAR_ID
, GLCLDR.QUARTER_ID QUARTER_ID
, GLCLDR.PERIOD_ID PERIOD_ID
, COUNT(DISTINCT(DECODE(BTCH.BATCH_STATUS
, 1
, BTCH.BATCH_ID
, NULL))) PND_BATCHES
, SUM(DECODE(BTCH.BATCH_STATUS
, 1
, IPND.TRANS_QTY * -1 * PMI_COMMON_PKG.PMICO_GET_COST(BDTL.ITEM_ID
, IPND.WHSE_CODE
, FISCALPLCY.GL_COST_MTHD
, PLAN_CMPLT_DATE)
, 0)) PND_VALUE
, COUNT(DISTINCT(DECODE(BTCH.BATCH_STATUS
, 2
, BTCH.BATCH_ID
, NULL))) WIP_BATCHES
, SUM(DECODE(BTCH.BATCH_STATUS
, 2
, IPND.TRANS_QTY * -1 * PMI_COMMON_PKG.PMICO_GET_COST(BDTL.ITEM_ID
, IPND.WHSE_CODE
, FISCALPLCY.GL_COST_MTHD
, PLAN_CMPLT_DATE)
, 0)) WIP_VALUE
, COUNT(DISTINCT(DECODE(BTCH.BATCH_STATUS
, 3
, BTCH.BATCH_ID
, 4
, BTCH.BATCH_ID
, NULL))) CMP_BATCHES
, SUM(DECODE(BTCH.BATCH_STATUS
, 3
, IPND.TRANS_QTY * -1 * PMI_COMMON_PKG.PMICO_GET_COST(BDTL.ITEM_ID
, IPND.WHSE_CODE
, FISCALPLCY.GL_COST_MTHD
, PLAN_CMPLT_DATE)
, 4
, IPND.TRANS_QTY * -1 * PMI_COMMON_PKG.PMICO_GET_COST(BDTL.ITEM_ID
, IPND.WHSE_CODE
, FISCALPLCY.GL_COST_MTHD
, PLAN_CMPLT_DATE)
, 0)) CMP_VALUE
FROM GME_BATCH_HEADER BTCH
, GME_MATERIAL_DETAILS BDTL
, SY_ORGN_MST ORG
, GL_PLCY_MST FISCALPLCY
, IC_TRAN_PND IPND
, PMI_GL_TIME_V GLCLDR
WHERE BTCH.PLANT_CODE = ORG.ORGN_CODE
AND ORG.CO_CODE = FISCALPLCY.CO_CODE
AND BTCH.BATCH_ID = BDTL.BATCH_ID
AND GLCLDR.SET_OF_BOOKS_NAME = FISCALPLCY.SET_OF_BOOKS_NAME
AND BTCH.BATCH_STATUS IN (1
, 2
, 3
, 4)
AND BTCH.BATCH_TYPE = 0
AND BTCH.BATCH_STATUS > 0
AND BDTL.LINE_TYPE = -1
AND IPND.TRANS_QTY <> 0
AND IPND.DOC_TYPE = 'PROD'
AND IPND.DOC_ID = BTCH.BATCH_ID
AND IPND.LINE_ID = BDTL.MATERIAL_DETAIL_ID
AND TRUNC(DECODE(BATCH_STATUS
, 1
, PLAN_CMPLT_DATE
, 2
, ACTUAL_START_DATE
, ACTUAL_CMPLT_DATE)) BETWEEN GLCLDR.PERIOD_START_DATE
AND GLCLDR.PERIOD_END_DATE
AND 'TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(ORG.ORGN_CODE) GROUP BY ORG.CO_CODE
, ORG.ORGN_CODE
, GLCLDR.YEAR_ID
, GLCLDR.QUARTER_ID
, GLCLDR.PERIOD_ID