APPS.EAM_PN_EXTRACTION_PUB SQL Statements

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

Line: 10

	select 'Y' into l_asset_exists
	from dual
	where exists
	( select * from csi_item_instances
	where pn_location_id = p_location_id
	and nvl(active_start_date, sysdate-1) < sysdate
	and nvl(active_end_date, sysdate+1) > sysdate);

Line: 35

  X_INSERT OUT NOCOPY NUMBER,
  X_INSERT_MODE OUT NOCOPY NUMBER,
  X_INSERT_STATUS OUT NOCOPY NUMBER,
  X_RETURN_STATUS OUT NOCOPY VARCHAR2,
  X_MSG_COUNT OUT NOCOPY NUMBER,
  X_MSG_DATA OUT NOCOPY VARCHAR2)
IS

    --  X_INSERT, if equals 1 then the row will be inserted in the interface tables.
    --  X_INSERT_MODE => 0 - create a new row and 1 - Update the existing row
    --  X_INSERT_STATUS, specifies the current status of Asset Number (3 Resides in stores )
    --  X_RETURN_STATUS, X_MSG_COUNT OUT NOCOPY NUMBER,  X_MSG_DATA OUT NOCOPY VARCHAR2
    --    Standard API out parameter (for error handling).

    l_api_version     CONSTANT NUMBER          := 1.0;

Line: 90

        x_insert := 1;

Line: 98

                SELECT cii.active_start_date, cii.active_end_date, msn.gen_object_id, cii.instance_id
		 INTO l_start_date, l_end_date, l_gen_object_id, l_instance_id
		 FROM mtl_serial_numbers msn, csi_item_instances cii
		 WHERE cii.pn_location_id = p_pn_location_id
		       and msn.current_organization_id = cii.last_vld_organization_id
		       and msn.inventory_item_id = cii.inventory_item_id
		       and msn.serial_number = cii.serial_number;

Line: 112

	        x_insert := 1;

Line: 113

            	x_insert_mode := 1;

Line: 114

            	x_insert_status := 4;

Line: 117

            		select 'Y' into l_parent_exists_in_eam from dual
            		where exists
                	(select * from csi_item_instances where
                	pn_location_id = p_parent_location_id);

Line: 129

		      x_insert := 1;

Line: 130

		      x_insert_mode := 1;

Line: 131

		      x_insert_status := 4;

Line: 134

	                	select 'Y' into l_hr_exists from dual
				where exists
                		(select * from mtl_object_genealogy
                		where object_id = l_gen_object_id);

Line: 144

			         x_insert := 1;

Line: 145

			         x_insert_mode := 1;

Line: 146

			         x_insert_status := 4;

Line: 148

			         x_insert := 1;

Line: 149

			         x_insert_mode := 1;

Line: 150

			         x_insert_status := 4;

Line: 152

	        	        select pl.active_start_date, pl.active_end_date
        	        	into l_pn_start_date, l_pn_end_date
                		from pn_locations_all pl
                		where location_id = p_pn_location_id;

Line: 158

	                	SELECT COUNT(*)
                                 INTO l_parent_exists_in_mog
                                 FROM mtl_object_genealogy mog, mtl_serial_numbers msn
                                 WHERE mog.object_id = l_gen_object_id
                                 AND msn.gen_object_id = mog.parent_object_id
                                 AND mog.genealogy_type = 5
                                 AND mog.start_date_active = l_pn_start_date
                                 AND ( mog.end_date_active = l_pn_end_date OR
                                     (l_pn_end_date IS NULL and mog.end_date_active is NULL))
                                 AND rownum = 1 ;

Line: 170

                                  x_insert := 0;

Line: 175

	    x_insert := 1;

Line: 176

            x_insert_mode := 0;

Line: 177

            x_insert_status := 4;