0 Replies Latest reply on Sep 20, 2017 4:18 AM by 1567114

    MERGE JOIN CARTESIAN in using HZ tables with OKC Tables

    1567114

      Hello,

       

      Trying to Join Service Contracts Table with HZ tables getting MERGE JOIN CARTESIAN:

       

      EXPLAIN PLAN FOR

      (

      SELECT

        hdr.*

           FROM apps.okc_k_headers_all_b hdr,

                apps.okc_k_rel_objs_v ord_hdr,

                apps.okx_order_headers_v ord_inf,

                apps.csi_item_instances cii,

                apps.csi_instance_statuses csi,

                apps.okc_k_items oki,

                apps.okc_k_lines_b okl,

                apps.OKS_AUTH_LINES_V okt,

                apps.OKS_K_HEADERS_b khr,

                apps.oks_line_details_v okld,

                apps.mtl_system_items_b msi,

                apps.mtl_system_items_b msib,

                apps.hz_cust_site_uses_all hcsu_ship,

                apps.hz_cust_acct_sites_all hcas_ship,

                apps.hz_cust_accounts_all hca_ship,

                apps.hz_parties hp_ship,

                apps.hz_party_sites hps_ship,

                apps.hz_locations hl_ship,

                  --       apps.fnd_territories_tl ftt_ship,

           --     apps.hz_cust_site_uses_all hcsu_bill,

           --     apps.hz_cust_acct_sites_all hcas_bill,

           --     apps.hz_cust_accounts hca_bill,

           --     apps.hz_parties hp_bill,

           --     apps.hz_party_sites hps_bill,

           --     apps.hz_locations hl_bill,

           --     apps.fnd_territories_tl ftt_bill,

                apps.okc_k_lines_b ser_line                                    --4/9

          WHERE     1 = 1

                AND oki.jtot_object1_code = 'OKX_CUSTPROD'

                AND ser_line.dnz_chr_id = hdr.id                               --4/9

                AND ser_line.id = okl.cle_id                                   --4/9

                AND TO_CHAR (cii.instance_id) = oki.object1_id1

                AND (CASE

                        WHEN msib.segment1 LIKE 'URL%'

                        THEN

                           (NVL (

                               NVL (

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1,

                                          apps.okc_k_lines_b okl2,

                                          apps.okc_k_items kkk2,

                                          apps.mtl_system_items_b msi

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND okl2.chr_id = hdr1.id

                                          AND okl2.id = okl1.cle_id

                                          AND okl2.id = kkk2.cle_id

                                          AND kkk2.jtot_object1_code =

                                                 'OKX_SERVICE'

                                          AND kkk2.object1_id1 =

                                                 msi.inventory_item_id

                                          AND kkk2.object1_id2 =

                                                 msi.organization_id

                                          AND msi.segment1 LIKE 'URL%'

                                          -- and hdr1.scs_code = hdr.scs_code

                                          AND hdr1.scs_code = 'SERVICE'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'ACTIVE', 1,

                                                         'SIGNED', 1,

                                                         'ENTERED', 1,

                                                         0)),

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1,

                                          apps.okc_k_lines_b okl2,

                                          apps.okc_k_items kkk2,

                                          apps.mtl_system_items_b msi

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND okl2.chr_id = hdr1.id

                                          AND okl2.id = okl1.cle_id

                                          AND okl2.id = kkk2.cle_id

                                          AND kkk2.jtot_object1_code =

                                                 'OKX_SERVICE'

                                          AND kkk2.object1_id1 =

                                                 msi.inventory_item_id

                                          AND kkk2.object1_id2 =

                                                 msi.organization_id

                                          AND msi.segment1 LIKE 'URL%'

                                          -- and hdr1.scs_code = hdr.scs_code

                                          AND hdr1.scs_code = 'WARRANTY'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'ACTIVE', 1,

                                                         'SIGNED', 1,

                                                         'ENTERED', 1,

                                                         0)))                  --NVL

                                                             ,

                               NVL (

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1,

                                          apps.okc_k_lines_b okl2,

                                          apps.okc_k_items kkk2,

                                          apps.mtl_system_items_b msi

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND okl2.chr_id = hdr1.id

                                          AND okl2.id = okl1.cle_id

                                          AND okl2.id = kkk2.cle_id

                                          AND kkk2.jtot_object1_code =

                                                 'OKX_SERVICE'

                                          AND kkk2.object1_id1 =

                                                 msi.inventory_item_id

                                          AND kkk2.object1_id2 =

                                                 msi.organization_id

                                          AND msi.segment1 LIKE 'URL%'

                                          AND hdr1.scs_code = 'SERVICE'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'TERMINATED', 1,

                                                         'CANCELLED', 1,

                                                         'EXPIRED', 1,

                                                         0)),

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1,

                                          apps.okc_k_lines_b okl2,

                                          apps.okc_k_items kkk2,

                                          apps.mtl_system_items_b msi

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND okl2.chr_id = hdr1.id

                                          AND okl2.id = okl1.cle_id

                                          AND okl2.id = kkk2.cle_id

                                          AND kkk2.jtot_object1_code =

                                                 'OKX_SERVICE'

                                          AND kkk2.object1_id1 =

                                                 msi.inventory_item_id

                                          AND kkk2.object1_id2 =

                                                 msi.organization_id

                                          AND msi.segment1 LIKE 'URL%'

                                          AND hdr1.scs_code = 'WARRANTY'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'TERMINATED', 1,

                                                         'CANCELLED', 1,

                                                         'EXPIRED', 1,

                                                         0)))                  --nvl

                                                             )                 --NVL

                                                              )

                        ELSE

                           (NVL (

                               NVL (

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          -- and hdr1.scs_code = hdr.scs_code

                                          AND hdr1.scs_code = 'SERVICE'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'ACTIVE', 1,

                                                         'SIGNED', 1,

                                                         'ENTERED', 1,

                                                         0)),

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          -- and hdr1.scs_code = hdr.scs_code

                                          AND hdr1.scs_code = 'WARRANTY'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'ACTIVE', 1,

                                                         'SIGNED', 1,

                                                         'ENTERED', 1,

                                                         0)))                  --NVL

                                                             ,

                               NVL (

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND hdr1.scs_code = 'SERVICE'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'TERMINATED', 1,

                                                         'CANCELLED', 1,

                                                         'EXPIRED', 1,

                                                         0)),

                                  (SELECT MAX (okl1.end_date)

                                     FROM apps.okc_k_items kkk,

                                          apps.okc_k_headers_all_b hdr1,

                                          apps.okc_k_lines_b okl1

                                    WHERE     kkk.jtot_object1_code =

                                                 'OKX_CUSTPROD'

                                          AND kkk.object1_id1 =

                                                 TO_CHAR (cii.instance_id)

                                          AND kkk.dnz_chr_id = hdr1.id

                                          AND okl1.id = kkk.cle_id

                                          AND okl1.dnz_chr_id = hdr1.id

                                          AND hdr1.scs_code = 'WARRANTY'

                                          AND 1 =

                                                 DECODE (okl1.sts_code,

                                                         'TERMINATED', 1,

                                                         'CANCELLED', 1,

                                                         'EXPIRED', 1,

                                                         0)))                  --nvl

                                                             )                 --NVL

                                                              )

                     END) = okl.end_date

                --aded on 11/11/2014

                AND oki.cle_id = okl.id

                AND okl.id = okt.id

                AND hdr.id = khr.chr_id

                AND okl.dnz_chr_id = hdr.id

                AND okld.contract_id = hdr.id

                AND okl.cle_id = okld.line_id

                AND okld.object1_id1 = TO_CHAR (msib.inventory_item_id)

                AND okld.object1_id2 = TO_CHAR (msib.organization_id)

                AND ord_inf.id1(+) = ord_hdr.object1_id1

                AND ord_hdr.chr_id(+) = hdr.id

                AND msi.inventory_item_id = cii.inventory_item_id

                AND msi.organization_id = cii.inv_master_organization_id

                AND cii.instance_status_id = csi.instance_status_id

                AND ord_hdr.cle_id(+) IS NULL

                AND ord_hdr.jtot_object1_code(+) = 'OKX_ORDERHEAD'

                -- SHIP To --

                AND NVL (hdr.ship_to_site_use_id, ser_line.ship_to_site_use_id) =

                       hcsu_ship.site_use_id                                   --4/9

                AND hcsu_ship.site_use_code = 'SHIP_TO'

                AND hcas_ship.cust_acct_site_id = hcsu_ship.cust_acct_site_id

                AND hca_ship.cust_account_id = hcas_ship.cust_account_id

                --AND hl_ship.country = ftt_ship.territory_code

                AND hps_ship.location_id = hl_ship.location_id

                AND hl_ship.location_id =hps_ship.location_id

                AND hps_ship.party_site_id = hcas_ship.party_site_id

                AND hp_ship.party_id = hps_ship.party_id

                AND hp_ship.party_id = hca_ship.party_id

                --BILL TO --

                /*

                AND NVL (hdr.BILL_TO_SITE_USE_ID, ser_line.BILL_TO_SITE_USE_ID) =

                       hcsu_bill.site_use_id                                   --4/9

                AND hcsu_bill.site_use_code = 'BILL_TO'

                AND hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id

                AND hca_bill.cust_account_id = hcas_bill.cust_account_id

                AND hl_bill.country = ftt_bill.territory_code

                AND hps_bill.location_id = hl_bill.location_id

                AND hps_bill.party_site_id = hcas_bill.party_site_id

                AND hp_bill.party_id = hps_bill.party_id

                AND hp_bill.party_id = hca_bill.party_id

                */

                )