The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
CURSOR l_trail_space(l_table_owner varchar2) IS SELECT column_name
FROM all_tab_columns
WHERE table_name = 'WIP_DISCRETE_JOBS'
AND data_type = 'VARCHAR2'
AND owner = l_table_owner;
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
'select we.wip_entity_name Job , we.wip_entity_id JobId, we.organization_id OrganizationID, '||
' decode(wdj.status_type, '||
' 1,''Unreleased'', '||
' 3, ''Released'', '||
' 4, ''Complete'', '||
' 5, ''Complete NoCharge'', '||
' 6, ''On Hold'', '||
' 7, ''Cancelled'', '||
' 8, ''Pend Bill Load'', '||
' 9, ''Failed Bill Load'', '||
' 10, ''Pend Rtg Load'', '||
' 11, ''Failed Rtg Load'', '||
' 12, ''Closed'', '||
' 13, ''Pending- Mass Loaded'', '||
' 14, ''Pending Close'', '||
' 15, ''Failed Close'', '||
' wdj.status_type) Status, wdj.DATE_RELEASED, wdj.DATE_CLOSED '||
'from wip_entities we, '||
' wip_discrete_jobs wdj '||
'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
'and wdj.date_released is null '||
'and WDJ.STATUS_TYPE IN (3, 4, 5, 6, 14, 15) ';
'select substr(we.wip_entity_name, 1,15) Job, wdj.wip_entity_id JobId, we.organization_id OrganizationID, wdj.primary_item_id ItemId, wdj.start_quantity, '||
' wdj.quantity_completed, wdj.quantity_scrapped, wdj.net_quantity, wdj.creation_date '||
'from wip_discrete_jobs wdj, wip_entities we '||
'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
'and exists (select 1 '||
' from mtl_material_transactions mmt '||
' where mmt.transaction_source_type_id = 5 '||
' and mmt.transaction_source_id = wdj.wip_entity_id '||
' and mmt.organization_id = wdj.organization_id '||
' and mmt.inventory_item_id = wdj.primary_item_id '||
' and mmt.transaction_action_id in (31,32)) '||
'and quantity_completed <> (select sum(mmt.primary_quantity) '|| /*Bug 6049344: Replaced trx qty with primary qty*/
' from mtl_material_transactions mmt '||
' where mmt.transaction_source_type_id = 5 '||
' and mmt.transaction_source_id = wdj.wip_entity_id '||
' and mmt.organization_id = wdj.organization_id '||
' and mmt.inventory_item_id = wdj.primary_item_id '||
' and mmt.transaction_action_id in (31,32)) order by we.wip_entity_id ';
' select substr(we.wip_entity_name, 1, 30) Job,'||
' wop.wip_entity_id JobId, '||
' wop.organization_id OrganizationID, wop.repetitive_schedule_id ScheduleId, '||
' wop.operation_seq_num, '||
' wor.resource_id, '||
' wor.resource_seq_num, '||
' wop.first_unit_start_date Operation_Start, '||
' wop.last_unit_completion_date Operation_Completion, '||
' wor.start_date Resource_Start, '||
' wor.completion_date Resource_Completion'||
' from wip_operation_resources wor, '||
' wip_operations wop, '||
' wip_entities we '||
' where ' || we_dyn_where_clause || ' wop.wip_entity_id = wor.wip_entity_id '||
' and wop.organization_id = wor.organization_id '||
' and wop.operation_seq_num = wor.operation_seq_num '||
' and nvl(wor.REPETITIVE_SCHEDULE_ID, -1) = nvl(wop.REPETITIVE_SCHEDULE_ID, -1) '||
' and we.wip_entity_id = wop.wip_entity_id '||
' and ( (wop.first_unit_start_date > wor.start_date) '||
' or '||
' (wop.last_unit_completion_date < wor.completion_date)) order by wop.organization_id, wop.wip_entity_id ';
'select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, we.organization_id OrganizationID, wro.inventory_item_id, '||
' wro.operation_seq_num, wro.quantity_per_assembly, wro.required_quantity, '||
' wro.quantity_issued, Sum(mmt.primary_quantity)*(-1) Inventory_Quantity '||
'from wip_discrete_jobs wdj, wip_entities we, '||
' wip_requirement_operations wro, mtl_material_transactions mmt '||
'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
'AND wdj.organization_id = we.organization_id '||
'AND wdj.wip_entity_id = wro.wip_entity_id '||
'AND wdj.organization_id = wro.organization_id '||
'AND wdj.wip_entity_id = mmt.transaction_source_id '||
'AND wdj.organization_id = mmt.organization_id '||
'AND mmt.transaction_source_type_id = 5 '||
'AND mmt.operation_seq_num = wro.operation_seq_num '||
'AND mmt.inventory_item_id = wro.inventory_item_id '||
'AND mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
' HAVING Sum(mmt.primary_quantity) <> wro.quantity_issued*(-1) '||
'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), we.organization_id, wro.inventory_item_id, wro.operation_seq_num, '||
' wro.quantity_per_assembly, wro.required_quantity, wro.quantity_issued ';
'SELECT distinct substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, '||
' decode(wdj.status_type, '||
' 1,''Unreleased'', '||
' 3, ''Released'', '||
' 4, ''Complete'', '||
' 5, ''Complete NoCharge'', '||
' 6, ''On Hold'', '||
' 7, ''Cancelled'', '||
' 8, ''Pend Bill Load'', '||
' 9, ''Failed Bill Load'', '||
' 10, ''Pend Rtg Load'', '||
' 11, ''Failed Rtg Load'', '||
' 12, ''Closed'', '||
' 13, ''Pending- Mass Loaded'', '||
' 14, ''Pending Close'', '||
' 15, ''Failed Close'', '||
' wdj.status_type) Status '||
'FROM wip_discrete_jobs wdj, wip_entities we, '||
' wip_operations wo '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type = 1 '||
'AND wdj.wip_entity_id = wo.wip_entity_id '||
'AND wdj.organization_id = wo.organization_id '||
'AND (wo.quantity_in_queue <> 0 '||
' OR wo.quantity_running <> 0 '||
' OR wo.quantity_waiting_to_move <> 0 '||
' OR wo.quantity_scrapped <> 0 '||
' OR wo.quantity_rejected <> 0 '||
' OR wo.quantity_completed <> 0) ';
'SELECT distinct substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, ' ||
' decode(wdj.status_type, '||
' 1,''Unreleased'', '||
' 3, ''Released'', '||
' 4, ''Complete'', '||
' 5, ''Complete NoCharge'', '||
' 6, ''On Hold'', '||
' 7, ''Cancelled'', '||
' 8, ''Pend Bill Load'', '||
' 9, ''Failed Bill Load'', '||
' 10, ''Pend Rtg Load'', '||
' 11, ''Failed Rtg Load'', '||
' 12, ''Closed'', '||
' 13, ''Pending- Mass Loaded'', '||
' 14, ''Pending Close'', '||
' 15, ''Failed Close'', '||
' wdj.status_type) Status '||
'FROM wip_discrete_jobs wdj, wip_entities we, '||
' wip_operations wo '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (3,4) '||
'AND wdj.wip_entity_id = wo.wip_entity_id '||
'AND wdj.organization_id = wo.organization_id '||
'AND wo.quantity_in_queue = 0 '||
'AND wo.quantity_running = 0 '||
'AND wo.quantity_waiting_to_move = 0 '||
'AND wo.quantity_scrapped = 0 '||
'AND wo.quantity_rejected = 0 '||
'AND wo.quantity_completed = 0 ' ||
' AND ( wo.PREVIOUS_OPERATION_SEQ_NUM is null /*for first operation*/' ||
' OR 0 >= ' ||
' (Select sum(wo1.quantity_in_queue) + sum(wo1.quantity_running) + ' ||
' sum(quantity_waiting_to_move) + sum(quantity_scrapped) + sum(quantity_rejected) + '||
' sum(quantity_completed) from wip_operations wo1 ' ||
' WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND wo1.organization_id = wo.organization_id '||
'AND wo1.wip_entity_id = wo.wip_entity_id '||
'AND wo1.OPERATION_SEQ_NUM <= wo.PREVIOUS_OPERATION_SEQ_NUM ))';
sqltxt := ' SELECT substr(we.wip_entity_name, 1,20) Job, wop.wip_entity_id JobId , wop.organization_id, wop.operation_seq_num ' ||
' FROM WIP_OPERATIONS wop , wip_entities we, wip_discrete_jobs wdj ' ||
' WHERE ' || wdj_dyn_where_clause ||
' we.wip_entity_id = wdj.wip_entity_id ' ||
' and wdj.wip_entity_id = wop.wip_entity_id ' ||
' and ( ' ||
' ( wop.quantity_in_queue ' ||
' - Decode(Nvl(wop.PREVIOUS_OPERATION_SEQ_NUM, 0), 0, wdj.start_quantity,0) ' ||
' - ( SELECT Decode(Nvl(wop.PREVIOUS_OPERATION_SEQ_NUM, 0), 0,Sum(Nvl(OVERCOMPLETION_PRIMARY_QTY,0)),0) ' ||
' FROM wip_move_transactions wmt2 ' ||
' WHERE wmt2.wip_entity_id = wop.wip_entity_id ) ' ||
' <> ((SELECT SUM( DECODE(wop.operation_seq_num, ' ||
' wmt_rec.fm_operation_seq_num, ' ||
' -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
' 1,ROUND(wmt_rec.primary_quantity,6), ' ||
' 2,0,3,0,4,0,5,0 ' ||
' ),0) + ' ||
' DECODE(wop.operation_seq_num, ' ||
' wmt_rec.to_operation_seq_num, ' ||
' DECODE(wmt_rec.to_intraoperation_step_type, ' ||
' 1,ROUND(wmt_rec.primary_quantity,6), ' ||
' 2,0,3,0,4,0,5,0),0) ' ||
' ) ' ||
' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
' WHERE wop1.rowid = wop.ROWID ' ||
' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num) ) ' ||
' ) ' ||
' ) ' ||
' OR ' ||
' ( ' ||
' wop.quantity_running <> ' ||
' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
' 1,0, ' ||
' 2,ROUND(wmt_rec.primary_quantity,6), ' ||
' 3,0,4,0,5,0),0) + ' ||
' DECODE(wop.operation_seq_num, ' ||
' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
' 1,0, ' ||
' 2,ROUND(wmt_rec.primary_quantity,6), ' ||
' 3,0,4,0,5,0),0) ) ' ||
' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
' WHERE wop1.rowid = wop.ROWID ' ||
' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
' ) ' ||
' OR ' ||
' ( ' ||
' wop.quantity_waiting_to_move ' ||
' + Decode(Nvl(wop.next_operation_seq_num, 0), 0, wdj.quantity_completed , 0) ' ||
' <> (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
' 1,0,2,0, ' ||
' 3,ROUND(wmt_rec.primary_quantity,6), ' ||
' 4,0,5,0),0) + ' ||
' DECODE(wop.operation_seq_num, ' ||
' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
' 1,0,2,0, ' ||
' 3,ROUND(wmt_rec.primary_quantity,6), ' ||
' 4,0,5,0),0) ) ' ||
' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
' WHERE wop1.rowid = wop.ROWID ' ||
' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
' ) ' ||
' OR ' ||
' ( ' ||
' wop.quantity_rejected <> ' ||
' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
' 1,0,2,0,3,0, ' ||
' 4,ROUND(wmt_rec.primary_quantity,6), ' ||
' 5,0),0) + ' ||
' DECODE(wop.operation_seq_num, ' ||
' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
' 1,0,2,0,3,0, ' ||
' 4,ROUND(wmt_rec.primary_quantity,6), ' ||
' 5,0),0) ) ' ||
' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
' WHERE wop1.rowid = wop.ROWID ' ||
' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num) ) ' ||
' ) ' ||
' OR ' ||
' ( ' ||
' wop.quantity_scrapped <> ' ||
' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
' 1,0,2,0,3,0,4,0, ' ||
' 5,ROUND(wmt_rec.primary_quantity,6)),0) + ' ||
' DECODE(wop.operation_seq_num, ' ||
' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
' 1,0,2,0,3,0,4,0, ' ||
' 5,ROUND(wmt_rec.primary_quantity,6)),0) ) ' ||
' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
' WHERE wop1.rowid = wop.ROWID ' ||
' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
' ) ' ||
' OR ' ||
' ( wop.quantity_completed ' ||
' <> (SELECT NVL(SUM(wti.primary_quantity * ' ||
' DECODE(sign(wti.to_operation_seq_num-wti.fm_operation_seq_num), ' ||
' 0,DECODE(sign(wti.fm_intraoperation_step_type-2), ' ||
' 0,DECODE(sign(wti.to_intraoperation_step_type-2), ' ||
' 0,-1, ' ||
' -1,-1, ' ||
' 1,1), ' ||
' -1,DECODE(sign(wti.to_intraoperation_step_type-2), ' ||
' 0,-1,-1,-1,1,1), ' ||
' 1,-1), ' ||
' 1, 1, ' ||
' -1,-1)),0) ' ||
' FROM WIP_OPERATIONS wop1, WIP_MOVE_TRANSACTIONS wti ' ||
' WHERE wop1.rowid = wop.rowid ' ||
' AND wop1.organization_id = wti.organization_id ' ||
' AND wop1.wip_entity_id = wti.wip_entity_id ' ||
' AND ( ' ||
' (wop1.operation_seq_num >= wti.fm_operation_seq_num ' ||
' + DECODE(sign(wti.fm_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
' AND wop1.operation_seq_num < wti.to_operation_seq_num ' ||
' + DECODE(sign(wti.to_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
' AND (wti.to_operation_seq_num > wti.fm_operation_seq_num ' ||
' OR (wti.to_operation_seq_num = wti.fm_operation_seq_num ' ||
' AND wti.fm_intraoperation_step_type<=2 ' ||
' AND wti.to_intraoperation_step_type>2)) ' ||
' AND (wop1.count_point_type < 3 ' ||
' OR wop1.operation_seq_num = wti.fm_operation_seq_num ' ||
' OR (wop1.operation_seq_num = wti.to_operation_seq_num ' ||
' AND wti.to_intraoperation_step_type > 2))) ' ||
' OR ' ||
' (wop1.operation_seq_num < wti.fm_operation_seq_num ' ||
' + DECODE(sign(wti.fm_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
' AND wop1.operation_seq_num >= wti.to_operation_seq_num ' ||
' + DECODE(sign(wti.to_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
' AND (wti.fm_operation_seq_num > wti.to_operation_seq_num ' ||
' OR (wti.fm_operation_seq_num = wti.to_operation_seq_num ' ||
' AND wti.to_intraoperation_step_type<=2 ' ||
' AND wti.fm_intraoperation_step_type>2)) ' ||
' AND (wop1.count_point_type < 3 ' ||
' OR (wop1.operation_seq_num = wti.to_operation_seq_num and wop1.count_point_type < 3 ) ' ||
' OR (wop1.operation_seq_num = wti.fm_operation_seq_num ' ||
' AND wti.fm_intraoperation_step_type > 2))) ' ||
' )) ' ||
' ) ' ||
' ) ' ;
'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_operations data exists'' Message '||
' FROM wip_operations wo ' ||
'WHERE ' || wo_dyn_where_clause ||
' NOT EXISTS (SELECT 1 FROM wip_entities we '||
' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
' AND wo.organization_id = we.organization_id) '||
'UNION '||
'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_requirement_operations data exists'' Message FROM wip_requirement_operations wo '||
'WHERE ' || wo_dyn_where_clause ||
' NOT EXISTS (SELECT 1 FROM wip_entities we '||
' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
' AND wo.organization_id = we.organization_id) '||
'UNION '||
'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_operation_resources data exists'' Message FROM wip_operation_resources wo '||
'WHERE ' || wo_dyn_where_clause ||
' NOT EXISTS (SELECT 1 FROM wip_entities we '||
' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
' AND wo.organization_id = we.organization_id)'||
'UNION '||
'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_period_balances data exists'' Message FROM wip_period_balances wo '||
'WHERE ' || wo_dyn_where_clause ||
' NOT EXISTS (SELECT 1 FROM wip_entities we '||
' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
' AND wo.organization_id = we.organization_id) '||
'UNION '||
'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_discrete_jobs data exists'' Message FROM wip_discrete_jobs wo '||
'WHERE ' || wo_dyn_where_clause ||
' NOT EXISTS (SELECT 1 FROM wip_entities we '||
' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
' AND wo.organization_id = we.organization_id) ';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wo.operation_seq_num, Count(*) '||
'FROM wip_discrete_jobs wdj, wip_entities we, '||
' wip_operations wo, wip_operation_resources wor '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3) '||
'AND wdj.wip_entity_id = wo.wip_entity_id '||
'AND wdj.organization_id = wo.organization_id '||
'AND wo.wip_entity_id = wor.wip_entity_id '||
'AND wo.organization_id = wor.organization_id '||
'AND wo.operation_seq_num = wor.operation_seq_num '||
'AND wor.autocharge_type = 4 '||
'HAVING Count(*) > 1 '||
'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), '||
' wdj.organization_id, wo.operation_seq_num ';
sqltxt:='SELECT 1 from dual where 1=2';
sqltext1:= 'SELECT nvl(Min('||
1 ||
' ),0) FROM wip_discrete_jobs wdj WHERE ' ||
'Length('||
i.column_name||
')<>Length(RTrim('||
i.column_name||
'))AND '||
wdj_dyn_where_clause||
'1=1';
sqltext:='select '||
sqltext||
' from wip_discrete_jobs wdj,all_tab_columns atc where '||
wdj_dyn_where_clause||
wdj_atc_where_clause||
' and owner= '''||
p_table_owner||
'''';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_inventories mi '||
' WHERE wdj.completion_subinventory = mi.secondary_inventory_name '||
' AND wdj.organization_id = mi.organization_id '||
' AND mi.secondary_inventory_name <> ''AX_INTRANS'' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, msik.concatenated_segments, '||
' wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we, mtl_system_items_kfv msik '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND msik.inventory_item_id = wdj.primary_item_id '||
'AND msik.organization_id = wdj.organization_id '||
'AND msik.restrict_subinventories_code = 1 '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_item_sub_inventories mi '||
' WHERE wdj.completion_subinventory = mi.secondary_inventory '||
' AND wdj.organization_id = mi.organization_id '||
' AND wdj.primary_item_id = mi.inventory_item_id) ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND inv_material_status_grp.is_status_applicable( '||
' NULL, NULL, 44, NULL, NULL, '||
' wdj.organization_id, wdj.primary_item_id, '||
' wdj.completion_subinventory, '||
' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND wma_special_lovs.locatorControl(wdj.organization_id, '||
' wdj.completion_subinventory, '||
' wdj.primary_item_id) <> 1 '||
'AND completion_locator_id IS NULL ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || 'we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND wma_special_lovs.locatorControl(wdj.organization_id, '||
' wdj.completion_subinventory, '||
' wdj.primary_item_id) = 1 '||
'AND completion_locator_id IS NOT NULL ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NULL '||
'AND wdj.completion_locator_id IS NOT NULL ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory, '||
' inv_project.get_locator(wdj.completion_locator_id, '||
' wdj.organization_id) Locator '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND wdj.completion_locator_id IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_item_locations mil '||
' WHERE wdj.completion_locator_id = mil.inventory_location_id '||
' AND wdj.organization_id = mil.organization_id '||
' AND wdj.completion_subinventory = mil.subinventory_code '||
' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory, msik.concatenated_segments, '||
' inv_project.get_locator(wdj.completion_locator_id, wdj.organization_id) Locator '||
'FROM wip_discrete_jobs wdj, wip_entities we, mtl_system_items_kfv msik '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND msik.inventory_item_id = wdj.primary_item_id '||
'AND msik.organization_id = wdj.organization_id '||
'AND msik.restrict_locators_code = 1 '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND wdj.completion_locator_id IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_locators msl '||
' WHERE wdj.completion_locator_id = msl.secondary_locator '||
' AND wdj.organization_id = msl.organization_id '||
' AND wdj.primary_item_id = msl.inventory_item_id) ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
' wdj.organization_id OrganizationID, wdj.completion_subinventory, '||
' inv_project.get_locator(wdj.completion_locator_id, wdj.organization_id) Locator '||
'FROM wip_discrete_jobs wdj, wip_entities we '||
'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
'AND we.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wdj.completion_subinventory IS NOT NULL '||
'AND wdj.completion_locator_id IS NOT NULL '||
'AND inv_material_status_grp.is_status_applicable( '||
' NULL, NULL, 44, NULL, NULL, '||
' wdj.organization_id, wdj.primary_item_id, '||
' wdj.completion_subinventory, wdj.completion_locator_id, '||
' NULL, NULL, ''Z'') <> ''Y'' ';
reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, '||
' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_inventories mi '||
' WHERE wro.supply_subinventory = mi.secondary_inventory_name '||
' AND wro.organization_id = mi.organization_id '||
' AND mi.secondary_inventory_name <> ''AX_INTRANS'' '||
' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId,wro.organization_id OrganizationID, msik.concatenated_segments, '||
' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
'FROM wip_discrete_jobs wdj,wip_requirement_operations wro, mtl_system_items_kfv msik '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND msik.inventory_item_id = wro.inventory_item_id '||
'AND msik.organization_id = wro.organization_id '||
'AND msik.restrict_subinventories_code = 1 '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_item_sub_inventories mi '||
' WHERE wro.supply_subinventory = mi.secondary_inventory '||
' AND wro.organization_id = mi.organization_id '||
' AND wro.inventory_item_id = mi.inventory_item_id) ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, '||
' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND inv_material_status_grp.is_status_applicable( '||
' NULL, NULL, 44, NULL, NULL, '||
' wro.organization_id, wro.inventory_item_id, '||
' wro.supply_subinventory, '||
' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND wma_special_lovs.locatorControl(wro.organization_id, '||
' wro.supply_subinventory, '||
' wro.inventory_item_id) <> 1 '||
'AND wro.supply_locator_id IS NULL ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND wma_special_lovs.locatorControl(wro.organization_id, '||
' wro.supply_subinventory, '||
' wro.inventory_item_id) = 1 '||
'AND wro.supply_locator_id IS NOT NULL ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NULL '||
'AND wro.supply_locator_id IS NOT NULL ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory, '||
' inv_project.get_locator(wro.supply_locator_id, '||
' wro.organization_id) Locator '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND wro.supply_locator_id IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_item_locations mil '||
' WHERE wro.supply_locator_id = mil.inventory_location_id '||
' AND wro.organization_id = mil.organization_id '||
' AND wro.supply_subinventory = mil.subinventory_code '||
' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory, msik.concatenated_segments, '||
' inv_project.get_locator(wro.supply_locator_id, wro.organization_id) Locator '||
'FROM wip_discrete_jobs wdj,wip_requirement_operations wro, mtl_system_items_kfv msik '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND msik.inventory_item_id = wro.inventory_item_id '||
'AND msik.organization_id = wro.organization_id '||
'AND msik.restrict_locators_code = 1 '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND wro.supply_locator_id IS NOT NULL '||
'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_locators msl '||
' WHERE wro.supply_locator_id = msl.secondary_locator '||
' AND wro.organization_id = msl.organization_id '||
' AND wro.inventory_item_id = msl.inventory_item_id) ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
' wro.inventory_item_id,wro.supply_subinventory, '||
' inv_project.get_locator(wro.supply_locator_id, wro.organization_id) Locator '||
'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
'AND wro.organization_id = wdj.organization_id '||
'AND wdj.status_type IN (1,3,4) '||
'AND wro.supply_subinventory IS NOT NULL '||
'AND wro.supply_locator_id IS NOT NULL '||
'AND inv_material_status_grp.is_status_applicable( '||
' NULL, NULL, 44, NULL, NULL, '||
' wro.organization_id, wro.inventory_item_id, '||
' wro.supply_subinventory, wro.supply_locator_id, '||
' NULL, NULL, ''Z'') <> ''Y'' ';
reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
sqltxt VARCHAR2(9999); -- SQL select statement
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
' (select wdj.wip_entity_id from wip_discrete_jobs wdj' ||
' where ' || wdj_dyn_where_clause || ' wdj.status_type = 15) ';
' select wip_entity_id JobID, organization_id OrganizationID, ''Pending MOVE Transactions Exists'' "Pending Txns.." '||
' from wip_move_txn_interface '||
' where ' || wg_dyn_where_clause || ' wip_entity_id in ' || l_check_failed_close_jobs ||
' UNION ALL '||
' select wip_entity_id JobID, organization_id OrganizationID, ''Pending RESOURCE Transactions Exists'' "Pending Txns.." '||
' from wip_cost_txn_interface '||
' where ' || wg_dyn_where_clause || ' wip_entity_id in ' || l_check_failed_close_jobs ||
' UNION ALL '||
' select transaction_source_id JobID, organization_id OrganizationID, ''Pending UNCOSTED Material Transactions Exists'' "Pending Txns.." '||
' from mtl_material_transactions '||
' where ' || wti_dyn_where_clause || ' transaction_source_type_id = 5 '||
' and costed_flag in (''N'',''E'') '||
' and transaction_source_id in ' || l_check_failed_close_jobs ||
' UNION ALL '||
' select transaction_source_id JobID, organization_id OrganizationID, ''Pending Material Transactions Exists'' "Pending Txns.." '||
' from mtl_material_transactions_temp mmtt '||
' where ' || wti_dyn_where_clause || ' transaction_source_type_id = 5 '||
' and transaction_source_id not in ( '||
' select txn_source_id '||
' from mtl_txn_request_lines '||
' where txn_source_id = mmtt.transaction_source_id '||
' and organization_id = mmtt.organization_id '||
' and line_status = 9) '||
' and transaction_source_id in ' || l_check_failed_close_jobs ||
' UNION ALL '||
' select wip_entity_id JobID, organization_id OrganizationID, ''Pending Operation Yields Exists'' "Pending Txns.." '||
' from wip_operation_yields '||
' where ' || wg_dyn_where_clause || ' status IN (1, 3) '||
' and wip_entity_id in ' || l_check_failed_close_jobs ||
' UNION ALL '||
' select we.wip_entity_id JobID, we.organization_id OrganizationID, ''Pending PUT-AWAY Transactions Exists'' "Pending Txns.." '||
' from wip_lpn_completions we, '||
' wms_license_plate_numbers lpn '||
' where ' || we_dyn_where_clause || ' we.lpn_id = lpn.lpn_id '||
' and lpn.lpn_context = 2 '||
' and wip_entity_id in ' || l_check_failed_close_jobs ;
' Select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, '||
' decode(wdj.status_type, '||
' 1,''Unreleased'', '||
' 3, ''Released'', '||
' 4, ''Complete'', '||
' 5, ''Complete NoCharge'', '||
' 6, ''On Hold'', '||
' 7, ''Cancelled'', '||
' 8, ''Pend Bill Load'', '||
' 9, ''Failed Bill Load'', '||
' 10, ''Pend Rtg Load'', '||
' 11, ''Failed Rtg Load'', '||
' 12, ''Closed'', '||
' 13, ''Pending- Mass Loaded'', '||
' 14, ''Pending Close'', '||
' 15, ''Failed Close'', '||
' wdj.status_type) status_type, '||
' decode(entity_type,1, ''1=Discrete Job'', '||
' 2, ''2=Repetitive Assly'', '||
' 3, ''3=Closed Discr Job'', '||
' 4, ''4=Flow Schedule'', '||
' 5, ''5=Lot Based Job'', '||
' entity_type) entity_type, '||
' wdj.creation_date, '||
' wdj.date_released, '||
' wdj.date_completed '||
' from wip_discrete_jobs wdj, '||
' wip_entities we '||
' where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
' and wdj.organization_id = we.organization_id '||
' and wdj.status_type <> 12 '||
' and we.entity_type in (3,7,8) -- closed DJ, closed EAM, closed LBJ '||
' order by 1,2 ' ;
'Jobs that are not closed but its entity_type updated to closed',true,null,'Y',row_limit);
reportStr := 'The rows returned above signify that there are jobs that are not closed but its entity_type updated to closed.';
' select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, '||
' decode(wdj.status_type, '||
' 1,''Unreleased'', '||
' 3, ''Released'', '||
' 4, ''Complete'', '||
' 5, ''Complete NoCharge'', '||
' 6, ''On Hold'', '||
' 7, ''Cancelled'', '||
' 8, ''Pend Bill Load'', '||
' 9, ''Failed Bill Load'', '||
' 10, ''Pend Rtg Load'', '||
' 11, ''Failed Rtg Load'', '||
' 12, ''Closed'', '||
' 13, ''Pending- Mass Loaded'', '||
' 14, ''Pending Close'', '||
' 15, ''Failed Close'', '||
' wdj.status_type) status_type, '||
' decode(entity_type,1, ''1=Discrete Job'', '||
' 2, ''2=Repetitive Assly'', '||
' 3, ''3=Closed Discr Job'', '||
' 4, ''4=Flow Schedule'', '||
' 5, ''5=Lot Based Job'', '||
' entity_type) entity_type, '||
' wdj.creation_date, '||
' wdj.date_released, '||
' wdj.date_completed '||
' from wip_discrete_jobs wdj, '||
' wip_entities we '||
' where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
' and wdj.organization_id = we.organization_id '||
' and wdj.status_type = 12 '||
' and we.entity_type not in (3,7,8) '||
' order by 1,2';
'Jobs that are closed but its entity_type not updated to closed',true,null,'Y',row_limit);
reportStr := 'The rows returned above signify that there are jobs that are closed but its entity_type not updated to closed.';