[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select gpm.co_code co_code,
c.period_start_date period_start_date,
c.period_end_date period_end_date,
c.period_num period_num,
c.quarter_num quarter_num,
c.period_year period_year,
c.period_name period_name,
c.quarter_name quarter_name,
c.year_name year_name ,
c.period_set_name period_set_name,
d.last_start_date last_start_date,
d.last_end_date last_end_date
FROM
( select a.set_of_books_name set_of_books_name ,
a.period_num period_num,
a.period_start_date cur_start_date,
a.period_end_Date cur_end_Date,
b.period_start_date last_start_date,
b.period_end_Date last_end_date
from PMI_GL_TIME_V a,PMI_GL_TIME_V b
where a.set_of_books_name = b.set_of_books_name and
sysdate between a.period_start_date and a.period_end_Date AND
p_last_run_date between b.period_start_date and b.period_end_Date
) d,
PMI_GL_TIME_V c,
GL_PLCY_MST gpm
where c.set_of_books_name = d.set_of_books_name
and gpm.set_of_books_name = c.set_of_books_name
and c.period_start_date >= d.last_start_date
and c.period_start_date <= sysdate
order by gpm.co_code,c.period_start_date desc;
/* Delete data for the current processing period from summary table and insert summary data
into summary table using transaction tables.
*/
DELETE PMI_PROD_SUM
WHERE co_Code = cldr_rec.co_code
AND period_set_name = cldr_rec.period_Set_name
AND period_name = cldr_rec.period_name;
INSERT INTO PMI_PROD_SUM(FISCAL_YEAR
,CO_CODE
,ORGN_CODE
,WHSE_CODE
,PERIOD_ID
,QUARTER
,PERIOD
,CONVERTIBLE_UOM
,ITEM_ID
,ITEM_NO
,ITEM_UM
,WHSE_USAGE_QTY
,WHSE_USAGE_CONV
,WHSE_USAGE_VALUE
,WHSE_YIELD_QTY
,WHSE_YIELD_CONV
,WHSE_YIELD_VALUE
,LOG_END_DATE
,PERIOD_IND
,WHSE_NAME
,ORGN_NAME
,PERIOD_NAME
,QUARTER_NAME
,PERIOD_SET_NAME
,GL_PERIOD_YEAR
,GL_FISCAL_YEAR_NAME
,GL_PERIOD_START_DATE
,GL_PERIOD_END_DATE
,MISS_CONV_FACT_CNT)
(SELECT cldr_rec.period_year FISCAL_YEAR
,cldr_rec.co_code CO_CODE
,org.ORGN_CODE
,whs.WHSE_CODE
,cldr_rec.period_num PERIOD_ID
,cldr_rec.quarter_num QUARTER
,cldr_rec.period_num PERIOD
,PV_conv_uom CONVERTIBLE_UOM
,iim.ITEM_ID
,iim.ITEM_NO
,iim.ITEM_UM
,prod_tr.usage_qty WHSE_USAGE_QTY
,decode(PV_conv_uom,iim.item_um,prod_tr.usage_qty ,
gmicuom.i2uom_cv(iim.item_id,0,iim.item_um,prod_tr.usage_qty ,PV_conv_uom)) WHSE_USAGE_CONV
,prod_tr.usage_qty *
pmi_common_pkg.PMICO_GET_COST(iim.item_id,whs.whse_code,null,cldr_rec.period_start_date) WHSE_USAGE_VALUE
,prod_tr.yield_qty WHSE_YIELD_QTY
,decode(PV_conv_uom,iim.item_um,prod_tr.yield_qty ,
gmicuom.i2uom_cv(iim.item_id,0,iim.item_um,prod_tr.yield_qty ,PV_conv_uom)) WHSE_YIELD_CONV
,prod_tr.yield_qty *
pmi_common_pkg.PMICO_GET_COST(iim.item_id,whs.whse_code,null,cldr_rec.period_start_date) WHSE_YIELD_VALUE
,p_log_end_date LOG_END_DATE
,0 PERIOD_IND
,whs.WHSE_NAME
,org.ORGN_NAME
,cldr_rec.period_name PERIOD_NAME
,cldr_rec.quarter_name QUARTER_NAME
,cldr_rec.period_set_name PERIOD_SET_NAME
,cldr_rec.period_year GL_PERIOD_YEAR
,cldr_rec.year_name GL_FISCAL_YEAR_NAME
,cldr_rec.period_start_date GL_PERIOD_START_DATE
,cldr_rec.period_end_date GL_PERIOD_END_DATE
,0 MISS_CONV_FACT_CNT
FROM (SELECT orgn_code,whse_code,item_id,sum(yield_qty) yield_qty,sum(usage_qty) usage_qty
FROM (SELECT ORGN_CODE,WHSE_CODE,ITEM_ID,decode(line_type,1,trans_qty,0) yield_qty,
DECODE(line_type,-1,trans_qty,0) usage_qty
FROM IC_TRAN_PND
WHERE trunc(trans_date) between cldr_rec.period_start_date AND
cldr_rec.period_end_date
AND doc_type ='PROD'
AND completed_ind = 1 and delete_mark = 0
UNION ALL
SELECT ORGN_CODE,WHSE_CODE,ITEM_ID,decode(line_type,1,trans_qty,0) yield_qty,
DECODE(line_type,-1,trans_qty,0) usage_qty FROM IC_TRAN_CMP
WHERE trunc(trans_date) between cldr_rec.period_start_date AND
cldr_rec.period_end_date
AND doc_type = 'PROD')
group by orgn_code,whse_code,item_id) prod_tr,
IC_WHSE_MST whs,
SY_ORGN_MST org,
IC_ITEM_MST iim
WHERE org.co_code = cldr_rec.co_code
AND prod_tr.item_id = iim.item_id
AND prod_tr.whse_code = whs.whse_code
AND prod_tr.orgn_code = org.orgn_code );
SELECT LAST_RUN_DATE,ATTR1
FROM PMI_SUMMARY_LOG_TABLE
WHERE summary_table = 'PMI_PROD_SUM';
SELECT min(trans_date)
from (Select (min(trans_date)) trans_date
from IC_TRAN_PND
WHERE doc_type = 'PROD' AND (l_last_run_date IS NULL OR
(l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))
UNION ALL
(Select (min(trans_date)) trans_date
from IC_TRAN_CMP
WHERE DOC_TYPE = 'PROD' AND (l_last_run_date IS NULL OR
(l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))));
SELECT 1 into rows_exists
FROM PMI_PROD_SUM
WHERE ROWNUM = 1;
/* Delete data from summary table. now we need to populate using new conversion UOM */
buff32k := FND_MESSAGE.get_number('PMI','PMI_CONV_UOM_VAL_CHG') ||'-'||
FND_MESSAGE.get_string('PMI','PMI_CONV_UOM_VAL_CHG');
DELETE pmi_prod_sum;
UPDATE PMI_SUMMARY_LOG_TABLE
SET LAST_RUN_DATE = l_log_end_date,
ATTR1 = PV_conv_uom
WHERE summary_table = 'PMI_PROD_SUM';
INSERT INTO PMI_SUMMARY_LOG_TABLE (SUMMARY_TABLE,LAST_RUN_DATE,ATTR1,ATTR2,ATTR3,ATTR4,ATTR5,ATTR6)
VALUES ('PMI_PROD_SUM',l_log_end_date,PV_conv_uom,null,null,null,null,null);
SELECT TABLE_OWNER INTO l_table_owner
FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'PMI_PROD_SUM';