GO DROP VIEW [VOUCHER_VIEW] GO CREATE VIEW [dbo].[VOUCHER_VIEW] AS SELECT VOUCHER.PR_KEY AS PR_KEY_CTU, VOUCHER.ORGANIZATION_ID,VOUCHER.TRAN_ID, VOUCHER.TRAN_NO, VOUCHER.TRAN_DATE, VOUCHER.REFERENCE_NO as REFERENCE_NO_CTU, VOUCHER.CONTRACT_NO, VOUCHER.CONTRACT_DATE, VOUCHER.CONTACT_PERSON, VOUCHER.ADDRESS, VOUCHER.PAYMENT_METHOD_ID, VOUCHER.PAYMENT_TERM_ID, VOUCHER.PAYMENT_DATE, VOUCHER.ATTACHED_DOCUMENTS, VOUCHER.COMMENTS, VOUCHER.STATUS, VOUCHER_DETAIL.* FROM VOUCHER INNER JOIN VOUCHER_DETAIL ON VOUCHER.PR_KEY = VOUCHER_DETAIL.FR_KEY GO DROP VIEW SALE_TRAN_VIEW GO CREATE VIEW [dbo].[SALE_TRAN_VIEW] AS SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.ACCOUNT_ID_PR, dbo.SALE.ACCOUNT_ID_VAT, dbo.SALE.ACCOUNT_ID_EXPORT_TAX, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.EXCHANGE_RATE_EXTRA, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.STATUS, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.SALE.IMPLEMENTED_EMPLOYEE_ID, dbo.SALE.SALE_DATE, dbo.SALE.IMPLEMENTED_DATE, dbo.DM_ITEM.ORIGIN, HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE.ITEM_SOURCE_ID, SHIPPING_METHOD_ID, VEHICLE_ID, TAX_OFFICE_ID, dbo.SALE_DETAIL.*,DM_PR_DETAIL.PROVINCE_ID,SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,SALE.ORIG_TRAN_NO FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SALE_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN HR_EMPLOYEE_INFO ON dbo.SALE_DETAIL.EMPLOYEE_ID = HR_EMPLOYEE_INFO.EMPLOYEE_ID GO GO DROP VIEW PURCHASE_TRAN_VIEW GO CREATE VIEW [dbo].[PURCHASE_TRAN_VIEW] AS SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU, dbo.PURCHASE.TRAN_ID, dbo.PURCHASE.TRAN_NO, dbo.PURCHASE.TRAN_DATE, dbo.PURCHASE.REFERENCE_NO, dbo.PURCHASE.PO_TRAN_NO, dbo.PURCHASE.PO_TRAN_ID, dbo.PURCHASE.VAT_TRAN_NO, dbo.PURCHASE.VAT_TRAN_DATE, dbo.PURCHASE.VAT_TRAN_SERIE, dbo.PURCHASE.VAT_PURCHASE_ID, dbo.PURCHASE.WAREHOUSE_ID_ISSUE, dbo.PURCHASE.ACCOUNT_ID_PR, dbo.PURCHASE.ACCOUNT_ID_VAT, dbo.PURCHASE.ACCOUNT_ID_IMPORT_TAX, dbo.PURCHASE.MARKET_ID, dbo.PURCHASE.EXCHANGE_RATE, dbo.PURCHASE.EXCHANGE_RATE_EXTRA, dbo.PURCHASE.CURRENCY_ID, dbo.PURCHASE.PR_DETAIL_ID, dbo.PURCHASE.PR_DETAIL_NAME, dbo.PURCHASE.CONTACT_PERSON, dbo.PURCHASE.ADDRESS, dbo.PURCHASE.TAX_FILE_NUMBER, dbo.PURCHASE.PAYMENT_METHOD_ID, dbo.PURCHASE.COMMENTS, dbo.PURCHASE.PAYMENT_TERM_ID, dbo.PURCHASE.STATUS, dbo.PURCHASE.PAYMENT_DATE, dbo.PURCHASE.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.PURCHASE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.PURCHASE_DETAIL.PR_KEY, dbo.PURCHASE_DETAIL.FR_KEY, dbo.PURCHASE_DETAIL.LIST_ORDER, dbo.PURCHASE_DETAIL.LOT_NO, dbo.PURCHASE_DETAIL.MANU_DATE, dbo.PURCHASE_DETAIL.EXPIRED_TERM, dbo.PURCHASE_DETAIL.EXPIRED_DATE, dbo.PURCHASE_DETAIL.ITEM_ID, dbo.PURCHASE_DETAIL.DESCRIPTION, dbo.PURCHASE_DETAIL.DESCRIPTION_ULS, dbo.PURCHASE_DETAIL.UNIT_ID, dbo.PURCHASE_DETAIL.QUANTITY, dbo.PURCHASE_DETAIL.QUANTITY_EXTRA, dbo.PURCHASE_DETAIL.QUANTITY_WH, dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG, dbo.PURCHASE_DETAIL.UNIT_PRICE, dbo.PURCHASE_DETAIL.UNIT_PRICE_WH, dbo.PURCHASE_DETAIL.AMOUNT_ORIG, dbo.PURCHASE_DETAIL.AMOUNT, dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT, dbo.PURCHASE_DETAIL.VAT_TAX_ID, dbo.PURCHASE_DETAIL.VAT_TAX_RATE, dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT, dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE, dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT, dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.PURCHASE_COST, dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG, dbo.PURCHASE_DETAIL.COG_UNIT_PRICE, dbo.PURCHASE_DETAIL.COG_AMOUNT, dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.DISCOUNT_RATE, dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT, dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.TOTAL_AMOUNT, dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.ACCOUNT_ID, dbo.PURCHASE_DETAIL.ACCOUNT_ID_COST, dbo.PURCHASE_DETAIL.ACCOUNT_ID_RETURN, dbo.PURCHASE_DETAIL.ACCOUNT_ID_COGS, dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM, dbo.PURCHASE_DETAIL.EXPENSE_ID, dbo.PURCHASE_DETAIL.JOB_ID, dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.PURCHASE_DETAIL.AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.COG_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.PURCHASE_COST_EXTRA, dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.LUX_TAX_RATE, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.PURCHASE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.PURCHASE_DETAIL.EMPLOYEE_COMMISSION, dbo.PURCHASE_DETAIL.EMPLOYEE_COMMISSION_EXTRA, dbo.PURCHASE_DETAIL.EMPLOYEE_ID, dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED, dbo.PURCHASE_DETAIL.ITEM_OP_ID, dbo.PURCHASE_DETAIL.WAREHOUSE_ID, dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ORIGIN, HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA,TAX_OFFICE_ID,PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,PURCHASE_DETAIL.FIXED_UNIT_PRICE,PURCHASE_DETAIL.FIXED_AMOUNT,PURCHASE.ORIG_TRAN_NO,PURCHASE.VEHICLE_ID FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.PURCHASE_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN HR_EMPLOYEE_INFO ON dbo.PURCHASE_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID GO DROP VIEW SALE_VIEW GO CREATE VIEW [dbo].[SALE_VIEW] AS (SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE.ITEM_SOURCE_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.SALE.DRIVER_NAME, dbo.SALE.ORIG_TRAN_NO, dbo.SALE.ORIG_TRAN_ID, dbo.SALE.EMPLOYEE_ID,SALE.EXCHANGE_RATE_EXTRA,SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, SALE_DETAIL.ROOM_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID WHERE (SALE_DETAIL.ITEM_OP_ID = '' OR DM_ITEM_OP.OP_TYPE='X') AND SYS_TRAN.IS_SALE=1 ) UNION ALL (SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, '' AS REFERENCE_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, dbo.POS_SALE.MARKET_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.PR_DETAIL_NAME, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.COMMENTS, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.POS_SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, '' AS SBO_TRAN_ID, '' AS SBO_TRAN_NO, dbo.POS_SALE.SO_TRAN_ID, dbo.POS_SALE.SO_TRAN_NO, '' AS SII_TRAN_ID, '' AS SII_TRAN_NO, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_PURCHASE_ID, dbo.POS_SALE.REC_PR_DETAIL_ID, dbo.POS_SALE.ITEM_SOURCE_ID, '' AS SHIPPING_METHOD_ID, '' AS VEHICLE_ID, dbo.POS_SALE.TAX_OFFICE_ID, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.RECEIVE_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EXPORT_TAX_RATE, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.SALE_COST, dbo.POS_SALE_DETAIL.SALE_COST_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_UNIT_PRICE, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_AMOUNT, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, '' AS DRIVER_NAME, '' AS ORIG_TRAN_NO, '' AS ORIG_TRAN_ID, dbo.POS_SALE.EMPLOYEE_ID,1 AS EXCHANGE_RATE_EXTRA,POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, '' AS ROOM_ID FROM dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON POS_SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON POS_SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID WHERE (POS_SALE_DETAIL.ITEM_OP_ID = '' OR DM_ITEM_OP.OP_TYPE='X') AND SYS_TRAN.IS_SALE=1 ) GO GO DROP VIEW PURCHASE_VIEW GO CREATE VIEW [dbo].[PURCHASE_VIEW] AS (SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU, dbo.PURCHASE.TRAN_ID, dbo.PURCHASE.TRAN_NO, dbo.PURCHASE.TRAN_DATE, dbo.PURCHASE.REFERENCE_NO, dbo.PURCHASE.PO_TRAN_NO, dbo.PURCHASE.PO_TRAN_ID, dbo.PURCHASE.VAT_TRAN_NO, dbo.PURCHASE.VAT_TRAN_DATE, dbo.PURCHASE.VAT_TRAN_SERIE, dbo.PURCHASE.VAT_PURCHASE_ID, dbo.PURCHASE.WAREHOUSE_ID_ISSUE, dbo.PURCHASE.MARKET_ID, dbo.PURCHASE.EXCHANGE_RATE, dbo.PURCHASE.CURRENCY_ID, dbo.PURCHASE.PR_DETAIL_ID, dbo.PURCHASE.PR_DETAIL_NAME, dbo.PURCHASE.CONTACT_PERSON, dbo.PURCHASE.ADDRESS, dbo.PURCHASE.TAX_FILE_NUMBER, dbo.PURCHASE.PAYMENT_METHOD_ID, dbo.PURCHASE.COMMENTS, dbo.PURCHASE.PAYMENT_TERM_ID, dbo.PURCHASE.PAYMENT_DATE, dbo.PURCHASE.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.PURCHASE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.PURCHASE_DETAIL.PR_KEY, dbo.PURCHASE_DETAIL.FR_KEY, dbo.PURCHASE_DETAIL.LIST_ORDER, dbo.PURCHASE_DETAIL.LOT_NO, dbo.PURCHASE_DETAIL.MANU_DATE, dbo.PURCHASE_DETAIL.EXPIRED_TERM, dbo.PURCHASE_DETAIL.EXPIRED_DATE, dbo.PURCHASE_DETAIL.ITEM_ID, dbo.PURCHASE_DETAIL.DESCRIPTION, dbo.PURCHASE_DETAIL.DESCRIPTION_ULS, dbo.PURCHASE_DETAIL.UNIT_ID, dbo.PURCHASE_DETAIL.QUANTITY, dbo.PURCHASE_DETAIL.QUANTITY_EXTRA, dbo.PURCHASE_DETAIL.QUANTITY_WH, dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG, dbo.PURCHASE_DETAIL.UNIT_PRICE, dbo.PURCHASE_DETAIL.UNIT_PRICE_WH, dbo.PURCHASE_DETAIL.AMOUNT_ORIG, dbo.PURCHASE_DETAIL.AMOUNT, dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT, dbo.PURCHASE_DETAIL.VAT_TAX_ID, dbo.PURCHASE_DETAIL.VAT_TAX_RATE, dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT, dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE, dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT, dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.PURCHASE_COST, dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG, dbo.PURCHASE_DETAIL.COG_UNIT_PRICE, dbo.PURCHASE_DETAIL.COG_AMOUNT, dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.DISCOUNT_RATE, dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT, dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.TOTAL_AMOUNT, dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.PURCHASE_DETAIL.LUX_TAX_RATE, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.ITEM_OP_ID, dbo.PURCHASE_DETAIL.WAREHOUSE_ID, dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.PURCHASE.TAX_OFFICE_ID, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED, dbo.PURCHASE_DETAIL.FIXED_UNIT_PRICE, dbo.PURCHASE_DETAIL.FIXED_AMOUNT, dbo.PURCHASE_DETAIL.QUANTITY_BILL, dbo.PURCHASE_DETAIL.QUANTITY_MEASURE,PURCHASE.EXCHANGE_RATE_EXTRA,PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,PURCHASE.VEHICLE_ID FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON PURCHASE.TRAN_ID=SYS_TRAN.TRAN_ID WHERE SYS_TRAN.IS_SALE=1 ) UNION ALL ( SELECT dbo.POS_PURCHASE.PR_KEY AS PR_KEY_CTU, dbo.POS_PURCHASE.TRAN_ID, dbo.POS_PURCHASE.TRAN_NO, dbo.POS_PURCHASE.TRAN_DATE, '' AS REFERENCE_NO, '' AS PO_TRAN_NO, '' AS PO_TRAN_ID, dbo.POS_PURCHASE.VAT_TRAN_NO, dbo.POS_PURCHASE.VAT_TRAN_DATE, dbo.POS_PURCHASE.VAT_TRAN_SERIE, '' AS VAT_POS_PURCHASE_ID, dbo.POS_PURCHASE.WAREHOUSE_ID_ISSUE, '' AS MARKET_ID, dbo.POS_PURCHASE.EXCHANGE_RATE, dbo.POS_PURCHASE.CURRENCY_ID, dbo.POS_PURCHASE.PR_DETAIL_ID, dbo.POS_PURCHASE.PR_DETAIL_NAME, dbo.POS_PURCHASE.CONTACT_PERSON, dbo.POS_PURCHASE.ADDRESS, dbo.POS_PURCHASE.TAX_FILE_NUMBER, dbo.POS_PURCHASE.PAYMENT_METHOD_ID, dbo.POS_PURCHASE.COMMENTS, dbo.POS_PURCHASE.PAYMENT_TERM_ID, dbo.POS_PURCHASE.PAYMENT_DATE, dbo.POS_PURCHASE.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.POS_PURCHASE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.POS_PURCHASE_DETAIL.PR_KEY, dbo.POS_PURCHASE_DETAIL.FR_KEY, dbo.POS_PURCHASE_DETAIL.LIST_ORDER, dbo.POS_PURCHASE_DETAIL.LOT_NO, dbo.POS_PURCHASE_DETAIL.MANU_DATE, dbo.POS_PURCHASE_DETAIL.EXPIRED_TERM, dbo.POS_PURCHASE_DETAIL.EXPIRED_DATE, dbo.POS_PURCHASE_DETAIL.ITEM_ID, dbo.POS_PURCHASE_DETAIL.DESCRIPTION, '' AS DESCRIPTION_ULS, dbo.POS_PURCHASE_DETAIL.UNIT_ID, dbo.POS_PURCHASE_DETAIL.QUANTITY, dbo.POS_PURCHASE_DETAIL.QUANTITY_EXTRA, dbo.POS_PURCHASE_DETAIL.QUANTITY_WH, dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_PURCHASE_DETAIL.UNIT_PRICE, dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_WH, dbo.POS_PURCHASE_DETAIL.AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.AMOUNT, dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT, dbo.POS_PURCHASE_DETAIL.VAT_TAX_ID, dbo.POS_PURCHASE_DETAIL.VAT_TAX_RATE, dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST_ORIG, dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE, dbo.POS_PURCHASE_DETAIL.COG_AMOUNT, dbo.POS_PURCHASE_DETAIL.COG_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.DISCOUNT_RATE, dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT, dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.POS_PURCHASE_DETAIL.LUX_TAX_RATE, dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA, dbo.POS_PURCHASE_DETAIL.ITEM_OP_ID, dbo.POS_PURCHASE_DETAIL.WAREHOUSE_ID, dbo.POS_PURCHASE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.POS_PURCHASE.TAX_OFFICE_ID, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED, dbo.POS_PURCHASE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_PURCHASE_DETAIL.FIXED_AMOUNT, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL, dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE,1 AS EXCHANGE_RATE_EXTRA,POS_PURCHASE_DETAIL.ITEM_ID + POS_PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,'' AS VEHICLE_ID FROM dbo.POS_PURCHASE INNER JOIN dbo.POS_PURCHASE_DETAIL ON dbo.POS_PURCHASE.PR_KEY = dbo.POS_PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON POS_PURCHASE.TRAN_ID=SYS_TRAN.TRAN_ID WHERE SYS_TRAN.IS_SALE=1 ) UNION ALL (SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, '' AS PO_TRAN_NO, '' AS PO_TRAN_ID, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.VAT_PURCHASE_ID, '' AS WAREHOUSE_ID_ISSUE, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.PAYMENT_DATE, dbo.SALE.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.DESCRIPTION_ULS, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, 0 AS IMPORT_TAX_RATE, 0 AS IMPORT_TAX_AMOUNT, 0 AS IMPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.SALE.TAX_OFFICE_ID, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, 0 AS QUANTITY_BILL, 0 AS QUANTITY_MEASURE,SALE.EXCHANGE_RATE_EXTRA,SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,SALE.VEHICLE_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID WHERE (SALE_DETAIL.ITEM_OP_ID = '' OR DM_ITEM_OP.OP_TYPE='N') AND SYS_TRAN.IS_SALE=1 ) UNION ALL ( SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, '' AS REFERENCE_NO, '' AS PO_TRAN_NO, '' AS PO_TRAN_ID, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, '' AS VAT_POS_SALE_ID, '' AS WAREHOUSE_ID_ISSUE, '' AS MARKET_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.PR_DETAIL_NAME, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.COMMENTS, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.POS_SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, '' AS DESCRIPTION_ULS, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST_ORIG, 0 AS COG_UNIT_PRICE, 0 AS COG_AMOUNT, 0 AS COG_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, 0 AS COG_UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.POS_SALE.TAX_OFFICE_ID, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE,1 AS EXCHANGE_RATE_EXTRA,POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID,'' AS VEHICLE_ID FROM dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON POS_SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON POS_SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID WHERE (POS_SALE_DETAIL.ITEM_OP_ID = '' OR DM_ITEM_OP.OP_TYPE='X') AND SYS_TRAN.IS_SALE=1 ) GO DROP VIEW DM_ACCOUNT_CONTRA GO CREATE VIEW [dbo].[DM_ACCOUNT_CONTRA] AS SELECT ACCOUNT_ID AS ACCOUNT_ID_CONTRA,ACCOUNT_NAME,ACCOUNT_NAME_ULS,ACCOUNT_NAME_JP,ACCOUNT_NAME_KR FROM DM_ACCOUNT GO DROP VIEW LEDGER_VIEW GO CREATE VIEW [dbo].[LEDGER_VIEW] AS SELECT LEDGER.PR_KEY_LEDGER,LEDGER.PR_KEY, LEDGER.PR_KEY_DETAIL, LEDGER.DEBIT_CREDIT, LEDGER.TRAN_ID, LEDGER.TRAN_DATE, LEDGER.TRAN_NO, LEDGER.CONTRACT_NO, LEDGER.CONTRACT_DATE, LEDGER.VAT_TRAN_NO, LEDGER.VAT_TRAN_SERIE, LEDGER.VAT_TRAN_DATE, LEDGER.PAYMENT_METHOD_ID, LEDGER.PAYMENT_TERM_ID, LEDGER.PAYMENT_DATE, LEDGER.CONTACT_PERSON, LEDGER.ADDRESS, LEDGER.COMMENTS, LEDGER.DESCRIPTION, LEDGER.DESCRIPTION_ULS, LEDGER.ACCOUNT_ID, DM_ACCOUNT.ACCOUNT_NAME,DM_ACCOUNT.ACCOUNT_NAME_ULS,DM_ACCOUNT_CONTRA.ACCOUNT_NAME AS ACCOUNT_NAME_CONTRA,DM_ACCOUNT_CONTRA.ACCOUNT_NAME_ULS AS ACCOUNT_NAME_CONTRA_ULS, LEDGER.ACCOUNT_ID_CONTRA, LEDGER.CURRENCY_ID, LEDGER.EXCHANGE_RATE, LEDGER.EXCHANGE_RATE_EXTRA, LEDGER.QUANTITY, LEDGER.UNIT_PRICE, LEDGER.UNIT_PRICE_ORIG, LEDGER.AMOUNT, LEDGER.AMOUNT_ORIG, LEDGER.AMOUNT_EXTRA, LEDGER.PR_DETAIL_ID, LEDGER.EXPENSE_ID, LEDGER.JOB_ID, LEDGER.ITEM_ID, LEDGER.IS_BOOKED, LEDGER.IS_PRODUCT_COST, LEDGER.ORGANIZATION_ID, ISNULL(DM_EXPENSE.EXPENSE_CLASS_ID, N' ') AS EXPENSE_CLASS_ID, ISNULL(DM_JOB.JOB_CLASS_ID, N' ') AS JOB_CLASS_ID, ISNULL(DM_PR_DETAIL.PR_DETAIL_CLASS_ID, N' ') AS PR_DETAIL_CLASS_ID, LEDGER.REFERENCE_NO, ISNULL(DM_PR_DETAIL.PR_DETAIL_NAME, N' ') AS PR_DETAIL_NAME, LEDGER.REFERENCE_AMOUNT, ISNULL(DM_JOB.JOB_NAME, N' ') AS JOB_NAME, ISNULL(DM_EXPENSE.EXPENSE_NAME, N' ') AS EXPENSE_NAME,LEDGER.EXCHANGE_RATE_COST, LEDGER.AMOUNT_COST,LEDGER.REGISTER_NO,LEDGER.REGISTER_DATE,DM_JOB.JOB_VALUE, LEDGER.BANK_ID FROM LEDGER LEFT OUTER JOIN DM_EXPENSE ON LEDGER.EXPENSE_ID = DM_EXPENSE.EXPENSE_ID LEFT OUTER JOIN DM_JOB ON LEDGER.JOB_ID = DM_JOB.JOB_ID LEFT OUTER JOIN DM_PR_DETAIL ON DM_PR_DETAIL.PR_DETAIL_ID = LEDGER.PR_DETAIL_ID LEFT OUTER JOIN DM_ACCOUNT ON LEDGER.ACCOUNT_ID=DM_ACCOUNT.ACCOUNT_ID LEFT OUTER JOIN DM_ACCOUNT_CONTRA ON LEDGER.ACCOUNT_ID_CONTRA=DM_ACCOUNT_CONTRA.ACCOUNT_ID_CONTRA GO DROP VIEW WAREHOUSE_VIEW GO CREATE VIEW [dbo].[WAREHOUSE_VIEW] AS SELECT dbo.WAREHOUSE.PR_KEY, dbo.WAREHOUSE.PR_KEY_DETAIL, dbo.WAREHOUSE.ISSUE_RECEIVE, dbo.WAREHOUSE.TRAN_ID, dbo.WAREHOUSE.TRAN_DATE, dbo.WAREHOUSE.TRAN_NO, dbo.WAREHOUSE.COMMENTS, dbo.WAREHOUSE.WAREHOUSE_ID, dbo.WAREHOUSE.ITEM_ID, dbo.WAREHOUSE.LOT_NO, dbo.WAREHOUSE.RECEIVE_DATE, dbo.WAREHOUSE.MANU_DATE, dbo.WAREHOUSE.EXPIRED_TERM, dbo.WAREHOUSE.EXPIRED_DATE, dbo.WAREHOUSE.DESCRIPTION, dbo.WAREHOUSE.DESCRIPTION_ULS, dbo.WAREHOUSE.QUANTITY, dbo.WAREHOUSE.QUANTITY_EXTRA, dbo.WAREHOUSE.UNIT_PRICE, dbo.WAREHOUSE.AMOUNT, dbo.WAREHOUSE.AMOUNT_EXTRA, dbo.WAREHOUSE.ACCOUNT_ID, dbo.WAREHOUSE.JOB_QTY, dbo.WAREHOUSE.ACCOUNT_ID_CONTRA, dbo.WAREHOUSE.EXPENSE_ID, dbo.WAREHOUSE.JOB_ID, ISNULL(dbo.DM_ITEM.ITEM_CLASS_ID, N' ') AS ITEM_CLASS_ID, ISNULL(dbo.DM_JOB.JOB_CLASS_ID, N' ') AS JOB_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.ITEM_NAME, dbo.WAREHOUSE.ORGANIZATION_ID, ISNULL(dbo.DM_JOB.JOB_NAME, N' ') AS JOB_NAME, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE.PR_DETAIL_ID, dbo.WAREHOUSE.ITEM_SOURCE_ID, dbo.WAREHOUSE.WAREHOUSE_ID_ISSUE, dbo.WAREHOUSE.ITEM_OP_ID,UNIT_ID_ACTUAL,WAREHOUSE.ITEM_ID + WAREHOUSE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID FROM dbo.WAREHOUSE LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE.JOB_ID = dbo.DM_JOB.JOB_ID GO DROP VIEW [WAREHOUSE_BALANCE_VIEW] GO CREATE VIEW [dbo].[WAREHOUSE_BALANCE_VIEW] AS SELECT dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.ITEM_CLASS_ID, 'DEB' AS DEBIT_CREDIT, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE_BALANCE.PR_KEY, dbo.WAREHOUSE_BALANCE.TRAN_DATE, dbo.WAREHOUSE_BALANCE.ORGANIZATION_ID, dbo.WAREHOUSE_BALANCE.WAREHOUSE_ID, dbo.WAREHOUSE_BALANCE.ITEM_ID, dbo.WAREHOUSE_BALANCE.QUANTITY, dbo.WAREHOUSE_BALANCE.QUANTITY_EXTRA, dbo.WAREHOUSE_BALANCE.UNIT_PRICE, dbo.WAREHOUSE_BALANCE.AMOUNT, dbo.WAREHOUSE_BALANCE.AMOUNT_EXTRA, dbo.WAREHOUSE_BALANCE.USER_ID, dbo.WAREHOUSE_BALANCE.LOT_NO, dbo.WAREHOUSE_BALANCE.RECEIVE_DATE, dbo.WAREHOUSE_BALANCE.MANU_DATE, dbo.WAREHOUSE_BALANCE.EXPIRE_DATE, dbo.WAREHOUSE_BALANCE.ACCOUNT_ID, dbo.WAREHOUSE_BALANCE.ITEM_SOURCE_ID, dbo.WAREHOUSE_BALANCE.JOB_ID, dbo.DM_JOB.JOB_CLASS_ID,UNIT_ID_ACTUAL,DM_ITEM.ITEM_NAME,WAREHOUSE_BALANCE.ITEM_ID + WAREHOUSE_BALANCE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID FROM dbo.WAREHOUSE_BALANCE LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE_BALANCE.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE_BALANCE.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO DROP VIEW CA_BEGINNING_AMOUNT_VIEW GO CREATE VIEW CA_BEGINNING_AMOUNT_VIEW AS SELECT '154' AS ACCOUNT_ID, CA_BEGINNING_AMOUNT.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_BEGINNING_AMOUNT LEFT JOIN DM_JOB ON CA_BEGINNING_AMOUNT.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW CA_EXPENSE_DECREASE_VIEW GO CREATE VIEW CA_EXPENSE_DECREASE_VIEW AS SELECT CA_EXPENSE_DECREASE.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_EXPENSE_DECREASE LEFT JOIN DM_JOB ON CA_EXPENSE_DECREASE.JOB_ID=DM_JOB.JOB_ID GO GO DROP VIEW [CA_BUDGET_VIEW] GO CREATE VIEW [dbo].[CA_BUDGET_VIEW] AS SELECT dbo.DM_JOB.JOB_CLASS_ID, dbo.CA_BUDGET.*, dbo.DM_EXPENSE.EXPENSE_CLASS_ID FROM dbo.CA_BUDGET LEFT OUTER JOIN dbo.DM_JOB ON dbo.CA_BUDGET.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_EXPENSE ON dbo.CA_BUDGET.EXPENSE_ID = dbo.DM_EXPENSE.EXPENSE_ID GO DROP VIEW CA_BEGINNING_QUANTITY_VIEW GO CREATE VIEW [dbo].[CA_BEGINNING_QUANTITY_VIEW] AS SELECT CA_BEGINNING_QUANTITY.*, isnull(DM_ITEM.ITEM_CLASS_ID,'') as ITEM_CLASS_ID, isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID, ' ' as EXPENSE_CLASS_ID FROM CA_BEGINNING_QUANTITY LEFT JOIN DM_ITEM ON CA_BEGINNING_QUANTITY.ITEM_ID = DM_ITEM.ITEM_ID LEFT JOIN DM_JOB ON CA_BEGINNING_QUANTITY.JOB_ID=DM_JOB.JOB_ID GO GO DROP VIEW [CA_EXPENSE_RESULT_VIEW] GO CREATE VIEW [dbo].[CA_EXPENSE_RESULT_VIEW] AS SELECT CA_EXPENSE_RESULT.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_EXPENSE_RESULT LEFT JOIN DM_JOB ON CA_EXPENSE_RESULT.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW [CA_EXPENSE_VIEW] GO CREATE VIEW [dbo].[CA_EXPENSE_VIEW] AS SELECT CA_EXPENSE.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_EXPENSE LEFT JOIN DM_JOB ON CA_EXPENSE.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW [CA_FINISHED_QUANTITY_VIEW] GO CREATE VIEW [dbo].[CA_FINISHED_QUANTITY_VIEW] AS SELECT CA_FINISHED_QUANTITY.*, isnull(DM_ITEM.ITEM_CLASS_ID,'') as ITEM_CLASS_ID, isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID, ' ' AS EXPENSE_CLASS_ID FROM CA_FINISHED_QUANTITY LEFT JOIN DM_ITEM ON CA_FINISHED_QUANTITY.ITEM_ID = DM_ITEM.ITEM_ID LEFT JOIN DM_JOB ON CA_FINISHED_QUANTITY.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW [CA_PREVIOUS_PRODUCT_COST_VIEW] GO CREATE VIEW [dbo].[CA_PREVIOUS_PRODUCT_COST_VIEW] AS SELECT CA_PREVIOUS_PRODUCT_COST.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_PREVIOUS_PRODUCT_COST LEFT JOIN DM_JOB ON CA_PREVIOUS_PRODUCT_COST.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW [CA_PRODUCT_COST_VIEW] GO CREATE VIEW [dbo].[CA_PRODUCT_COST_VIEW] AS SELECT CA_PRODUCT_COST.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_PRODUCT_COST LEFT JOIN DM_JOB ON CA_PRODUCT_COST.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW PO_VIEW GO CREATE VIEW [dbo].[PO_VIEW] AS SELECT dbo.PO.PR_KEY AS PR_KEY_CTU, dbo.PO.TRAN_ID, dbo.PO.TRAN_NO, dbo.PO.TRAN_DATE, dbo.PO.RELEASE_DATE, dbo.PO.MARKET_ID, dbo.PO.EXCHANGE_RATE, dbo.PO.EXCHANGE_RATE_EXTRA, dbo.PO.CURRENCY_ID, dbo.PO.PR_DETAIL_ID, dbo.PO.PR_DETAIL_NAME, dbo.PO.CONTACT_PERSON, dbo.PO.ADDRESS, dbo.PO.TAX_FILE_NUMBER, dbo.PO.PAYMENT_METHOD_ID, dbo.PO.COMMENTS, dbo.PO.PAYMENT_TERM_ID, dbo.PO.STATUS, PO.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.PO.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID,dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.PO_DETAIL.*, dbo.DM_ITEM.ORIGIN,HR_EMPLOYEE_INFO.EMPLOYEE_NAME FROM dbo.PO INNER JOIN dbo.PO_DETAIL ON dbo.PO.PR_KEY = dbo.PO_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PO_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PO.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.PO_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN HR_EMPLOYEE_INFO ON dbo.PO_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID GO DROP VIEW SBO_VIEW GO CREATE VIEW [dbo].[SBO_VIEW] AS SELECT dbo.SBO.PR_KEY AS PR_KEY_CTU, dbo.SBO.ORGANIZATION_ID, dbo.SBO.TRAN_ID, dbo.SBO.TRAN_NO, dbo.SBO.TRAN_DATE, dbo.SBO.APPROVE_DATE, dbo.SBO.RELEASE_DATE, dbo.SBO.EMPLOYEE_ID, dbo.SBO.MARKET_ID, dbo.SBO.CURRENCY_ID, dbo.SBO.EXCHANGE_RATE, dbo.SBO.EXCHANGE_RATE_EXTRA, dbo.SBO.PR_DETAIL_ID, dbo.SBO.PR_DETAIL_NAME, dbo.SBO.CONTACT_PERSON, dbo.SBO.ADDRESS, dbo.SBO.TAX_FILE_NUMBER, dbo.SBO.PAYMENT_METHOD_ID, dbo.SBO.COMMENTS, dbo.SBO.STATUS, dbo.SBO.USER_ID, dbo.SBO.APPROVER_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_ITEM.WARRANTY_PERIOD, dbo.SBO_DETAIL.* FROM dbo.DM_PR_DETAIL RIGHT OUTER JOIN dbo.SBO_DETAIL INNER JOIN dbo.SBO ON dbo.SBO_DETAIL.FR_KEY = dbo.SBO.PR_KEY ON dbo.DM_PR_DETAIL.PR_DETAIL_ID = dbo.SBO.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SBO_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SBO_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO GO DROP VIEW SO_VIEW GO CREATE VIEW [dbo].[SO_VIEW] AS SELECT dbo.SO.PR_KEY AS PR_KEY_CTU, dbo.SO.ORGANIZATION_ID, dbo.SO.TRAN_ID, dbo.SO.TRAN_NO, dbo.SO.TRAN_DATE, dbo.SO.SBO_TRAN_ID, dbo.SO.SBO_TRAN_NO, dbo.SO.IS_OVER_CREDIT_LIMIT, dbo.SO.APPROVE_DATE, dbo.SO.RELEASE_DATE, dbo.SO.DELIVERY_DATE, dbo.SO.EMPLOYEE_ID, dbo.SO.MARKET_ID, dbo.SO.SHIPPING_METHOD_ID, dbo.SO.VEHICLE_ID, dbo.SO.CURRENCY_ID, dbo.SO.EXCHANGE_RATE, dbo.SO.EXCHANGE_RATE_EXTRA, dbo.SO.PR_DETAIL_NAME, dbo.SO.CONTACT_PERSON, dbo.SO.ADDRESS, dbo.SO.TAX_FILE_NUMBER, dbo.SO.PAYMENT_METHOD_ID, dbo.SO.PAYMENT_TERM_ID, dbo.SO.COMMENTS, dbo.SO.STATUS, dbo.SO.USER_ID, dbo.SO.APPROVER_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.SO.DRIVER_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, SO.DELIVER_ORGANIZATION_ID, dbo.SO_DETAIL.* FROM dbo.SO INNER JOIN dbo.SO_DETAIL ON dbo.SO.PR_KEY = dbo.SO_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SO_DETAIL.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SO_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SO_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO DROP VIEW [DM_POSTING_VIEW] GO CREATE VIEW [dbo].[DM_POSTING_VIEW] AS SELECT dbo.DM_POSTING.PR_KEY AS PR_KEY_CTU, dbo.DM_POSTING.ORGANIZATION_ID, dbo.DM_POSTING.TRAN_ID, dbo.DM_POSTING.DAY_START, dbo.DM_POSTING.DAY_END, dbo.DM_POSTING.TRAN_NO, dbo.DM_POSTING.ITEM_SOURCE_ID, dbo.DM_POSTING.ITEM_OP_ID, dbo.DM_POSTING.ITEM_CLASS_ID,dbo.DM_POSTING.ITEM_ID, dbo.DM_POSTING.PR_DETAIL_CLASS_ID, dbo.DM_POSTING.WAREHOUSE_CLASS_ID, dbo.DM_POSTING.ACTIVE, dbo.DM_POSTING.ISSUE_RECEIVE, dbo.DM_POSTING_DETAIL.* FROM dbo.DM_POSTING INNER JOIN dbo.DM_POSTING_DETAIL ON dbo.DM_POSTING.PR_KEY = dbo.DM_POSTING_DETAIL.FR_KEY GO DROP VIEW PO_VIEW GO CREATE VIEW [dbo].[PO_VIEW] AS SELECT dbo.PO.PR_KEY AS PR_KEY_CTU, dbo.PO.TRAN_ID, dbo.PO.TRAN_NO, dbo.PO.TRAN_DATE, dbo.PO.RELEASE_DATE, dbo.PO.MARKET_ID, dbo.PO.EXCHANGE_RATE, dbo.PO.EXCHANGE_RATE_EXTRA, dbo.PO.CURRENCY_ID, dbo.PO.PR_DETAIL_ID, dbo.PO.PR_DETAIL_NAME, dbo.PO.CONTACT_PERSON, dbo.PO.ADDRESS, dbo.PO.TAX_FILE_NUMBER, dbo.PO.PAYMENT_METHOD_ID, dbo.PO.COMMENTS, dbo.PO.PAYMENT_TERM_ID, dbo.PO.STATUS, dbo.PO.ITEM_SOURCE_ID, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.PO.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.PO_DETAIL.PR_KEY, dbo.PO_DETAIL.FR_KEY, dbo.PO_DETAIL.LIST_ORDER, dbo.PO_DETAIL.ITEM_OP_ID, dbo.PO_DETAIL.WAREHOUSE_ID, dbo.PO_DETAIL.JOB_ID, dbo.PO_DETAIL.EMPLOYEE_ID, dbo.PO_DETAIL.ITEM_ID, dbo.PO_DETAIL.DESCRIPTION, dbo.PO_DETAIL.DESCRIPTION_ULS, dbo.PO_DETAIL.UNIT_ID, dbo.PO_DETAIL.QUANTITY, dbo.PO_DETAIL.QUANTITY_EXTRA, dbo.PO_DETAIL.QUANTITY_WH, dbo.PO_DETAIL.UNIT_PRICE_ORIG, dbo.PO_DETAIL.UNIT_PRICE, dbo.PO_DETAIL.UNIT_PRICE_WH, dbo.PO_DETAIL.AMOUNT_ORIG, dbo.PO_DETAIL.AMOUNT, dbo.PO_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.PO_DETAIL.VAT_INCOME_AMOUNT, dbo.PO_DETAIL.VAT_TAX_ID, dbo.PO_DETAIL.VAT_TAX_RATE, dbo.PO_DETAIL.VAT_TAX_AMOUNT, dbo.PO_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.PO_DETAIL.IMPORT_TAX_RATE, dbo.PO_DETAIL.IMPORT_TAX_AMOUNT, dbo.PO_DETAIL.IMPORT_TAX_AMOUNT_ORIG, dbo.PO_DETAIL.PURCHASE_COST, dbo.PO_DETAIL.PURCHASE_COST_ORIG, dbo.PO_DETAIL.DISCOUNT_RATE, dbo.PO_DETAIL.DISCOUNT_AMOUNT, dbo.PO_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.PO_DETAIL.TOTAL_AMOUNT, dbo.PO_DETAIL.TOTAL_AMOUNT_ORIG, dbo.PO_DETAIL.AMOUNT_EXTRA, dbo.PO_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.PO_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.PO_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.PO_DETAIL.IMPORT_TAX_AMOUNT_EXTRA, dbo.PO_DETAIL.PURCHASE_COST_EXTRA, dbo.PO_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.PO_DETAIL.LUX_TAX_RATE, dbo.PO_DETAIL.LUX_TAX_AMOUNT, dbo.PO_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.PO_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.DM_ITEM.ORIGIN, HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA,PO.PO_CLASS_ID FROM dbo.PO INNER JOIN dbo.PO_DETAIL ON dbo.PO.PR_KEY = dbo.PO_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PO_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PO.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.PO_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN HR_EMPLOYEE_INFO ON dbo.PO_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID GO DROP VIEW DM_SBO GO CREATE VIEW [dbo].[DM_SBO] AS SELECT ORGANIZATION_ID + '-' + TRAN_ID + '-' + TRAN_NO + '_' + ITEM_ID AS SBO_ID, ORGANIZATION_ID, TRAN_ID AS SBO_TRAN_ID, TRAN_NO AS SBO_TRAN_NO, TRAN_DATE AS SBO_TRAN_DATE,PR_DETAIL_ID, PR_DETAIL_NAME, STATUS,PAYMENT_METHOD_ID,ITEM_ID, PAYMENT_TERM_ID,CURRENCY_ID,APPROVE_DATE,RELEASE_DATE,CREDIT_LIMIT FROM dbo.SBO_VIEW GO DROP VIEW DM_SO GO CREATE VIEW [dbo].[DM_SO] AS SELECT DISTINCT ORGANIZATION_ID + '-' + TRAN_ID + '-' + TRAN_NO + '-' + PR_DETAIL_ID AS SO_ID , ORGANIZATION_ID, TRAN_ID AS SO_TRAN_ID, TRAN_NO AS SO_TRAN_NO, TRAN_DATE AS SO_TRAN_DATE,PR_DETAIL_ID, PR_DETAIL_NAME, COMMENTS, STATUS,PAYMENT_METHOD_ID,PAYMENT_TERM_ID,CURRENCY_ID FROM dbo.SO_VIEW GO DROP VIEW DM_PO GO CREATE VIEW [dbo].[DM_PO] AS SELECT ORGANIZATION_ID + '-' + TRAN_ID + '-' + TRAN_NO AS PO_ID, ORGANIZATION_ID, TRAN_ID AS PO_TRAN_ID, TRAN_NO AS PO_TRAN_NO, TRAN_DATE AS PO_TRAN_DATE,PR_DETAIL_ID, PR_DETAIL_NAME, COMMENTS, STATUS,PAYMENT_TERM_ID,PAYMENT_METHOD_ID,CURRENCY_ID FROM dbo.PO GO DROP VIEW [VAT_TRANSACTION_VIEW] GO create view [dbo].[VAT_TRANSACTION_VIEW] AS SELECT VAT_TRANSACTION.*, VAT_TRANSACTION.AMOUNT AS AMOUNT_EXTRA FROM VAT_TRANSACTION GO DROP VIEW CONTRACT_VIEW GO CREATE VIEW [dbo].[CONTRACT_VIEW] AS SELECT CONTRACT.*, DM_PR_DETAIL.PR_DETAIL_NAME, DM_PR_DETAIL.PR_DETAIL_CLASS_ID FROM CONTRACT INNER JOIN DM_PR_DETAIL ON CONTRACT.PR_DETAIL_ID = DM_PR_DETAIL.PR_DETAIL_ID GO DROP VIEW CONTRACT_PAYMENT_VIEW GO CREATE VIEW [dbo].[CONTRACT_PAYMENT_VIEW] AS SELECT dbo.CONTRACT_PAYMENT.PR_KEY, dbo.CONTRACT_PAYMENT.CONTRACT_NO, dbo.CONTRACT_PAYMENT.TRAN_ID, dbo.CONTRACT_PAYMENT.TRAN_NO, dbo.CONTRACT_PAYMENT.TRAN_DATE, dbo.CONTRACT_PAYMENT.ACCOUNT_ID_CONTRA, dbo.CONTRACT_PAYMENT.COMMENTS, dbo.CONTRACT_PAYMENT.CURRENCY_ID, dbo.CONTRACT_PAYMENT.EXCHANGE_RATE, dbo.CONTRACT_PAYMENT.EXCHANGE_RATE_EXTRA, dbo.CONTRACT_PAYMENT.AMOUNT_ORIG, dbo.CONTRACT_PAYMENT.AMOUNT, dbo.CONTRACT_PAYMENT.AMOUNT_EXTRA, dbo.CONTRACT.CONTRACT_NAME, dbo.CONTRACT.CONTRACT_DATE, dbo.CONTRACT.CONTRACT_TYPE, dbo.CONTRACT.ACCOUNT_ID, dbo.CONTRACT.PR_DETAIL_ID, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.CONTRACT.ORGANIZATION_ID, dbo.CONTRACT.PAYMENT_TERM_ID FROM dbo.CONTRACT INNER JOIN dbo.CONTRACT_PAYMENT ON dbo.CONTRACT.CONTRACT_NO = dbo.CONTRACT_PAYMENT.CONTRACT_NO INNER JOIN dbo.DM_PR_DETAIL ON dbo.CONTRACT.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID GO DROP VIEW [CONTRACT_IMPLEMENTATION_VIEW] GO CREATE VIEW [dbo].[CONTRACT_IMPLEMENTATION_VIEW] AS SELECT dbo.CONTRACT_IMPLEMENTATION.PR_KEY, dbo.CONTRACT_IMPLEMENTATION.CONTRACT_NO, dbo.CONTRACT_IMPLEMENTATION.TRAN_ID, dbo.CONTRACT_IMPLEMENTATION.TRAN_NO, dbo.CONTRACT_IMPLEMENTATION.TRAN_DATE, dbo.CONTRACT_IMPLEMENTATION.COMMENTS, dbo.CONTRACT_IMPLEMENTATION.ACCOUNT_ID_CONTRA, dbo.CONTRACT_IMPLEMENTATION.ITEM_ID, dbo.CONTRACT_IMPLEMENTATION.UNIT_ID, dbo.CONTRACT_IMPLEMENTATION.DESCRIPTION, dbo.CONTRACT_IMPLEMENTATION.CURRENCY_ID, dbo.CONTRACT_IMPLEMENTATION.EXCHANGE_RATE, dbo.CONTRACT_IMPLEMENTATION.EXCHANGE_RATE_EXTRA, dbo.CONTRACT_IMPLEMENTATION.QUANTITY, dbo.CONTRACT_IMPLEMENTATION.UNIT_PRICE_ORIG, dbo.CONTRACT_IMPLEMENTATION.UNIT_PRICE, dbo.CONTRACT_IMPLEMENTATION.AMOUNT_ORIG, dbo.CONTRACT_IMPLEMENTATION.AMOUNT, dbo.CONTRACT_IMPLEMENTATION.AMOUNT_EXTRA, dbo.CONTRACT.CONTRACT_NAME, dbo.CONTRACT.CONTRACT_DATE, dbo.CONTRACT.CONTRACT_TYPE, dbo.CONTRACT.ACCOUNT_ID, dbo.CONTRACT.PR_DETAIL_ID, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.CONTRACT.ORGANIZATION_ID, dbo.CONTRACT.PAYMENT_TERM_ID FROM dbo.CONTRACT INNER JOIN dbo.CONTRACT_IMPLEMENTATION ON dbo.CONTRACT.CONTRACT_NO = dbo.CONTRACT_IMPLEMENTATION.CONTRACT_NO INNER JOIN dbo.DM_PR_DETAIL ON dbo.CONTRACT.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID GO DROP VIEW DM_REC_PR_DETAIL GO CREATE VIEW DM_REC_PR_DETAIL AS SELECT PR_DETAIL_ID AS REC_PR_DETAIL_ID,PR_DETAIL_NAME FROM DM_PR_DETAIL WHERE ACTIVE=1 GO DROP VIEW DM_TRANSFER_ITEM_OP GO CREATE VIEW DM_TRANSFER_ITEM_OP AS SELECT ITEM_OP_ID AS TRANSFER_ITEM_OP_ID,ITEM_OP_NAME FROM DM_ITEM_OP WHERE ACTIVE=1 GO DROP VIEW DM_ITEM_COMBO_VIEW GO CREATE VIEW DM_ITEM_COMBO_VIEW AS (SELECT ITEM_ID+UNIT_ID AS ITEM_COMBO_ID,ITEM_COMBO_NAME,ACTIVE FROM DM_ITEM_COMBO) UNION ALL (SELECT ITEM_ID + UNIT_ID,ITEM_NAME, ACTIVE FROM DM_ITEM) GO DROP VIEW BALANCE_VIEW GO CREATE VIEW [dbo].[BALANCE_VIEW] AS SELECT BALANCE.PR_KEY, BALANCE_DETAIL.PR_KEY AS PR_KEY_DETAIL,BALANCE.ORGANIZATION_ID, BALANCE.TRAN_ID, BALANCE.TRAN_DATE, BALANCE.ACCOUNT_ID, BALANCE.CURRENCY_ID, BALANCE_DETAIL.QUANTITY, BALANCE_DETAIL.QUANTITY_EXTRA, BALANCE_DETAIL.UNIT_PRICE, BALANCE_DETAIL.AMOUNT, BALANCE_DETAIL.AMOUNT_ORIG, BALANCE_DETAIL.AMOUNT_EXTRA, BALANCE_DETAIL.PR_DETAIL_ID, BALANCE_DETAIL.EXPENSE_ID, BALANCE_DETAIL.JOB_ID, BALANCE_DETAIL.BANK_ID, BALANCE_DETAIL.WAREHOUSE_ID, BALANCE_DETAIL.ITEM_ID, DM_ITEM.UNIT_ID AS UNIT_ID_WH, DM_ITEM.COST_METHOD, DM_ITEM.ITEM_CLASS_ID, DM_PR_DETAIL.PR_DETAIL_CLASS_ID, 'DEB' AS DEBIT_CREDIT, DM_ITEM.ITEM_NAME,DM_PR_DETAIL.PR_DETAIL_NAME,DM_JOB.JOB_CLASS_ID,DM_JOB.JOB_NAME,DM_EXPENSE.EXPENSE_CLASS_ID,DM_JOB.JOB_VALUE,'' as ACCOUNT_ID_CONTRA,EXCHANGE_RATE FROM BALANCE INNER JOIN BALANCE_DETAIL ON BALANCE.PR_KEY = BALANCE_DETAIL.FR_KEY LEFT OUTER JOIN DM_ITEM ON BALANCE_DETAIL.ITEM_ID = DM_ITEM.ITEM_ID LEFT OUTER JOIN DM_PR_DETAIL ON BALANCE_DETAIL.PR_DETAIL_ID = DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN DM_JOB ON BALANCE_DETAIL.JOB_ID=DM_JOB.JOB_ID LEFT OUTER JOIN DM_EXPENSE ON BALANCE_DETAIL.EXPENSE_ID=DM_EXPENSE.EXPENSE_ID WHERE (BALANCE.AMOUNT_DEBIT_ORIG <> 0 or BALANCE.AMOUNT_DEBIT <> 0 OR BALANCE.AMOUNT_DEBIT_EXTRA <> 0) UNION ALL SELECT BALANCE_3.PR_KEY, BALANCE_DETAIL_3.PR_KEY AS PR_KEY_DETAIL, BALANCE_3.ORGANIZATION_ID, BALANCE_3.TRAN_ID, BALANCE_3.TRAN_DATE, BALANCE_3.ACCOUNT_ID, BALANCE_3.CURRENCY_ID, BALANCE_DETAIL_3.QUANTITY, BALANCE_DETAIL_3.QUANTITY_EXTRA, BALANCE_DETAIL_3.UNIT_PRICE, BALANCE_DETAIL_3.AMOUNT, BALANCE_DETAIL_3.AMOUNT_ORIG, BALANCE_DETAIL_3.AMOUNT_EXTRA, BALANCE_DETAIL_3.PR_DETAIL_ID, BALANCE_DETAIL_3.EXPENSE_ID, BALANCE_DETAIL_3.JOB_ID,BALANCE_DETAIL_3.BANK_ID, BALANCE_DETAIL_3.WAREHOUSE_ID, BALANCE_DETAIL_3.ITEM_ID, DM_ITEM_1.UNIT_ID AS UNIT_ID_WH, DM_ITEM_1.COST_METHOD, DM_ITEM_1.ITEM_CLASS_ID, DM_PR_DETAIL_1.PR_DETAIL_CLASS_ID, 'CRD' AS CREDIT_CREDIT, DM_ITEM_1.ITEM_NAME,DM_PR_DETAIL_1.PR_DETAIL_NAME,DM_JOB.JOB_CLASS_ID,DM_JOB.JOB_NAME,DM_EXPENSE.EXPENSE_CLASS_ID,DM_JOB.JOB_VALUE,'' as ACCOUNT_ID_CONTRA,EXCHANGE_RATE FROM BALANCE AS BALANCE_3 INNER JOIN BALANCE_DETAIL AS BALANCE_DETAIL_3 ON BALANCE_3.PR_KEY = BALANCE_DETAIL_3.FR_KEY LEFT OUTER JOIN DM_ITEM AS DM_ITEM_1 ON BALANCE_DETAIL_3.ITEM_ID = DM_ITEM_1.ITEM_ID LEFT OUTER JOIN DM_PR_DETAIL AS DM_PR_DETAIL_1 ON BALANCE_DETAIL_3.PR_DETAIL_ID = DM_PR_DETAIL_1.PR_DETAIL_ID LEFT OUTER JOIN DM_JOB ON BALANCE_DETAIL_3.JOB_ID=DM_JOB.JOB_ID LEFT OUTER JOIN DM_EXPENSE ON BALANCE_DETAIL_3.EXPENSE_ID=DM_EXPENSE.EXPENSE_ID WHERE (BALANCE_3.AMOUNT_DEBIT_ORIG = 0 and BALANCE_3.AMOUNT_DEBIT=0 and BALANCE_3.AMOUNT_DEBIT_EXTRA=0) UNION ALL SELECT PR_KEY, '00000000-0000-0000-0000-000000000000' AS PR_KEY_DETAIL,ORGANIZATION_ID, TRAN_ID, TRAN_DATE, ACCOUNT_ID, CURRENCY_ID, AMOUNT_DEBIT * 0 AS QUANTITY, AMOUNT_DEBIT * 0 AS QUANTITY_EXTRA, AMOUNT_DEBIT * 0 AS UNIT_PRICE, AMOUNT_DEBIT AS AMOUNT, AMOUNT_DEBIT_ORIG AS AMOUNT_ORIG, AMOUNT_DEBIT_EXTRA AS AMOUNT_EXTRA, '' AS PR_DETAIL_ID, '' AS EXPENSE_ID, '' AS JOB_ID, '' AS BANK_ID, '' AS WAREHOUSE_ID, '' AS ITEM_ID, '' AS UNIT_ID_WH, '' AS COST_METHOD, '' AS ITEM_CLASS_ID, ' ' AS PR_DETAIL_CLASS_ID, 'DEB' AS DEBIT_CREDIT, ' ' AS ITEM_NAME,' ' AS PR_DETAIL_NAME,' ' AS JOB_CLASS_ID,' ' AS JOB_NAME,' ' AS EXPENSE_CLASS_ID,0 AS JOB_VALUE,'' as ACCOUNT_ID_CONTRA,EXCHANGE_RATE FROM BALANCE AS BALANCE_2 WHERE (AMOUNT_DEBIT_ORIG <> 0 OR AMOUNT_DEBIT <> 0 OR AMOUNT_DEBIT_EXTRA <> 0) AND (NOT EXISTS (SELECT 'TRUE' FROM BALANCE_DETAIL AS BALANCE_DETAIL_2 WHERE (BALANCE_2.PR_KEY = FR_KEY))) UNION ALL SELECT PR_KEY, '00000000-0000-0000-0000-000000000000' AS PR_KEY_DETAIL, ORGANIZATION_ID,TRAN_ID, TRAN_DATE, ACCOUNT_ID, CURRENCY_ID, AMOUNT_CREDIT * 0 AS QUANTITY, AMOUNT_DEBIT * 0 AS QUANTITY_EXTRA, AMOUNT_CREDIT * 0 AS UNIT_PRICE, AMOUNT_CREDIT AS AMOUNT, AMOUNT_CREDIT_ORIG AS AMOUNT_ORIG, AMOUNT_CREDIT_EXTRA AS AMOUNT_EXTRA, '' AS PR_DETAIL_ID, '' AS EXPENSE_ID, '' AS JOB_ID, '' AS BANK_ID, '' AS WAREHOUSE_ID, '' AS ITEM_ID, '' AS UNIT_ID_WH, '' AS COST_METHOD, '' AS ITEM_CLASS_ID, ' ' AS PR_DETAIL_CLASS_ID, 'CRD' AS CREDIT_CREDIT, ' ' AS ITEM_NAME,' ' AS PR_DETAIL_NAME,' ' AS JOB_CLASS_ID,' ' AS JOB_NAME,' ' AS EXPENSE_CLASS_ID, 0 AS JOB_VALUE,'' as ACCOUNT_ID_CONTRA,EXCHANGE_RATE FROM BALANCE AS BALANCE_1 WHERE (AMOUNT_DEBIT_ORIG = 0 AND AMOUNT_DEBIT = 0 AND AMOUNT_DEBIT_EXTRA = 0) AND (NOT EXISTS (SELECT 'TRUE' FROM BALANCE_DETAIL AS BALANCE_DETAIL_1 WHERE (BALANCE_1.PR_KEY = FR_KEY))) GO DROP VIEW [DM_BANK_ACCOUNT] GO CREATE VIEW [dbo].[DM_BANK_ACCOUNT] AS SELECT * FROM DM_ACCOUNT WHERE ACCOUNT_TYPE_ID IN ('02','11') GO DROP VIEW [DM_CASH_ACCOUNT] GO CREATE VIEW [dbo].[DM_CASH_ACCOUNT] AS SELECT * FROM DM_ACCOUNT WHERE ACCOUNT_TYPE_ID IN ('01') GO DROP VIEW [DM_CASH_BANK_ACCOUNT] GO CREATE VIEW [dbo].[DM_CASH_BANK_ACCOUNT] AS SELECT * FROM DM_ACCOUNT WHERE ACCOUNT_TYPE_ID IN ('01','02','11') GO DROP VIEW [DM_SII] GO CREATE VIEW [dbo].[DM_SII] AS SELECT DISTINCT ORGANIZATION_ID + '-' + TRAN_ID + '-' + TRAN_NO + '-' + PR_DETAIL_ID AS SII_ID , ORGANIZATION_ID, TRAN_ID AS SII_TRAN_ID, TRAN_NO AS SII_TRAN_NO, TRAN_DATE AS SII_TRAN_DATE,PR_DETAIL_ID, PR_DETAIL_NAME, COMMENTS, STATUS,PAYMENT_METHOD_ID,PAYMENT_TERM_ID,CURRENCY_ID FROM dbo.SO_VIEW WHERE TRAN_ID='SII' GO DROP VIEW [ASSET_VIEW] GO CREATE VIEW [dbo].[ASSET_VIEW] AS SELECT dbo.ASSET.PR_KEY AS PR_KEY_CTU, dbo.ASSET.TRAN_ID, dbo.ASSET.ASSET_NO, dbo.ASSET.ASSET_ID, dbo.ASSET.DESCRIPTION, dbo.ASSET.FA_CLASS_ID, dbo.ASSET.MANU_YEAR, dbo.ASSET.MANU_COUNTRY, dbo.ASSET.UNIT, dbo.ASSET.QUANTITY, dbo.ASSET.FA_STATUS_ID, dbo.ASSET.PR_DETAIL_ID, dbo.ASSET.PURCHASE_DATE, dbo.ASSET.USE_DATE, dbo.ASSET.LIQUID_DATE, dbo.ASSET.DEP_CEASE_DATE, dbo.ASSET.DEP_METHOD_ID, dbo.ASSET.DEP_LENGTH, dbo.ASSET.FA_ACCOUNT_ID, dbo.ASSET.DEP_ACCOUNT_ID, dbo.ASSET_DETAIL.LIST_ORDER, dbo.ASSET_DETAIL.FA_OPERATION_ID, dbo.ASSET_DETAIL.FA_SOURCE_ID, dbo.ASSET_DETAIL.EFFECTIVE_DATE, dbo.ASSET_DETAIL.END_DATE, dbo.ASSET_DETAIL.VALUE_PREVIOUS, dbo.ASSET_DETAIL.VALUE_INCREASE, dbo.ASSET_DETAIL.VALUE_ORIG, dbo.ASSET_DETAIL.DEP_VALUE_PREVIOUS, dbo.ASSET_DETAIL.DEP_VALUE_INCREASE, dbo.ASSET_DETAIL.DEP_VALUE_ORIG, dbo.ASSET_DETAIL.DEP_VALUE_ACCU, dbo.ASSET_DETAIL.DEP_VALUE_CURRENT, dbo.ASSET_DETAIL.REMAIN_VALUE_ORIG, dbo.ASSET_DETAIL.REMAIN_VALUE_CURRENT, dbo.ASSET_DETAIL.DEP_RATE, dbo.ASSET_DETAIL.VALUE_PREVIOUS_EXTRA, dbo.ASSET_DETAIL.VALUE_INCREASE_EXTRA, dbo.ASSET_DETAIL.VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_PREVIOUS_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_INCREASE_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_ACCU_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_CURRENT_EXTRA, dbo.ASSET_DETAIL.REMAIN_VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.REMAIN_VALUE_CURRENT_EXTRA, dbo.ASSET_DETAIL.DEP_RATE_EXTRA, dbo.ASSET_DETAIL.LOCKED, dbo.DM_FA_OPERATION.INC_DEC, dbo.ASSET.FA_ACCOUNT_ID AS ACCOUNT_ID, dbo.ASSET_DETAIL.EXCHANGE_RATE_EXTRA, dbo.ASSET.JOB_ID, dbo.ASSET.ORGANIZATION_ID, dbo.ASSET.EXPENSE_ID, dbo.ASSET.EXPENSE_ACCOUNT_ID, dbo.ASSET_DETAIL.FR_KEY, dbo.ASSET_DETAIL.PR_KEY, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.DM_FA_SOURCE.FA_SOURCE_NAME FROM dbo.ASSET INNER JOIN dbo.ASSET_DETAIL ON dbo.ASSET.PR_KEY = dbo.ASSET_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_FA_SOURCE ON dbo.ASSET_DETAIL.FA_SOURCE_ID = dbo.DM_FA_SOURCE.FA_SOURCE_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.ASSET.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_FA_OPERATION ON dbo.ASSET_DETAIL.FA_OPERATION_ID = dbo.DM_FA_OPERATION.FA_OPERATION_ID GO DROP VIEW DM_TAX_OFFICE GO CREATE VIEW DM_TAX_OFFICE AS SELECT PR_DETAIL_ID,PR_DETAIL_NAME FROM DM_PR_DETAIL WHERE PR_DETAIL_TYPE_ID='08' GO DROP VIEW DM_EMPLOYEE1 GO DROP VIEW DM_TRANSFER_ITEM_OP GO CREATE VIEW DM_TRANSFER_ITEM_OP AS SELECT ITEM_OP_ID AS TRANSFER_ITEM_OP_ID,ITEM_OP_NAME FROM DM_ITEM_OP WHERE ACTIVE=1 GO DROP VIEW [DM_SHIPPING_VENDOR] GO DROP VIEW [HR_EMPLOYEE_INFO_VIEW] GO CREATE VIEW [dbo].[HR_EMPLOYEE_INFO_VIEW] AS SELECT TOP (100) PERCENT dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.HR_EMPLOYEE_INFO.SEX, dbo.HR_EMPLOYEE_INFO.DOB, dbo.HR_EMPLOYEE_INFO.BIRTH_PLACE, dbo.HR_EMPLOYEE_INFO.ORIGIN_PLACE, dbo.HR_EMPLOYEE_INFO.ORIGIN_CITY, dbo.HR_EMPLOYEE_INFO.IDENTITY_NO, dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.PASSPORT_NO, dbo.HR_EMPLOYEE_INFO.PASSPORT_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.PASSPORT_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.PASSPORT_EXPIRE_DATE, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_NO, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.BANK_ACCOUNT_NO, dbo.HR_EMPLOYEE_INFO.BANK_NAME, dbo.HR_EMPLOYEE_INFO.BANK_BRANCH, dbo.HR_EMPLOYEE_INFO.MARITAL_STATUS_ID, dbo.HR_EMPLOYEE_INFO.FAMILY_ORIGIN_ID, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ORIGIN_ID, dbo.HR_EMPLOYEE_INFO.ETHNICS_ID, dbo.HR_EMPLOYEE_INFO.RELIGION_ID, dbo.HR_EMPLOYEE_INFO.NATIONALITY_ID, dbo.HR_EMPLOYEE_INFO.HOME_PHONE, dbo.HR_EMPLOYEE_INFO.WORK_PHONE, dbo.HR_EMPLOYEE_INFO.MOBILE_PHONE, dbo.HR_EMPLOYEE_INFO.PERSONAL_EMAIL, dbo.HR_EMPLOYEE_INFO.WORK_EMAIL, dbo.HR_EMPLOYEE_INFO.YAHOO_ID, dbo.HR_EMPLOYEE_INFO.SKYPE_ID, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_NAME, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID_L1, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_NAME_L1, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID, dbo.HR_EMPLOYEE_POSITION.POSITION_ID, dbo.HR_EMPLOYEE_POSITION.LINE_MANAGER_ID, dbo.HR_EMPLOYEE_POSITION.START_DATE, dbo.HR_EMPLOYEE_POSITION.END_DATE, dbo.HR_EMPLOYEE_POSITION.COMMENTS, REVERSE(SUBSTRING(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0, CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0))) AS FIRST_NAME, REVERSE(SUBSTRING(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0) + 1, LEN(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME)) - CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0))) AS LAST_NAME, dbo.HR_ORGANIZATION_VIEW.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, dbo.HR_ORGANIZATION_VIEW.HR_LIST_CHILD_ORGANIZATION_ID, dbo.HR_EMPLOYEE_POSITION.SALARY_POINT1, dbo.HR_EMPLOYEE_POSITION.SALARY_POINT2, dbo.HR_EMPLOYEE_POSITION.LEVEL_SALARY, '' AS CONTACT_TYPE_ID, '' AS STATUS_ID, dbo.HR_EMPLOYEE_INFO.DATE_COMPANY, a.EMPLOYEE_LEVEL_NAME, DATEDIFF(YEAR, dbo.HR_EMPLOYEE_INFO.DOB, GETDATE()) AS YEARS, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_DATE, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_PLACE, dbo.DM_POSITION.POSITION_NAME, dbo.HR_EMPLOYEE_POSITION.REASON_ID, dbo.HR_EMPLOYEE_POSITION.SALARY_RANK_ID, dbo.HR_EMPLOYEE_POSITION.SALARY_LEVEL_ID, dbo.HR_EMPLOYEE_INFO.ACCEPT_STATUS, dbo.HR_EMPLOYEE_INFO.COMMENTS_ACCEPT FROM dbo.HR_EMPLOYEE_INFO LEFT OUTER JOIN dbo.HR_EMPLOYEE_CURRENT_POS INNER JOIN dbo.HR_EMPLOYEE_POSITION ON dbo.HR_EMPLOYEE_CURRENT_POS.EMPLOYEE_ID = dbo.HR_EMPLOYEE_POSITION.EMPLOYEE_ID AND dbo.HR_EMPLOYEE_CURRENT_POS.START_DATE = dbo.HR_EMPLOYEE_POSITION.START_DATE LEFT OUTER JOIN dbo.DM_POSITION ON dbo.HR_EMPLOYEE_POSITION.POSITION_ID = dbo.DM_POSITION.POSITION_ID LEFT OUTER JOIN dbo.HR_ORGANIZATION_VIEW ON dbo.HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID = dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.HR_EMPLOYEE_CURRENT_POS.EMPLOYEE_ID LEFT OUTER JOIN (SELECT dbo.HR_EMPLOYEE_LEVEL.EMPLOYEE_ID, DM_EMPLOYEE_LEVEL_1.EMPLOYEE_LEVEL_NAME FROM dbo.HR_EMPLOYEE_LEVEL INNER JOIN (SELECT EMPLOYEE_ID, MAX(END_DATE) AS END_DATE FROM dbo.HR_EMPLOYEE_LEVEL AS HR_EMPLOYEE_LEVEL_1 GROUP BY EMPLOYEE_ID) AS a_1 ON dbo.HR_EMPLOYEE_LEVEL.EMPLOYEE_ID = a_1.EMPLOYEE_ID AND dbo.HR_EMPLOYEE_LEVEL.END_DATE = a_1.END_DATE LEFT OUTER JOIN dbo.DM_EMPLOYEE_LEVEL AS DM_EMPLOYEE_LEVEL_1 ON dbo.HR_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID = DM_EMPLOYEE_LEVEL_1.EMPLOYEE_LEVEL_ID) AS a ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = a.EMPLOYEE_ID WHERE (dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID NOT IN ('', '99999')) AND (dbo.HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID <> '') ORDER BY dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID GO DROP VIEW [SEC_PERMISSION_VIEW] GO DROP VIEW [DM_POS_SHIFT_PR_KEY] GO CREATE VIEW dbo.DM_POS_SHIFT_PR_KEY AS SELECT PR_KEY, COMMENTS FROM dbo.POS_SHIFT GO DROP VIEW [DM_PARTNER] GO CREATE VIEW DM_PARTNER AS SELECT PR_DETAIL_ID AS PARTNER_ID,PR_DETAIL_NAME AS PARTNER_NAME FROM DM_PR_DETAIL GO DROP VIEW [DM_PARENT_ACCOUNT] GO CREATE VIEW DM_PARENT_ACCOUNT AS SELECT ACCOUNT_ID,ACCOUNT_NAME,ACCOUNT_NAME_ULS,ACCOUNT_NAME_JP,ACCOUNT_NAME_KR,ACTIVE FROM DM_ACCOUNT WHERE IS_PARENT=1 GO DROP VIEW [DM_DETAIL_ACCOUNT] GO CREATE VIEW DM_DETAIL_ACCOUNT AS SELECT ACCOUNT_ID,ACCOUNT_NAME,ACCOUNT_NAME_ULS,ACCOUNT_NAME_JP,ACCOUNT_NAME_KR,ACTIVE FROM DM_ACCOUNT WHERE IS_PARENT=0 GO DROP VIEW [DM_EMPLOYEE] GO DROP VIEW [DM_VENDOR] GO DROP VIEW [DM_CUSTOMER] GO DROP VIEW [DM_EMPLOYEE_LIST] GO DROP VIEW [DM_ACCOUNT_DEBIT] GO CREATE VIEW DM_ACCOUNT_DEBIT AS SELECT ACCOUNT_ID AS ACCOUNT_ID_DEBIT,ACCOUNT_NAME FROM DM_ACCOUNT GO DROP VIEW [DM_ACCOUNT_CREDIT] GO CREATE VIEW DM_ACCOUNT_CREDIT AS SELECT ACCOUNT_ID AS ACCOUNT_ID_CREDIT,ACCOUNT_NAME FROM DM_ACCOUNT GO DROP VIEW [PR_EMPLOYEE_INFO_VIEW] GO DROP VIEW [DM_VEHICLE_TEAM] GO CREATE VIEW DM_VEHICLE_TEAM AS SELECT ORGANIZATION_ID AS VEHICLE_TEAM_ID,ORGANIZATION_NAME AS VEHICLE_TEAM_NAME FROM DM_ORGANIZATION GO DROP VIEW [ORGANIZATION_L1_VIEW] GO DROP VIEW [DM_EMPLOYEE_ACC] GO DROP VIEW [POS_SALE_INVOICE_VIEW] GO CREATE VIEW dbo.POS_SALE_INVOICE_VIEW AS SELECT dbo.POS_SALE_INVOICE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE_INVOICE.ORGANIZATION_ID, dbo.POS_SALE_INVOICE.TRAN_ID, dbo.POS_SALE_INVOICE.TRAN_NO, dbo.POS_SALE_INVOICE.TRAN_DATE, dbo.POS_SALE_INVOICE.VAT_TRAN_NO, dbo.POS_SALE_INVOICE.VAT_TRAN_DATE, dbo.POS_SALE_INVOICE.VAT_TRAN_SERIE, dbo.POS_SALE_INVOICE.PR_DETAIL_ID, dbo.POS_SALE_INVOICE.CONTACT_PERSON, dbo.POS_SALE_INVOICE.ADDRESS, dbo.POS_SALE_INVOICE.TAX_FILE_NUMBER, dbo.POS_SALE_INVOICE.COMMENTS, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.POS_SALE_INVOICE_DETAIL.PR_KEY, dbo.POS_SALE_INVOICE_DETAIL.FR_KEY, dbo.POS_SALE_INVOICE_DETAIL.PR_KEY_SALE_DETAIL, dbo.POS_SALE_INVOICE_DETAIL.ITEM_ID, dbo.POS_SALE_INVOICE_DETAIL.DESCRIPTION, dbo.POS_SALE_INVOICE_DETAIL.UNIT_ID, dbo.POS_SALE_INVOICE_DETAIL.UNIT_PRICE, dbo.POS_SALE_INVOICE_DETAIL.QUANTITY, dbo.POS_SALE_INVOICE_DETAIL.AMOUNT, dbo.POS_SALE_INVOICE_DETAIL.VAT_TAX_RATE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.POS_SALE_INVOICE.EMPLOYEE_ID, HR_EMPLOYEE_INFO.EMPLOYEE_NAME FROM dbo.DM_PR_DETAIL RIGHT OUTER JOIN dbo.POS_SALE_INVOICE LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.POS_SALE_INVOICE.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID ON dbo.DM_PR_DETAIL.PR_DETAIL_ID = dbo.POS_SALE_INVOICE.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_ITEM INNER JOIN dbo.POS_SALE_INVOICE_DETAIL ON dbo.DM_ITEM.ITEM_ID = dbo.POS_SALE_INVOICE_DETAIL.ITEM_ID ON dbo.POS_SALE_INVOICE.PR_KEY = dbo.POS_SALE_INVOICE_DETAIL.FR_KEY GO GO DROP VIEW [DM_PAYMENT_PR_DETAIL] GO CREATE VIEW DM_PAYMENT_PR_DETAIL AS SELECT PR_DETAIL_ID AS PAYMENT_PR_DETAIL_ID,PR_DETAIL_NAME FROM DM_PR_DETAIL GO DROP VIEW [DM_SHIPPING_PR_DETAIL] GO CREATE VIEW dbo.DM_SHIPPING_PR_DETAIL AS SELECT PR_DETAIL_ID AS SHIPPING_PR_DETAIL_ID, PR_DETAIL_NAME FROM dbo.DM_PR_DETAIL GO DROP VIEW [EXPENSE_VIEW] GO CREATE VIEW [dbo].[EXPENSE_VIEW] AS SELECT CA_BEGINNING_AMOUNT.*,isnull(DM_JOB.JOB_CLASS_ID,'') as JOB_CLASS_ID FROM CA_BEGINNING_AMOUNT LEFT JOIN DM_JOB ON CA_BEGINNING_AMOUNT.JOB_ID=DM_JOB.JOB_ID GO DROP VIEW DM_SO_ORDER GO DROP VIEW DM_RELATIONSHIP GO DROP VIEW DM_RELATIONSHIP GO DROP VIEW POS_SALE_VIEW GO CREATE VIEW POS_SALE_VIEW AS SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.ORGANIZATION_ID, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, dbo.POS_SALE.TRAN_HOUR, dbo.POS_SALE.TRAN_MINUTE, dbo.POS_SALE.SO_TRAN_ID, dbo.POS_SALE.SO_TRAN_NO, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, dbo.POS_SALE.VAT_PURCHASE_ID, dbo.POS_SALE.ITEM_SOURCE_ID, dbo.POS_SALE.WAREHOUSE_ID_RECEIVE, dbo.POS_SALE.EMPLOYEE_ID, dbo.POS_SALE.MARKET_ID, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.REC_PR_DETAIL_ID, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.COMMENTS, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.WARRANTY_PERIOD, dbo.POS_SALE.POS_SHIFT_PR_KEY, dbo.POS_SALE.PR_DETAIL_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.POS_SALE.TAX_OFFICE_ID, dbo.POS_SALE.SO_TRAN_DATE, dbo.POS_SALE.SO_TRAN_HOUR, dbo.POS_SALE.SO_TRAN_MINUTE, dbo.POS_SALE.PR_KEY_SO, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.RECEIVE_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EXPORT_TAX_RATE, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.POS_SALE_DETAIL.SALE_COST, dbo.POS_SALE_DETAIL.SALE_COST_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID FROM dbo.POS_SALE INNER JOIN POS_SALE_DETAIL ON POS_SALE.PR_KEY=POS_SALE_DETAIL.FR_KEY LEFT JOIN dbo.HR_EMPLOYEE_INFO ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.POS_SALE.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.DM_ITEM.ITEM_ID = dbo.POS_SALE_DETAIL.ITEM_ID GO DROP VIEW POS_PURCHASE_VIEW GO CREATE VIEW [dbo].[POS_PURCHASE_VIEW] AS SELECT dbo.POS_PURCHASE.PR_KEY AS PR_KEY_CTU, dbo.POS_PURCHASE.ORGANIZATION_ID, dbo.POS_PURCHASE.TRAN_ID, dbo.POS_PURCHASE.TRAN_NO, dbo.POS_PURCHASE.TRAN_DATE, dbo.POS_PURCHASE.TRAN_HOUR, dbo.POS_PURCHASE.TRAN_MINUTE, dbo.POS_PURCHASE.VAT_TRAN_NO, dbo.POS_PURCHASE.VAT_TRAN_DATE, dbo.POS_PURCHASE.VAT_TRAN_SERIE, dbo.POS_PURCHASE.VAT_PURCHASE_ID, dbo.POS_PURCHASE.ITEM_SOURCE_ID, dbo.POS_PURCHASE.WAREHOUSE_ID_ISSUE, dbo.POS_PURCHASE.EMPLOYEE_ID, dbo.POS_PURCHASE.CURRENCY_ID, dbo.POS_PURCHASE.EXCHANGE_RATE, dbo.POS_PURCHASE.REC_PR_DETAIL_ID, dbo.POS_PURCHASE.PR_DETAIL_ID, dbo.POS_PURCHASE.PR_DETAIL_NAME, dbo.POS_PURCHASE.CONTACT_PERSON, dbo.POS_PURCHASE.ADDRESS, dbo.POS_PURCHASE.TAX_FILE_NUMBER, dbo.POS_PURCHASE.PAYMENT_METHOD_ID, dbo.POS_PURCHASE.PAYMENT_TERM_ID, dbo.POS_PURCHASE.PAYMENT_DATE, dbo.POS_PURCHASE.COMMENTS, dbo.POS_PURCHASE.STATUS, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.POS_PURCHASE.POS_SHIFT_PR_KEY, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.POS_PURCHASE.TAX_OFFICE_ID, dbo.POS_PURCHASE_DETAIL.* FROM dbo.POS_PURCHASE INNER JOIN dbo.POS_PURCHASE_DETAIL ON dbo.POS_PURCHASE.PR_KEY = dbo.POS_PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID GO DROP VIEW [POS_SHIFT_VIEW] GO CREATE VIEW [dbo].[POS_SHIFT_VIEW] AS SELECT dbo.POS_SHIFT.PR_KEY AS PR_KEY_CTU, dbo.POS_SHIFT.ORGANIZATION_ID, dbo.POS_SHIFT.TRAN_ID, dbo.POS_SHIFT.TRAN_NO, dbo.POS_SHIFT.SHIFT_ID, dbo.POS_SHIFT.SHIFT_DATE, dbo.POS_SHIFT.COMMENTS, dbo.POS_SHIFT_EMPLOYEE.EMPLOYEE_ID, dbo.POS_SHIFT_EMPLOYEE.IS_TEAM_LEAD, dbo.POS_SHIFT_ITEM.PUMP_ID, dbo.POS_SHIFT_ITEM.ITEM_ID, dbo.POS_SHIFT_ITEM.OPENING_BALANCE, dbo.POS_SHIFT_ITEM.RECEIVE_QUANTITY, dbo.POS_SHIFT_ITEM.ENDING_BALANCE, dbo.POS_SHIFT_ITEM.NOTES, dbo.DM_PUMP.PUMP_NAME, dbo.DM_ITEM.ITEM_NAME, dbo.DM_SHIFT.SHIFT_NAME, dbo.DM_SHIFT.START_HOUR, dbo.DM_SHIFT.START_MINUTE, dbo.DM_SHIFT.END_HOUR, dbo.DM_SHIFT.END_MINUTE, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.POS_SHIFT_ITEM.ISSUE_QUANTITY, dbo.DM_SHIFT.LIST_ORDER, dbo.POS_SHIFT.NEXT_PR_KEY FROM POS_SHIFT INNER JOIN POS_SHIFT_ITEM ON POS_SHIFT.PR_KEY=POS_SHIFT_ITEM.FR_KEY LEFT JOIN POS_SHIFT_EMPLOYEE ON POS_SHIFT.PR_KEY = POS_SHIFT_EMPLOYEE.FR_KEY LEFT JOIN HR_EMPLOYEE_INFO ON POS_SHIFT_EMPLOYEE.EMPLOYEE_ID=HR_EMPLOYEE_INFO.EMPLOYEE_ID LEFT JOIN DM_ITEM ON POS_SHIFT_ITEM.ITEM_ID = DM_ITEM.ITEM_ID LEFT JOIN DM_SHIFT ON POS_SHIFT.SHIFT_ID=DM_SHIFT.SHIFT_ID LEFT JOIN DM_PUMP ON POS_SHIFT_ITEM.PUMP_ID = DM_PUMP.PUMP_ID GO