Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: Returning Related Customers for sorting

    Here is what I need to do in Oracle. I have a original SQL View that runs a Committee Agenda listing. Then from this original view I need to return all the related customers. Then if any or some of these related customers are in the original SQL View, I need to mark them with the TrxId for sorting purposes.

    So basically the Original SQL View would include another column called "Related Cust TrxId" or something like that. Then I will be able to sort by this Id. So all related customers will be grouped together for printing.

    Is there a way to join these two SQL statements and return all of the original SQL view and add a new column for the related customers and put the TrxId_relatedcust (or something like that)?

    Here is my SQL so far:

    SELECT DISTINCT AE.ADDR_ENTITY_ID, --this joins to find the related customers
    mb.processing_center_code, eb.entity_no, cm.committee_name,
    cm.committee_date, cm.committee_desc, cm.committee_phone,
    cm.committee_email,
    lookup_description_f('Participation Purchase or Sale', trxinfo.particip_purcsale_code) as TypeParticipation,
    trxinfo.particip_grossloan_amt,
    DECODE (e.object_type, 'financial_trx', TRIM (mb.bank_num_code) || '-' || TRIM (mb.branch_id)
    || TRIM (trxinfo.fin_trx_function_code), '098' || '-' || TRIM (fri.req_sales_cost_center)) AS cost_center,
    SUBSTR (customer_name_f (ae.addr_entity_id), 0, 255) AS customer_name,
    user_team_role_f (e.entity_id, RPAD ('8979645098917005705', 32)) AS banker_name,
    user_team_role_f (e.entity_id, RPAD ('1582563877044916802', 32)) AS rcc_analyst_name,
    user_team_role_f(e.entity_id, RPAD ('6226748212939600868', 32)) || user_team_role_f (e.entity_id,
    RPAD ('5241439225702303114', 32)) || user_team_role_f (e.entity_id, RPAD ('6548801500571278167', 32))
    || user_team_role_f (e.entity_id, RPAD ('310067990553624346', 32)) AS commitee_keeper,
    e.entity_no, custinfo.curr_risk_rating_code,
    lookup_description_f ('RCC Committee Types', fri.high_comm_type_code) AS highest_committee,
    DECODE(trxinfo.credit_action_code, NULL, lookup_description_f ('Credit Action Types', trxinfo.credit_action_code)
    || lookup_description_f ('RCC Maint Action Type', trxinfo.maint_trx_type_code), lookup_description_f ('Credit Action Types',
    trxinfo.credit_action_code)) AS credit_maint_action,
    DECODE (lookup_description_f ('Credit Action Types', trxinfo.credit_action_code), 'Renew', fintrx.total_net_financed, 0) AS renew_money,
    DECODE(lookup_description_f ('Credit Action Types', trxinfo.credit_action_code), 'New', fintrx.total_net_financed, 0) AS new_money,
    aee.total_amount
    FROM ccs_committee_meetings cm,
    ccs_mandi_branches mb,
    wfs_fin_request_info fri,
    wfs_financial_trx_info trxinfo,
    wfs_financial_trx fintrx,
    wfs_transactions trx,
    wfs_transactions trxb,
    wbs_addr_entities ae,
    wbs_entities e,
    wbs_entities eb,
    wfs_customers cust,
    wfs_customer_info custinfo,
    wfs_addr_ent_exposures aee,
    fc_entity_teams_v etv
    WHERE E.OBJECT_TYPE IN ('finance_request', 'financial_trx')
    and e.entity_id = etv.entity_id
    AND e.status_code <> 'BOOKED'
    AND e.canceled_flag <> 1
    AND etv.workflow_resp_id IN
    ('6226748212939600868', '5241439225702303114',
    '6548801500571278167', '310067990553624346',
    '8979645098917005705') --COMMITTEEKEEPER AND BANKER
    AND fri.fin_request_info_id = e.entity_id
    AND trx.fin_request_id = e.entity_id
    AND ( fri.dlc_comm_meet_id = cm.committee_meeting_id
    OR fri.clc_comm_meet_id = cm.committee_meeting_id
    OR fri.rlc_comm_meet_id = cm.committee_meeting_id )
    AND e.entity_id = trxb.transaction_id
    AND trx.customer_id = ae.addr_entity_id
    AND ( trxinfo.fin_trx_branch_id = mb.mandi_branch_id
    OR fri.req_sales_office_code = mb.mandi_branch_id )
    AND trx.transaction_id = trxinfo.financial_trx_info_id
    AND cust.customer_id = custinfo.customer_info_id
    AND cust.customer_id = eb.entity_id
    AND eb.entity_id = ae.addr_entity_id
    AND fintrx.financial_trx_id(+) = trxinfo.financial_trx_info_id
    AND ae.addr_entity_id = aee.addr_entity_id(+)
    AND E.ENTITY_NO LIKE '145453%'
    GROUP BY AE.ADDR_ENTITY_ID,
    mb.processing_center_code,
    eb.entity_no,
    cm.committee_name,
    cm.committee_date,
    cm.committee_desc,
    cm.committee_phone,
    cm.committee_email,
    trxinfo.particip_purcsale_code, trxinfo.particip_grossloan_amt,
    DECODE (e.object_type, 'financial_trx', TRIM (mb.bank_num_code) || '-'
    || TRIM (mb.branch_id)
    || TRIM (trxinfo.fin_trx_function_code),
    '098' || '-' || TRIM (fri.req_sales_cost_center)),
    SUBSTR (customer_name_f (ae.addr_entity_id), 0, 255),
    user_team_role_f (e.entity_id, RPAD ('8979645098917005705', 32)),
    user_team_role_f (e.entity_id, RPAD ('1582563877044916802', 32)),
    user_team_role_f (e.entity_id, RPAD ('6226748212939600868', 32)),
    user_team_role_f (e.entity_id, RPAD ('5241439225702303114', 32)),
    user_team_role_f (e.entity_id, RPAD ('6548801500571278167', 32)),
    user_team_role_f (e.entity_id, RPAD ('310067990553624346', 32)),
    e.entity_no,
    custinfo.curr_risk_rating_code,
    lookup_description_f ('RCC Committee Types', fri.high_comm_type_code),
    lookup_description_f ('Credit Action Types', trxinfo.credit_action_code),
    trxinfo.credit_action_code,
    trxinfo.maint_trx_type_code,
    DECODE (lookup_description_f ('Credit Action Types', trxinfo.credit_action_code), 'Renew', fintrx.total_net_financed, 0),
    DECODE (lookup_description_f ('Credit Action Types', trxinfo.credit_action_code), 'New', fintrx.total_net_financed, 0),
    aee.total_amount
    ORDER BY cm.committee_name,
    cm.committee_date,
    SUBSTR (customer_name_f (ae.addr_entity_id), 0, 255)





    /* Retrieve all the Related Customers to Kings Way Homes LLC for Request 145453 */
    select distinct e.entity_id, e.entity_no, SUBSTR(customer_name_f(AE.addr_entity_id),0,255) AS CUSTOMER_NAME
    from WBS_ENTITIES E,
    WBS_ADDR_ENTITIES AE,
    WBS_RELATIONS R
    WHERE e.entity_id = ae.addr_entity_id
    AND AE.ADDR_ENTITY_ID = R.FROM_ENTITY_ID(+)
    AND R.TO_ENTITY_ID = '8540172077040701048' --THE ORIGINAL CUSTOMER

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Is there no way you could present us with a much simplified example? We don't really need to see all the tables you use and how they join, all the DECODEs etc. All that irrelevant detail certainly baffles me and puts me off attempting to understand what you are trying to do, and may explain why no one else has replied either!

  3. #3
    Join Date
    Feb 2007
    Posts
    84

    Write PL/SQL Function to retrieve Related Customers

    Okay, It was probably confusing with the large SQL example. I would like to be able to write an PL/SQL function that reads from and returns the related customers to the original SQL View. So I have 2 statement here. One is the original SQL View that returns all the data! Then the PL/SQL I want to find all the Related customers and return them to the original SQL View.

    Here is my SQL statement that I want to turn into a PL/SQL, which will return a list of different customers, such as:
    CUST_NO CUSTOMER_NAME
    4941661 ALM Holding Company
    4674753 Texpar Energy Llc
    4677954 Mte Services Inc

    select e.entity_no as cust_no,
    ae.customer_name
    from WBS_ENTITIES E,
    WBS_ADDR_ENTITIES AE,
    WBS_RELATIONS R
    WHERE e.entity_id = ae.addr_entity_id
    AND AE.ADDR_ENTITY_ID = R.FROM_ENTITY_ID(+)
    AND R.TO_ENTITY_ID = '1197622387517609755' --THE ORIGINAL CUSTOMER coming from the SQL VIEW

    Then if the Cust_No is in the original SQL View, then add a column with this Customer No called "RELATED_CUSTOMER". Such as:

    New PL/SQL field
    CUST_NO CUSTOMER_NAME RELATED_CUSTOMER
    FC74801 Z Man
    4941661 ALM Holding Company 4941661
    FC74801 Z Man
    4677954 Mte Services Inc 4677954

    There could be multiple customers that are related in the PL/SQL. So I would need to retrieve more than one customer, so I'm assuming it would be a loop PL/SQL.

    Hope this is a better explanation...

    CC

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Ok, it's getting clearer, but still not completely clear (to me anyway).

    You keep refering to "the original SQL view", and I'm not sure what you mean by that. Do you mean that you have one SELECT statement that returns a list of customers (known as "the original SQL view"), and then you have another SELECT statement that returns another list of customers, and you want to identify which of those customers (in the second select) was also in the first?

  5. #5
    Join Date
    Feb 2007
    Posts
    84

    Related Customers

    In the Original SQL View, I will be doing reporting from. So the 2nd PL/SQL statement will not be used in the report. It will be used only to retrieve the Related Customers. In the SQL View, I will be retrieving many different Transactions with customers and other $ info.

    But in this SQL View or report, they want to be able to sort by the Related Customers so in meetings they talk about all the related customers at once. The related customers will only be the customers in the SQL View. But the only way to find who is related is by fetching this other statement, which gives a list of all the related customers per that SQL View trx on that particular customer. All the related custs may not be in every single SQL View trx, so those related custs will be ignored. But the custs that do exists in the SQL View, I need to add an additional column that flags that this customer is related to the original customer. To do this I thought of giving it the original customers Cust_NO. Then the report users can sort by this Cust_No and get all the related customers at once.

    Does this help? Need more examples?
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •