DBA Data[Home] [Help]

APPS.INV_SHORTSTATEMENT_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 118

		-- 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;
Line: 141

			'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;
Line: 183

       			' (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;
Line: 198

       			' (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;
Line: 223

       			' (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;
Line: 261

			' (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;
Line: 282

			' (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;
Line: 320

			'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;
Line: 361

       			' (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;
Line: 376

       			' (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;
Line: 401

       			' (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;
Line: 439

			' (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;
Line: 460

			' (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;
Line: 501

                        '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;
Line: 560

       			' (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;
Line: 584

       			' (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;
Line: 622

			' (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;
Line: 643

			' (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;
Line: 681

			'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;
Line: 739

       			' (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;
Line: 763

       			' (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;
Line: 801

			' (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;
Line: 822

			' (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;
Line: 866

			'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;
Line: 884

 			'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||
Line: 939

 			'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||
Line: 1139

		-- 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;
Line: 1166

       			' (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;
Line: 1181

       			' (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;
Line: 1206

       			' (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;
Line: 1244

			' (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;
Line: 1265

			' (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;
Line: 1304

		-- 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;
Line: 1353

       			' (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;
Line: 1377

       			' (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;
Line: 1415

			' (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;
Line: 1436

			' (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;
Line: 1480

 			'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;
Line: 1516

 			'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;
Line: 1606

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;
Line: 1625

     L_api_name 		CONSTANT VARCHAR2(30) := 'InsertUpdate';
Line: 1650

     	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
        );
Line: 1671

     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;
Line: 1685

     	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
        );
Line: 1706

     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;
Line: 1894

     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
     );