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 /*+ index(a BOM_INV_COMPS_INTERFACE_N6) */ * from BOM_INVENTORY_COMPS_INTERFACE A  /*Bug 8213562: Added hint*/
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 change_notice is null --added for bug 9447664
and
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 153

select * from BOM_INVENTORY_COMPS_INTERFACE
where process_flag = 1
and all_org = 1
and transaction_id is not null
and change_notice is null --added for bug 9447664
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 change_notice is null --added for bug 9447664
AND rownum =1
AND
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 180

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 change_notice is null --added for bug 9447664
and set_process_id = nvl(p_batch_id,0); -- Replace NULL batch id with 0 - table level default for set_process_id

Line: 192

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 change_notice is null --added for bug 9447664
and set_process_id = nvl(p_batch_id,0);

Line: 202

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 change_notice is null --added for bug 9447664
and
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 221

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 change_notice is null --added for bug 9447664
and
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 236

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 change_notice is null --added for bug 9447664
and
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 255

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 change_notice is null --added for bug 9447664
and
(
    ( (p_batch_id is null) and (batch_id is null) )
or  ( p_batch_id = batch_id )
);

Line: 269

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: 287

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: 525

                l_bom_header_rec.Delete_Group_Name   := bill_rec.Delete_Group_Name;

Line: 529

                l_bom_header_rec.enable_attrs_update := bill_rec.enable_attrs_update;

Line: 646

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

Line: 825

            l_return_status := Update_Interface_tables (err_text);

Line: 840

            l_return_status := Delete_Bom_OI(err_text,p_batch_id);

Line: 863

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

Line: 882

      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: 904

       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: 927

      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: 950

       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: 972

     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: 994

     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: 1007

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

Line: 1010

end Update_Interface_tables;

Line: 1014

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

Line: 1025

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: 1039

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: 1053

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: 1067

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: 1081

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: 1095

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

Line: 1107

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

Line: 1109

END Delete_Bom_OI;