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;

Query to find Details Of OM and Install Base

SELECT okh.contract_number         "Contract Number",cl.ATTRIBUTE6,
       okh.scs_code                "Code",
       ooh.order_number            "Order Number",
       TO_NUMBER(sl.line_number)   "SL Line",
       sl.lse_id                   "SL LSE ID",
       TO_NUMBER(cl.line_number)   "CL Line",
       cl.lse_id                   "CL LSE ID",
       TO_CHAR(cl.id)              "CL ID",
       msi.inventory_item_id,
       msi.segment1                "Item Number",
       cii.serial_number           "Serial Number",
       okh.start_date              "Contract Start Date",
       sl.start_date               "Line Start Date",
       cl.start_date               "Subline Start Date",
       cii.*
  FROM apps.okc_k_headers_all_b   okh,
       apps.okc_k_lines_b         sl,
       apps.okc_k_lines_b         cl,
       apps.okc_k_items           oki,
       apps.okc_k_rel_objs        rel,
       apps.csi_item_instances    cii,
       apps.mtl_system_items_b    msi,
       apps.oe_order_headers_all  ooh
 WHERE 1=1
   AND okh.id                   =  sl.chr_id
   AND cl.cle_id                =  sl.id
   AND sl.cle_id IS NULL
   AND cl.id                    =  oki.cle_id(+)
   AND oki.object1_id1          =  cii.instance_id
   AND cii.inventory_item_id    =  msi.inventory_item_id
   AND okh.inv_organization_id  =  msi.organization_id
   AND rel.chr_id               =  okh.id
   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'
   AND rel.object1_id1          =  ooh.header_id
   AND oki.object1_id2          =  '#'
   AND okh.contract_number = 'US000016348'
  -- AND cii.serial_number = 'FM1407CB00D'
 -- AND TO_CHAR(cl.id) = '97803583007615608399812980983947089161'
   -- AND okh.cust_po_number       =  ooh.cust_po_number
   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id
   /* if subline start date is earlier than line start date */
   -- AND sl.start_date > cl.start_date
   -- AND sl.line_number      = 1
 ORDER BY okh.contract_number,
       TO_NUMBER(sl.line_number),
       TO_NUMBER(cl.line_number);

3133: Activity instance 'FULFILL_LINE' is not a notified activity Error in OM / Progress Order lines STUCK in Fulfillment Set

If some of order lines in a fulfillment set are closed/cancelled (because of data corruption) while other lines of same set are still open then this is true case of data corruption .In such case open lines will got stuck in AWAITING FULFILLMENT.( Reason - Purpose of the Fulfillmet set is to fulfill all the lines in the set together, but if any of line is closed/cancelled then rest all lines will stuck at workflow activity FULFILL -NOTIFED or ERROR).

Here is issue description:

Order having 2 Line - Line1 and Line2 and Both lines are assigned to Fullfillment Ser "1".
But some how Line1 got closed, due this Even though we comepleted all the shipping process on Line, Still line2 is in Shipped Status and workflow erroring out with below error.


3133: Activity instance 'FULFILL_LINE' is not a notified activity

Solution - Remove the Fulfillment Set from Line2 and Retry the workflow activity.

FYI:

SELECT * FROM    APPS.oe_line_sets ls, APPS.oe_sets s WHERE  1=1
 --ls.line_id    = 402039
AND     ls.set_id     = s.set_id
AND s.set_id = 1636905;