The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- select clause and general from clause
L_Statement := L_Statement||
'BEGIN '||FND_GLOBAL.Newline||
'INSERT INTO mtl_short_chk_temp '||FND_GLOBAL.Newline||
'( seq_num '||FND_GLOBAL.Newline||
' ,organization_id '||FND_GLOBAL.Newline||
' ,inventory_item_id '||FND_GLOBAL.Newline||
' ,quantity_open '||FND_GLOBAL.Newline||
' ,uom_code '||FND_GLOBAL.Newline||
' ,object_type '||FND_GLOBAL.Newline||
' ,object_id '||FND_GLOBAL.Newline||
' ,object_detail_id '||FND_GLOBAL.Newline||
' ,last_updated_by '||FND_GLOBAL.Newline||
' ,last_update_login '||FND_GLOBAL.Newline||
' ,last_update_date '||FND_GLOBAL.Newline||
' ,created_by '||FND_GLOBAL.Newline||
' ,creation_date '||FND_GLOBAL.Newline||
') '||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
',WRO.organization_id '||FND_GLOBAL.Newline||
',WRO.inventory_item_id '||FND_GLOBAL.Newline||
',WRO.required_quantity-WRO.quantity_issued '||FND_GLOBAL.Newline||
',MSI.primary_uom_code '||FND_GLOBAL.Newline||
',1 '||FND_GLOBAL.Newline||
',WRO.wip_entity_id '||FND_GLOBAL.Newline||
',WRO.operation_seq_num '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',-1 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
'FROM wip_entities WE'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
',mtl_system_items MSI '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
',WRO.organization_id '||FND_GLOBAL.Newline||
',WRO.inventory_item_id '||FND_GLOBAL.Newline||
',WRO.required_quantity-WRO.quantity_issued '||FND_GLOBAL.Newline||
',MSI.primary_uom_code '||FND_GLOBAL.Newline||
',1 '||FND_GLOBAL.Newline||
',WRO.wip_entity_id '||FND_GLOBAL.Newline||
',WRO.operation_seq_num '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',-1 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
'FROM wip_entities WE'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
',mtl_system_items MSI '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
',WRO.organization_id '||FND_GLOBAL.Newline||
',WRO.inventory_item_id '||FND_GLOBAL.Newline||
',INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
' ( WRO.organization_id '||FND_GLOBAL.Newline||
' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
' , WRS.processing_work_days '||FND_GLOBAL.Newline||
' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
' , WRO.quantity_issued '||FND_GLOBAL.Newline||
' ) '||FND_GLOBAL.Newline||
',MSI.primary_uom_code '||FND_GLOBAL.Newline||
',2 '||FND_GLOBAL.Newline||
',WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
',WRO.operation_seq_num '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',-1 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
',mtl_system_items MSI '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL '||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
',WRO.organization_id '||FND_GLOBAL.Newline||
',WRO.inventory_item_id '||FND_GLOBAL.Newline||
',INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
' ( WRO.organization_id '||FND_GLOBAL.Newline||
' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
' , WRS.processing_work_days '||FND_GLOBAL.Newline||
' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
' , WRO.quantity_issued '||FND_GLOBAL.Newline||
' ) '||FND_GLOBAL.Newline||
',MSI.primary_uom_code '||FND_GLOBAL.Newline||
',2 '||FND_GLOBAL.Newline||
',WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
',WRO.operation_seq_num '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',-1 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
',0 '||FND_GLOBAL.Newline||
',sysdate '||FND_GLOBAL.Newline||
'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
',mtl_system_items MSI '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
'INSERT INTO mtl_short_chk_temp '||FND_GLOBAL.Newline||
'( seq_num '||FND_GLOBAL.Newline||
' ,organization_id '||FND_GLOBAL.Newline||
' ,inventory_item_id '||FND_GLOBAL.Newline||
' ,quantity_open '||FND_GLOBAL.Newline||
' ,uom_code '||FND_GLOBAL.Newline||
' ,object_type '||FND_GLOBAL.Newline||
' ,object_id '||FND_GLOBAL.Newline||
' ,object_detail_id '||FND_GLOBAL.Newline||
' ,last_updated_by '||FND_GLOBAL.Newline||
' ,last_update_login '||FND_GLOBAL.Newline||
' ,last_update_date '||FND_GLOBAL.Newline||
' ,created_by '||FND_GLOBAL.Newline||
' ,creation_date '||FND_GLOBAL.Newline||
') '||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
' ,SPL.warehouse_id '||FND_GLOBAL.Newline||
' ,SPL.inventory_item_id '||FND_GLOBAL.Newline||
' ,SUM(SPLD.requested_quantity-NVL(SPLD.shipped_quantity,0)) '||FND_GLOBAL.Newline||
' ,SL.unit_code '||FND_GLOBAL.Newline||
' ,4 '||FND_GLOBAL.Newline||
' ,SH.header_id '||FND_GLOBAL.Newline||
' ,SL.line_id '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,-1 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
'FROM so_headers SH '||FND_GLOBAL.Newline||
' ,so_lines SL '||FND_GLOBAL.Newline||
' ,so_line_details SLD '||FND_GLOBAL.Newline||
' ,so_picking_lines SPL '||FND_GLOBAL.Newline||
' ,so_picking_line_details SPLD '||FND_GLOBAL.Newline||
' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
'WHERE SPL.picking_header_id = 0 '||FND_GLOBAL.Newline||
'AND SPL.picking_line_id = SPLD.picking_line_id '||FND_GLOBAL.Newline||
'AND NVL(SPLD.released_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||' '||FND_GLOBAL.Newline||
'AND SPLD.requested_quantity > NVL(SPLD.shipped_quantity,0) '||FND_GLOBAL.Newline||
'AND SL.line_id = SPL.order_line_id '||FND_GLOBAL.Newline||
'AND SH.header_id = SL.header_id '||FND_GLOBAL.Newline||
'AND SLD.line_id = SL.line_id '||FND_GLOBAL.Newline||
'AND SL.ordered_quantity > NVL(SL.cancelled_quantity,0) '||FND_GLOBAL.Newline||
'AND (SPL.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
' OR L_Inventory_item_id IS NULL) '||FND_GLOBAL.Newline||
'AND SPL.warehouse_id = L_Organization_id '||FND_GLOBAL.Newline||
'AND SPL.inventory_item_id = MSI.inventory_item_id '||FND_GLOBAL.Newline||
'AND SPL.warehouse_id = MSI.organization_id '||FND_GLOBAL.Newline||
'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||') = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
'AND SL.service_parent_line_id IS NULL '||FND_GLOBAL.Newline||
'AND SL.open_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
'AND SLD.released_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
'GROUP BY '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
' ,SPL.warehouse_id '||FND_GLOBAL.Newline||
' ,SPL.inventory_item_id '||FND_GLOBAL.Newline||
' ,SL.unit_code '||FND_GLOBAL.Newline||
' ,4 '||FND_GLOBAL.Newline||
' ,SH.header_id '||FND_GLOBAL.Newline||
' ,SL.line_id '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,-1 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,sysdate; '||FND_GLOBAL.Newline||
'SELECT '||FND_GLOBAL.Newline||
' L_Seq_num '||FND_GLOBAL.Newline||
' ,wdd.organization_id '||FND_GLOBAL.Newline||
' ,wdd.inventory_item_id '||FND_GLOBAL.Newline||
' ,sum(wdd.requested_quantity) '||FND_GLOBAL.Newline||
' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
' ,4 '||FND_GLOBAL.Newline||
' ,wdd.source_header_id '||FND_GLOBAL.Newline||
' ,wdd.source_line_id '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,-1 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
'FROM wsh_delivery_details_ob_grp_v wdd '||FND_GLOBAL.Newline||
'WHERE wdd.released_status = '|| '''' ||'B' || '''' || FND_GLOBAL.Newline||
-- Fix bug 2115784, Notifications are sent to all planners
-- Added the following two line to make sure the shortage_temp records
-- are inserted only for specified org and item. Therefore, only the buyers
-- for the specified item will be notified.
-- Bug 2640828. Added nvl around inventory_item_id and
-- organization_id
' AND wdd.inventory_item_id = nvl(L_Inventory_item_id ,wdd.inventory_item_id)'||FND_GLOBAL.Newline||
' AND wdd.organization_id = nvl(L_Organization_id ,wdd.organization_id)'||FND_GLOBAL.Newline||
' GROUP BY ' ||
' L_Seq_num '||FND_GLOBAL.Newline||
' ,wdd.organization_id '||FND_GLOBAL.Newline||
' ,wdd.inventory_item_id '||FND_GLOBAL.Newline||
' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
' ,4 '||FND_GLOBAL.Newline||
' ,wdd.source_header_id '||FND_GLOBAL.Newline||
' ,wdd.source_line_id '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,-1 '||FND_GLOBAL.Newline||
' ,sysdate '||FND_GLOBAL.Newline||
' ,0 '||FND_GLOBAL.Newline||
' ,sysdate; ' || FND_GLOBAL.Newline||
-- select clause and general from clause
L_Statement := L_Statement||
'SELECT '||
'NVL(SUM(WRO.required_quantity-WRO.quantity_issued),0) '||FND_GLOBAL.Newline||
'INTO '||FND_GLOBAL.Newline||
' L_WIP_jobs_short_quantity '||FND_GLOBAL.Newline||
'FROM wip_entities WE'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
-- select clause and general from clause
L_Statement := L_Statement||
'SELECT'||FND_GLOBAL.Newline||
'NVL(SUM(INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
' ( WRO.organization_id '||FND_GLOBAL.Newline||
' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
' , WRS.processing_work_days '||FND_GLOBAL.Newline||
' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
' , WRO.quantity_issued '||FND_GLOBAL.Newline||
' ) '||FND_GLOBAL.Newline||
' ) '||FND_GLOBAL.Newline||
' , 0 '||FND_GLOBAL.Newline||
' ) '||FND_GLOBAL.Newline||
'INTO '||FND_GLOBAL.Newline||
' L_WIP_rep_short_quantity '||FND_GLOBAL.Newline||
'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
',wip_requirement_operations WRO '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
' ,mtl_parameters MP'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
' AND BCD1.calendar_date+NVL('||
TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
' FROM wip_operations WO'||FND_GLOBAL.Newline||
' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' NVL(SUM(DECODE(SL.unit_code, '||FND_GLOBAL.Newline||
' MSI.primary_uom_code, (SPLD.requested_quantity - NVL(SPLD.shipped_quantity,0)), '||FND_GLOBAL.Newline||
' INV_CONVERT.INV_UM_CONVERT ( '||FND_GLOBAL.Newline||
' SPL.inventory_item_id '||FND_GLOBAL.Newline||
' ,NULL '||FND_GLOBAL.Newline||
' ,(SPLD.requested_quantity - NVL(SPLD.shipped_quantity,0))'||FND_GLOBAL.Newline||
' ,SL.unit_code '||FND_GLOBAL.Newline||
' ,MSI.primary_uom_code '||FND_GLOBAL.Newline||
' ,NULL '||FND_GLOBAL.Newline||
' ,NULL ) ) ),0) '||FND_GLOBAL.Newline||
'INTO '||FND_GLOBAL.Newline||
' :oe_short_quantity '||FND_GLOBAL.Newline||
'FROM so_headers SH '||FND_GLOBAL.Newline||
' ,so_lines SL '||FND_GLOBAL.Newline||
' ,so_line_details SLD '||FND_GLOBAL.Newline||
' ,so_picking_lines SPL '||FND_GLOBAL.Newline||
' ,so_picking_line_details SPLD '||FND_GLOBAL.Newline||
' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
'WHERE SPL.picking_header_id = 0 '||FND_GLOBAL.Newline||
'AND SPL.picking_line_id = SPLD.picking_line_id '||FND_GLOBAL.Newline||
'AND NVL(SPLD.released_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||' '||FND_GLOBAL.Newline||
'AND SPLD.requested_quantity > NVL(SPLD.shipped_quantity,0) '||FND_GLOBAL.Newline||
'AND SL.line_id = SPL.order_line_id '||FND_GLOBAL.Newline||
'AND SH.header_id = SL.header_id '||FND_GLOBAL.Newline||
'AND SLD.line_id = SL.line_id '||FND_GLOBAL.Newline||
'AND SL.ordered_quantity > NVL(SL.cancelled_quantity,0) '||FND_GLOBAL.Newline||
'AND SPL.inventory_item_id = MSI.inventory_item_id '||FND_GLOBAL.Newline||
'AND MSI.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
'AND SPL.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
'AND SPLD.warehouse_id = L_Organization_id '||FND_GLOBAL.Newline||
'AND SL.service_parent_line_id IS NULL '||FND_GLOBAL.Newline||
'AND SL.open_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
'AND SLD.released_flag = '||''''||'Y'||''''||'; '||FND_GLOBAL.Newline;
'SELECT '||FND_GLOBAL.Newline||
' NVL(sum(DECODE(wdd.requested_quantity_uom, '||FND_GLOBAL.Newline||
' MSI.primary_uom_code, wdd.requested_quantity, '||FND_GLOBAL.Newline||
' INV_CONVERT.INV_UM_CONVERT ( '||FND_GLOBAL.Newline||
' wdd.inventory_item_id '||FND_GLOBAL.Newline||
' ,NULL '||FND_GLOBAL.Newline||
' ,wdd.requested_quantity '||FND_GLOBAL.Newline||
' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
' ,MSI.primary_uom_code '||FND_GLOBAL.Newline||
' ,NULL '||FND_GLOBAL.Newline||
' ,NULL ) ) ),0) '||FND_GLOBAL.Newline||
'INTO '||FND_GLOBAL.Newline||
' :oe_short_quantity '||FND_GLOBAL.Newline||
'FROM wsh_delivery_details_ob_grp_v wdd '||FND_GLOBAL.Newline||
' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
'WHERE wdd.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
'AND MSI.inventory_item_id = wdd.inventory_item_id '||FND_GLOBAL.Newline||
'AND MSI.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
'AND wdd.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
-- Fix bug 2101710, short alert appears even for not backordered sales order
-- added the following line to query only the backordered lines.
-- this is to make the where clauses consistent with the detail statements
'AND wdd.released_status = '|| '''' ||'B' || '''' || FND_GLOBAL.Newline||
'AND (wdd.requested_quantity IS NOT NULL ' ||FND_GLOBAL.Newline||
'AND wdd.requested_quantity > 0); '||FND_GLOBAL.Newline;
PROCEDURE InsertUpdate (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_short_stat_sum IN LONG,
p_short_stat_detail IN LONG
)
IS
CURSOR crsStatement ( p_organization_id NUMBER,
p_detail_sum_flag NUMBER ) IS
SELECT 1
FROM mtl_short_chk_statements
WHERE organization_id = p_organization_id
AND detail_sum_flag = p_detail_sum_flag;
L_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate';
INSERT INTO mtl_short_chk_statements (
organization_id,
detail_sum_flag,
short_statement,
last_updated_by,
last_update_login,
last_update_date,
created_by,
creation_date
)
VALUES (
p_organization_id,
1,
p_short_stat_detail,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate
);
UPDATE mtl_short_chk_statements
SET short_statement = p_short_stat_detail,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND detail_sum_flag = 1;
INSERT INTO mtl_short_chk_statements (
organization_id,
detail_sum_flag,
short_statement,
last_updated_by,
last_update_login,
last_update_date,
created_by,
creation_date
)
VALUES (
p_organization_id,
2,
p_short_stat_sum,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate
);
UPDATE mtl_short_chk_statements
SET short_statement = p_short_stat_sum,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND detail_sum_flag = 2;
InsertUpdate (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_organization_id,
p_short_stat_sum => L_Short_stat_sum,
p_short_stat_detail => L_Short_stat_detail
);