This discussion is archived
8 Replies Latest reply: Jun 20, 2011 12:08 AM by 857132 RSS

relational database..........real time example

857132 Newbie
Currently Being Moderated
can anyone give me the real time examples of hierarchical,network and relational database and why we use relational database....and the best real time example for relational database?
  • 1. Re: relational database..........real time example
    orawiss Oracle ACE
    Currently Being Moderated
    http://lmgtfy.com/?q=+relational+database+example
  • 2. Re: relational database..........real time example
    EdStevens Guru
    Currently Being Moderated
    854129 wrote:
    can anyone give me the real time examples of hierarchical,network and relational database and why we use relational database....and the best real time example for relational database?
    What do you mean by a "real time" example?

    What do you mean by "best" "real time example"?
  • 3. Re: relational database..........real time example
    857132 Newbie
    Currently Being Moderated
    we have a seminar in our college regarding different types of databases......to make it easy for everyone one to understand i thought of explaining it in the most common way using real time examples which we encounter in our daily life......

    thanks for the raising the issue.....
  • 4. Re: relational database..........real time example
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You are asking for "Real World" examples. Not "Real Time".

    "Real Time" has a very different meaning.


    Hemant K Chitale
  • 5. Re: relational database..........real time example
    857132 Newbie
    Currently Being Moderated
    ok...........give me real world examples!!!!!
  • 6. Re: relational database..........real time example
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    EMPLOYEE Information in an HRMS database.
    SALES Information in a Sales database.


    How about Oracle EBusiness Suite ? Or Peoplesoft Enterprise ?

    http://www.oracle.com/us/products/applications/ebusiness/index.html

    http://www.oracle.com/us/products/applications/peoplesoft-enterprise/index.html


    OR download and install an Oracle database with the Example schemas which contain sample schemas holding HR, Sales and other data.


    Hemant K Chitale
  • 7. Re: relational database..........real time example
    EdStevens Guru
    Currently Being Moderated
    854129 wrote:
    ok...........give me real world examples!!!!!
    The only hierachical database (product) I know about is IBM's IMS-DB
    There are many relational database products on the market, Oracle, MS SQL Server, and IBM's DB2 (two flavors) being the dominant
    I am not at all familiar with network database products.

    Of course, if your intent is to say "this website and/or application is backed by this or that type of database" . . . well, good luck with that. That information would only be known by people inside the organization using the particular database.
  • 8. Re: relational database..........real time example
    857132 Newbie
    Currently Being Moderated
    GL
    1)s.o.b name,descritpion,calendar name,currency,c.o.a name for all.
    select gsb.name "S.O.B NAME",
    gsb.description "DESC",
    gsb.period_set_name CALENDAR_NAME,
    gsb.currency_code CURRENCY,
    fs.id_flex_structure_code
    from
    gl_sets_of_books gsb,
    fnd_id_flex_structures FS
    WHERE
    gsb.chart_of_accounts_id=fs.id_flex_num;
    AND gsb.name like '468%';
    ------------------------------------------------

    2)calendar name,desc,period name,begin of period,end of period,period type,period type desc for all.
    SELECT gps.period_set_name CAL_NAME,
    gps.description CAL_DESC,
    GP.PERIOD_NAME ,GP.START_DATE,GP.END_DATE,
    GPT.PERIOD_TYPE,gpt.description PERIOD_TYPE_DESC
    FROM
    GL_PERIOD_SETS GPS,
    GL_PERIODS GP,
    GL_PERIOD_TYPES GPT
    WHERE
    GPS.PERIOD_SET_NAME=GP.PERIOD_SET_NAME
    AND GP.PERIOD_TYPE=GPT.PERIOD_TYPE
    AND gps.period_set_name LIKE '468%';

    ------------------------------------------------
    3)s.o.b name,period name,status for all open periods for ur s.o.b
    SELECT SOB.NAME,
    PSTAT.PERIOD_NAME,
    PSTAT.CLOSING_STATUS,
    GLK.MEANING
    FROM GL_PERIOD_STATUSES PSTAT,
    GL_SETS_OF_BOOKS SOB,
    GL_LOOKUPS GLK
    WHERE SOB.SET_OF_BOOKS_ID=pstat.set_of_books_id
    AND PSTAT.CLOSING_STATUS LIKE 'O'
    AND GLK.LOOKUP_CODE=pstat.closing_status
    AND GLK.LOOKup_type='CLOSING_STATUS'
    and sob.name like '%464%';
    ------------------------------------------------
    4)c.o.a for accounting flex field
    structure code,structure type,structure desc

    SELECT fifs.id_flex_structure_code "COA Code",
    tl.id_flex_structure_name "COA Name",
    tl.description
    FROM fnd_id_flexs fif,
         fnd_id_flex_structures fifs,
         fnd_id_flex_structures_tl tl
    WHERE fif.id_flex_code = fifs.id_flex_code
    AND fifs.application_id = fifs.application_id
    AND fifs.id_flex_code =tl.id_flex_code
    AND fifs.id_flex_num =tl.id_flex_num
    AND fif.id_flex_name like 'Accounting Flexfield'
    AND id_flex_structure_code like '%464%' ;
    ------------------------------------------------
    5)value set lo
    segment name,value set and values
    5)
    select fifs.Segment_name,
    ffvs.flex_value_set_name,
    ffv.flex_value
    from fnd_id_flex_segments fifs,
    fnd_flex_value_sets ffvs,
    fnd_flex_values_vl ffv
    where 1=1
    and fifs.flex_value_set_id = ffvs.flex_value_set_id
    and id_flex_num=52569;
    ------------------------------------------------
    6)journal name,desc,journal source,j.category,j.period,j.curr for different dates.
    6)
    SELECT name,
    je_category,
    je_source,
    period_name,
    currency_code,
    description
    FROM gl_je_headers
    WHERE je_source='Payables';
    ------------------------------------------------
    7)display the jour name,sob id,j.batch_name,j.source,j.category,period name,status for all unposted journals.
    7)
    select gjh.name,
    gjh.set_of_books_id,
    gjb.name,
    gjh.je_source,
    gjh.je_category,
    gjh.period_name,
    gjh.status
    from gl_je_headers gjh,
    gl_je_batches gjb
    where gjh.je_batch_id = gjb.je_batch_id
    and upper(gjh.status) = 'U';
    ------------------------------------------------

    8)j.name,j.line account,amount debited,credited,j.line desc
    8)
    select gjh.name,
    gcc.segment1 ||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 "Account Details",
    gjl.accounted_dr,
    gjl.accounted_cr,
    gjl.description
    from gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations gcc
    where 1=1
    and gjh.je_header_id = gjl.je_header_id
    and gjl.code_combination_id = gcc.code_combination_id;






    Inventory
    ============================================================
    1. Pull the details of item lot numbers.
    1).
    select msib.segment1 "Item Name",
    msib.description,
    ml.meaning,
    msib.auto_lot_alpha_prefix "Lot Prefix",
    msib.start_auto_lot_number "Lot Starting Number",
    mln.lot_number
    from mtl_system_items_b msib,
    mtl_lot_numbers mln,
    mfg_lookups ml
    where msib.organization_id = mln.organization_id
    and msib.inventory_item_id = mln.inventory_item_id
    and msib.lot_control_code = ml.lookup_code
    and ml.lookup_type like 'MTL_LOT_CONTROL'
    and msib.segment1 like 'sample_464';
    --and msib.segment1 like 'Lot control item';
    ---------------------------------------------------
    2. Display transaction quantity at Sub-Inventory level.
    2).
    select msib.segment1,
    moq.subinventory_code,
    sum(moq.transaction_quantity)
    from mtl_system_items_b msib,
    mtl_onhand_quantities moq --mtl_material_transactions
    where msib.inventory_item_id = moq.inventory_item_id
    and msib.organization_id = moq.organization_id
    and msib.inventory_item_id = 13128
    group by msib.segment1,
    moq.subinventory_code,
    moq.organization_id;
    ---------------------------------------------------
    3. Display details of Items, Organization and controls on that item.
    3).
    select item.segment1,
    org.organization_code,
    l1.meaning,
    l2.meaning,
    l3.meaning
    from mtl_system_items_b item,
    mtl_parameters org,
    mfg_lookups l1,
    mfg_lookups l2,
    mfg_lookups l3
    where 1=1
    and org.organization_id=item.organization_id
    and l1.lookup_code=item.lot_control_code
    and l1.lookup_type='MTL_LOT_CONTROL'
    and l2.Lookup_Code=Item.serial_number_control_code
    and l2.lookup_type='MTL_SERIAL_NUMBER'
    and l3.Lookup_Code=item.shelf_life_code
    and l3.lookup_type='MTL_SHELF_LIFE'
    and item.segment1 like 'sample_464';
    ---------------------------------------------------
    4. Query to list out main assembly item and corresponding Components
    4).
    select msib.segment1 "Component Name" ,
    msib1.segment1 "Sub-Components Name"
    from mtl_system_items_b msib,
    mtl_system_items_b msib1,
    bom_bill_of_materials bbom,
    bom_inventory_components bic
    where bbom.organization_id = msib.organization_id
    and bbom.assembly_item_id = msib.inventory_item_id
    and bbom.bill_sequence_id = bic.bill_sequence_id
    and bic.component_item_id = msib1.inventory_item_id
    and msib.segment1 like 'NokiaMobile'
    and bbom.alternate_bom_designator is null
    and msib1.organization_id = msib.organization_id;
    ---------------------------------------------------
    Components
    5. Item NO, Organization, Cost Type Name and material Cost of Item.
    5).
    select msib.segment1 "Item Name",
    mp.organization_code,
    cict.cost_type,
    cic.material_cost
    from mtl_system_items_b msib,
    mtl_parameters mp,
    cst_item_costs cic,
    cst_cost_types cict
    where 1-1=0
    and msib.organization_id = mp.organization_id
    and msib.organization_id = cic.organization_id
    and msib.inventory_item_id = cic.inventory_item_id
    and cic.cost_type_id = cict.cost_type_id
    and msib.segment1 like 'NokiaMobile'
    and cic.organization_id = 207;
    ---------------------------------------------------
    6. Display details of all Routing Information, if Standard Operation display Standard Operation Name.
    6).
    select msib.segment1,
    mp.organization_code,
    msib.description,
    bd.department_class_code,
    bd.department_code,
    br.resource_code,
    bos.operation_description,
    bso.operation_code
    from mtl_system_items_b msib,
    mtl_parameters mp,
    bom_operational_routings bor,
    bom_operation_sequences bos,
    bom_standard_operations bso,
    bom_departments bd,
    bom_department_classes bdc,
    bom_operation_resources borE,
    bom_resources br
    where 1=1
    and msib.organization_id = mp.organization_id
    and msib.inventory_item_id = bor.assembly_item_id
    and msib.organization_id = bor.organization_id
    and bor.routing_sequence_id = bos.routing_sequence_id
    and bos.standard_operation_id = bso.standard_operation_id (+)
    and bos.department_id = bd.department_id
    and bd.organization_id = bdc.organization_id
    and bd.department_class_code = bdc.department_class_code
    and bos.operation_sequence_id = bore.operation_sequence_id
    and bore.resource_id = br.resource_id
    and bd.organization_id = br.organization_id
    and msib.segment1 like 'NokiaMobile'
    and msib.organization_id = 207
    and bor.alternate_routing_designator is null;
    ---------------------------------------------------

    7.Write a query to find the Item name, Organization, Revision, Inventory Item Id of a item based on the Item
    name(Inventory).
    7).
    select msib.segment1 "Item Name",
    mp.organization_code,
    mir.revision_label
    from mtl_system_items_b msib,
    mtl_item_revisions mir,
    mtl_parameters mp
    where 1-1=0
    and msib.organization_id = mp.organization_id
    and msib.inventory_item_id = mir.inventory_item_id
    and msib.organization_id = mir.organization_id
    and msib.segment1 like 'NokiaMobile';

    Purchasing
    =================================================================================
    1)QUERY TO BE PREAPRED TO LIST ALL THE REQUISTIONS WITH STATUS
    REQNO,DOF REQ,APPROVED BY,AMOUNT OF REQ,STATUS,DESCRIPTION


    1).
    select h.SEGMENT1,h.CREATION_DATE,h.AUTHORIZATION_STATUS,
    h.DESCRIPTION,l.TO_PERSON_ID,AH.ACTION_CODE,
    E.FULL_NAME, L.quantity*l.unit_price "req.amt"
    from po_requisition_headers_all h,
    po_requisition_lines_all l,
    po_action_history AH,
    Hr_employees E
    where H.REQUISITION_HEADER_ID = L.REQUISITION_HEADER_ID
    AND H.REQUISITION_HEADER_ID = AH.OBJECT_ID
    AND AH.EMPLOYEE_ID = E.EMPLOYEE_ID
    AND H.SEGMENT1 = TO_CHAR(6076);
    -------------------------------------------------------------------------------- 2)QUERY TO PULL THE DEATILS OF RECEIVING TRANSACTION
    INVENTORY_CODE,INVEN NAME,ITEM NO,ORDERD QUANTITY,RECEIVED QUANTITY,VENDOR NAME,VENDOR ADD AND RECEIPT NO

    2)
    select msi.secondary_inventory_name, msi.description,
    msib.segment1, pla.quantity,sum(rsl.quantity_received),
    pv.vendor_name,pvsa.address_line1||' '||pvsa.address_line2||' '||
    pvsa.city||' '||pvsa.state "vendor address",
    ft.territory_short_name,rsh.receipt_num
    from po_headers_all pha,
    po_lines_all pla,
    mtl_system_items_b msib,
    rcv_shipment_headers rsh,
    rcv_shipment_lines rsl,
    mtl_secondary_inventories msi,
    po_vendors pv,
    po_vendor_sites_all pvsa,
    fnd_territories_tl ft
    where pv.vendor_name='Supriya'
    --and pha.segment1         = '6076'
    and pha.po_header_id = pla.po_header_id
    and pla.item_id = msib.inventory_item_id
    and pha.org_id = msib.organization_id
    and rsh.shipment_header_id = rsl.shipment_header_id
    and pha.po_header_id = rsl.po_header_id
    and pla.po_line_id = rsl.po_line_id
    and rsl.to_organization_id = msi.organization_id
    and rsl.to_subinventory = msi.secondary_inventory_name
    and pha.vendor_id = pv.vendor_id
    and pha.vendor_site_id = pvsa.vendor_site_id
    and pvsa.country = ft.territory_code
    group by msi.secondary_inventory_name, msi.description,
    msib.segment1, pla.quantity,pv.vendor_name,
    pvsa.address_line1,
    pvsa.address_line2,pvsa.city,pvsa.state,
    ft.territory_short_name,rsh.receipt_num;
    --------------------------------------------------------------------------------
    3)QUERY TO PULL THE DETAILS OF PURCHASE ORDERS
    PO NO,PO TYPE,PO CREATOR NAME,BUYER,APPROVER,STATUS,ITEM NO,ITEM DESCRIPTION,PO AMOUNT

    3).

    select pha.segment1, plct.description, hpc.party_name "po_creator",
    hpa.party_name "po_approver",plcs.displayed_field,
    plcs.description,msib.segment1,msib.description,
    (pla.unit_price * pla.quantity)
    from po_headers_all pha,
    hz_parties hpc,
    po_action_history pah,
    po_lookup_codes plct,
    hz_parties hpa,
    po_lookup_codes plcs,
    po_lines_all pla, mtl_system_items_b msib
    where 1=1
    and pha.segment1 = '4947'
    and pha.agent_id = hpc.person_identifier
    and pha.po_header_id = pah.object_id
    and pah.action_code = 'APPROVE'
    and plct.lookup_type = 'PO TYPE'
    and plct.lookup_code = pah.object_sub_type_code
    and pah.employee_id = hpa.person_identifier
    and plcs.lookup_type = 'AUTHORIZATION STATUS'
    and pha.authorization_status = plcs.lookup_code
    and pha.po_header_id = pla.po_header_id
    and pla.item_id = msib.inventory_item_id
    and pha.org_id = msib.organization_id;

    --------------------------------------------------------------------------------
    4)QUERY TO B PREPARED TO LIST THE DEATILS OF SUPPLIER,SUPPLIER DETAILS ALONG WITH THE NO.OF.PO PLACED DURING THE LAST_FINANCIAL_YEAR(THIS JUNE TO LAST_JUNE)

    4).

    select pv.vendor_name,
    pvsa.address_line1||' '||pvsa.address_line2||' '||
    pvsa.city||' '||pvsa.state "vendor address",ft.territory_short_name ,
    COUNT(pv.vendor_name)
    from po_vendors pv,
    po_headers_all pha,
    po_vendor_sites_all pvsa,
    fnd_territories_tl ft
    where 1=1
    and pv.vendor_name = 'Supriya'
    and pvsa.vendor_id = pv.vendor_id
    and pha.vendor_id = pv.vendor_id
    and pha.vendor_site_id = pvsa.vendor_site_id
    and pvsa.country = ft.territory_code
    and pha.org_id = 204
    GROUP BY pvsa.address_line1,pvsa.address_line2,pv.vendor_name,
    pvsa.city,pvsa.state,ft.territory_short_name;

    --------------------------------------------------------------------------------
    5)COMPLETE PO FROM REQNO TO RECEIPTS
    REQ NO,ITEM NO,REQ QUANTITY,ORG,TRANS QUANTITY,SUBINV CODE,DISTRIBUTION ACCOUNT,PO NO,PO TYPE,PO QUANTITY,VENDOR ID,VENDOR NAME,VENDOR ADD

    5).
    SELECT msib.segment1,prh.segment1 "Req Num",plc.description,
    pha.segment1 "PO Num", msi.secondary_inventory_name,pv.vendor_name,
    pvs.ADDRESS_LINE1||' '|| pvs.CITY, pla.quantity,prl.quantity,
    sum(pla.quantity*pla.unit_price),sum(prl.quantity*prl.unit_price),
    sum(rcv2.quantity) "quantity delivered"
    ,sum(rcvl.quantity_shipped) "quantity shipped"
    FROM po_headers_all pha,
    po_lines_all pla,
    po_distributions_all pda,
    po_req_distributions_all prd,
    po_requisition_lines_all prl,
    po_requisition_headers_all prh,
    rcv_transactions rcv2,
    rcv_shipment_headers rcvh,
    rcv_shipment_lines rcvl,
    po_vendors pv,
    po_vendor_sites_all pvs,
    mtl_system_items_b msib,
    mtl_secondary_inventories msi,
    po_lookup_codes plc
    WHERE pv.vendor_name='Supriya'
    AND pha.po_header_id = pda.po_header_id
    AND pda.req_distribution_id = prd.distribution_id
    AND prd.requisition_line_id = prl.requisition_line_id
    AND prl.requisition_header_id = prh.requisition_header_id
    and pha.po_header_id=pla.po_header_id
    and pha.org_id=204
    --and pha.segment1='&enter'
    AND pha.po_header_id =rcv2.po_header_id
    and pha.po_header_id =pla.po_header_id
    and pla.po_line_id=rcvl.po_line_id
    and pla.po_line_id=rcv2.po_line_id
    AND rcv2.transaction_type ='DELIVER'
    AND rcvh.shipment_header_id=rcv2.shipment_header_id
    and pha.vendor_id=pv.vendor_id
    and pha.vendor_site_id=pvs.vendor_site_id
    and msib.inventory_item_id=pla.item_id
    and msib.inventory_item_id=rcvl.item_id
    and msib.organization_id=rcvl.to_organization_id
    and plc.lookup_type='PO TYPE'
    and pha.type_lookup_code=plc.lookup_code
    --and msi.organization_id=rcvl.to_organization_id
    --and pha.org_id=204
    and msi.secondary_inventory_name=rcv2.subinventory
    and msib.organization_id=msi.organization_id
    group by msib.segment1,prh.segment1,plc.description,
    pha.segment1, msi.secondary_inventory_name,pv.vendor_name,
    pvs.ADDRESS_LINE1||' '|| pvs.CITY, pla.quantity,prl.quantity;





    Payables
    =================================================================================
    1)list all the invoices and supplier information
    supplier_no,supp name,supplier site,invoice no,invoice curr,amount

    1).
    select pv.segment1 "Supplier Number",
    pv.vendor_name "Supplier Name",
    pvsa.vendor_site_id "Supplier Site",
    aia.invoice_num "Invoice Number",
    aia.invoice_currency_code "Currency Code",
    aia.invoice_amount
    from ap_invoices_all aia,
    po_vendors pv,
    po_vendor_sites_all pvsa
    where 1=1
    and pv.vendor_id = pvsa.vendor_id
    and aia.vendor_id = pv.vendor_id
    and aia.vendor_site_id = pvsa.vendor_site_id
    and aia.org_id = 204
    and aia.invoice_num = 'HYD01';


    2)list all the open invoices showing balances(not paid and partially paid)
    invoice no,invoice amount,supplier no,supplier name,date of invoice creation,status(paid or not) and balance

    2).
    select aia.invoice_num
    ,aia.invoice_amount
    ,pv.vendor_id
    ,pv.vendor_name
    ,aia.invoice_date --New
    ,alc.displayed_field
    ,apsa.amount_remaining
    from ap_invoices_all aia,
    po_vendors pv,
    ap_payment_schedules_all apsa,
    ap_lookup_codes alc
    where 1=1
    and aia.vendor_id=pv.vendor_id
    and aia.invoice_id = apsa.invoice_id
    and apsa.payment_status_flag = alc.lookup_code
    and aia.org_id = 204
    and alc.lookup_type='INVOICE PAYMENT STATUS'
    and alc.displayed_field not in ('Yes')
    and aia.invoice_num = 'HYD01';

    3)list all the invoices on which the payment has been padi
    invoice no,do of invoice creation,do of payment of invoice,supplier name,payment document no

    3).
    select aia.invoice_num,
    aia.invoice_date,
    aipa.creation_date,
    aca.vendor_name,
    aca.check_number
    from ap_invoices_all aia,
    ap_invoice_payments_all aipa,
    ap_checks_all aca
    where 1=1
    and aia.invoice_id = aipa.invoice_id
    and aipa.check_id = aca.check_id
    and aia.org_id = 204
    and aia.invoice_num = 'HYD01';

    4)invoice no,do of invoice creation,do of payment of invoice,supplier name,distribution account

    4).
    select aia.invoice_num,
    aia.invoice_date,
    aia.creation_date,
    aida.amount,
    aia.invoice_id,
    aia.invoice_amount,
    aipa.creation_date payment_date,
    pv.vendor_name,
    gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5 "Distribution A/C"
    from ap_invoices_all aia,
    po_vendors pv,
    ap_invoice_distributions_all aida,
    gl_code_combinations gcc,
    ap_invoice_payments_all aipa
    where 1=1
    and aia.vendor_id = pv.vendor_id
    and aia.invoice_id = aida.invoice_id
    and aida.dist_code_combination_id = gcc.code_combination_id
    and aipa.invoice_id = aia.invoice_id
    and aipa.invoice_id = 10250
    and aia.org_id = 204
    --and pv.vendor_id = 1937
    order by aipa.invoice_id;



    OM
    =================================================================================
    1)develop a query which lists the orders which r on hold

    i)order date,order no,order type(name),order item,date of hold,reason of hold,hold by,days of hold.

    ii)for same query the no of days of hold should be a parameter

    1).
    SELECT h.order_number,
    ot.name,
    l.ordered_item,
    ohsa.creation_date,
    ohsa.hold_comment,
    fu.user_name,
    TRUNC(sysdate)-TRUNC(ohsa.creation_date)
    FROM oe_order_headers_all h,
    oe_order_lines_all l,
    oe_transaction_types_tl ot,
    oe_order_holds_all ooha,
    oe_hold_sources_all ohsa,
    fnd_user fu
    WHERE h.header_id = l.header_id
    AND h.order_type_id = ot.transaction_type_id
    AND h.header_id = ooha.header_id
    AND ooha.hold_source_id = ohsa.hold_source_id
    AND ohsa.created_by = fu.user_id
    AND order_number =57265;

    2)develop a query to list all the unbooked sales orders

    order no,order item,order quantity,order date,order status

    2).

    select oha.order_number,
    ola.ordered_item,
    ola.ordered_quantity,
    oha.ordered_date,
    oha.flow_status_code "Status"
    from oe_order_headers_all oha,
    oe_order_lines_all ola
    where 1=1
    and oha.header_id = ola.header_id
    and oha.org_id = 204
    and ola.flow_status_code = 'ENTERED';


    3)list all the orders which r backordered

    order no,customer no,cust name,order type(name),status,date of order,item no,description

    3).

    select oha.order_number,
    hp.party_name,
    hca.account_number,
    ott.name "Order Type",
    oha.flow_status_code "Order Status",
    ola.flow_status_code "Line Status",
    oha.ordered_date,
    ola.ordered_item,
    wdd.item_description
    from oe_order_headers_all oha,
    oe_order_lines_all ola,
    wsh_delivery_details wdd,
    fnd_lookup_values flv,
    oe_transaction_types_tl ott,
    hz_parties hp,
    hz_cust_accounts hca
    where 1=1
    and oha.header_id = ola.header_id
    and ott.transaction_type_id = oha.ORDER_TYPE_ID
    and wdd.source_header_id = ola.header_id
    and oha.sold_to_org_id = hca.cust_account_id(+)
    and hp.party_id = hca.party_id
    and flv.lookup_code = wdd.released_status
    and wdd.released_status = 'B'
    and oha.org_id = 204
    and oha.order_number = 57271
    and flv.lookup_type = 'PICK_STATUS';


    4)develop a query to list all the orders which r ready for shipping

    4).

    select oha.order_number,
    hp.party_name,
    oha.ordered_date,
    oha.batch_id,
    ola.flow_status_code
    from oe_order_headers_all oha,
    oe_order_lines_all ola,
    wsh_delivery_details wdd,
    hz_parties hp,
    hz_cust_accounts hca
    where 1=1
    and oha.header_id = ola.header_id
    and wdd.source_header_id = ola.header_id
    and wdd.source_line_id = ola.line_id
    and hca.cust_account_id = oha.sold_to_org_id
    and hp.party_id = hca.party_id
    and oha.org_id = 204
    and ola.flow_status_code = 'AWAITING_SHIPPING';








    Receivables
    =================================================================================
    1)develop a query to list customer details

    cust name,cust no,profile class,primary bill_to_address(country,address,city,state,postal code),primary ship_to_address(country,address,city,state,po)

    1)
    select hp1.party_name,
    hp1.party_id,
    hca.account_number,
    hcpc.name,
    hcsua.site_use_code,
    hl.country||' '||hl.address1||' '||hl.city||' '||hl.state||' '||hl.postal_code "Address"
    from hz_parties hp1
    , hz_cust_accounts hca
    , HZ_PARTY_SITES hps
    , hz_cust_profile_classes hcpc
    , hz_customer_profiles hcp
    , hz_locations hl
    , hz_cust_acct_sites_all hcasa
    , hz_cust_site_uses_all hcsua
    where 1=1
    and hca.account_number=3896
    and hcp.profile_class_id=hcpc.profile_class_id(+)
    and hca.cust_account_id=hcp.cust_account_id
    and hp1.party_id=hca.party_id
    --and   hca.cust_account_id=hcasa.cust_account_id
    and hps.party_site_id=hcasa.party_site_id
    and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id
    and hp1.party_id=hps.party_id
    and hl.location_id=hps.location_id
    and hcsua.primary_flag='Y'
    and hcp.site_use_id is null
    and hcasa.org_id=204
    ;



    -----------------------------------------------------------------------------------------------------------
    2)to extract customer invoice details,cust name,total invoice relevant to customer

    cust no,cust name,date of invoice generated,amount of invoice,amount of invoice last paid by the customer,credit limit,balance amount unpaid

    2)
    select hca.account_number
    , hp.party_name
    , rcta.trx_date
    , (select sum(rctla.extended_amount) from ra_customer_trx_lines_all rctla
    where rcta.customer_trx_id = rctla.customer_trx_id) "INVOICE AMOUNT"
    , hcpa.overall_credit_limit
    , apsa.amount_due_remaining
    , acra.amount
    from hz_parties hp
    , hz_cust_accounts hca
    , hz_customer_profiles hcp
    , hz_cust_profile_amts hcpa
    , ra_customer_trx_all rcta
    --, ra_customer_trx_lines_all rctla
    , ar_cash_receipts_all acra
    , ar_receivable_applications_all araa
    , ar_payment_schedules_all apsa
    where 1=1
    and hp.party_id = hca.party_id
    and hca.cust_account_id = hcp.cust_account_id
    and hcp.site_use_id is null
    and hcpa.cust_account_profile_id = hcp.cust_account_profile_id
    and hcpa.currency_code = 'USD'
    and rcta.sold_to_customer_id = hca.cust_account_id
    and rcta.org_id = 204
    --and       rcta.customer_trx_id    =   rctla.customer_trx_id
    and rcta.customer_trx_id = apsa.customer_trx_id
    and araa.applied_customer_trx_id = apsa.customer_trx_id
    and acra.cash_receipt_id = araa.cash_receipt_id
    and rcta.trx_number = '11792'
    and acra.cash_receipt_id = (select max(cash_receipt_id) from ar_receivable_applications_all araa_2
    where araa_2.applied_customer_trx_id = apsa.customer_trx_id)
    ;


    -----------------------------------------------------------------------------------------------------------
    3)to list all the invoices whicha r pending to b paid for more than 60 days from sysdate

    cust no,cust name,invoice no,date of invoice,amount,period of invoice pending for 30-60 days,period of invoice pending for 60-120 days,period of invoice pending for more than 120 days

    3)
    select hca.account_number
    , hp.party_name
    , rcta.trx_number
    , rcta.trx_date
    , sum(rctla.extended_amount)
    , case when trunc(sysdate-rcta.trx_date) between 30 and 60
    then rcta.trx_number
    end "Pending for 30 - 60 days"
    , case when trunc(sysdate-rcta.trx_date) between 60 and 120
    then rcta.trx_number
    end "Pending for 60 - 120 days"
    , case when trunc(sysdate-rcta.trx_date) > 120
    then rcta.trx_number
    end "Pending for more than 120 days"
    from hz_parties hp
    , hz_cust_accounts hca
    , ra_customer_trx_all rcta
    , ra_customer_trx_lines_all rctla
    where hp.party_id = hca.party_id
    and rcta.org_id = 204
    and hca.cust_account_id = rcta.sold_to_customer_id
    and rcta.customer_trx_id = rctla.customer_trx_id
    and hca.account_number = 3896
    group by hca.account_number
    , hp.party_name
    , rcta.trx_number
    , rcta.trx_date
    ;


    -----------------------------------------------------------------------------------------------------------

    -

    4)to list all the invoices paid during past 6 months

    cust no,cust name,invoice no,receipt no,date of invoive,date of payment,amount

    4)
    SELECT hca.account_number
    , hp.party_name
    , rcta.trx_number
    , acra.receipt_number
    , rcta.trx_date
    , acra.creation_date
    , acra.amount
    FROM hz_parties hp
    , hz_cust_accounts hca
    , ra_customer_trx_all rcta
    --, ra_customer_trx_lines_all rctla
    , ar_cash_receipts_all acra
    , ar_payment_schedules_all apsa
    , ar_receivable_applications_all araa
    , ar_lookups al
    where 1=1
    and hp.party_id = hca.party_id
    and rcta.sold_to_customer_id = hca.cust_account_id
    and rcta.org_id = 204
    --and       rcta.customer_trx_id    =   rctla.customer_trx_id
    and rcta.customer_trx_id = apsa.customer_trx_id
    and araa.applied_customer_trx_id = apsa.customer_trx_id
    and acra.cash_receipt_id = araa.cash_receipt_id
    and acra.status = al.lookup_code
    and al.lookup_type = 'PAYMENT_TYPE'
    and rcta.trx_number = '11792'
    and trunc(months_between(sysdate,araa.creation_date)) <= 6;

    =================================================================================================================

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points