Query - Sales Order Line Closed, Shipped, not Interfaced to Inventory

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10)  "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0)  "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0)  "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N')  "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS')  "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS')  "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS')  "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
     --  AND ORD.HEADER_ID                  = 194000
     --  AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.INV_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     AND LIN.FLOW_STATUS_CODE = 'CLOSED'
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
 , nvl(LIN.shipment_number, -1)
 , nvl(LIN.option_number, -1)
 , nvl(LIN.service_number, -1);

***************************************************************************
For Online Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Query - Sales Order Line Closed, Shipped, not Interfaced to OM

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10)  "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0)  "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0)  "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N')  "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS')  "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS')  "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS')  "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
    --  AND ORD.HEADER_ID                  = 194000
     -- AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.OE_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     AND LIN.FLOW_STATUS_CODE = 'CLOSED'
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
 , nvl(LIN.shipment_number, -1)
 , nvl(LIN.option_number, -1)
 , nvl(LIN.service_number, -1);

Query - Sales Order Line has been Shipped but not Interfaced to OM

select
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10)  "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0)  "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0)  "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N')  "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS')  "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS')  "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS')  "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN,
     WSH_DELIVERY_DETAILS             DET
where 1=1
    -- AND ORD.HEADER_ID                  = 194000
    -- AND LIN.HEADER_ID              = 194000
     AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
     AND DET.SOURCE_CODE            = 'OE'
     AND DET.OE_INTERFACED_FLAG = 'N'
     AND LIN.SHIPPABLE_FLAG = 'Y'
     AND nvl(LIN.SHIPPED_QUANTITY,0) > 0
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
 , nvl(LIN.shipment_number, -1)
 , nvl(LIN.option_number, -1)
 , nvl(LIN.service_number, -1);

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Query To Find Sales Order Line Ordered Qty does not equal Shipped Qty

SELECT
    substr(to_char(LIN.line_number) ||
          decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
          decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
          decode(LIN.component_number, null, null,
                 decode(LIN.option_number, null, '.',null)||
                 '.'||to_char(LIN.component_number))||
          decode(LIN.service_number,null,null,
                 decode(LIN.component_number, null, '.' , null) ||
                        decode(LIN.option_number, null, '.', null ) ||
                        '.'|| to_char(LIN.service_number)),1,10)  "Line Number",
     LIN.LINE_ID                   "Line ID",
     LIN.INVENTORY_ITEM_ID         "Item ID",
     substr(LIN.FLOW_STATUS_CODE,1,22) "Workflow Status",
     nvl(LIN.ORDERED_QUANTITY,0)   "Ordered QTY",
     LIN.ORDER_QUANTITY_UOM        "Ordered UOM",
     nvl(LIN.SHIPPED_QUANTITY,0)   "Shipped QTY",
     nvl(LIN.SHIPPING_QUANTITY,0)  "Shipping QTY",
     nvl(FULFILLED_QUANTITY,0)     "Fulfilled QTY",
     nvl(LIN.CANCELLED_QUANTITY,0) "Cancelled QTY",
     nvl(LIN.INVOICED_QUANTITY,0)  "Invoiced QTY",
     nvl(LIN.OPEN_FLAG,'N')        "Open",
     nvl(LIN.BOOKED_FLAG,'N')      "Booked",
     nvl(LIN.SHIPPABLE_FLAG,'N')   "Shipped",
     nvl(LIN.CANCELLED_FLAG,'N')   "Cancelled",
     nvl(LIN.FULFILLED_FLAG, 'N')  "Fullfilled",
     nvl(LIN.SHIPPING_INTERFACED_FLAG,'N')    "Shipping Interface",
     LIN.SHIP_FROM_ORG_ID          "Ship From",
     nvl(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') "Invoice Interface",
     LIN.SHIP_TOLERANCE_ABOVE                 "Ship Tolerance Above",
     LIN.SHIP_TOLERANCE_BELOW                 "Ship Tolerance Below",
     to_char(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR_HH24:MI:SS')  "Actual Ship",
     to_char(LIN.CREATION_DATE,'DD-MON-RR_HH24:MI:SS')  "Create Date",
     to_char(LIN.LAST_UPDATE_DATE,'DD-MON-RR_HH24:MI:SS')  "Update Date"
from
     OE_ORDER_HEADERS_ALL                ORD,
     OE_ORDER_LINES_ALL                LIN
where 1=1
     --ORD.HEADER_ID                  = 194000
     --and LIN.HEADER_ID              = 194000
     AND LIN.SHIPPED_QUANTITY IS NOT NULL
     AND LIN.ORDERED_QUANTITY != LIN.SHIPPED_QUANTITY
     and   NVL('506530',0)     in (0,LIN.LINE_ID,
                                         LIN.TOP_MODEL_LINE_ID,
                                          LIN.ATO_LINE_ID,
                                         LIN.LINK_TO_LINE_ID,
                                         LIN.SERVICE_REFERENCE_LINE_ID)
     order by nvl(LIN.line_set_id, 0), LIN.line_number
 , nvl(LIN.shipment_number, -1)
 , nvl(LIN.option_number, -1)
 , nvl(LIN.service_number, -1);

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

BOM Query

SELECT msi.segment1 Item,msi.organization_id, msi.description item_Desc, bom.alternate_bom_designator Alternate_BOM,
msil.segment1 Component_Item, msil.description Component_Item_Desc,
BIC.component_quantity
FROM apps.MTL_SYSTEM_ITEMS MSI ,
apps.BOM_BILL_OF_MATERIALS BOM ,
apps.BOM_INVENTORY_COMPONENTS BIC,
apps.MTL_SYSTEM_ITEMS MSIL
WHERE BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND bom.bill_sequence_id = bic.bill_sequence_id
AND nvl(bic.disable_date, sysdate) >= SYSDATE
AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
AND msi.segment1 = 'BOM_ITEM_TEST'
ORDER BY msi.segment1, bom.alternate_bom_designator, bic.item_num;

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Responsibilities Assigned to User.

-- Query To find Responsibilities Assigned to User.

    SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM apps.fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('SLAKKIREDDY')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;

Customer Query

-- Customer Contact Details :

 select account_number "Account Number"
     , obj.party_name "Customer Name"
     , sub.party_name "Contact Name"
     , hcp.contact_point_type || ': ' ||
       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
                                    , 'WEB'  , hcp.url
                                    , 'Unknow contact Point Type ' || hcp.contact_point_type
             ) "How to Contact"
  from apps.hz_cust_accounts  hca
     , apps.hz_parties        obj
     , apps.hz_relationships  rel
     , apps.hz_contact_points hcp
     , apps.hz_parties        sub
 where hca.party_id           = rel.object_id
   and hca.party_id           = obj.party_id
   and rel.subject_id         = sub.party_id
   and rel.relationship_type  = 'CONTACT'
   and rel.directional_flag   = 'F'
   and rel.party_id           = hcp.owner_table_id
   and hcp.owner_table_name   = 'HZ_PARTIES'
   AND obj.party_name LIKE 'Oxford Unive%';

============================ Customer ==================================

SELECT    hl.orig_system_reference,hl.LAST_UPDATE_DATE
         ,hl.country
         ,hl.address1
         ,hl.address2
         ,hl.address3
         ,hl.address4
         ,hl.city
         ,hl.postal_code
         ,hl.state
         ,hl.province
         ,hl.county
         ,hl.content_source_type
         ,hl.actual_content_source actual_content_source
         ,hps.party_site_number
         ,hps.identifying_address_flag
         ,hps.status
         ,hps.party_site_name
         ,hps.created_by_module
         ,hps.actual_content_source site_actual_content_source
         ,hcasa.orig_system_reference site_orig_system_reference
         ,hcasa.status         acct_site_status
         ,hp.party_number
         ,hca.account_number
         ,hou.NAME operting_unit_name
         ,bill_to_flag
   FROM  apps.hz_locations        hl
        ,apps.hz_party_sites      hps
        ,apps.hz_cust_acct_sites_all  hcasa
        ,apps.hz_parties     hp
        ,apps.hz_cust_accounts   hca
        ,apps.hr_operating_units hou
   WHERE hcasa.party_site_id     = hps.party_site_id
   AND   hps.location_id         = hl.location_id
   AND   hp.party_id             = hca.party_id
   AND   hp.party_id             = hps.party_id
   AND   hca.cust_account_id     = hcasa.cust_account_id
   AND   hcasa.org_id            = hou.organization_id
   AND   hp.party_type           ='ORGANIZATION'
   AND   hps.status              = 'A'
   AND   hcasa.status            = 'A'
   AND   hp.status               = 'A'
   AND   hca.status              = 'A'
  -- AND hl.address1  like '8900%Uni%';
  -- AND   hp.party_number = '14613899';
  AND hp.party_name = 'Stanford University';
 

============================ Customer ==================================
 
   SELECT
       ----------------------------------------------------------
       -- Party Information
       ----------------------------------------------------------
       hp.party_number                      "Registry ID",
       hp.party_name                        "Party Name",
       hp.party_type                        "Party Type",
       DECODE(hp.status,
              'A', 'Active',
              'I', 'Inactive',
              hp.status)                    "Party Status",
       ----------------------------------------------------------
       -- Account Information
       ----------------------------------------------------------
       hca.account_number                   "Account Number",
       DECODE(hca.status,
              'A', 'Active',
              'I', 'Inactive',
              hca.status)                   "Account Status",
       hca.account_name                     "Account Description",
       hca.customer_class_code              "Classification",
       DECODE(hca.customer_type,
              'R', 'External',
              'I', 'Internal',
              hca.customer_type)            "Account Type",
       ----------------------------------------------------------
       -- Site Information
       ----------------------------------------------------------
       hps.party_site_number                "Customer Site Number",
       DECODE(hcas.status,
              'A', 'Active',
              'Inactive')                   "Site Status",
       DECODE(hcas.bill_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.bill_to_flag)            "Bill To Flag",
       DECODE(hcas.ship_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.ship_to_flag)            "Ship To Flag",
       hcas.cust_acct_site_id               "Customer Acct Site ID",
       ----------------------------------------------------------
       -- Address Information
       ----------------------------------------------------------
       hl.address1                          "Address1",
       hl.address2                          "Address2",
       hl.address3                          "Address3",
       hl.address4                          "Address4",
       hl.city                              "City",
       hl.state                             "State",
       hl.postal_code                       "Zip Code",
       ter.name                             "Territory",

       ----------------------------------------------------------
       -- Collector Information
       ----------------------------------------------------------
       col.name                             "Collector Name",
       ----------------------------------------------------------
       -- Account Profile Information
       ----------------------------------------------------------
       hcp.credit_checking                  "Credit Check Flag",
       hcp.credit_hold                      "Credit Hold Flag",
       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",
       hcpa.overall_credit_limit            "Credit Limit",
       hcpa.trx_credit_limit                "Order Credit Limit",
       ----------------------------------------------------------
       -- Attachment Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.fnd_documents_vl doc,
                   apps.fnd_lobs         blo,
                   apps.fnd_attached_documents att
             WHERE doc.media_id = blo.file_id
               AND doc.document_id = att.document_id
               AND att.entity_name = 'AR_CUSTOMERS'
               AND att.pk1_value   = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Attachment Flag",
       ----------------------------------------------------------
       -- Party Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND hzcar.relationship_type = 'ALL'
               AND ROWNUM = 1), 'N'
       ) "Party Relationship Flag",
       ----------------------------------------------------------
       -- Account Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Account Relationship Flag",
       ----------------------------------------------------------
       -- Party Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_parties hp2
             WHERE 1=1
               AND hp2.party_id = hp.party_id
               AND (
                    hp2.url IS NOT NULL OR
                    -- LENGTH(TRIM(hp.email_address)) > 5
                    INSTR(hp2.email_address, '@') > 0 OR
                    hp2.primary_phone_purpose IS NOT NULL
                    )
           ), 'N'
       ) "Party Contact Flag",
       ----------------------------------------------------------
       -- Account Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (SELECT hcar.party_id
                      FROM apps.hz_cust_account_roles   hcar,
                           apps.ar_contacts_v           acv
                     WHERE hcar.cust_account_id   = hca.cust_account_id
                       AND hcar.cust_account_role_id = acv.contact_id
                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only
                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
                    )
               AND ROWNUM = 1), 'N'
       ) "Account Contact Flag",
       ----------------------------------------------------------
       -- Site Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (
                      SELECT hcar.party_id
                        FROM apps.hz_cust_account_roles   hcar,
                             apps.ar_contacts_v           acv
                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id
                         AND hcar.cust_account_role_id  =  acv.contact_id
                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)
                   )
               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition
       ) "Site Contact Flag"
  FROM
       apps.hz_parties              hp,
       apps.hz_party_sites          hps,
       apps.hz_cust_accounts_all    hca,
       apps.hz_cust_acct_sites_all  hcas,
       apps.hz_customer_profiles    hcp,
       apps.hz_cust_profile_amts    hcpa,
       apps.hz_locations            hl,
       apps.ra_territories          ter,
       apps.ar_collectors           col
 WHERE
       1=1
   AND party_name like  'Florida Power%'
   AND hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id 
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcas.territory_id      =  ter.territory_id(+)
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
 --  AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
  -- AND hp.status              = 'A'               -- only Active Parties/Customers
   AND hp.party_number        = 8899
 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;