Search This Blog

Saturday, April 7, 2012

SQL Query to Extract Service Contracts Lines Information

SELECT DISTINCT msib.segment1 NAME,
                (SELECT klt.invoice_text
                   FROM apps.okc_k_lines_b okl2,
                        apps.oks_k_lines_b klb,
                        apps.oks_k_lines_tl klt
                  WHERE okl2.cle_id = okl.ID
                    AND okl2.ID = klb.cle_id
                    AND klb.ID = klt.ID
                    AND klt.invoice_text IS NOT NULL
                    AND ROWNUM < 2) invoice_text,  --Version 1.2
                okl.*,
                (SELECT oisv.NAME
                   FROM apps.okc_k_lines_b okl2,
                        apps.okc_k_lines_b okl1,
                        apps.okc_k_items oki,
                        apps.oks_cov_action_times_v cov,
                        apps.okx_incident_severits_v oisv
                  WHERE okl.ID = okl1.cle_id
                    AND okl1.ID = okl2.cle_id
                    AND okl2.ID = oki.cle_id
                    AND oki.cle_id = cov.bus_process_cle_id
                    AND cov.incident_severity_id1 = oisv.id1
                    AND ROWNUM < 2) severity,
                osl.sequence_no seq_num, osl.level_periods number_of_periods,
                osl.start_date bill_start_date, osl.end_date bill_end_date,
                osl.uom_per_period DURATION,
                (SELECT unit_of_measure
                   FROM apps.mtl_units_of_measure
                  WHERE uom_code = osl.uom_code) period,
                osl.level_amount amount,
                  NVL (osl.level_periods, 0)
                * NVL (osl.level_amount, 0) total_amount,
                osl.invoice_offset_days, osl.interface_offset_days
           FROM apps.okc_k_headers_b okh,
                apps.okc_k_lines_b okl,
                apps.mtl_system_items_b msib,
                apps.oks_stream_levels_b osl,
                apps.okc_k_items oki1
          WHERE okh.ID = okl.dnz_chr_id
            AND okl.ID = oki1.cle_id
            AND msib.inventory_item_id = oki1.object1_id1
            AND msib.organization_id = 144
            AND okl.ID = osl.cle_id
            AND okl.lse_id IN (1, 12)
            AND okh.sts_code IN ('ACTIVE', 'EXPIRED', 'QA_HOLD')
            AND okh.contract_number = &CONTRACT_NUMBER
       ORDER BY okl.line_number;

1 comment: