[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'DELETE FROM WIP_TEMP_REPORTS
WHERE PROGRAM_SOURCE = ''WIPREVAL''';
SELECT OOD.ORGANIZATION_NAME,
OOD.CHART_OF_ACCOUNTS_ID,
SOB.CURRENCY_CODE,
FC.EXTENDED_PRECISION,
FC.PRECISION,
RPT_RUN_OPT.MEANING,
MP.CALENDAR_CODE,
MP.CALENDAR_EXCEPTION_SET_ID,
ML1.MEANING,
ML2.MEANING
INTO ORG_NAME,
CHART_OF_ACCTS_ID,
CURRENCY_CODE,
EXT_PRECISION,
PRECISION,
REPORT_OPTION,
CALENDAR_CODE,
EXCEPTION_SET_ID,
C_Include_Bulk,
C_Include_Vendor
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS SOB,
ORG_ORGANIZATION_DEFINITIONS OOD,
MFG_LOOKUPS RPT_RUN_OPT,
MTL_PARAMETERS MP,
MFG_LOOKUPS ML1,
MFG_LOOKUPS ML2
WHERE OOD.ORGANIZATION_ID = ORG_ID
AND OOD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND MP.ORGANIZATION_ID = ORG_ID
AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
AND FC.ENABLED_FLAG = 'Y'
AND RPT_RUN_OPT.LOOKUP_TYPE = 'CST_WIP_VALUE_REPORT_TYPE'
AND RPT_RUN_OPT.LOOKUP_CODE = REPORT_RUN_OPT
AND ML1.LOOKUP_CODE = NVL(P_Include_Bulk,2)
AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML2.LOOKUP_CODE = NVL(P_Include_Vendor,2)
AND ML2.LOOKUP_TYPE = 'SYS_YES_NO';
SELECT NVL(MIN(WE.WIP_ENTITY_NAME),'X')
INTO FROM_ASSEMBLY
FROM WIP_ENTITIES WE
WHERE WE.ORGANIZATION_ID = ORG_ID
AND WE.ENTITY_TYPE = 2;
SELECT NVL(MAX(WE.WIP_ENTITY_NAME),'X')
INTO TO_ASSEMBLY
FROM WIP_ENTITIES WE
WHERE WE.ORGANIZATION_ID = ORG_ID
AND WE.ENTITY_TYPE = 2;
SELECT NVL(MIN(WRI.CLASS_CODE),'X')
INTO FROM_CLASS
FROM WIP_REPETITIVE_ITEMS WRI
WHERE WRI.ORGANIZATION_ID = ORG_ID;
SELECT NVL(MAX(WRI.CLASS_CODE),'X')
INTO TO_CLASS
FROM WIP_REPETITIVE_ITEMS WRI
WHERE WRI.ORGANIZATION_ID = ORG_ID;
SELECT NVL(MIN(WL.LINE_CODE),'X')
INTO FROM_LINE
FROM WIP_LINES WL
WHERE WL.ORGANIZATION_ID = ORG_ID;
SELECT NVL(MAX(WL.LINE_CODE),'X')
INTO TO_LINE
FROM WIP_LINES WL
WHERE WL.ORGANIZATION_ID = ORG_ID;
MODE="SELECT" DISPLAY="ALL"
TABLEALIAS="GCC"');*/
MODE="SELECT" DISPLAY="ALL"
TABLEALIAS="MSI"');*/
EXECUTE IMMEDIATE'delete from wip_temp_reports
where program_source = ''WIPREVAL''';
EXECUTE IMMEDIATE 'insert into wip_temp_reports
(organization_id,
program_source,
last_updated_by,
wip_entity_id,
key1,
key2,
key3,
key4,
key5,
attribute1,
date1,
date2)
select /*+ RULE */
wrs.organization_id,
''WIPREVAL'',
31,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
decode(sign(trunc(wrs.last_unit_start_date)-
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1)))),
0,
0,
''N'',to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
,
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
from
bom_calendar_dates bcd1,
bom_calendar_dates bcd2,
wip_entities we,
wip_lines wl,
wip_repetitive_items wri,
wip_repetitive_schedules wrs
where
bcd1.calendar_date =
decode(sign(trunc(wrs.last_unit_start_date) -
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, trunc(wrs.last_unit_start_date),
1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.last_unit_start_date))
and bcd2.calendar_date =
decode(sign(
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
trunc(wrs.first_unit_start_date)),
-1, trunc(wrs.first_unit_start_date),
1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.first_unit_start_date))
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
and we.entity_type = 2
and we.organization_id = :ORG_ID
and wrs.wip_entity_id = we.wip_entity_id
and wl.line_code between :FROM_LINE and :TO_LINE
and wl.organization_id = :ORG_ID
and wrs.line_id = wl.line_id
and wri.line_id = wrs.line_id
and wri.class_code between :FROM_CLASS and :TO_CLASS
and wri.organization_id = :ORG_ID
and wri.wip_entity_id = wrs.wip_entity_id
and wrs.organization_id = :ORG_ID
and ((trunc(wrs.first_unit_start_date) between
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
or (trunc(wrs.last_unit_start_date) between
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
or ((trunc(wrs.first_unit_start_date) <
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
and (trunc(wrs.last_unit_start_date) >
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))))
and not exists
(select mmta.repetitive_schedule_id
from mtl_material_txn_allocations mmta,
mtl_material_transactions mmt
where mmt.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt.transaction_date < to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt.transaction_id = mmta.transaction_id
and mmta.organization_id = mmt.organization_id
and mmt.organization_id = :ORG_ID
and mmt.transaction_source_id = we.wip_entity_id
and mmt.transaction_source_type_id + 0 = 5
and mmta.repetitive_schedule_id = wrs.repetitive_schedule_id)
and wrs.repetitive_schedule_id in
(select repetitive_schedule_id
from wip_period_balances
where repetitive_schedule_id = wrs.repetitive_schedule_id
and organization_id = wrs.organization_id
and wip_entity_id = wrs.wip_entity_id)
group by
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
wrs.last_unit_start_date,
wrs.processing_work_days,
(bcd1.prior_seq_num - bcd2.next_seq_num)'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID
, FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID , ORG_ID ;
EXECUTE IMMEDIATE 'insert into wip_temp_reports
(organization_id,
program_source,
last_updated_by,
wip_entity_id,
key1,
key2,
key3,
key4,
key5,
attribute1,
date1,
date2)
select /*+ RULE */
wrs.organization_id,
''WIPREVAL'',
31,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
decode(sign(trunc(wrs.last_unit_start_date)-
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))))),
1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
(bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
(decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1)))))),
sum(mmta1.primary_quantity),
sum(mmta2.primary_quantity),
''N'',
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
from
mtl_material_transactions mmt1,
mtl_material_txn_allocations mmta1,
mtl_material_transactions mmt2,
mtl_material_txn_allocations mmta2,
bom_calendar_dates bcd1,
bom_calendar_dates bcd2,
wip_lines wl,
wip_repetitive_items wri,
wip_repetitive_schedules wrs,
wip_entities we
where
bcd1.calendar_date =
decode(sign(trunc(wrs.last_unit_start_date) -
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, trunc(wrs.last_unit_start_date),
1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.last_unit_start_date))
and bcd2.calendar_date =
decode(sign(
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
trunc(wrs.first_unit_start_date)),
-1, trunc(wrs.first_unit_start_date),
1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.first_unit_start_date))
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and mmt1.transaction_action_id in (31,32)
and mmt1.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt1.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt1.transaction_id = mmta1.transaction_id
and mmta1.organization_id = mmt1.organization_id
and mmt1.organization_id = :ORG_ID
and mmt1.transaction_source_id = we.wip_entity_id
and mmt1.transaction_source_type_id + 0 = 5
and mmt2.transaction_source_id = we.wip_entity_id
and mmt2.transaction_source_type_id + 0 = 5
and mmt2.transaction_action_id = 30
and mmt2.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt2.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt2.transaction_id = mmta2.transaction_id
and mmta2.organization_id = mmt2.organization_id
and mmt2.organization_id = :ORG_ID
and mmta2.repetitive_schedule_id = wrs.repetitive_schedule_id
and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
and we.entity_type = 2
and we.organization_id = :ORG_ID
and wrs.wip_entity_id = we.wip_entity_id
and wl.line_code between :FROM_LINE and :TO_LINE
and wl.organization_id = :ORG_ID
and wrs.line_id = wl.line_id
and wri.line_id = wrs.line_id
and wri.class_code between :FROM_CLASS and :TO_CLASS
and wri.organization_id = :ORG_ID
and wri.wip_entity_id = wrs.wip_entity_id
and wrs.organization_id = :ORG_ID
and mmta1.repetitive_schedule_id = wrs.repetitive_schedule_id
group by mmta1.repetitive_schedule_id,
mmta2.repetitive_schedule_id,
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
wrs.last_unit_start_date,
wrs.processing_work_days,
(bcd1.prior_seq_num - bcd2.next_seq_num)'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID , ORG_ID , ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY , ORG_ID
, FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS, TO_CLASS , ORG_ID , ORG_ID ;
EXECUTE IMMEDIATE 'update wip_temp_reports wtr
set key4 =
(select sum(mmta1.primary_quantity)
from mtl_material_transactions mmt1,
mtl_material_txn_allocations mmta1
where mmt1.transaction_action_id in (31,32)
and mmt1.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt1.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt1.transaction_id = mmta1.transaction_id
and mmta1.organization_id = mmt1.organization_id
and mmt1.organization_id = :ORG_ID
and mmt1.transaction_source_id = wtr.wip_entity_id
and mmt1.transaction_source_type_id + 0 = 5
and mmta1.repetitive_schedule_id = wtr.key2)
where wtr.key4 <> 0
and wtr.key5 <> 0
and wtr.program_source = ''WIPREVAL'''
USING
ORG_ID;
EXECUTE IMMEDIATE 'update wip_temp_reports wtr
set key5 =
(select sum(mmta2.primary_quantity)
from mtl_material_transactions mmt2,
mtl_material_txn_allocations mmta2
where mmt2.transaction_action_id = 30
and mmt2.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt2.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt2.transaction_id = mmta2.transaction_id
and mmta2.organization_id = mmt2.organization_id
and mmt2.organization_id = :ORG_ID
and mmt2.transaction_source_id = wtr.wip_entity_id
and mmt2.transaction_source_type_id + 0 = 5
and mmta2.repetitive_schedule_id = wtr.key2)
where wtr.key4 <> 0
and wtr.key5 <> 0
and wtr.program_source = ''WIPREVAL'''
USING
ORG_ID;
EXECUTE IMMEDIATE 'insert into wip_temp_reports
(organization_id,
program_source,
last_updated_by,
wip_entity_id,
key1,
key2,
key3,
key4,
key5,
attribute1,
date1,
date2)
select /*+ RULE */
wrs.organization_id,
''WIPREVAL'',
31,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
decode(sign(trunc(wrs.last_unit_start_date)-
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))))),
1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
(bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
(decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1)))))),
sum(mmta1.primary_quantity),
0,
''N'',
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
from
mtl_material_transactions mmt1,
mtl_material_txn_allocations mmta1,
bom_calendar_dates bcd1,
bom_calendar_dates bcd2,
wip_lines wl,
wip_repetitive_schedules wrs,
wip_repetitive_items wri,
wip_entities we
where
bcd1.calendar_date =
decode(sign(trunc(wrs.last_unit_start_date) -
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, trunc(wrs.last_unit_start_date),
1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.last_unit_start_date))
and bcd2.calendar_date =
decode(sign(
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
trunc(wrs.first_unit_start_date)),
-1, trunc(wrs.first_unit_start_date),
1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.first_unit_start_date))
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and mmt1.transaction_action_id in (31,32)
and mmt1.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt1.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')+ 1
and mmt1.transaction_id = mmta1.transaction_id
and mmta1.organization_id = mmt1.organization_id
and mmt1.organization_id = :ORG_ID
and mmt1.transaction_source_id = we.wip_entity_id
and mmt1.transaction_source_type_id + 0 = 5
and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
and we.entity_type = 2
and we.organization_id = :ORG_ID
and wrs.wip_entity_id = we.wip_entity_id
and wl.line_code between :FROM_LINE and :TO_LINE
and wl.organization_id = :ORG_ID
and wrs.line_id = wl.line_id
and wri.line_id = wrs.line_id
and wri.class_code between :FROM_CLASS and :TO_CLASS
and wri.organization_id = :ORG_ID
and wri.wip_entity_id = wrs.wip_entity_id
and wrs.organization_id = :ORG_ID
and mmta1.repetitive_schedule_id = wrs.repetitive_schedule_id
and mmta1.repetitive_schedule_id not in
(select key2
from wip_temp_reports
where program_source = ''WIPREVAL'')
group by mmta1.repetitive_schedule_id,
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
wrs.last_unit_start_date,
wrs.processing_work_days,
(bcd1.prior_seq_num - bcd2.next_seq_num)'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID ;
EXECUTE IMMEDIATE 'insert into wip_temp_reports
(organization_id,
program_source,
last_updated_by,
wip_entity_id,
key1,
key2,
key3,
key4,
key5,
attribute1,
date1,
date2)
select /*+ RULE */
wrs.organization_id,
''WIPREVAL'',
31,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
decode(sign(trunc(wrs.last_unit_start_date)-
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))))),
1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
(bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
(decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1)))))),
0,
sum(mmta2.primary_quantity),
''N'',
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
from
mtl_material_transactions mmt2,
mtl_material_txn_allocations mmta2,
bom_calendar_dates bcd1,
bom_calendar_dates bcd2,
wip_lines wl,
wip_repetitive_schedules wrs,
wip_repetitive_items wri,
wip_entities we
where
bcd1.calendar_date =
decode(sign(trunc(wrs.last_unit_start_date) -
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, trunc(wrs.last_unit_start_date),
1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.last_unit_start_date))
and bcd2.calendar_date =
decode(sign(
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
trunc(wrs.first_unit_start_date)),
-1, trunc(wrs.first_unit_start_date),
1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.first_unit_start_date))
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and mmt2.transaction_action_id = 30
and mmt2.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt2.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt2.transaction_id = mmta2.transaction_id
and mmta2.organization_id = mmt2.organization_id
and mmt2.organization_id = :ORG_ID
and mmt2.transaction_source_id = we.wip_entity_id
and mmt2.transaction_source_type_id + 0 = 5
and mmta2.repetitive_schedule_id = wrs.repetitive_schedule_id
and mmta2.repetitive_schedule_id not in
(select key2
from wip_temp_reports
where program_source = ''WIPREVAL'')
and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
and we.entity_type = 2
and we.organization_id = :ORG_ID
and wrs.wip_entity_id = we.wip_entity_id
and wl.line_code between :FROM_LINE and :TO_LINE
and wl.organization_id = :ORG_ID
and wrs.line_id = wl.line_id
and wri.line_id = wrs.line_id
and wri.class_code between :FROM_CLASS and :TO_CLASS
and wri.organization_id = :ORG_ID
and wri.wip_entity_id = wrs.wip_entity_id
and wrs.organization_id = :ORG_ID
group by mmta2.repetitive_schedule_id,
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
wrs.last_unit_start_date,
wrs.processing_work_days,
(bcd1.prior_seq_num - bcd2.next_seq_num)'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID;
EXECUTE IMMEDIATE 'insert into wip_temp_reports
(organization_id,
program_source,
last_updated_by,
wip_entity_id,
key1,
key2,
key3,
key4,
key5,
attribute1,
date1,
date2)
select /*+ RULE */
wrs.organization_id,
''WIPREVAL'',
31,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
decode(sign(trunc(wrs.last_unit_start_date)-
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))))),
1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
(bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
(decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
-1, 0,
1, (bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1))),
(bcd1.prior_seq_num - bcd2.next_seq_num +
decode(mod(wrs.processing_work_days,1),0,1,
mod(wrs.processing_work_days,1)))))),
0,
0,
''Y'',
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
from
mtl_material_transactions mmt3,
mtl_material_txn_allocations mmta3,
bom_calendar_dates bcd1,
bom_calendar_dates bcd2,
wip_lines wl,
wip_repetitive_schedules wrs,
wip_repetitive_items wri,
wip_entities we
where
bcd1.calendar_date =
decode(sign(trunc(wrs.last_unit_start_date) -
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
-1, trunc(wrs.last_unit_start_date),
1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.last_unit_start_date))
and bcd2.calendar_date =
decode(sign(
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
trunc(wrs.first_unit_start_date)),
-1, trunc(wrs.first_unit_start_date),
1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
trunc(wrs.first_unit_start_date))
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and mmt3.transaction_action_id in (1,27,33,34)
and mmt3.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt3.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt3.transaction_id = mmta3.transaction_id
and mmta3.organization_id = mmt3.organization_id
and mmt3.organization_id = :ORG_ID
and mmt3.transaction_source_id = we.wip_entity_id
and mmt3.transaction_source_type_id + 0 = 5
and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
and mmta3.repetitive_schedule_id not in
(select key2
from wip_temp_reports
where program_source = ''WIPREVAL'')
and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
and we.entity_type = 2
and we.organization_id = :ORG_ID
and wrs.wip_entity_id = we.wip_entity_id
and wl.line_code between :FROM_LINE and :TO_LINE
and wl.organization_id = :ORG_ID
and wrs.line_id = wl.line_id
and wri.line_id = wrs.line_id
and wri.class_code between :FROM_CLASS and :TO_CLASS
and wri.organization_id = :ORG_ID
and wri.wip_entity_id = wrs.wip_entity_id
and wrs.organization_id = :ORG_ID
and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
group by mmta3.repetitive_schedule_id,
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id,
wrs.last_unit_start_date,
wrs.processing_work_days,
(bcd1.prior_seq_num - bcd2.next_seq_num)'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY , ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID;
EXECUTE IMMEDIATE 'update wip_temp_reports wtr
set attribute1 = ''Y''
where key2 in
(select /*+ RULE */
wrs.repetitive_schedule_id
from
wip_repetitive_schedules wrs,
mtl_material_transactions mmt3,
mtl_material_txn_allocations mmta3
where
mmt3.transaction_action_id in (1,27,33,34)
and mmt3.transaction_date >=
to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
and mmt3.transaction_date <
to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
and mmt3.transaction_id = mmta3.transaction_id
and mmta3.organization_id = mmt3.organization_id
and mmt3.organization_id = :ORG_ID
and mmt3.transaction_source_id = wrs.wip_entity_id
and mmt3.transaction_source_type_id + 0 = 5
and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
and wrs.repetitive_schedule_id = wtr.key2
and wrs.line_id = wtr.key1
and wrs.organization_id = :ORG_ID
group by mmta3.repetitive_schedule_id,
wrs.organization_id,
wrs.wip_entity_id,
wrs.line_id,
wrs.repetitive_schedule_id)
and attribute1 = ''N'''
USING
ORG_ID , ORG_ID;
EXECUTE IMMEDIATE 'update wip_temp_reports wtr
set wtr.key3 = (wtr.key3 - 1)
where wtr.program_source = ''WIPREVAL''
and wtr.key2 =
(select wrs.repetitive_schedule_id
from wip_repetitive_schedules wrs,
bom_calendar_dates bcd1,
bom_calendar_dates bcd2
where wrs.repetitive_schedule_id = wtr.key2
and wrs.organization_id = wtr.organization_id
and bcd1.calendar_date = trunc(wrs.last_unit_start_date)
and bcd2.calendar_date = trunc(wrs.first_unit_start_date)
and bcd1.calendar_code = :CALENDAR_CODE
and bcd2.calendar_code = :CALENDAR_CODE
and bcd1.exception_set_id = :EXCEPTION_SET_ID
and bcd2.exception_set_id = :EXCEPTION_SET_ID
and ((bcd1.prior_seq_num - bcd2.next_seq_num + 1)
> ceil(wrs.processing_work_days)))'
USING
CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID;
EXECUTE IMMEDIATE 'update wip_temp_reports wtr
set wtr.key6 =
(select wtr.key3 * wrs.daily_production_rate
from wip_repetitive_schedules wrs
where wrs.repetitive_schedule_id = wtr.key2)
where wtr.program_source = ''WIPREVAL''';