crssconfig/crss-metering/application-oracle-query.yml

mq:
  manifest:
    header:
      insert: >
        INSERT INTO TXN_MQ_MANIFEST_HEADER
        (
          HEADER_ID, TRANSACTION_ID, MSP_SHORTNAME, FILE_COUNT, NOTIFICATION_SENT, TAIL_RECEIVED, CATEGORY, UPLOAD_BY, UPLOAD_DATETIME
        )
        VALUES (
          MQ_MANIFEST_HEADER_SEQ.NEXTVAL, :transactionID, :mspShortName, :fileCount, 'N', 'N', :category, :uploadedBy, :uploadDateTime
        )
      query: >
        SELECT HEADER_ID AS headerID, TRANSACTION_ID AS transactionID, MSP_SHORTNAME AS mspShortName, FILE_COUNT AS fileCount,
          CATEGORY AS category, NOTIFICATION_SENT AS notificationSent, NOTIFICATION_DATETIME AS notificationDateTime,
          TAIL_RECEIVED AS tailReceived, UPLOAD_BY AS uploadedBy, UPLOAD_DATETIME AS UploadDateTime
        FROM TXN_MQ_MANIFEST_HEADER
        WHERE HEADER_ID = :headerID
      count: >
        SELECT COUNT(*) FROM TXN_MQ_MANIFEST_HEADER WHERE TAIL_RECEIVED = 'N' AND HEADER_ID = :headerID
    trailer:
      update: >
        UPDATE TXN_MQ_MANIFEST_HEADER SET TAIL_RECEIVED = 'Y' WHERE HEADER_ID = :headerID
    file:
      insert: >
        INSERT INTO TXN_MQ_MANIFEST_FILE (file_id, header_id, transaction_id, filename, filetype, filesize, checksum, upload_datetime)
        VALUES (
          MQ_MANIFEST_FILE_SEQ.NEXTVAL, :headerID, :transactionID, :fileName, :fileType, :fileSize, :checksum, :uploadDateTime
        )
      status: >
        SELECT FILE_ID AS fileID,
               HEADER_ID AS headerID,
               TRANSACTION_ID AS transactionID,
               FILENAME,
               PROCESS_FLAG AS processFlag,
               STATUS,
               ERROR_DETAILS AS errorDetails
        FROM TXN_MQ_MANIFEST_FILE
        WHERE HEADER_ID = :headerID
          AND PROCESS_FLAG = 'Y'
      query: >
        SELECT A.FILE_ID as fileID,
               A.HEADER_ID AS headerID,
               A.TRANSACTION_ID AS transactionID,
               A.FILENAME,
               A.FILETYPE,
               A.FILESIZE,
               A.CHECKSUM,
               A.UPLOAD_DATETIME AS uploadDateTime,
               A.PROCESS_FLAG AS processFlag,
               A.STATUS,
               A.ERROR_DETAILS AS errorDetails
        FROM TXN_MQ_MANIFEST_FILE A INNER JOIN TXN_MQ_MANIFEST_HEADER B ON A.HEADER_ID = B.HEADER_ID
        WHERE B.HEADER_ID = :headerID
      unprocessed: >
        SELECT A.FILE_COUNT - COUNT(B.HEADER_ID) AS UNPROCESSED_COUNT
        FROM TXN_MQ_MANIFEST_HEADER A
        INNER JOIN TXN_MQ_MANIFEST_FILE B ON A.HEADER_ID = B.HEADER_ID
        WHERE A.HEADER_ID = :headerID
        GROUP BY A.HEADER_ID
    status: >
      UPDATE TXN_MQ_MANIFEST_FILE SET PROCESS_FLAG = 'Y', STATUS = :status, ERROR_DETAILS = :errorDetail, PROCESS_DATETIME = :processDateTime
      WHERE FILE_ID = :fileID
    upload:
      status: >
         select count(*) from txn_mq_manifest_header h
         where
           file_count=(
             select count(*) from txn_mq_manifest_file
               where header_id=h.header_id and process_flag='Y')
           and header_id=:headerID
      report: >
        select h.header_id as headerID, h.msp_shortname as mspShortName, h.upload_by as uploadedBy, h.upload_datetime as uploadDateTime,
          sum(case when status='ACCEPTED' then 1 else 0 end) as acceptedFileCount,
          sum(case when status='REJECTED' then 1 else 0 end) as rejectedFileCount
        from
          txn_mq_manifest_header h, txn_mq_manifest_file f
        where
          f.header_id = h.header_id and h.header_id=:headerID
        GROUP BY h.header_id, h.upload_datetime
      notif.status: >
        UPDATE TXN_MQ_MANIFEST_HEADER SET NOTIFICATION_SENT = 'Y', NOTIFICATION_DATETIME = :dateTime WHERE HEADER_ID = :headerID
      notif.stale: >
        SELECT HEADER_ID FROM TXN_MQ_MANIFEST_HEADER WHERE NOTIFICATION_SENT = 'N' AND UPLOAD_DATETIME < CURRENT_DATE
    filter-by.status: >
        select * from txn_mq_manifest_file where status=:status and header_id=:headerID
  meter:
    daily:
      insert: >
        INSERT INTO TXN_METER_DATA_DAILY (
          METER_DATA_ID, FILE_ID, SEIN, INTERVAL, READING_DATETIME,
          KWD, KWD_CHANNEL_STATUS, KWD_INTERVAL_STATUS,
          KWHD, KWHD_CHANNEL_STATUS, KWHD_INTERVAL_STATUS,
          KVARHD, KVARHD_CHANNEL_STATUS, KVARHD_INTERVAL_STATUS,
          KWR, KWR_CHANNEL_STATUS, KWR_INTERVAL_STATUS,
          KWHR, KWHR_CHANNEL_STATUS, KWHR_INTERVAL_STATUS,
          KVARHR, KVARHR_CHANNEL_STATUS, KVARHR_INTERVAL_STATUS,
          VAN, VAN_CHANNEL_STATUS, VAN_INTERVAL_STATUS,
          VBN, VBN_CHANNEL_STATUS, VBN_INTERVAL_STATUS,
          VCN, VCN_CHANNEL_STATUS, VCN_INTERVAL_STATUS,
          IAN, IAN_CHANNEL_STATUS, IAN_INTERVAL_STATUS,
          IBN, IBN_CHANNEL_STATUS, IBN_INTERVAL_STATUS,
          ICN, ICN_CHANNEL_STATUS, ICN_INTERVAL_STATUS,
          PF, PF_CHANNEL_STATUS, PF_INTERVAL_STATUS,
          ESTIMATION_FLAG, MSP_SHORTNAME, CATEGORY, CREATED_DATE_TIME)
        VALUES (
          MQ_DATA_DAILY_SEQ.NEXTVAL, :fileID, :sein, :interval, :readingDateTime,
          :kwd, :kwdChannelStatus, :kwdIntervalStatus,
          :kwhd, :kwhdChannelStatus, :kwhdIntervalStatus,
          :kvarhd, :kvarhdChannelStatus, :kvarhdIntervalStatus,
          :kwr, :kwrChannelStatus, :kwrIntervalStatus,
          :kwhr, :kwhrChannelStatus, :kwhrIntervalStatus,
          :kvarhr, :kvarhrChannelStatus, :kvarhrIntervalStatus,
          :van, :vanChannelStatus, :vanIntervalStatus,
          :vbn, :vbnChannelStatus, :vbnIntervalStatus,
          :vcn, :vcnChannelStatus, :vcnIntervalStatus,
          :ian, :ianChannelStatus, :ianIntervalStatus,
          :ibn, :ibnChannelStatus, :ibnIntervalStatus,
          :icn, :icnChannelStatus, :icnIntervalStatus,
          :pf, :pfChannelStatus, :pfIntervalStatus,
          :estimationFlag, :mspShortName, :uploadType, :createdDateTime)
    monthly:
      insert: >
        INSERT INTO TXN_METER_DATA_MONTHLY (
          METER_DATA_ID, FILE_ID, SEIN, INTERVAL, READING_DATETIME,
          KWD, KWD_CHANNEL_STATUS, KWD_INTERVAL_STATUS,
          KWHD, KWHD_CHANNEL_STATUS, KWHD_INTERVAL_STATUS,
          KVARHD, KVARHD_CHANNEL_STATUS, KVARHD_INTERVAL_STATUS,
          KWR, KWR_CHANNEL_STATUS, KWR_INTERVAL_STATUS,
          KWHR, KWHR_CHANNEL_STATUS, KWHR_INTERVAL_STATUS,
          KVARHR, KVARHR_CHANNEL_STATUS, KVARHR_INTERVAL_STATUS,
          VAN, VAN_CHANNEL_STATUS, VAN_INTERVAL_STATUS,
          VBN, VBN_CHANNEL_STATUS, VBN_INTERVAL_STATUS,
          VCN, VCN_CHANNEL_STATUS, VCN_INTERVAL_STATUS,
          IAN, IAN_CHANNEL_STATUS, IAN_INTERVAL_STATUS,
          IBN, IBN_CHANNEL_STATUS, IBN_INTERVAL_STATUS,
          ICN, ICN_CHANNEL_STATUS, ICN_INTERVAL_STATUS,
          PF, PF_CHANNEL_STATUS, PF_INTERVAL_STATUS,
          ESTIMATION_FLAG, MSP_SHORTNAME, CATEGORY, CREATED_DATE_TIME)
        VALUES (
          MQ_DATA_MONTHLY_SEQ.NEXTVAL, :fileID, :sein, :interval, :readingDateTime,
          :kwd, :kwdChannelStatus, :kwdIntervalStatus,
          :kwhd, :kwhdChannelStatus, :kwhdIntervalStatus,
          :kvarhd, :kvarhdChannelStatus, :kvarhdIntervalStatus,
          :kwr, :kwrChannelStatus, :kwrIntervalStatus,
          :kwhr, :kwhrChannelStatus, :kwhrIntervalStatus,
          :kvarhr, :kvarhrChannelStatus, :kvarhrIntervalStatus,
          :van, :vanChannelStatus, :vanIntervalStatus,
          :vbn, :vbnChannelStatus, :vbnIntervalStatus,
          :vcn, :vcnChannelStatus, :vcnIntervalStatus,
          :ian, :ianChannelStatus, :ianIntervalStatus,
          :ibn, :ibnChannelStatus, :ibnIntervalStatus,
          :icn, :icnChannelStatus, :icnIntervalStatus,
          :pf, :pfChannelStatus, :pfIntervalStatus,
          :estimationFlag, :mspShortName, :uploadType, :createdDateTime)
bcq:
  manifest: >
    INSERT INTO TXN_BCQ_UPLOAD_FILE (
      FILE_ID,
      TRANSACTION_ID,
      FILE_NAME,
      FILE_SIZE,
      SUBMITTED_DATE,
      VALIDATION_STATUS)
    VALUES (HIBERNATE_SEQUENCE.NEXTVAL, ?, ?, ?, ?, ?)
  header:
    insert: >
      INSERT INTO TXN_BCQ_HEADER (
        HEADER_ID,
        FILE_ID,
        SELLING_MTN,
        BILLING_ID,
        BUYING_PARTICIPANT_NAME,
        BUYING_PARTICIPANT_SHORT_NAME,
        SELLING_PARTICIPANT_NAME,
        SELLING_PARTICIPANT_SHORT_NAME,
        STATUS,
        TRADING_DATE,
        DEADLINE_DATE,
        UPDATED_VIA,
        CREATED_DATE,
        MODIFIED_DATE)
      VALUES (NEXTVAL('HIBERNATE_SEQUENCE'),
        :fileId, :sellingMtn, :billingId,
        :buyingParticipantName, :buyingParticipantShortName,
        :sellingParticipantName, :sellingParticipantShortName,
        :status, :tradingDate, :deadlineDate, :updatedVia,
        now(), now())
    status:
      update: >
        UPDATE TXN_BCQ_HEADER SET
          STATUS = :status,
          MODIFIED_DATE = now()
        WHERE HEADER_ID = :headerId
      update-settlement: >
        UPDATE TXN_BCQ_HEADER SET
          STATUS = :status,
          UPDATED_VIA = :updatedVia,
          MODIFIED_DATE = now()
        WHERE HEADER_ID = :headerId
      select-by-status-and-deadlinedate-plus-days: >
        SELECT
          HEADER_ID
        FROM TXN_BCQ_HEADER
        WHERE
          STATUS = :status AND
          (DEADLINE_DATE + (:plusDays || ' day') :: interval) :: date <= current_date;  
    list:
      header-join-file: >
        TXN_BCQ_HEADER A INNER JOIN TXN_BCQ_UPLOAD_FILE B ON A.FILE_ID = B.FILE_ID
      sub-select:
        transaction-id: >
          (SELECT STRING_AGG(D.TRANSACTION_ID, ', ' ORDER BY D.SUBMITTED_DATE)
           FROM TXN_BCQ_HEADER C INNER JOIN TXN_BCQ_UPLOAD_FILE D ON C.FILE_ID = D.FILE_ID
           WHERE A.SELLING_MTN = C.SELLING_MTN
             AND A.BILLING_ID = C.BILLING_ID
             AND A.TRADING_DATE = C.TRADING_DATE
             AND C.STATUS != 'VOID')
        submitted-date: >
          (SELECT STRING_AGG(TO_CHAR(D.SUBMITTED_DATE, 'YYYY-DD-MM hh:MI AM'), ', ' ORDER BY D.SUBMITTED_DATE)
           FROM TXN_BCQ_HEADER C INNER JOIN TXN_BCQ_UPLOAD_FILE D ON C.FILE_ID = D.FILE_ID
           WHERE A.SELLING_MTN = C.SELLING_MTN
             AND A.BILLING_ID = C.BILLING_ID
             AND A.TRADING_DATE = C.TRADING_DATE
             AND C.STATUS != 'VOID')
        deadline-date: >
          (SELECT STRING_AGG(TO_CHAR(C.DEADLINE_DATE, 'YYYY-DD-MM'), ', ' ORDER BY D.SUBMITTED_DATE)
           FROM TXN_BCQ_HEADER C INNER JOIN TXN_BCQ_UPLOAD_FILE D ON C.FILE_ID = D.FILE_ID
           WHERE A.SELLING_MTN = C.SELLING_MTN
             AND A.BILLING_ID = C.BILLING_ID
             AND A.TRADING_DATE = C.TRADING_DATE
             AND C.STATUS != 'VOID')
        status: >
          (SELECT STRING_AGG(C.STATUS, ', ' ORDER BY D.SUBMITTED_DATE)
           FROM TXN_BCQ_HEADER C INNER JOIN TXN_BCQ_UPLOAD_FILE D ON C.FILE_ID = D.FILE_ID
           WHERE A.SELLING_MTN = C.SELLING_MTN
             AND A.BILLING_ID = C.BILLING_ID
             AND A.TRADING_DATE = C.TRADING_DATE
             AND C.STATUS != 'VOID')
        updated-via: >
          (SELECT STRING_AGG(COALESCE(C.UPDATED_VIA, ' '), ', ' ORDER BY D.SUBMITTED_DATE)
           FROM TXN_BCQ_HEADER C INNER JOIN TXN_BCQ_UPLOAD_FILE D ON C.FILE_ID = D.FILE_ID
           WHERE A.SELLING_MTN = C.SELLING_MTN
             AND A.BILLING_ID = C.BILLING_ID
             AND A.TRADING_DATE = C.TRADING_DATE
             AND C.STATUS != 'VOID')
      unique: >
        HEADER_ID IN(
          SELECT
            DISTINCT ON (SELLING_MTN, BILLING_ID, TRADING_DATE) HEADER_ID
          FROM TXN_BCQ_HEADER A
            INNER JOIN TXN_BCQ_UPLOAD_FILE B
              ON A.FILE_ID = B.FILE_ID
          ORDER BY
            SELLING_MTN,
            BILLING_ID,
            TRADING_DATE,
            SUBMITTED_DATE DESC)
  data:
    insert: >
      INSERT INTO TXN_BCQ_DATA (
        BCQ_DATA_ID,
        BCQ_HEADER_ID,
        REFERENCE_MTN,
        START_TIME,
        END_TIME,
        BCQ)
      VALUES (HIBERNATE_SEQUENCE.NEXTVAL, ?, ?, ?, ?, ?)
  report:
    flattened: >
      SELECT
        header.selling_participant_name || ' (' ||
          header.selling_participant_short_name || ')' as selling_participant,
        header.buying_participant_name || ' (' ||
          header.buying_participant_short_name || ')' as buying_participant,
        header.selling_mtn,
        header.billing_id,
        header.trading_date,
        file.transaction_id,
        file.submitted_date,
        header.deadline_date,
        header.status,
        header.updated_via,
        data.reference_mtn,
        data.end_time,
        data.bcq
      FROM txn_bcq_data data
        INNER JOIN txn_bcq_header header ON data.header_id = header.header_id
        INNER JOIN txn_bcq_upload_file file ON header.file_id = file.file_id
      WHERE header.status != 'VOID'
  event:
    insert: >
      INSERT INTO TXN_BCQ_SPECIAL_EVENT (
        EVENT_ID,
        DEADLINE_DATE,
        REMARKS,
        CREATED_DATE)
      VALUES (NEXTVAL('HIBERNATE_SEQUENCE'), :deadlineDate, :remarks, now())
    trading-date:
      insert: >
        INSERT INTO TXN_BCQ_EVENT_TRADING_DATE (
          EVENT_ID,
          TRADING_DATE)
        VALUES (?, ?)
    participant:
      insert: >
        INSERT INTO TXN_BCQ_EVENT_PARTICIPANT (
          EVENT_ID,
          TRADING_PARTICIPANT,
          PARTICIPANT_NAME)
        VALUES (?, ?, ?)
    validate: >
      SELECT DISTINCT
        ep.trading_participant,
        etd.trading_date,
        count(1) OVER (PARTITION BY date_trunc('day', etd.trading_date), ep.trading_participant) AS trading_date_count
      FROM txn_bcq_special_event se
      INNER JOIN txn_bcq_event_participant ep
        ON se.event_id = ep.event_id
      INNER JOIN txn_bcq_event_trading_date etd
        ON se.event_id = etd.event_id
      WHERE se.deadline_date >= :dateToday
        AND ep.trading_participant IN (:tradingParticipants)
        AND date_trunc('day', etd.trading_date) IN (:tradingDates)
      ORDER BY etd.trading_date asc
    list: >
      SELECT
        se.event_id,
        se.created_date,
        se.deadline_date,
        se.remarks,
        ARRAY_AGG(DISTINCT ep.participant_name || ' (' || ep.trading_participant || ')') AS trading_participants,
        ARRAY_AGG(DISTINCT etd.trading_date) AS trading_dates
      FROM txn_bcq_special_event se
        INNER JOIN txn_bcq_event_participant ep
          ON se.event_id = ep.event_id
        INNER JOIN txn_bcq_event_trading_date etd
          ON se.event_id = etd.event_id
      GROUP BY se.event_id, se.deadline_date, se.remarks
      ORDER BY se.created_date DESC;