VIEW: APPS.PMIFV_INVENTORY_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT 'C', completedtrans.TRANS_ID, completedtrans.ITEM_ID, completedtrans.LINE_ID, completedtrans.CO_CODE, completedtrans.ORGN_CODE, completedtrans.WHSE_CODE, completedtrans.LOT_ID, completedtrans.LOCATION, completedtrans.DOC_ID, completedtrans.DOC_TYPE, completedtrans.DOC_LINE, completedtrans.LINE_TYPE, substr(pmi_cost_analz_pack.gmca_get_meaning('LINE_TYPE', completedtrans.LINE_TYPE),1,80), completedtrans.REASON_CODE, trunc(completedtrans.TRANS_DATE), decode(item.item_um,completedtrans.TRANS_UM,completedtrans.trans_qty, gmicuom.i2uom_cv(completedtrans.item_id,0,item.item_um,completedtrans.trans_qty, completedtrans.TRANS_UM)), completedtrans.TRANS_QTY2, completedtrans.QC_GRADE, completedtrans.LOT_STATUS, completedtrans.TRANS_STAT, completedtrans.TRANS_UM, completedtrans.TRANS_UM2, completedtrans.OP_CODE, completedtrans.GL_POSTED_IND, completedtrans.EVENT_ID, item.item_no, item.item_desc1, item.item_um, currcal.fiscal_year, currcal.period, currcal.period_desc, currcal.start_date, currcal.end_date, completedtrans.CREATED_BY, completedtrans.CREATION_DATE, completedtrans.LAST_UPDATE_DATE, completedtrans.LAST_UPDATED_BY FROM ic_tran_cmp completedtrans, ic_item_mst item, pmi_inv_calendar_v currcal WHERE completedtrans.item_id = item.item_id and completedtrans.co_code = currcal.orgn_code and trunc( completedtrans.trans_date) between currcal.start_date and currcal.end_date and (completedtrans.orgn_code is NULL or PMI_SECURITY_PKG.show_record(completedtrans.orgn_code) = 'TRUE') UNION ALL SELECT 'P', pendingtrans.TRANS_ID, pendingtrans.ITEM_ID, pendingtrans.LINE_ID, pendingtrans.CO_CODE, pendingtrans.ORGN_CODE, pendingtrans.WHSE_CODE, pendingtrans.LOT_ID, pendingtrans.LOCATION, pendingtrans.DOC_ID, pendingtrans.DOC_TYPE, pendingtrans.DOC_LINE, pendingtrans.LINE_TYPE, substr(pmi_cost_analz_pack.gmca_get_meaning('LINE_TYPE', pendingtrans.LINE_TYPE),1,80), pendingtrans.REASON_CODE, trunc(pendingtrans.TRANS_DATE), decode(item.item_um,pendingtrans.TRANS_UM,pendingtrans.trans_qty, gmicuom.i2uom_cv(pendingtrans.item_id,0,item.item_um,pendingtrans.trans_qty, pendingtrans.TRANS_UM)), pendingtrans.TRANS_QTY2, pendingtrans.QC_GRADE, pendingtrans.LOT_STATUS, pendingtrans.TRANS_STAT, pendingtrans.TRANS_UM, pendingtrans.TRANS_UM2, pendingtrans.OP_CODE, pendingtrans.GL_POSTED_IND, pendingtrans.EVENT_ID, item.item_no, item.item_desc1, item.item_um, currcal.fiscal_year, currcal.period, currcal.period_desc, currcal.start_date, currcal.end_date, pendingtrans.CREATED_BY, pendingtrans.CREATION_DATE, pendingtrans.LAST_UPDATE_DATE, pendingtrans.LAST_UPDATED_BY FROM ic_tran_pnd pendingtrans, ic_item_mst item, pmi_inv_calendar_v currcal WHERE pendingtrans.item_id = item.item_id and pendingtrans.completed_ind = 1 and pendingtrans.delete_mark = 0 and pendingtrans.co_code = currcal.orgn_code and trunc( pendingtrans.trans_date) between currcal.start_date and currcal.end_date and (pendingtrans.orgn_code is NULL or pendingtrans.orgn_code IN (SELECT secorg.orgn_code FROM sy_orgn_mst secorg WHERE PMI_SECURITY_PKG.show_record(secorg.orgn_code) = 'TRUE')) with read only

View Text - HTML Formatted

SELECT 'C'
, COMPLETEDTRANS.TRANS_ID
, COMPLETEDTRANS.ITEM_ID
, COMPLETEDTRANS.LINE_ID
, COMPLETEDTRANS.CO_CODE
, COMPLETEDTRANS.ORGN_CODE
, COMPLETEDTRANS.WHSE_CODE
, COMPLETEDTRANS.LOT_ID
, COMPLETEDTRANS.LOCATION
, COMPLETEDTRANS.DOC_ID
, COMPLETEDTRANS.DOC_TYPE
, COMPLETEDTRANS.DOC_LINE
, COMPLETEDTRANS.LINE_TYPE
, SUBSTR(PMI_COST_ANALZ_PACK.GMCA_GET_MEANING('LINE_TYPE'
, COMPLETEDTRANS.LINE_TYPE)
, 1
, 80)
, COMPLETEDTRANS.REASON_CODE
, TRUNC(COMPLETEDTRANS.TRANS_DATE)
, DECODE(ITEM.ITEM_UM
, COMPLETEDTRANS.TRANS_UM
, COMPLETEDTRANS.TRANS_QTY
, GMICUOM.I2UOM_CV(COMPLETEDTRANS.ITEM_ID
, 0
, ITEM.ITEM_UM
, COMPLETEDTRANS.TRANS_QTY
, COMPLETEDTRANS.TRANS_UM))
, COMPLETEDTRANS.TRANS_QTY2
, COMPLETEDTRANS.QC_GRADE
, COMPLETEDTRANS.LOT_STATUS
, COMPLETEDTRANS.TRANS_STAT
, COMPLETEDTRANS.TRANS_UM
, COMPLETEDTRANS.TRANS_UM2
, COMPLETEDTRANS.OP_CODE
, COMPLETEDTRANS.GL_POSTED_IND
, COMPLETEDTRANS.EVENT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, CURRCAL.FISCAL_YEAR
, CURRCAL.PERIOD
, CURRCAL.PERIOD_DESC
, CURRCAL.START_DATE
, CURRCAL.END_DATE
, COMPLETEDTRANS.CREATED_BY
, COMPLETEDTRANS.CREATION_DATE
, COMPLETEDTRANS.LAST_UPDATE_DATE
, COMPLETEDTRANS.LAST_UPDATED_BY
FROM IC_TRAN_CMP COMPLETEDTRANS
, IC_ITEM_MST ITEM
, PMI_INV_CALENDAR_V CURRCAL
WHERE COMPLETEDTRANS.ITEM_ID = ITEM.ITEM_ID
AND COMPLETEDTRANS.CO_CODE = CURRCAL.ORGN_CODE
AND TRUNC( COMPLETEDTRANS.TRANS_DATE) BETWEEN CURRCAL.START_DATE
AND CURRCAL.END_DATE
AND (COMPLETEDTRANS.ORGN_CODE IS NULL OR PMI_SECURITY_PKG.SHOW_RECORD(COMPLETEDTRANS.ORGN_CODE) = 'TRUE') UNION ALL SELECT 'P'
, PENDINGTRANS.TRANS_ID
, PENDINGTRANS.ITEM_ID
, PENDINGTRANS.LINE_ID
, PENDINGTRANS.CO_CODE
, PENDINGTRANS.ORGN_CODE
, PENDINGTRANS.WHSE_CODE
, PENDINGTRANS.LOT_ID
, PENDINGTRANS.LOCATION
, PENDINGTRANS.DOC_ID
, PENDINGTRANS.DOC_TYPE
, PENDINGTRANS.DOC_LINE
, PENDINGTRANS.LINE_TYPE
, SUBSTR(PMI_COST_ANALZ_PACK.GMCA_GET_MEANING('LINE_TYPE'
, PENDINGTRANS.LINE_TYPE)
, 1
, 80)
, PENDINGTRANS.REASON_CODE
, TRUNC(PENDINGTRANS.TRANS_DATE)
, DECODE(ITEM.ITEM_UM
, PENDINGTRANS.TRANS_UM
, PENDINGTRANS.TRANS_QTY
, GMICUOM.I2UOM_CV(PENDINGTRANS.ITEM_ID
, 0
, ITEM.ITEM_UM
, PENDINGTRANS.TRANS_QTY
, PENDINGTRANS.TRANS_UM))
, PENDINGTRANS.TRANS_QTY2
, PENDINGTRANS.QC_GRADE
, PENDINGTRANS.LOT_STATUS
, PENDINGTRANS.TRANS_STAT
, PENDINGTRANS.TRANS_UM
, PENDINGTRANS.TRANS_UM2
, PENDINGTRANS.OP_CODE
, PENDINGTRANS.GL_POSTED_IND
, PENDINGTRANS.EVENT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, CURRCAL.FISCAL_YEAR
, CURRCAL.PERIOD
, CURRCAL.PERIOD_DESC
, CURRCAL.START_DATE
, CURRCAL.END_DATE
, PENDINGTRANS.CREATED_BY
, PENDINGTRANS.CREATION_DATE
, PENDINGTRANS.LAST_UPDATE_DATE
, PENDINGTRANS.LAST_UPDATED_BY
FROM IC_TRAN_PND PENDINGTRANS
, IC_ITEM_MST ITEM
, PMI_INV_CALENDAR_V CURRCAL
WHERE PENDINGTRANS.ITEM_ID = ITEM.ITEM_ID
AND PENDINGTRANS.COMPLETED_IND = 1
AND PENDINGTRANS.DELETE_MARK = 0
AND PENDINGTRANS.CO_CODE = CURRCAL.ORGN_CODE
AND TRUNC( PENDINGTRANS.TRANS_DATE) BETWEEN CURRCAL.START_DATE
AND CURRCAL.END_DATE
AND (PENDINGTRANS.ORGN_CODE IS NULL OR PENDINGTRANS.ORGN_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) WITH READ ONLY