8 Replies Latest reply: Jun 20, 2011 2:08 AM by 857132 RSS

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

    857132
      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
          http://lmgtfy.com/?q=+relational+database+example
          • 2. Re: relational database..........real time example
            EdStevens
            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
              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
                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
                  ok...........give me real world examples!!!!!
                  • 6. Re: relational database..........real time example
                    Hemant K Chitale
                    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
                      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
                        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;

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