mq: manifest: header: insert: > INSERT INTO TXN_MQ_MANIFEST_HEADER (HEADER_ID, TRANSACTION_ID, FILE_COUNT, TAIL_RECEIVED, CATEGORY, UPLOAD_BY, UPLOAD_DATETIME) VALUES ( MQ_MANIFEST_HEADER_SEQ.NEXTVAL, :transactionID, :fileCount, 'N', :category, :uploadedBy, :uploadDateTime ) query: > SELECT HEADER_ID AS headerID, TRANSACTION_ID AS transactionID, FILE_COUNT AS fileCount, CATEGORY AS category, TAIL_RECEIVED AS tailReceived 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 ) 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 status: > UPDATE TXN_MQ_MANIFEST_FILE SET PROCESS_FLAG = 'Y', STATUS = :status, ERROR_DETAILS = :errorDetail 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.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.target.department: MSP,METERING 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 ( BCQ_HEADER_ID, FILE_ID, SELLING_MTN, BILLING_ID, BUYING_PARTICIPANT_USER_ID, BUYING_PARTICIPANT_NAME, BUYING_PARTICIPANT_SHORT_NAME, SELLING_PARTICIPANT_USER_ID, SELLING_PARTICIPANT_NAME, SELLING_PARTICIPANT_SHORT_NAME, STATUS, TRADING_DATE, DEADLINE_DATE) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) update: > UPDATE TXN_BCQ_HEADER SET FILE_ID = ?, STATUS = ?, UPDATED_VIA = ? WHERE SELLING_MTN = ? AND BILLING_ID = ? AND TRADING_DATE = ? AND SELLING_PARTICIPANT_SHORT_NAME = ? count: > SELECT COUNT(*) FROM TXN_BCQ_HEADER WHERE SELLING_MTN = ? AND BILLING_ID = ? AND TRADING_DATE = ? AND SELLING_PARTICIPANT_SHORT_NAME = ? id: > SELECT BCQ_HEADER_ID FROM TXN_BCQ_HEADER WHERE SELLING_MTN = ? AND BILLING_ID = ? AND TRADING_DATE = ? AND SELLING_PARTICIPANT_SHORT_NAME = ? status: > UPDATE TXN_BCQ_HEADER SET STATUS = ? WHERE BCQ_HEADER_ID = ? data: insert: > INSERT INTO TXN_BCQ_DATA ( BCQ_DATA_ID, BCQ_HEADER_ID, REFERENCE_MTN, START_TIME, END_TIME, BCQ) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, ?, ?, ?, ?, ?) update: > UPDATE TXN_BCQ_DATA SET REFERENCE_MTN = ?, BCQ = ? WHERE END_TIME = ? AND BCQ_HEADER_ID = ? details: > SELECT REFERENCE_MTN, END_TIME, BCQ FROM TXN_BCQ_DATA WHERE BCQ_HEADER_ID = ? display: data: > SELECT BCQ_HEADER_ID, SELLING_MTN, BILLING_ID, BUYING_PARTICIPANT_USER_ID, BUYING_PARTICIPANT_NAME, BUYING_PARTICIPANT_SHORT_NAME, SELLING_PARTICIPANT_USER_ID, SELLING_PARTICIPANT_NAME, SELLING_PARTICIPANT_SHORT_NAME, TRADING_DATE, DEADLINE_DATE, TRANSACTION_ID, SUBMITTED_DATE, STATUS, UPDATED_VIA FROM TXN_BCQ_HEADER A INNER JOIN TXN_BCQ_UPLOAD_FILE B ON A.FILE_ID = B.FILE_ID count: > SELECT COUNT(*) FROM TXN_BCQ_HEADER A INNER JOIN TXN_BCQ_UPLOAD_FILE B ON A.FILE_ID = B.FILE_ID paginate: > SELECT * FROM ( SELECT AB.*, ROWNUM RNUM FROM ({SELECT_QUERY}) AB WHERE ROWNUM < ((({PAGE_NO} + 1) * {PAGE_SIZE}) + 1)) WHERE RNUM >= (({PAGE_NO} * {PAGE_SIZE}) + 1)