APPS.OKL_BILLING_UTIL_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 43

   SELECT max(ractrx.trx_date)
   FROM ra_customer_trx_all ractrx
   WHERE EXISTS (
               SELECT 'x'
               FROM ra_customer_trx_lines_all ractrl
               WHERE ractrl.customer_trx_id = ractrx.customer_trx_id
               AND ractrl.interface_line_attribute6 = (SELECT contract_number
                                                       FROM okc_k_headers_b
                                                       WHERE ID = p_contract_id)
               );

Line: 112

   SELECT chr.id,
          SUM(ractrl.amount_due_original) amount
   FROM ra_customer_trx_lines_all ractrl,
        okc_k_headers_b chr
   WHERE chr.contract_number = ractrl.interface_line_attribute6
   AND EXISTS(SELECT sty.code
              FROM OKL_STRM_TYPE_B sty
              WHERE sty.stream_type_purpose = p_stream_purpose
              AND sty.code = ractrl.interface_line_attribute9)
   GROUP BY chr.id;

Line: 156

  SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  LINK_TO_CUST_TRX_LINE_ID = p_line_id;

Line: 184

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad,
             ra_customer_trx_lines_all lines
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('INV','CM') --Receipt can be applied against credit memo
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CASH'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
      AND    lines.link_to_cust_trx_line_id = p_line_id
      AND    lines.line_type = 'TAX';

Line: 205

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 250

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad,
             ra_customer_trx_lines_all lines
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   = 'INV'
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
      AND    lines.link_to_cust_trx_line_id = p_line_id
      AND    lines.line_type = 'TAX';

Line: 271

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 321

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) line_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad
      WHERE  app.status                  = 'APP'
      AND    app.payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('CM') --Receipt can be applied against credit memo
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = p_line_id;

Line: 335

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad,
             ra_customer_trx_lines_all lines
      WHERE  app.status                  = 'APP'
      AND    app.payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('CM')
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
      AND    lines.link_to_cust_trx_line_id = p_line_id
      AND    lines.line_type = 'TAX';

Line: 356

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 438

SELECT SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0)) AMOUNT_DUE_ORIGINAL
FROM   AR_PAYMENT_SCHEDULES_ALL APS,
       RA_CUSTOMER_TRX_ALL RACTRX
WHERE  RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND    RACTRX.CUSTOMER_TRX_ID = p_header_id;

Line: 456

      SELECT NVL(SUM(app.amount_applied),0) AMOUNT_APPLIED
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('INV','CM') --Receipt can be applied against credit memo
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CASH';

Line: 476

      SELECT NVL(SUM(app.amount_applied),0) AMOUNT_CREDITED
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   = 'INV'
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM';

Line: 496

SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
FROM   AR_PAYMENT_SCHEDULES_ALL APS,
       RA_CUSTOMER_TRX_ALL RACTRX
WHERE  RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND    RACTRX.CUSTOMER_TRX_ID = p_header_id;

Line: 513

SELECT RACTRXLN.customer_trx_line_id
FROM   RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
WHERE  RACTRXLN.LINE_TYPE = 'LINE'
AND    RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
AND    RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;

Line: 536

SELECT RACTRXLN.line_number
FROM   RA_CUSTOMER_TRX_LINES_ALL RACTRXLN
WHERE  RACTRXLN.LINE_TYPE = 'LINE'
AND    RACTRXLN.interface_line_context = 'OKL_CONTRACTS'
AND    RACTRXLN.CUSTOMER_TRX_ID = p_customer_trx_id;

Line: 560

      SELECT new_seq.nextval into l_seq_num from dual;

Line: 561

      INSERT INTO DEBUG_TABLE_k VALUES(l_seq_num,SYSDATE, msg);

Line: 568

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 574

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 611

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 617

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 654

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 660

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 697

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 703

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 754

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 760

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 766

  SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  LINK_TO_CUST_TRX_LINE_ID = p_line_id
  AND    LINE_TYPE='TAX';

Line: 850

SELECT LNS.CUSTOMER_TRX_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL LNS,
       RA_CUSTOMER_TRX_ALL HDR
WHERE  LNS.INTERFACE_LINE_ATTRIBUTE6 = p_contract_number
  AND  HDR.TRX_NUMBER                = p_cust_trx_number
  AND  HDR.CUSTOMER_TRX_ID           = LNS.CUSTOMER_TRX_ID;

Line: 889

  SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  LINK_TO_CUST_TRX_LINE_ID = p_line_id
  AND    LINE_TYPE='TAX';

Line: 933

                SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
                FROM OKL_TXL_AR_INV_LNS_B
                     , OKL_TXD_AR_LN_DTLS_B
                     , OKL_TRX_AR_INVOICES_B
                     , OKL_K_HEADERS
                     , OKC_K_HEADERS_B
                WHERE OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
                      AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
                      AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
                      AND OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
                      AND OKL_TXD_AR_LN_DTLS_B.ID NOT IN
                      (  SELECT OKL_XTL_SELL_INVS_B.TLD_ID
                         FROM OKL_XTL_SELL_INVS_B
                              , OKL_EXT_SELL_INVS_B
                         WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
                                AND OKL_XTL_SELL_INVS_B.TLD_ID IS NOT NULL)
                UNION
                SELECT OKC_K_HEADERS_B.CONTRACT_NUMBER
                FROM OKL_TXL_AR_INV_LNS_B TIL
                     , OKL_TRX_AR_INVOICES_B
                     , OKL_K_HEADERS
                     , OKC_K_HEADERS_B
                WHERE OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
                      AND OKL_K_HEADERS.ID = OKL_TRX_AR_INVOICES_B.KHR_ID
                      AND TIL.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
                      AND NOT EXISTS (
                       SELECT 1 FROM OKL_TXD_AR_LN_DTLS_B TXD
                       WHERE TXD.TIL_ID_DETAILS = TIL.ID
                      )
                      AND TIL.ID NOT IN
                      (SELECT OKL_XTL_SELL_INVS_B.TIL_ID
                       FROM OKL_XTL_SELL_INVS_B
                            , OKL_EXT_SELL_INVS_B
                       WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
                             AND OKL_XTL_SELL_INVS_B.TIL_ID IS NOT NULL);

Line: 975

            SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
            FROM OKL_XTL_SELL_INVS_B
                ,OKL_EXT_SELL_INVS_B
                ,OKL_XTL_SELL_INVS_TL
                ,OKL_TRX_AR_INVOICES_B
                ,OKL_TXL_AR_INV_LNS_B
                ,OKL_TXD_AR_LN_DTLS_B
                ,OKC_K_HEADERS_B
            WHERE OKL_EXT_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_B.XSI_ID_DETAILS
            AND OKL_XTL_SELL_INVS_B.ID = OKL_XTL_SELL_INVS_TL.ID
            AND OKL_XTL_SELL_INVS_B.LSM_ID IS NULL
            AND (OKL_TXL_AR_INV_LNS_B.ID = OKL_XTL_SELL_INVS_B.TIL_ID OR OKL_XTL_SELL_INVS_B.TLD_ID = OKL_TXD_AR_LN_DTLS_B.ID)
            AND OKL_TXD_AR_LN_DTLS_B.TIL_ID_DETAILS = OKL_TXL_AR_INV_LNS_B.ID
            AND  OKL_TXL_AR_INV_LNS_B.TAI_ID = OKL_TRX_AR_INVOICES_B.ID
            AND OKL_TRX_AR_INVOICES_B.KHR_ID = OKC_K_HEADERS_B.id;

Line: 995

                SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
                FROM OKL_CNSLD_AR_STRMS_B
                        , OKL_CNSLD_AR_LINES_B
                        , OKL_CNSLD_AR_HDRS_B
                        , OKL_K_HEADERS
                        , OKC_K_HEADERS_B
                WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
                        AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
                        AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
                        AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
                        AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
                        AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NULL;

Line: 1011

                SELECT DISTINCT OKC_K_HEADERS_B.CONTRACT_NUMBER
                FROM OKL_CNSLD_AR_STRMS_B
                        , OKL_CNSLD_AR_LINES_B
                        , OKL_CNSLD_AR_HDRS_B
                        , OKL_K_HEADERS
                        , OKC_K_HEADERS_B
                WHERE OKL_CNSLD_AR_LINES_B.CNR_ID = OKL_CNSLD_AR_HDRS_B.ID
                        AND OKL_CNSLD_AR_STRMS_B.LLN_ID = OKL_CNSLD_AR_LINES_B.ID
                        AND OKC_K_HEADERS_B.ID = OKL_K_HEADERS.ID
                        AND OKL_K_HEADERS.ID = OKL_CNSLD_AR_STRMS_B.KHR_ID
                        AND OKL_CNSLD_AR_STRMS_B.KHR_ID = OKL_CNSLD_AR_LINES_B.KHR_ID
                        AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID < 0
                        AND OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID IS NOT NULL;

Line: 1241

SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  INTERFACE_LINE_ATTRIBUTE14 = p_interface_line_attribute14
AND    INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS';

Line: 1247

SELECT COUNT(1) LINE_COUNT
FROM   RA_CUSTOMER_TRX_LINES_ALL
WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
AND    LINE_TYPE = 'LINE';

Line: 1317

SELECT SUM(NVL(APS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING
FROM   AR_PAYMENT_SCHEDULES_ALL APS,
       RA_CUSTOMER_TRX_ALL RACTRX
WHERE  RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND    RACTRX.CUSTOMER_TRX_ID = p_header_id;

Line: 1336

  SELECT NVL(EXTENDED_AMOUNT, 0) LINE_AMOUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  CUSTOMER_TRX_ID = p_header_id
  AND    CUSTOMER_TRX_LINE_ID = p_line_id;

Line: 1360

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   = 'INV'
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = p_line_id;

Line: 1387

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) line_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('INV','CM') --Receipt can be applied against credit memo
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CASH'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = p_line_id;

Line: 1415

SELECT SUM(NVL(APS.AMOUNT_ADJUSTED,0)) AMOUNT_ADJUSTED
FROM   AR_PAYMENT_SCHEDULES_ALL APS,
       RA_CUSTOMER_TRX_ALL RACTRX
WHERE  RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND    RACTRX.CUSTOMER_TRX_ID = p_header_id;

Line: 1434

  SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
  FROM   ar_adjustments_all adj,
         ar_payment_schedules_all sch,
         ar_distributions_all dist,
         ra_customer_trx_lines_all lines
  WHERE  adj.payment_schedule_id = sch.payment_schedule_id
  AND    sch.class = 'INV'
  AND    sch.customer_trx_id = p_header_id
  AND    adj.ADJUSTMENT_ID = dist.source_id
  AND    dist.source_table = 'ADJ'
  AND    dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
  AND    lines.link_to_cust_trx_line_id = p_line_id
  AND    lines.line_type = 'TAX';

Line: 1453

  SELECT COUNT(1) LINE_COUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
  AND    LINE_TYPE = 'LINE';

Line: 1502

  SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) amt_adjsuted
  FROM   ar_distributions_all dist ,
         ar_adjustments_all adj ,
         ar_payment_schedules_all aps
  WHERE  dist.source_table = 'ADJ'
  AND    dist.source_id = adj.adjustment_id
  AND    aps.customer_trx_id = p_header_id
  AND    adj.payment_schedule_id = aps.payment_schedule_id
  AND    aps.class = 'INV'
  AND    ref_customer_trx_line_id = p_line_id;

Line: 1531

  SELECT SUM(NVL(EXTENDED_AMOUNT, 0)) LINE_TAX_AMOUNT
  FROM   RA_CUSTOMER_TRX_LINES_ALL
  WHERE  LINK_TO_CUST_TRX_LINE_ID = p_line_id
  AND    AMOUNT_INCLUDES_TAX_FLAG = 'Y';

Line: 1559

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) tax_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad,
             ra_customer_trx_lines_all lines
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   IN ('INV','CM') --Receipt can be applied against credit memo
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CASH'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
      AND    lines.link_to_cust_trx_line_id = p_line_id
      AND    lines.line_type = 'TAX'
      AND    lines.amount_includes_tax_flag = 'Y';

Line: 1595

      SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) credit_applied
      FROM   ar_receivable_applications_all app,
             ar_payment_schedules_all sch,
             ar_distributions_all ad,
             ra_customer_trx_lines_all lines
      WHERE  app.status                  = 'APP'
      AND    app.applied_payment_schedule_id = sch.payment_schedule_id
      AND    sch.class                   = 'INV'
      AND    sch.customer_trx_id         = p_header_id
      AND    app.application_type = 'CM'
      AND    app.receivable_application_id = ad.source_id
      AND    ad.source_table = 'RA'
      AND    ad.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
      AND    lines.link_to_cust_trx_line_id = p_line_id
      AND    lines.line_type = 'TAX'
      AND    lines.amount_includes_tax_flag = 'Y';

Line: 1632

  SELECT SUM(nvl(dist.amount_cr,0))- SUM(nvl(dist.amount_dr,0)) tax_adjusted
  FROM   ar_adjustments_all adj,
         ar_payment_schedules_all sch,
         ar_distributions_all dist,
         ra_customer_trx_lines_all lines
  WHERE  adj.payment_schedule_id = sch.payment_schedule_id
  AND    sch.class = 'INV'
  AND    sch.customer_trx_id = p_header_id
  AND    adj.ADJUSTMENT_ID = dist.source_id
  AND    dist.source_table = 'ADJ'
  AND    dist.ref_Customer_trx_Line_Id = lines.customer_trx_line_id
  AND    lines.link_to_cust_trx_line_id = p_line_id
  AND    lines.line_type = 'TAX'
  AND    lines.amount_includes_tax_flag = 'Y';

Line: 1709

   SELECT SUM(ractrl.amount_due_original) amount
     FROM ra_customer_trx_lines_all ractrl
    WHERE ractrl.interface_line_attribute6 IN
             (select chr.contract_number
                from okc_k_headers_b chr,
                     okc_governances gv,
                     okc_k_headers_b crchr
               where crchr.id = p_cr_contract_id
                 and crchr.sts_code = 'ACTIVE'
                 and crchr.scs_code = 'CREDITLINE_CONTRACT'
                 and gv.chr_id_referred = crchr.id
                 and gv.dnz_chr_id = chr.id
                 and chr.scs_code = 'LEASE'
               UNION
               select chr.contract_number
                 from okc_k_headers_b   mla_chr,
                      okc_governances   mla_g1,
                      okc_k_headers_b   chr
                where mla_chr.id       = mla_g1.chr_id_referred
                  and mla_chr.scs_code = 'MASTER_LEASE'
                  and mla_chr.id IN
                      (
                       select cl_gl.dnz_chr_id
                         from okc_k_headers_b   cl_chr,
                              okc_governances   cl_gl
                        where cl_chr.id       = p_cr_contract_id
                          and cl_chr.sts_code = 'ACTIVE'
                          and cl_chr.scs_code = 'CREDITLINE_CONTRACT'
                          and cl_chr.id       = cl_gl.chr_id_referred
                      )
                  and chr.id = mla_g1.dnz_chr_id
                  and chr.scs_code = 'LEASE'
              )
      AND EXISTS(SELECT sty.code
                   FROM OKL_STRM_TYPE_B sty
                  WHERE sty.stream_type_purpose = p_stream_purpose
                    AND sty.code = ractrl.interface_line_attribute9);