APPS.JMF_SUBCONTRCT_DIAG_UTIL SQL Statements

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

Line: 68

  l_sqltxt := ' SELECT
                organization_id "MP Organization Id",
                organization_code "MP Organization Code"
              FROM MTL_PARAMETERS mp
              WHERE trading_partner_org_flag = ''Y''
              AND NOT EXISTS
                (SELECT 1 FROM WIP_PARAMETERS wp
                WHERE mp.organization_id = wp.organization_id)';

Line: 105

  l_sqltxt := ' SELECT
                organization_id "MP Organization Id",
                organization_code "MP Organization Code"
              FROM mtl_parameters mp
              WHERE trading_partner_org_flag = ''Y''
              AND NOT EXISTS
                (SELECT   1
                    FROM org_acct_periods oap
                    WHERE oap.organization_id = mp.organization_id
                    AND (Trunc(period_start_date) < Trunc(SYSDATE)
                    AND Trunc(schedule_close_date) > Trunc(SYSDATE))
                    AND open_flag = ''Y'' )';

Line: 150

  l_sqltxt := ' SELECT
                  msi.segment1 "Outsourced Assembly",
                  mp.organization_code "Organization Code"
                FROM
                  mtl_system_items_b msi,
                  mtl_parameters mp
                WHERE
                  msi.OUTSOURCED_ASSEMBLY = 1
                  AND msi.organization_id = mp.organization_id
                  AND mp.trading_partner_org_flag = ''Y''
                  AND EXISTS
                    (SELECT 1 FROM  bom_operational_routings bor
                      WHERE bor.organization_id = msi.organization_id
                      AND bor.assembly_item_id = msi.inventory_item_id)';

Line: 193

  l_sqltxt := ' SELECT  DISTINCT
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "MP Organization Code"
              FROM jmf_subcontract_orders jso
              WHERE  NOT EXISTS
                (SELECT 1 FROM mtl_interorg_parameters mip
                WHERE mip.from_organization_id = jso.oem_organization_id
                AND   mip.to_organization_id   = jso.tp_organization_id
                AND SUBCONTRACTING_TYPE IS NOT NULL)';

Line: 231

  l_sqltxt := '  SELECT  DISTINCT
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "TP Organization Code"
              FROM jmf_subcontract_orders jso
              WHERE  NOT EXISTS
                (SELECT 1 FROM mtl_interorg_ship_methods mism
                WHERE mism.from_organization_id = jso.oem_organization_id
                AND   mism.to_organization_id   = jso.tp_organization_id
                AND mism.default_flag = 1)
              UNION
              SELECT  DISTINCT
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
                (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "TP Organization Code"
              FROM jmf_subcontract_orders jso
              WHERE  NOT EXISTS
                (SELECT 1 FROM mtl_interorg_ship_methods mism
                WHERE mism.from_organization_id = jso.tp_organization_id
                AND   mism.to_organization_id   = jso.oem_organization_id
                AND mism.default_flag = 1)';

Line: 281

  l_sqltxt := '  SELECT
                  ORGANIZATION_CODE "OEM Organization" FROM mtl_parameters   mp
                  WHERE Nvl(trading_partner_org_flag, ''N'') = ''N''
                  AND EXISTS(
                  SELECT  1 FROM
                  hr_organization_information hoi
                  WHERE mp.organization_id = hoi.organization_id
                  AND org_information_context = ''Customer/Supplier Association''
                  AND (org_information3 IS NULL
                      OR org_information4 IS NULL) )
                  AND EXISTS
                  ( SELECT 1 FROM mtl_system_items_b msi
                    WHERE msi.organization_id = mp.organization_id
                    AND msi.outsourced_assembly = 1)';

Line: 308

  l_sqltxt := '  SELECT
                  ORGANIZATION_CODE "MP Organization" FROM mtl_parameters   mp
                  WHERE Nvl(trading_partner_org_flag, ''N'') = ''Y''
                  AND EXISTS(
                  SELECT  1 FROM
                  hr_organization_information hoi
                  WHERE mp.organization_id = hoi.organization_id
                  AND org_information_context = ''Customer/Supplier Association''
                  AND (org_information1 IS NULL
                      OR org_information2 IS NULL
                      OR org_information4 IS NULL
                      OR org_information4 IS NULL) ) ';

Line: 350

  l_sqltxt := '  SELECT DISTINCT
                  SEGMENT1 "Item"
                  FROM mtl_system_items_b msi
                  WHERE subcontracting_component IN (1,2)
                  AND EXISTS
                  ( SELECT 1 FROM  mtl_parameters mp
                    WHERE mp.organization_id = msi.organization_id
                    AND Nvl(trading_partner_org_flag, ''N'') = ''Y'')
                  AND NOT EXISTS
                  ( SELECT 1 FROM  qp_list_lines
                    WHERE   qp_price_list_pvt.get_inventory_item_id(list_line_id) = msi.inventory_item_id)';