APPS.BOM_STRUCT_SYNC_PUB SQL Statements

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

Line: 54

    SELECT
      1
    INTO
      l_orderable_item
    FROM
      MTL_SYSTEM_ITEMS_B
    WHERE
          CUSTOMER_ORDER_FLAG = 'Y'
      AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
      AND inventory_item_id = p_item_id
      AND organization_id = p_org_id;

Line: 113

    select
      count(top_item_id)
    into
      l_items_count
    from
      bom_explosions be
    where
          be.top_item_id       = p_item_id
      and be.organization_id   = p_org_id
      and ( be.customer_order_flag = 'N'
            OR  be.CUSTOMER_ORDER_ENABLED_FLAG = 'N' );

Line: 229

  SELECT
    XMLElement("db:listOfBillOfMaterial",
    XMLATTRIBUTES ( 'http://xmlns.oracle.com/pcbpel/adapter/db/APPS/BOM_STRUCT_SYNC_PUB/GET_STRUCTURE_PAYLOAD/' AS "xmlns:db"),
               XMLAgg(XMLElement(
                        "db:billOfMaterial",
                        XMLAttributes(comp_bill_seq_id as "BillSequenceId"),
                        XMLForest(component_item_name  as "db:AssemblyName"),
                        XMLForest(nvl(component_item_id, assembly_item_id)  as "db:AssemblyItemId"),
                        XMLForest(OPERATING_UNIT_NAME as "db:OperatingUnit"),
                        XMLForest(ORGANIZATION_CODE as "db:OrganizationCode"),
                        XMLForest(ORGANIZATION_ID  as "db:OrganizationId"),
			XMLForest(OPERATING_UNIT_ID as "db:OperatingUnitId"),
                        (SELECT XMLElement("db:listOfBomComponent",
                                           XMLAgg(XMLElement(
                                                    "db:billOfMaterialComponent",
                                                    XMLAttributes(
                                                      component_item_id as "Id"),
                                                    XMLForest(
                                                      component_sequence_id as "db:ComponentSequenceId",
                                                      component_item_name as "db:ComponentName",
                                                      Component_Quantity as "db:Quantity",
                                                      EFFECTIVITY_DATE as "db:EffectivityDate",
                                                      DISABLE_DATE as "db:DisableDate",
                                                      ORGANIZATION_CODE as "db:OrganizationCode",
                                                      ORGANIZATION_ID as "db:OrganizationId",
						      OPERATING_UNIT_ID as "db:OperatingUnitId"))))
                           FROM bom_structure_sync_v c
                           WHERE c.top_bill_sequence_id=a.top_bill_sequence_id
                           and   c.bill_sequence_id= a.comp_bill_seq_id
                           and   c.top_item_id <> c.component_item_id                                                         and   c.effectivity_date <= sysdate --bug#5891992
                           )))) into p_bom
    FROM bom_structure_sync_v a
    where a.top_item_id = p_item_id
    and a.organization_id = p_org_id
    and a.comp_bill_seq_id is not null
    order by bill_sequence_id;