[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from pmi_batch_status;
/* Insert records for Plan Start */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'1',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.PLAN_START_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'1',
sysdate;
/*Insert records for Plan Complete */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'3',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.PLAN_CMPLT_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'3',
sysdate;
/* Insert records for Actual Start */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'2',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and bh.batch_status > 1
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.ACTUAL_START_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'2',
sysdate;
/* Insert records for Actual Complete */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'4',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and bh.batch_status > 2
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.ACTUAL_CMPLT_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'4',
sysdate;
/* Insert records for Closed */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'5',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and bh.batch_status = 4
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.BATCH_CLOSE_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'5',
sysdate;
/* Insert records for Cancelled */
Insert into pmi_batch_status (CO_CODE,
PLANT_CODE,
WHSE_CODE,
FISCAL_YEAR,
PERIOD_NUM,
PERIOD_NAME,
ITEM_ID,
BATCH_STATUS_CODE,
BATCH_COUNT,
LAST_UPDATE_DATE)
select og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'6',
count(*),
sysdate
from gme_batch_header bh, gme_material_details bd, pmi_gl_calendar_v cl,sy_orgn_mst og
where bh.batch_id = bd.batch_id
and bh.batch_status = -1
and line_type = 1
and bh.plant_CODE = og.ORGN_CODE
and og.CO_CODE = cl.CO_CODE
AND trunc(bh.LAST_UPDATE_DATE) between
CL.start_date and CL.end_date
group by og.co_code,
bh.plant_code,
bh.wip_whse_code,
cl.period_year,
cl.period_num,
cl.period_name,
bd.item_id,
'6',
sysdate;
SELECT TABLE_OWNER INTO l_table_owner
FROM USER_SYNONYMS
WHERE SYNONYM_NAME = 'PMI_BATCH_STATUS';
SELECT nvl(max(trans_id),0) from ic_tran_cmp_vw1
where item_id = item_id_v
and lot_id = lot_id_v
and doc_type = 'GRDI'
and reason_code = fnd_profile.value('PMI$REASON_CODE');
SELECT QC_GRADE into qc_grade_v
from ic_tran_cmp_vw1 where trans_id = trans_id_vi;
SELECT QC_GRADE into qc_grade_v
from ic_tran_cmp_vw1 where trans_id = trans_id_v;
select count(*) into NO_OF_SAMPLES_v from GMD_SAMPLES
where batch_id = BATCH_ID_VI;
Select count(*) into COMPLET_SAMPLES_v
from GMD_SAMPLES Smp,
GMD_EVENT_SPEC_DISP EVT_SPEC_DISP,
GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
WHERE SMP.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID
AND EVT_SPEC_DISP.EVENT_SPEC_DISP_ID = SMP_SPEC_DISP.EVENT_SPEC_DISP_ID
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND SMP_SPEC_DISP.DISPOSITION IN ('4A','5AV','6RJ')
AND SMP.BATCH_ID = BATCH_ID_VI;
Select count(*) into PASSED_SAMPLES_V
from GMD_SAMPLES Smp,
GMD_EVENT_SPEC_DISP EVT_SPEC_DISP,
GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
WHERE SMP.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID
AND EVT_SPEC_DISP.EVENT_SPEC_DISP_ID = SMP_SPEC_DISP.EVENT_SPEC_DISP_ID
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND SMP_SPEC_DISP.DISPOSITION IN ('4A','5AV')
AND SMP.BATCH_ID = BATCH_ID_VI;
select count(distinct to_char(item_id)||to_char(TRANS_DATE,'dd/mm/yy hh24:mi:ss'))
into ADJUST_BATCH_V
from ic_tran_cmp_vw1
where doc_id = BATCH_ID_VI
and doc_type = 'PROD'
and reason_code = fnd_profile.value('PMI$REASON_CODE_BTCH_ADJ');
SELECT shift_start,shift_duration
FROM ps_schd_dtl sd, ps_schd_hdr sh,
mr_shcl_dtl sc, mr_shdy_dtl sl
WHERE sd.schedule_id = sh.schedule_id
AND sd.Calendar_id = sc.Calendar_id
AND sc.shopday_no = sl.shopday_no
AND sh.schedule = SCHEDULE_V
AND sd.orgn_code = PLANT_CODE_V
AND trunc(sc.calendar_date) = trunc(batch_day_V)
order by shift_start;
SELECT PLANT_CODE,ACTUAL_START_DATE,ACTUAL_CMPLT_DATE
into PLANT_CODE_V1,ACTUAL_START_DATE_V, ACTUAL_CMPLT_DATE_V
from gme_batch_header
where batch_id = batch_id_vi;
select count(*) into count2_v
from ps_schd_dtl sd, ps_schd_hdr sh
where sd.schedule_id = sh.schedule_id
and sh.schedule = SCHEDULE_VI
AND sd.orgn_code = PLANT_CODE_V1;
SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
BtchDtl.PLAN_QTY,ItemMst.ITEM_UM)*pmi_common_pkg.PMICO_GET_COST(
BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE))
into PLAN_ING_VALUE_V
FROM
GME_BATCH_HEADER BtchHdr,
GME_MATERIAL_DETAILS BtchDtl,
IC_ITEM_MST ItemMst
WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
AND BtchDtl.LINE_TYPE=-1;
SELECT sum(gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM,
BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*pmi_common_pkg.PMICO_GET_COST(
BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE))
into ACTUAL_ING_VALUE_V
FROM
GME_BATCH_HEADER BtchHdr,
GME_MATERIAL_DETAILS BtchDtl,
IC_ITEM_MST ItemMst
WHERE BtchHdr.BATCH_ID = BATCH_ID_VI
AND BtchHdr.BATCH_ID = BtchDtl.BATCH_ID
AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID
AND BtchDtl.LINE_TYPE=-1;