This discussion is archived
1 Reply Latest reply: Dec 9, 2013 7:23 AM by ActiveSomeTimes RSS

hi all,i will get  some duplicate record with the below query please  help me

f41ed4a8-2a70-4b0d-997f-1138848733b1 Newbie
Currently Being Moderated

select      oos.name source_name,

            ara.apply_date inv_paid_date,

            rsa.name sales_person,

            eso.org_id,

            cii.serial_number,

            rt.transaction_date received_date,

            rt.transaction_type,

            eso.order_number eso,

            eso.orig_sys_document_ref,

            eso.ordered_date eso_order_entry_date,

            eso.booked_date eso_booked_date,

            eso_ln.schedule_arrival_date eso_requested_arrival_date,

            eso_ln.line_number,

            eso_ln.ordered_item,

            eso_ln.flow_status_code eso_status,

            (   eso_ln.line_number

             || '.'

             || eso_ln.shipment_number

             || '.'

             || eso_ln.option_number

             || '.'

             || eso_ln.component_number)

               eso_line_number,

            prl.requisition_line_id,

            prh.segment1,

            iso.order_number iso,

            iso.ordered_date iso_order_entry_date,

            iso_ln.line_number iso_line_number,

            iso_ln.actual_shipment_date iso_ship_date,

            we.wip_entity_name,

            wfs.date_closed wip_completion_date,

            eso_ln.actual_shipment_date eso_ship_date,

            iso_ln.flow_status_code iso_status,

            rct.trx_date invoice_date,

--            SUM (rcl.extended_amount / rcl.quantity_invoiced) invoice_amount,

            rcgd.gl_posted_date revenue_recognition_date,

            eso_ln.ordered_quantity,

            cii.instance_number ib_number,

            cii.active_start_date install_start_date,

            cii.install_date,

            cii.active_end_date install_closure_date,

            hp.party_number customer_party_number,

            hp.party_name,

            (   hl.address1

             || ''

             || hl.address2

             || ' '

             || hl.address3

             || ''

             || hl.address4)

               customer_ship_to_address,

            hl.city ship_to_city,

            ftt.territory_short_name ship_to_country,

            (   bill_hl.address1

             || ''

             || bill_hl.address2

             || ' '

             || bill_hl.address3

             || ''

             || bill_hl.address4)

               bill_to_address,

            bill_hl.city bill_to_city,

            bill_ftt.territory_short_name bill_to_country,

            oos.description source_description

FROM        oe_order_headers_all                eso,

            oe_order_lines_all                  eso_ln,

            po_requisition_headers_all          prh,

            po_requisition_lines_all            prl,

            oe_order_headers_all                iso,

            oe_order_lines_all                  iso_ln,

            wip_flow_schedules                  wfs,

            ra_customer_trx_all                 rct,

            ra_customer_trx_lines_all           rcl,

            apps.ra_cust_trx_line_gl_dist_all   rcgd,

            apps.ar_receivable_applications_all ara,

            ra_salesreps_all                    rsa,

            csi_item_instances                  cii,

            rcv_transactions                    rt,

            rcv_serial_transactions             rst,

            wip_entities                        we,

            hz_cust_site_uses_all               ship_su,

            hz_cust_acct_sites_all              hcas,

            hz_party_sites                      hps,

            hz_locations                        hl,

            hz_parties                          hp,

            fnd_territories_tl                  ftt,

            hz_cust_site_uses_all               bill_su,

            hz_cust_acct_sites_all              bill_hcas,

            hz_party_sites                      bill_hps,

            hz_locations                        bill_hl,

            hz_parties                          bill_hp,

            fnd_territories_tl                  bill_ftt,

            oe_order_sources                    oos,

            mtl_interorg_ship_methods           mis

     WHERE   eso_ln.header_id               = eso.header_id

            AND eso.header_id               = prh.attribute15

            AND eso_ln.line_id              = prl.attribute15

            AND iso_ln.source_document_id   = prh.requisition_header_id

            AND iso_ln.source_document_line_id = prl.requisition_line_id

            AND iso_ln.ship_from_org_id     = 7017

            AND mis.FROM_ORGANIZATION_ID    = 7017

            AND iso_ln.header_id                = iso.header_id

            AND iso_ln.ordered_item             = eso_ln.ordered_item

            AND wfs.demand_source_line(+)       = iso_ln.line_id

            AND rct.interface_header_attribute1(+) = TO_CHAR (eso.order_number)

            AND rct.customer_trx_id             = rcl.customer_trx_id(+)

            AND ara.applied_customer_trx_id(+)  = rct.customer_trx_id

            AND rcgd.customer_trx_id(+)            = rcl.customer_trx_id

            AND rcgd.customer_trx_line_id(+)       = rcl.customer_trx_line_id           

            AND rt.requisition_line_id(+)          = prl.requisition_line_id

            AND rt.transaction_id                  = rst.transaction_id(+)

            AND rsa.salesrep_id                    = eso_ln.salesrep_id

            AND we.wip_entity_id(+)                = wfs.wip_entity_id

            AND cii.last_oe_order_line_id(+)       = eso_ln.line_id

            AND iso_ln.ship_from_org_id            = 7017

             AND mis.from_organization_id          =7017

            AND UPPER (rcl.line_type(+)) IN ('LINE', 'TAX')

            AND TRUNC (eso.creation_date) BETWEEN '01-JAN-2010' AND '01-DEC-2013'

            --AND rt.source_document_code     IN ('INVENTORY','PO','REQ','RMA')

            --AND rcgd.account_class IN ('REV', 'TAX')

--            AND rt.transaction_type IN ('RECEIVE','APPROVED','CORRECT','DELIVER','RETURN TO CUSTOMER','REJECT','TRANSFER','RETURN TO VENDOR','RETURN TO RECEIVING')          

            AND rt.TRANSACTION_TYPE !='DELIVER'

            AND ship_su.site_use_id         = eso.ship_to_org_id

            AND hcas.cust_acct_site_id      = ship_su.cust_acct_site_id

            AND hps.party_site_id           = hcas.party_site_id

            AND hl.location_id              = hps.location_id

            AND hp.party_id                 = hps.party_id

            AND ftt.territory_code          = hl.country

            AND ftt.language                = USERENV ('lang')

            AND bill_su.site_use_id         = eso.invoice_to_org_id

            AND bill_hcas.cust_acct_site_id = bill_su.cust_acct_site_id

            AND bill_hps.party_site_id      = bill_hcas.party_site_id

            AND bill_hl.location_id         = bill_hps.location_id

            AND bill_hp.party_id            = bill_hps.party_id

            AND bill_ftt.territory_code     = bill_hl.country

            AND bill_ftt.language           = USERENV ('lang') 

            AND oos.order_source_id         = eso.order_source_id

            AND mis.to_organization_id      = eso_ln.ship_from_org_id

    GROUP BY eso.ordered_date,

            eso.booked_date,

            eso.order_number,

            iso.order_number,

            iso.ordered_date,

            iso_ln.line_number,

            eso.orig_sys_document_ref,

            eso_ln.schedule_arrival_date,

            iso_ln.actual_shipment_date,

            wfs.date_closed,

            eso_ln.actual_shipment_date,

            iso_ln.flow_status_code,

            eso_ln.flow_status_code,

            rct.trx_date,

            rcgd.gl_posted_date,

            cii.instance_number,

            cii.active_start_date,

            cii.active_end_date,

            eso_ln.ordered_item,

            eso_ln.line_number,

            cii.install_date,

            cii.serial_number,

            rt.transaction_date,

            rt.transaction_type,

            ara.apply_date,

            we.wip_entity_name,

            prl.requisition_line_id,

            hp.party_number,

            hp.party_name,

            (   hl.address1

             || ''

             || hl.address2

             || ' '

             || hl.address3

             || ''

             || hl.address4),

            hl.city,

            ftt.territory_short_name,

            (   bill_hl.address1

             || ''

             || bill_hl.address2

             || ' '

             || bill_hl.address3

             || ''

             || bill_hl.address4),

            bill_hl.city,

            bill_ftt.territory_short_name,

            oos.name,

            oos.description,

            rsa.name,

            eso_ln.ordered_quantity,

            (   eso_ln.line_number

             || '.'

             || eso_ln.shipment_number

             || '.'

             || eso_ln.option_number

             || '.'

             || eso_ln.component_number),

            prh.segment1,

            eso.org_id,

            (eso_ln.schedule_arrival_date - mis.intransit_time);

Legend

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