reports

CREATE OR REPLACE FORCE VIEW  "CASH_COUNTER_SUMMARY_REPORT" ("P_DATE", "CASH", "NETS_OR_CDA", "CASHCARD", "CHEQUE", "CREDIT_CARD", "VOUCHER", "IB", "COMPANY_CODE", "CLINIC_CODE", "PAYMENT_MODE", "TOTAL") AS
  SELECT To_date('2010-8-8', 'yyyy-mm-dd') as p_Date,'CASH1' as CASH, 'NETS1' as NETS_or_CDA, 'CASHCARD1' as CASHCARD
, 'CHEQUE1' as CHEQUE
, 'CREDIT1' as CREDIT_CARD
, 'VOUCHER1' as VOUCHER
, 'IB1' as IB
, 'A' as company_code
, '5' as clinic_code
, 'CHECK' as payment_mode
,10 as Total
FROM eposdba.ADDRESS_MS

评论

17 楼 rogerhunt 2010-08-07  

No, Company Code, Agency code, Agency Name, Bill No, Amount Assigned, Assignment Date, Amount Collected, Collection Date,
No of days to collect, % of Collection (Amt collected vs Amount Assigned)


CREATE OR REPLACE FORCE VIEW "EPOSDBA"."COLLECTION_PERFORMANCE" ("NO", "COMPANY_CODE", "AGENCY_CODE", "AGENCY_NAME",
"BILL_NO", "AMOUNT_ASSIGNED", "ASSIGNMENT_DATE", "AMOUNT_COLLECTED", "COLLECTION_DATE", "COLLECT_DAYS_NO",
"COLLECTION") AS
SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'08' as AGENCY_CODE,
'TAISENG' as AGENCY_NAME,
'01' as BILL_NO,
'1234' as AMOUNT_ASSIGNED,
To_date('2010-8-8', 'yyyy-mm-dd') as ASSIGNMENT_DATE,
'ADU' as AMOUNT_COLLECTED,
To_date('2010-8-9', 'yyyy-mm-dd') as COLLECTION_DATE,
'89' as COLLECT_DAYS_NO,
'PR' as COLLECTION
FROM
DOMAIN_MS dm;


BlockedBlack_Listed Customer Listing

Customer_Listing


No, Company Code, Customer Group, Customer Code, Customer Name, Block indicator, Block Date, Block Reason, Black Listed indicator,
Black Listed Date, Black Listed Reason

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."COSTOMER_LISTING" ("NO", "COMPANY_CODE", "CUSTOMER_GROUP", "CUSTOMER_CODE",
"CUSTOMER_NAME", "BLOCK_INDICATOR", "BLOCK_DATE", "BLOCK_REASON", "BLACK_LISTED_INDICATOR", "BLACK_LISTED_DATE",
"BLACK_LISTED_REASON") AS
SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'08' as CUSTOMER_GROUP,
'TAISENG' as CUSTOMER_GROUP,
'01' as CUSTOMER_NAME,
'1234' as BLOCK_INDICATOR,
To_date('2010-8-8', 'yyyy-mm-dd') as BLOCK_DATE,
'ADU' as BLOCK_REASON,
'LEVE01' as BLACK_LISTED_INDICATOR,
To_date('2010-8-9', 'yyyy-mm-dd') as BLACK_LISTED_DATE,
'NOREASON' as BLACK_LISTED_REASON
FROM
DOMAIN_MS dm;


--------------------------------------------------------------------------------------------------------------

Recalctrant Debtors Report

No Company Code Customer Group Customer Code Customer Name Contact Nos. Doc Date Posting Date

Doc No. Outstanding Amount Remarks

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."RECALCTRANT_DEBTORS" ("NO", "COMPANY_CODE", "CUSTOMER_GROUP", "CUSTOMER_CODE",
"CUSTOMER_NAME", "CONTACT_NOS", "DOC_DATE", "POSTING_DATE", "DOC_NO", "OUTSTANDING_AMOUNT", "REMARKS") AS
SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'08' as CUSTOMER_GROUP,
'01' as CUSTOMER_CODE,
'TAISENG' as CUSTOMER_NAME,
'1234' as CONTACT_NOS,
To_date('2010-8-8', 'yyyy-mm-dd') as DOC_DATE,
To_date('2010-8-9', 'yyyy-mm-dd') as POSTING_DATE,
'DOC01' as DOC_NO,
'1234' as OUTSTANDING_AMOUNT,
'NOREASON' as REMARKS
FROM
DOMAIN_MS dm;


------------------------------------------------------------------------------------------------------------------

No, Company Code, Clinic, Customer Code, Customer Name, Visit Date, Doc Date, Doc No, Bill Amount, Outstanding Amount, Posting Date, Amount Round-off

Small Shortage Amount Round-off Report

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."SHORTAGE_AMOUNT" ("NO", "COMPANY_CODE", "CLINIC", "CUSTOMER_CODE",
"CUSTOMER_NAME", "VISIT_DATE", "DOC_DATE", "DOC_NO", "BILL_AMOUNT", "OUTSTANDING_AMOUNT", "POSTING_DATE","AMOUNT_ROUND_OFF") AS
SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'08' as CLINIC,
'01' as CUSTOMER_CODE,
'TAISENG' as CUSTOMER_NAME,
To_date('2010-8-8', 'yyyy-mm-dd') as VISIT_DATE,
To_date('2010-8-9', 'yyyy-mm-dd') as DOC_DATE,
'DOC001' as DOC_NO,
'DOC01' as BILL_AMOUNT,
'1234' as OUTSTANDING_AMOUNT,
To_date('2010-8-10', 'yyyy-mm-dd') as POSTING_DATE,
'OFF' as AMOUNT_ROUND_OFF
FROM
DOMAIN_MS dm;T


--------------------------------------------------------------------------------------------------------

Company Code, Bank Code, Bank Account No, Clinic,  Payment mode, Transaction Date, Transaction Amount,

Un-reconciled amount,  Reconciled  Amount, Reconciled Date,   Document No, Assignment No, status

– Bank Reconciliation


CREATE OR REPLACE FORCE VIEW "EPOSDBA"."BANK_RECONCILIATION" ("COMPANY_CODE", "BANK_CODE", "BANK_ACCOUNT_NO", "CLINIC",
"PAYMENT_DATE", "TRANSACTION_AMOUNT", "UN_RECONCILED_AMOUNT", "RECONCILED_AMOUNT", "RECONCILED_DATE", "DOCUMENT_NO",
"ASSIGNMENT_NO","STATUS") AS
SELECT
'C0091' as COMPANY_CODE,
'B0091' as BANK_CODE,
'BA0091' as BANK_ACCOUNT_NO,
'08' as CLINIC,
To_date('2010-8-8', 'yyyy-mm-dd') as PAYMENT_DATE,
'100' as TRANSACTION_AMOUNT,
'21' as UN_RECONCILED_AMOUNT,
'12' as RECONCILED_AMOUNT,
To_date('2010-8-8', 'yyyy-mm-dd') as RECONCILED_DATE,
'DOC001' as DOCUMENT_NO,
'ASSOC001' as ASSIGNMENT_NO,
'0' as STATUS
FROM
DOMAIN_MS dm;

16 楼 rogerhunt 2010-08-07  

-------------4.21. RPT-AR-026 – Collection Performance Report


  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."COLLECTION_PERFORMANCE" ("NO", "COMPANY_CODE", "AGENCY_CODE", "AGENCY_NAME", "BILL_NO", "AMOUNT_ASSIGNED", "ASSIGNMENT_DATE", "AMOUNT_COLLECTED", "COLLECTION_DATE", "COLLECT_DAYS_NO", "COLLECTION") AS
  SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'08' as AGENCY_CODE,
'TAISENG' as AGENCY_NAME,
'01' as BILL_NO,
'1234' as AMOUNT_ASSIGNED,
To_date('2010-8-8', 'yyyy-mm-dd') as ASSIGNMENT_DATE,
'ADU' as AMOUNT_COLLECTED,
To_date('2010-8-9', 'yyyy-mm-dd') as COLLECTION_DATE,
'89' as COLLECT_DAYS_NO,
'PR' as COLLECTION
FROM
DOMAIN_MS dm;

15 楼 rogerhunt 2010-08-07  

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@localhost:1521:xe

eposdba

password

14 楼 rogerhunt 2010-08-07  

-----------RPT-AR-019 – Outstanding Balance Exceeding Credit Limit Listing


CREATE OR REPLACE FORCE VIEW "EPOSDBA"."EXCEEDING_CREDIT_LIMIT_LISTING" ("NO", "COMPANY_CODE", "CUSTOMER_CODE", "CUSTOMER_NAME", "INVOICE_DATE", "INVOICE_AMOUNT", "CREDIT_LIMIT", "CURRENT_BALANCE", "AMOUNT_EXCEEDED", "REMARKS") AS
SELECT
dm.id as NO,
'C0091' as COMPANY_CODE,
'CUS0091' as CUSTOMER_CODE,
'ROGER.TONG' as CUSTOMER_NAME,
To_date('2010-8-8', 'yyyy-mm-dd') as INVOICE_DATE,
'300' as INVOICE_AMOUNT,
'100' as CREDIT_LIMIT,
'BALANCE' as CURRENT_BALANCE,
'20' as AMOUNT_EXCEEDED,
'NOTHING' as REMARKS
FROM
DOMAIN_MS dm;

13 楼 rogerhunt 2010-08-07  

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."SERVICE_CONSUMPTION" ("NO", "POLYCLINIC", "DEPT", "SERVICE_CAT","SERVICE_SUB_CAT", "SERVICE_CODE", "SERVICE_DESCRIPTION", "ADU", "CHI", "ELD", "PR", "NR", "PTE", "CAP_APPLICABLE", "OPENCODE", "SUBVENTION_GROUP_ID", "DIAGNOSIS_TYPE", "DRUG_CLASS") AS
SELECT
dm.id as NO,
'S0091' as POLYCLINIC,
'DEPT' as DEPT,
'SERVICECAT' as SERVICE_CAT,
'SERVICESUBCAT' as SERVICE_SUB_CAT,
'SERVICE123' as SERVICE_CODE,
'NORMAL' as SERVICE_DESCRIPTION,
'ADU' as ADU,
'>80' as CHI,
'89' as ELD,
'PR' as PR,
'NR001' as NR,
'333' as PTE,
'APPLICABLE' as CAP_APPLICABLE,
'Y' as OPENCODE,
'SG112' as SUBVENTION_GROUP_ID,
'NORMAL' as DIAGNOSIS_TYPE,
'DRUG_CLASS002' as DRUG_CLASS
FROM
DOMAIN_MS dm;

12 楼 rogerhunt 2010-08-07  

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."SERVICE_CONSUMPTION" ("NO", "POLYCLINIC", "DEPT", "SERVICE_CAT","SERVICE_SUB_CAT", "SERVICE_CODE", "SERVICE_DESCRIPTION", "ADU", "CHI", "ELD", "PR", "NR", "PTE", "CAP_APPLICABLE","OPEN_CODE", "SUBVENTION_GROUP_ID", "DIAGNOSIS_TYPE", "DRUG_CLASS") AS
SELECT
dm.id as NO,
'S0091' as POLYCLINIC,
'DEPT' as DEPT,
'SERVICECAT' as SERVICE_CAT,
'SERVICESUBCAT' as SERVICE_SUB_CAT,
'SERVICE123' as SERVICE_CODE,
'NORMAL' as SERVICE_DESCRIPTION,
'ADU' as ADU,
'>80' as CHI,
'89' as ELD,
'PR' as PR,
'NR001' as NR,
'333' as PTE,
'APPLICABLE' as CAP_APPLICABLE,
'Y' as OPEN_CODE,
'SG112' as SUBVENTION_GROUP_ID,
'NORMAL' as DIAGNOSIS_TYPE,
'DRUG_CLASS002' as DRUG_CLASS
FROM
DOMAIN_MS dm;

11 楼 rogerhunt 2010-08-07  

-------------4.9.4.11. RPT-AR-014 – Service Consumption Report

CREATE OR REPLACE FORCE VIEW "EPOSDBA"."SERVICE_CONSUMPTION" ("NO", "POLYCLINIC", "DEPT", "SERVICE_CAT",
"SERVICE_SUB_CAT", "SERVICE_CODE", "SERVICE_DESCRIPTION", "ADU", "CHI", "ELD", "PR", "NR", "PTE", "CAP_APPLICABLE",
"OPEN_CODE", "SUBVENTION_GROUP_ID", "DIAGNOSIS_TYPE", "DRUG_CLASS") AS
SELECT
dm.id as NO,
'S0091' as POLYCLINIC,
'DEPT' as DEPT,
'SERVICECAT' as SERVICE_CAT,
'SERVICESUBCAT' as SERVICE_SUB_CAT,
'SERVICE123' as SERVICE_CODE,
'NORMAL' as SERVICE_DESCRIPTION,
'ADU' as ADU,
'>80' as CHI,
'89' as ELD,
'PR' as PR,
'NR001' as NR,
'333' as PTE,
'APPLICABLE' as CAP_APPLICABLE,
'Y' as OPEN_CODE,
'SG112' as SUBVENTION_GROUP_ID,
'NORMAL' as DIAGNOSIS_TYPE,
'DRUG_CLASS002' as DRUG_CLASS
FROM
DOMAIN_MS dm;

10 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as CLINIC_CODE,
'SHORT' as MEDISAVE_TYPE,
'EMPLOYER' as MEDISAVE_GROUP,
'13' as PATIENT_HRN_NO,
'MRSS1' as PATIENT_MRN,
'AAS1' as PATIENT_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as VISIT_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as BILL_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'AMT333' as MEDISAVE_CLAIM_AMT,
'$5000GD' as MEDISAVE_PAID,
TO_DATE('2010-05-13','YYYY-MM-DD') as DATE_OF_PAYMENT,
'MD112' as MEDICARE,
'NOT MEDICARE' as AMOUNT_RECLASS_MEDICARE,
'RECLASS1' as AMOUNT_RECLASS_PATIENT
FROM
DOMAIN_MS dm;

select * from Waiver_Patients_View

9 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as CLINIC_CODE,
'SHORT' as MEDISAVE_TYPE,
'EMPLOYER' as MEDISAVE_GROUP,
'13' as PATIENT_HRN_NO,
'MRSS1' as PATIENT_MRN,
'AAS1' as PATIENT_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'AMT333' as MEDISAVE_CLAIM_AMT,
'$5000GD' as MEDISAVE_PAID,
TO_DATE('2010-05-13','YYYY-MM-DD') as DATE_OF_PAYMENT,
'MD112' as MEDICARE,
'NOT MEDICARE' as AMOUNT_RECLASS_MEDICARE,
'RECLASS1' as AMOUNT_RECLASS_PATIENT
FROM
DOMAIN_MS dm;

8 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as CLINIC_CODE,
'SHORT' as MEDISAVE_TYPE,
'EMPLOYER' as MEDISAVE_GROUP,
'13' as PATIENT_HRN_NO,
'MRSS1' as PATIENT_MRN,
'AAS1' as PATIENT_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'SG0098' as MEDISAVE_CLAIM_AMT,
'v0.9' as MEDISAVE_PAID,
TO_DATE('2010-05-13','YYYY-MM-DD') as DATE_OF_PAYMENT,
'11098.00' as MEDICARE,
'STHH Bu hh - 1009' as AMOUNT_RECLASS_MEDICARE,
'8878 9981' as AMOUNT_RECLASS_PATIENT

7 楼 rogerhunt 2010-07-31  

CREATE OR REPLACE FORCE VIEW  "CASH_COUNTER_SUMMARY_REPORT" ("P_DATE", "CASH", "NETS_OR_CDA", "CASHCARD", "CHEQUE", "CREDIT_CARD", "VOUCHER", "IB", "COMPANY_CODE", "CLINIC_CODE", "PAYMENT_MODE", "TOTAL") AS
  SELECT To_date('2010-8-8', 'yyyy-mm-dd') as p_Date,'CASH1' as CASH, 'NETS1' as NETS_or_CDA, 'CASHCARD1' as CASHCARD
, 'CHEQUE1' as CHEQUE
, 'CREDIT1' as CREDIT_CARD
, 'VOUCHER1' as VOUCHER
, 'IB1' as IB
, 'A' as company_code
, '5' as clinic_code
, 'CHECK' as payment_mode
,10 as Total
FROM eposdba.ADDRESS_MS

6 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as CLINIC_CODE,
'SHORT' as MEDISAVE_TYPE,
'EMPLOYER' as MEDISAVE_GROUP,
'13' as PATIENT_HRN_NO,
'MRSS1' as PATIENT_MRN,
'AAS1' as PATIENT_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'SG0098' as MEDISAVE_CLAIM_AMT,
'v0.9' as MEDISAVE_PAID,
TO_DATE('2010-05-13','YYYY-MM-DD') as DATE_OF_PAYMENT,
'11098.00' as MEDICARE,
'STHH Bu hh - 1009' as AMOUNT_RECLASS_MEDICARE,
'8878 9981' as AMOUNT_RECLASS_PATIENT
FROM
DOMAIN_MS dm;

5 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as CLINIC_CODE,
'SHORT' as MEDISAVE_TYPE,
'EMPLOYER' as MEDISAVE_GROUP,
'13' as PATIENT_HRN_NO,
'MRSS1' as PATIENT_MRN,
'AAS1' as PATIENT_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'SG0098' as MEDISAVE_CLAIM_AMT,
'v0.9' as MEDISAVE_PAID,
'10091.00' as DATE_OF_PAYMENT,
'11098.00' as MEDICARE,
'STHH Bu hh - 1009' as AMOUNT_RECLASS_MEDICARE,
'8878 9981' as AMOUNT_RECLASS_PATIENT
FROM
DOMAIN_MS dm;

4 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as COMPANT_CODE,
'GS1010' as AGENCY_CODE,
'FAMK' as AGENCY_NAME,
'13' as CUSTOMER_CODE,
'AAS1' as CUSTOMER_NAME,
'AAS1' as CUSTOMER_NAME,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'11098.00' as OUTSTANDING_AMOUNT,
'STHH Bu hh - 1009' as ADDRESS,
'8878 9981' as CONTACT_NO_HOME
FROM
DOMAIN_MS dm;

3 楼 rogerhunt 2010-07-31  

CREATE OR REPLACE FORCE VIEW  "CASH_COUNTER_SUMMARY_REPORT" ("P_DATE", "CASH", "NETS_OR_CDA", "CASHCARD", "CHEQUE", "CREDIT_CARD", "VOUCHER", "IB", "COMPANY_CODE", "CLINIC_CODE", "PAYMENT_MODE", "TOTAL") AS
  SELECT To_date('2010-8-8', 'yyyy-mm-dd') as p_Date,'CASH1' as CASH, 'NETS1' as NETS_or_CDA, 'CASHCARD1' as CASHCARD
, 'CHEQUE1' as CHEQUE
, 'CREDIT1' as CREDIT_CARD
, 'VOUCHER1' as VOUCHER
, 'IB1' as IB
, 'A' as company_code
, '5' as clinic_code
, 'CHECK' as payment_mode
,10 as Total
FROM eposdba.ADDRESS_MS

2 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."COLLECTING_AGENCY_LISTING" ("NO", "COMPANT_CODE", "AGENCY_CODE", "AGENCY_NAME", "CUSTOMER_CODE", "CUSTOMER_NAME", "ASSIGNMENT_START_DATE", "ASSIGNMENT_END_DATE", "VISIT_DATE", "BILL_DATE", "ADJUSTMENT_DATE", "TYPE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "OUTSTANDING_AMOUNT", "ADDRESS", " CONTACT_NO_HOME", "CONTACT_NO_OFFICE", "CONTACT_NO_HANDPHONE", "EMAIL_ADDRESS", "REMARKS") AS
  SELECT
dm.id as NO,
'S0091' as COMPANT_CODE,
'GS1010' as AGENCY_CODE,
'FAMK' as AGENCY_NAME,
'13' as CUSTOMER_CODE,
'AAS1' as CUSTOMER_NAME,
TO_DATE('2010-02-13','YYYY-MM-DD') as ASSIGNMENT_START_DATE,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
TO_DATE('2010-05-13','YYYY-MM-DD') as BILL_DATE,
TO_DATE('2010-06-13','YYYY-MM-DD') as ADJUSTMENT_DATE,
'SG' as TYPE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'11098.00' as OUTSTANDING_AMOUNT,
'STHH Bu hh - 1009' as ADDRESS,
'8878 9981' as CONTACT_NO_HOME,
'8871 9911' as CONTACT_NO_OFFICE,
'3878 9181' as CONTACT_NO_HANDPHONE,
'[e-mail]' as EMAIL_ADDRESS,
'THA007' as REMARKS
FROM
DOMAIN_MS dm;

1 楼 rogerhunt 2010-07-31  

  CREATE OR REPLACE FORCE VIEW "EPOSDBA"."Transfer_Medisave_Claims_Patient_Listing" ("NO", "CLINIC", "MEDISAVE_TYPE", "MEDISAVE_GROUP", "PATIENT_HRN_NO", "PATIENT_MRN", "PATIENT_NAME", "VIST_DATE", "BILL_DATE", "BILL_NO", "VERSION_NO", "BILL_AMOUNT", "MEDISAVE_CLAIM_AMT", "MEDISAVE_PAID", "DATE_OF_PAYMENT", "MEDICARE", "AMOUNT_RECLASS_MEDICARE", "AMOUNT_RECLASS_PATIENT") AS
  SELECT
dm.id as NO,
'S0091' as COMPANT_CODE,
'GS1010' as AGENCY_CODE,
'FAMK' as AGENCY_NAME,
'13' as CUSTOMER_CODE,
'AAS1' as CUSTOMER_NAME,
TO_DATE('2010-02-13','YYYY-MM-DD') as ASSIGNMENT_START_DATE,
TO_DATE('2010-03-13','YYYY-MM-DD') as ASSIGNMENT_END_DATE,
TO_DATE('2010-04-13','YYYY-MM-DD') as VISIT_DATE,
TO_DATE('2010-05-13','YYYY-MM-DD') as BILL_DATE,
TO_DATE('2010-06-13','YYYY-MM-DD') as ADJUSTMENT_DATE,
'SG' as TYPE,
'SG0098' as BILL_NO,
'v0.9' as VERSION_NO,
'10091.00' as BILL_AMOUNT,
'11098.00' as OUTSTANDING_AMOUNT,
'STHH Bu hh - 1009' as ADDRESS,
'8878 9981' as CONTACT_NO_HOME
FROM
DOMAIN_MS dm;