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: 220

      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: 285

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: 303

      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: 323

      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: 343

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: 360

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: 383

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: 407

      SELECT new_seq.nextval into l_seq_num from dual;

Line: 408

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

Line: 415

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: 421

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

Line: 458

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: 464

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

Line: 501

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: 507

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

Line: 544

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: 550

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

Line: 601

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: 607

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

Line: 613

  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: 697

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: 736

  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: 780

                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: 822

            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: 842

                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: 858

                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: 1088

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: 1094

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

Line: 1164

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: 1183

  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: 1207

      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: 1234

      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: 1260

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: 1279

  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: 1317

  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: 1346

  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: 1374

      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: 1410

      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: 1447

  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';