APPS.BOM_RTG_OPEN_INTERFACE SQL Statements

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

Line: 45

FUNCTION Update_Rtg_Interface_Tables(
		x_err_text   IN OUT NOCOPY  VARCHAR2)
return integer;

Line: 49

FUNCTION Delete_Rtg_OI (
		x_err_text   IN OUT NOCOPY  VARCHAR2
		, p_batch_id IN	 NUMBER)		-- Added parameter p_batch_id for Batch Import
return integer;

Line: 58

   , p_delete_rows 	IN  NUMBER
   , x_err_text		IN OUT NOCOPY VARCHAR2
) RETURN INTEGER
IS
  l_return_status INTEGER := 0;

Line: 67

                      p_delete_rows => p_delete_rows,
                      x_err_text => x_err_text,
                      p_batch_id  => NULL);

Line: 76

   , p_delete_rows	IN  NUMBER
   , x_err_text		IN OUT NOCOPY VARCHAR2
   , p_batch_id         IN  NUMBER
) RETURN INTEGER IS

cursor get_rtg_header is
select * from BOM_OP_ROUTINGS_INTERFACE
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
)
order by alternate_routing_designator desc; -- bug 3684819, we need primary routings to be processed first

Line: 95

select * from MTL_RTG_ITEM_REVS_INTERFACE
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and inventory_item_number = cp_ass_item_name
and organization_code = cp_org_code
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
)
order by process_revision; -- bug fix 3693102 we need to prorcess revs in alpha numeric sort order

Line: 111

select * from MTL_RTG_ITEM_REVS_INTERFACE
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 124

select * from BOM_OP_SEQUENCES_INTERFACE A
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
)
order by operation_type desc;

Line: 142

select * from BOM_OP_SEQUENCES_INTERFACE A
Where process_flag = 1
and p_all_org = 1
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
)
and rownum = 1
UNION ALL
SELECT * from BOM_OP_SEQUENCES_INTERFACE A
Where process_flag = 1
and p_all_org = 2 and organization_Id = p_organization_id
and transaction_id is not null
and rownum = 1
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 169

select * from BOM_OP_NETWORKS_INTERFACE
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 184

select * from BOM_OP_NETWORKS_INTERFACE
Where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 197

select * from BOM_OP_RESOURCES_INTERFACE A
where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 212

select * from BOM_OP_RESOURCES_INTERFACE
where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 224

select * from BOM_SUB_OP_RESOURCES_INTERFACE
where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and nvl(alternate_routing_designator, '##$$') = nvl(cp_alt_rtg_des,'##$$')
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 239

select * from BOM_SUB_OP_RESOURCES_INTERFACE
where process_flag = 1
and (p_all_org = 1 or (p_all_org = 2 and organization_id = p_organization_id))
and transaction_id is not null
and
(
    ( (p_batch_id is null) and (batch_id is null) )
    or ( p_batch_id = batch_id )
);

Line: 481

	  g_rtg_header_rec.Delete_Group_Name		:= rtg_header_rec.DELETE_GROUP_NAME; -- newly added

Line: 593

		g_op_tbl(i).Delete_Group_Name		:= op_seqs_rec.DELETE_GROUP_NAME;  --newly added

Line: 809

All the sucessful rows will be commited since bo will not update errored rows*/
   COMMIT;

Line: 818

            l_return_status := Update_Rtg_Interface_tables (x_err_text);

Line: 833

         if(p_delete_rows = 1) then
            l_return_status := Delete_Rtg_OI(x_err_text, p_batch_id);

Line: 856

Function Update_Rtg_Interface_tables (x_err_text   IN OUT NOCOPY  VARCHAR2)
return Integer
Is
  l_process_flag Number;

Line: 875

      Update BOM_OP_ROUTINGS_INTERFACE
      set    process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
      where  transaction_id = g_rtg_header_rec.Row_Identifier;

Line: 897

       Update MTL_RTG_ITEM_REVS_INTERFACE
       set    process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
       where  transaction_id = g_rtg_revision_rec.row_identifier;

Line: 920

      Update BOM_OP_SEQUENCES_INTERFACE
      set    process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
      where  transaction_id = g_op_rec.row_identifier;

Line: 943

       Update BOM_OP_RESOURCES_INTERFACE
       set   process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
       where  transaction_id = g_op_res_rec.row_identifier;

Line: 965

     Update BOM_SUB_OP_RESOURCES_INTERFACE
     set    process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
     where  transaction_id = g_sub_op_res_rec.row_identifier;

Line: 987

     Update BOM_OP_NETWORKS_INTERFACE
     set    process_flag = l_process_flag,
            REQUEST_ID =  Fnd_Global.Conc_Request_Id,
            PROGRAM_ID = Fnd_Global.Conc_program_Id,
            PROGRAM_APPLICATION_ID = Fnd_Global.prog_appl_id,
           PROGRAM_UPDATE_DATE = sysdate
     where  transaction_id = g_nwk_rec.row_identifier;

Line: 1000

      x_err_text := 'Update_Rtg_Interface_Tables'||stmt_num||substrb(SQLERRM,1,500);

Line: 1003

end Update_Rtg_Interface_tables;

Line: 1005

FUNCTION Delete_Rtg_OI (
        x_err_text    IN OUT NOCOPY VARCHAR2
	, p_batch_id  IN  NUMBER
)
    return INTEGER
IS
    stmt_num    NUMBER;

Line: 1015

DELETE FROM BOM_OP_ROUTINGS_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1029

DELETE FROM BOM_OP_SEQUENCES_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1043

DELETE FROM BOM_OP_RESOURCES_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1057

DELETE FROM BOM_SUB_OP_RESOURCES_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1071

DELETE FROM BOM_OP_NETWORKS_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1085

DELETE FROM MTL_RTG_ITEM_REVS_INTERFACE
WHERE PROCESS_FLAG = 7
AND
(
    ( (p_batch_id IS NULL) AND (BATCH_ID IS NULL) )
    OR ( p_batch_id = BATCH_ID )
)
AND rownum < 500;

Line: 1101

        x_err_text := 'DELETE_RTG_OI(' || stmt_num || ')' || substrb(SQLERRM,1,240);

Line: 1103

END Delete_Rtg_OI;