APPS.BOM_OPEN_INTERFACE_API SQL Statements

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

Line: 40

Function Update_Interface_tables (
    err_text   IN OUT NOCOPY  VARCHAR2)
return integer;

Line: 45

FUNCTION Delete_Bom_OI (
        err_text    IN OUT NOCOPY VARCHAR2,
        p_batch_id  IN	NUMBER
)return integer;

Line: 120

select * from BOM_BILL_OF_MTLS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_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_bom_designator DESC;

Line: 134

select * from BOM_INVENTORY_COMPS_INTERFACE A
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and   assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and (alternate_bom_designator is NULL or
(alternate_bom_designator is not Null and
alternate_bom_designator= cp_alt_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: 152

select * from BOM_INVENTORY_COMPS_INTERFACE
where process_flag = 1
and 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_INVENTORY_COMPS_INTERFACE
WHERE process_flag = 1
AND all_org = 2 and organization_id = org_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: 177

select * from mtl_item_revisions_interface
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and   item_number = cp_ass_item_name
and organization_code = cp_org_code
and transaction_id is not null
and set_process_id = nvl(p_batch_id,0); -- Replace NULL batch id with 0 - table level default for set_process_id

Line: 188

select * from mtl_item_revisions_interface
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and transaction_id is not null
and set_process_id = nvl(p_batch_id,0);

Line: 197

select * from BOM_REF_DESGS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and   assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and (alternate_bom_designator is NULL or
(alternate_bom_designator is not Null and
alternate_bom_designator= cp_alt_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: 215

SELECT *
FROM BOM_REF_DESGS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_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: 229

select * from bom_sub_comps_interface
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and   assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and (alternate_bom_designator is NULL or
(alternate_bom_designator is not Null and
alternate_bom_designator= cp_alt_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: 247

SELECT * FROM BOM_SUB_COMPS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_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: 260

SELECT *  FROM BOM_COMPONENT_OPS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_id))
and   assembly_item_number = cp_ass_item_name
and organization_code = cp_org_code
and (alternate_bom_designator is NULL or
(alternate_bom_designator is not Null and
alternate_bom_designator= cp_alt_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: 278

SELECT * FROM BOM_COMPONENT_OPS_INTERFACE
where process_flag = 1
and (all_org = 1 or (all_org = 2 and organization_id = org_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: 516

                l_bom_header_rec.Delete_Group_Name   := bill_rec.Delete_Group_Name;

Line: 520

                l_bom_header_rec.enable_attrs_update := bill_rec.enable_attrs_update;

Line: 637

                l_bom_component_tbl(i).Delete_Group_Name     :=comp_rec.Delete_Group_Name;

Line: 816

            l_return_status := Update_Interface_tables (err_text);

Line: 831

            l_return_status := Delete_Bom_OI(err_text,p_batch_id);

Line: 854

Function Update_Interface_tables (err_text   IN OUT NOCOPY  VARCHAR2)
return Integer
Is
  l_process_flag Number;

Line: 873

      Update bom_bill_of_mtls_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 = l_bom_header_rec.Row_Identifier;

Line: 895

       Update mtl_item_revisions_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 = l_bom_revision_rec.row_identifier;

Line: 918

      Update bom_inventory_comps_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 = l_bom_component_rec.row_identifier;

Line: 941

       Update bom_ref_desgs_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 = l_bom_ref_designator_rec.row_identifier;

Line: 963

     Update BOM_SUB_COMPS_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 = l_bom_sub_component_rec.row_identifier;

Line: 985

     Update BOM_COMPONENT_OPS_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 = l_bom_comp_ops_rec.row_identifier;

Line: 998

      err_text := 'Update_Interface_Tables'||stmt_num||substrb(SQLERRM,1,500);

Line: 1001

end Update_Interface_tables;

Line: 1005

FUNCTION Delete_Bom_OI (
        err_text    IN OUT NOCOPY VARCHAR2,
        p_batch_id  IN	NUMBER
)
    return INTEGER
IS
    stmt_num    NUMBER;

Line: 1016

DELETE FROM BOM_BILL_OF_MTLS_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: 1030

DELETE FROM BOM_INVENTORY_COMPS_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: 1044

DELETE FROM BOM_REF_DESGS_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: 1058

DELETE FROM BOM_COMPONENT_OPS_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: 1072

DELETE FROM BOM_SUB_COMPS_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: 1086

DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE PROCESS_FLAG = 7
AND SET_PROCESS_ID = NVL(p_batch_id,0)
AND rownum < 500;

Line: 1098

        err_text := 'delete_bom_oi(' || stmt_num || ')' || substrb(SQLERRM,1,240);

Line: 1100

END Delete_Bom_OI;