APPS.BOM_OPRESOURCE_PVT SQL Statements

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

Line: 26

                  Select assembly_item_id,
                         organization_id,
                         alternate_routing_designator
                  From bom_operational_routings
                  Where routing_sequence_id = P_RtgSeqId;

Line: 32

                  Select organization_id
                  From mtl_parameters
                  Where organization_code = P_Code;

Line: 37

                  select routing_sequence_id
                  from bom_operational_routings
                  where organization_id = P_OrgId
                  and   assembly_item_id = P_AssyItemId
                  and   nvl(alternate_routing_designator, 'Primary Alternate') =
                        nvl(P_Alternate, 'Primary Alternate');

Line: 44

		  Select operation_type,
		         reference_flag
		  From bom_operation_sequences
		  Where operation_sequence_id = P_OpSeqId;

Line: 51

		  select operation_sequence_id
        	  from bom_operation_sequences
        	  where routing_sequence_id = P_RtgSeqId
        	  and   operation_seq_num = P_SeqNum
                  /* Bug # 1376700  */
                  and   trunc(effectivity_date) = trunc(P_EffDate)
		  and   nvl(operation_type, g_event) = g_event
		  and   nvl(reference_flag, g_no) = g_no;

Line: 60

		  select resource_id
                  from bom_resources
                  where resource_code = P_Code
                  and organization_id = P_OrgId;

Line: 65

		  select activity_id
                  from cst_activities
                  where activity = P_Code
                  and nvl(organization_id, P_OrgId) = P_OrgId;

Line: 70

		  select br.default_basis_type,
               		 br.default_activity_id,
                         decode(bd.location_id,
				NULL, decode(br.AUTOCHARGE_TYPE,
					     NULL, 2,
					     3, 2,
					     4, 2,
					     br.AUTOCHARGE_TYPE),
 				nvl(br.AUTOCHARGE_TYPE, 2)) default_autocharge,
               		 br.standard_rate_flag
          	  from bom_resources br,
		       bom_departments bd,
		       bom_operation_sequences bos
         	  where br.resource_id = P_ResourceId
		  and   bos.operation_sequence_id = P_OpSeqId
		  and   bd.department_id = bos.department_id;

Line: 332

			  Select 'x' dummy
			  From dual
			  Where not exists(
			    select null
        		    from bom_operation_sequences
        		    where operation_sequence_id = P_OpSeqId
			    and   nvl(operation_type, g_event) = g_event
			    and   nvl(reference_flag, g_no) = g_no);

Line: 341

			  select 'x' dummy
			  from dual
			  where not exists (
        		    select null
                	    from bom_resources br,
			    bom_department_resources bdr,
			    bom_operation_sequences bos
                	    where br.resource_id = P_ResourceId
			    and bos.operation_sequence_id = P_OpSeqId
               		    and nvl(br.disable_date, bos.effectivity_date + 1)
			        > bos.effectivity_date
                	    and bdr.department_id = bos.department_id
                	    and bdr.resource_id = br.resource_id);

Line: 355

			  select bdr.AVAILABLE_24_HOURS_FLAG,
				 bd.location_id
             		  from bom_department_resources bdr,
                  	       bom_departments bd,
			       bom_operation_sequences bos
            		  where bdr.resource_id = P_ResourceId
			  and   bos.operation_sequence_id = P_OpSeqId
              		  and bdr.department_id = bos.department_id
              		  and bdr.department_id = bd.department_id;

Line: 365

		   	Select 'x' dummy
			From dual
			Where not exists(
			  select null
                	  from cst_activities ca,
			       bom_operation_sequences bos,
			       bom_operational_routings bor
                	  where ca.activity_id = P_ActivityId
			  and   bos.operation_sequence_id = P_OpSeqId
			  and   bos.routing_sequence_id =
			 	bor.routing_sequence_id
                	  and   nvl(ca.organization_id, bor.organization_id)
                        	= bor.organization_id
                	  and   nvl(ca.disable_date, bos.effectivity_date + 1)
			        > bos.effectivity_date);

Line: 382

			  Select 'x' dummy
			  From dual
			  Where exists(
			    Select null
        		    from bom_operation_resources bor
        		    where bor.operation_sequence_id = P_OpSeqId
        		    and   bor.resource_seq_num = P_NewSeqNum
        		    and   (bor.resource_seq_num <> P_OldSeqNum
				   or P_OldSeqNum is null));

Line: 396

			  Select unit_of_measure
			  From bom_resources
			  Where resource_id = P_ResourceId;

Line: 400

			  Select uom_class
			  From mtl_units_of_measure
			  Where uom_code = P_Code;

Line: 404

			  Select 'x' dummy
			  From dual
			  where not exists(
			    select null
                            from mtl_uom_conversions a,
                                 mtl_uom_conversions b
                       	    where a.uom_code = l_ResUomCode
                       	    and   a.uom_class = l_ResUomClass
                       	    and   a.inventory_item_id = 0
                       	    and   nvl(a.disable_date, sysdate + 1) > sysdate
                       	    and   b.uom_code = l_HourUomCode
                       	    and   b.inventory_item_id = 0
                       	    and   b.uom_class = a.uom_class);

Line: 419

			  Select 'x' dummy
			  From dual
        		  Where exists(
			    select null
        		    from bom_operation_resources bor
        		    where operation_sequence_id = P_OpSeqId
			    and (resource_seq_num <> P_SeqNum or
			         P_SeqNum is null)
        		    and schedule_flag = P_SchedType);

Line: 433

			  select 'x' dummy
			  from dual
			  where exists(
       			    select null
        		    from bom_operation_resources
        		    where operation_sequence_id = P_OpSeqId
			    and (P_SeqNum is null or
				 resource_seq_num <> P_SeqNum)
        		    and   autocharge_type = l_POMove);

Line: 443

        		  select 1 dummy
        		  from dual
        		  where not exists(
			    select 'no dept loc'
                    	    from bom_departments bd,
			         bom_operation_sequences bos
                      	    where bos.operation_sequence_id = P_OpSeqId
			    and   bd.department_id = bos.department_id
                    	    and   bd.location_id is not null);

Line: 776

l_ProgramUpdate         date;

Line: 979

  End if; -- validate before inserting

Line: 988

    l_ProgramUpdate := null;

Line: 990

    l_ProgramUpdate := sysdate;

Line: 993

  Insert into bom_operation_resources(
    operation_sequence_id,
    resource_seq_num,
    resource_id,
    activity_id,
    standard_rate_flag,
    assigned_units,
    usage_rate_or_amount,
    usage_rate_or_amount_inverse,
    basis_type,
    schedule_flag,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    resource_offset_percent,
    autocharge_type,
    attribute_category,
    attribute1,
    attribute2,
    attribute3,
    attribute4,
    attribute5,
    attribute6,
    attribute7,
    attribute8,
    attribute9,
    attribute10,
    attribute11,
    attribute12,
    attribute13,
    attribute14,
    attribute15,
    principle_flag,
    schedule_seq_num,
    substitute_group_num,
    request_id,
    program_application_id,
    program_id,
    program_update_date)
  values(
    l_resource_rec.operation_sequence_id,
    l_resource_rec.new_resource_seq_num,
    l_resource_rec.resource_id,
    l_resource_rec.activity_id,
    l_resource_rec.standard_rate_flag,
    l_resource_rec.assigned_units,
    round(l_resource_rec.usage_rate_or_amount,G_round_off_val), /* Bug 7322996 */
    round(l_resource_rec.usage_rate_or_amount_inverse,G_round_off_val), /* Bug 7322996 */
    l_resource_rec.basis_type,
    l_resource_rec.schedule_flag,
    sysdate,
    l_UserId,
    sysdate,
    l_UserId,
    l_LoginId,
    l_resource_rec.resource_offset_percent,
    l_resource_rec.autocharge_type,
    l_resource_rec.attribute_category,
    l_resource_rec.attribute1,
    l_resource_rec.attribute2,
    l_resource_rec.attribute3,
    l_resource_rec.attribute4,
    l_resource_rec.attribute5,
    l_resource_rec.attribute6,
    l_resource_rec.attribute7,
    l_resource_rec.attribute8,
    l_resource_rec.attribute9,
    l_resource_rec.attribute10,
    l_resource_rec.attribute11,
    l_resource_rec.attribute12,
    l_resource_rec.attribute13,
    l_resource_rec.attribute14,
    l_resource_rec.attribute15,
    l_resource_rec.principle_flag,
    l_resource_rec.schedule_seq_num,
    l_resource_rec.schedule_seq_num,
    l_RequestId,
    l_ApplicationId,
    l_ProgramId,
    l_ProgramUpdate);

Line: 1114

PROCEDURE UpdateResource(
  p_api_version         IN	NUMBER,
  p_init_msg_list	IN	VARCHAR2 := FND_API.G_FALSE,
  p_commit	    	IN  	VARCHAR2 := FND_API.G_FALSE,
  p_validation_level	IN  	NUMBER	:= FND_API.G_VALID_LEVEL_FULL,
  x_return_status	IN OUT NOCOPY VARCHAR2,
  x_msg_count		IN OUT NOCOPY NUMBER,
  x_msg_data		IN OUT NOCOPY VARCHAR2,
  p_resource_rec	IN	RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
  x_resource_rec	IN OUT NOCOPY  RESOURCE_REC_TYPE
) IS
l_api_name		CONSTANT VARCHAR2(30)	:= 'UpdateResource';

Line: 1135

l_ProgramUpdate         DATE;

Line: 1139

			  Select *
			  From bom_operation_resources bor
			  Where bor.operation_sequence_id = P_OpSeqId
			  And   bor.resource_seq_num = P_SeqNum;

Line: 1146

  SAVEPOINT UpdateResource_Pvt;

Line: 1342

    l_ProgramUpdate := null;

Line: 1344

    l_ProgramUpdate := sysdate;

Line: 1347

  update bom_operation_resources set
    resource_seq_num = l_resource_rec.new_resource_seq_num,
    resource_id = l_resource_rec.resource_id,
    activity_id = l_resource_rec.activity_id,
    standard_rate_flag = l_resource_rec.standard_rate_flag,
    assigned_units = l_resource_rec.assigned_units,
    usage_rate_or_amount = round(l_resource_rec.usage_rate_or_amount,G_round_off_val), /* Bug 7322996 */
    usage_rate_or_amount_inverse = round(l_resource_rec.usage_rate_or_amount_inverse,G_round_off_val), /* Bug 7322996 */
    basis_type = l_resource_rec.basis_type,
    schedule_flag = l_resource_rec.schedule_flag,
    last_update_date = sysdate,
    last_updated_by = l_UserId,
    creation_date = nvl(creation_date,sysdate),
    created_by = l_UserId,
    last_update_login = l_LoginId,
    resource_offset_percent = l_resource_rec.resource_offset_percent,
    autocharge_type = l_resource_rec.autocharge_type,
    attribute_category = l_resource_rec.attribute_category,
    attribute1 = l_resource_rec.attribute1,
    attribute2 = l_resource_rec.attribute2,
    attribute3 = l_resource_rec.attribute3,
    attribute4 = l_resource_rec.attribute4,
    attribute5 = l_resource_rec.attribute5,
    attribute6 = l_resource_rec.attribute6,
    attribute7 = l_resource_rec.attribute7,
    attribute8 = l_resource_rec.attribute8,
    attribute9 = l_resource_rec.attribute9,
    attribute10 = l_resource_rec.attribute10,
    attribute11 = l_resource_rec.attribute11,
    attribute12 = l_resource_rec.attribute12,
    attribute13 = l_resource_rec.attribute13,
    attribute14 = l_resource_rec.attribute14,
    attribute15 = l_resource_rec.attribute15,
    principle_flag = l_resource_rec.principle_flag,
    request_id = l_RequestId,
    program_application_id = l_ApplicationId,
    program_id = l_ProgramId,
    program_update_date = l_ProgramUpdate
  where operation_sequence_id = l_resource_rec.operation_sequence_id
  and   resource_seq_num = l_resource_rec.resource_seq_num;

Line: 1402

    ROLLBACK TO UpdateResource_Pvt;

Line: 1409

    ROLLBACK TO UpdateResource_Pvt;

Line: 1416

    ROLLBACK TO UpdateResource_Pvt;

Line: 1425

END UpdateResource;

Line: 1426

PROCEDURE DeleteResource(
  p_api_version         IN	NUMBER,
  p_init_msg_list	IN	VARCHAR2 := FND_API.G_FALSE,
  p_commit	    	IN  	VARCHAR2 := FND_API.G_FALSE,
  p_validation_level	IN  	NUMBER	:= FND_API.G_VALID_LEVEL_FULL,
  x_return_status	IN OUT NOCOPY VARCHAR2,
  x_msg_count		IN OUT NOCOPY NUMBER,
  x_msg_data		IN OUT NOCOPY VARCHAR2,
  p_resource_rec	IN	RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
  x_resource_rec	IN OUT NOCOPY RESOURCE_REC_TYPE
) IS
l_api_name		CONSTANT VARCHAR2(30)	:= 'DeleteResource';

Line: 1445

  SAVEPOINT DeleteResource_Pvt;

Line: 1482

  delete bom_operation_resources
  where operation_sequence_id = l_resource_rec.operation_sequence_id
  and   resource_seq_num = l_resource_rec.resource_seq_num;

Line: 1505

    ROLLBACK TO DeleteResource_Pvt;

Line: 1512

    ROLLBACK TO DeleteResource_Pvt;

Line: 1519

    ROLLBACK TO DeleteResource_Pvt;

Line: 1528

END DeleteResource;