CREATE OR REPLACE FORCE VIEW "JFCI"."FCI_VIW_PC_LIV11" ("TRN_BNO", "TRN_SEG", "TRN_VOU", "TRN_DTE", "TRN_DOC_DUE", "TRN_ACC", "TRN_VENDOR", "TRN_VNAME", "TRN_DR", "TRN_CR", "TRN_DR_CURR", "TRN_CR_CURR", "TRN_CURR", "TRN_EXCH", "TRN_BL_DATE", "TRN_PMT", "TRN_NAR", "TRN_INV", "TRN_INV_DATE", "TRN_EQNO", "TRN_PAYTERM", "TRN_LOC", "TRN_SYS", "TRN_CREATE", "TRN_WHT_CODE", "TRN_WHT_BASE", "TRN_STREET", "TRN_CITY", "TRN_TAX_CODE", "TRN_PROFIT", "VOUCHER_NO", "AP_INV", "INV_NO", "FI_DOC_NUMBER", "VENDOR_CODE", "EXPORT_TYPE", "TRN_BUS_AREA") AS
SELECT /*+ no_use_hash_aggregation */ TRN_BNO,
TRN_SEG,
TRN_VOU,
TRN_DTE,
TRN_DOC_DUE,
TRN_ACC,
TRN_VENDOR,
TRN_VNAME,
SUM (TRN_DR) TRN_DR,
SUM (TRN_CR) TRN_CR,
SUM (TRN_DR_CURR) TRN_DR_CURR,
SUM (TRN_CR_CURR) TRN_CR_CURR,
TRN_CURR,
TRN_EXCH,
TRN_BL_DATE,
TRN_PMT,
TRN_NAR,
TRN_INV,
TRN_INV_DATE,
TRN_EQNO,
TRN_PAYTERM,
TRN_LOC,
TRN_SYS,
TRN_CREATE,
TRN_WHT_CODE,
TRN_WHT_BASE,
TRN_STREET,
TRN_CITY,
TRN_TAX_CODE,
TRN_PROFIT,
VOUCHER_NO,
AP_INV,
INV_NO,
FI_DOC_NUMBER,
VENDOR_CODE,
EXPORT_TYPE,
TRN_BUS_AREA
FROM ( SELECT DECODE(HD.COM_CODE,'1000','Y ','3000','K ') TRN_BNO,
' ' TRN_SEG,
DAYEND.VOUCHER_NO AS TRN_VOU,
DECODE(NVL(HD.CANCEL_DATE, ' '),' ',
TO_CHAR (TO_DATE (HD.AP_DATE, 'YYYYMMDD'),'DDMMYYYY'),
DECODE(SUBSTR(DAYEND.FI_DOC_NUMBER,1,2),'ZA' ,TO_CHAR (TO_DATE (SUBSTR(HD.CANCEL_DATE,1,8), 'YYYYMMDD'),'DDMMYYYY'),HD.CANCEL_DATE)) TRN_DTE,
hd.inv_date TRN_DOC_DUE,
ITEM.GLACCOUNT AS TRN_ACC,
HD.VENDOR_CODE AS TRN_VENDOR,
VEN.VENDOR_NAME AS TRN_VNAME,
ITEM.DC_INDICATOR DC_INDICATOR,
DECODE(ITEM.DC_INDICATOR,'D',ITEM.AMOUNT_LOC,0) TRN_DR,
DECODE(ITEM.DC_INDICATOR,'C',ITEM.AMOUNT_LOC,0) TRN_CR,
DECODE(ITEM.DC_INDICATOR,'D',ITEM.AMOUNT_DOC,0) TRN_DR_CURR,
DECODE(ITEM.DC_INDICATOR,'C',ITEM.AMOUNT_DOC,0) TRN_CR_CURR,
DECODE(NVL(HD.DOC_CURR,' '),' ','THB',HD.DOC_CURR) TRN_CURR,
HD.EXCH_RATE AS TRN_EXCH,
DECODE(SIGN(LENGTH(NVL(HD.BILL_DATE,' '))-8),1,
TO_CHAR(TO_DATE(HD.BILL_DATE, 'YYYYMMDD'),'DDMMYYYY'),
HD.BILL_DATE) TRN_BL_DATE,
VEN.PAY_METH AS TRN_PMT,
'' AS TRN_NAR,
DAYEND.INV_NO AS TRN_INV,
TO_CHAR (TO_DATE (HD.INV_DATE, 'YYYYMMDD'), 'DDMMYYYY') AS TRN_INV_DATE,
' ' AS TRN_EQNO,
VEN.PAY_TERM AS TRN_PAYTERM,
CASE
WHEN HD.PLANT_CODE = 'D'
THEN
'LCB2'
WHEN HD.PLANT_CODE = 'H'
THEN
'LCB4'
WHEN HD.PLANT_CODE = 'F'
THEN
'LCB1'
WHEN HD.PLANT_CODE = 'G'
THEN
'LCB3'
WHEN HD.PLANT_CODE = 'B'
THEN
'MEC'
WHEN HD.PLANT_CODE = 'X'
THEN
'LCB2'
WHEN NVL (HD.PLANT_CODE, ' ') = ' '
AND HD.COM_CODE = '3000'
THEN
'MEC'
WHEN NVL (HD.PLANT_CODE, ' ') = ' '
AND HD.COM_CODE = '1000'
AND HD.BUS_PLACE_CODE = '0009'
THEN
'LCB3'
WHEN NVL (HD.PLANT_CODE, ' ') = ' '
AND HD.COM_CODE = '1000'
AND HD.BUS_PLACE_CODE = '0002'
THEN
'LCB2'
WHEN NVL (HD.PLANT_CODE, ' ') = ' '
AND HD.COM_CODE = '1000'
AND HD.BUS_PLACE_CODE = '0013'
THEN
'LCB4'
END
AS TRN_LOC,
TO_CHAR (SYSDATE, 'DDMMYYYY') AS TRN_SYS,
HD.CREATED_BY AS TRN_CREATE,
HD.VAT_CODE AS TRN_WHT_CODE,
0 AS TRN_WHT_BASE,
'' AS TRN_STREET,
'' AS TRN_CITY,
'' AS TRN_TAX_CODE,
NVL (BOI.PROFIT, ' ') AS TRN_PROFIT,
DAYEND.VOUCHER_NO AS VOUCHER_NO,
DAYEND.AP_INV AS AP_INV,
DAYEND.INV_NO AS INV_NO,
DAYEND.FI_DOC_NUMBER AS FI_DOC_NUMBER,
DAYEND.VENDOR_CODE AS VENDOR_CODE,
DAYEND.EXPORT_TYPE AS EXPORT_TYPE,
HD.BUS_AREA_CODE TRN_BUS_AREA
FROM FCI_TBL_PC_E_DAYEND_INVOICE DAYEND
INNER JOIN FCI_TBL_LIV_T_MAN_VOUCHER_HD HD
ON DAYEND.BUS_PLACE_CODE = HD.BUS_PLACE_CODE
AND DAYEND.INV_NO = HD.INV_NO
AND DAYEND.AP_INV = HD.AP_INV
AND DAYEND.FI_DOC_NUMBER = HD.ACC_CODE
INNER JOIN FCI_TBL_LIV_T_FI_ITEM ITEM
ON HD.COM_CODE = ITEM.COM_CODE
AND HD.BUS_AREA_CODE = ITEM.BUS_AREA_CODE
AND HD.ACC_CODE = ITEM.FI_DOC_NUMBER
LEFT OUTER JOIN FCI_TBL_LIV_M_VENDOR VEN
ON HD.VENDOR_CODE = VEN.VENDOR_CODE
AND HD.COM_CODE = VEN.COM_CODE
LEFT OUTER JOIN FCI_TBL_LIV_M_EXCHANGE_RATE EXCH
ON ITEM.EXRATE_CODE = EXCH.EXCH_RATE_TYPE
INNER JOIN FCI_TBL_LIV_T_MAN_VOUCHER_IT IT
ON IT.COM_CODE = HD.COM_CODE
AND ITEM.FI_DOC_NUMBER = HD.ACC_CODE
AND IT.COM_CODE = HD.COM_CODE
AND IT.AP_INV = HD.AP_INV
AND IT.ITEM_NO = ITEM.ITEM_NO
LEFT OUTER JOIN FCI_TBL_PC_M_PROFIT_FOR_BOI BOI
ON BOI.PROD_CODE = IT.MODEL_NAME
WHERE NVL (DAYEND.LIV11EXPORTED_DATE, ' ') = ' '
-- AND DAYEND.voucher_no = 'CF201207000001'
ORDER BY ITEM.FI_DOC_NUMBER, ITEM.DC_INDICATOR DESC, ITEM.ITEM_NO)
GROUP BY TRN_BNO,
TRN_SEG,
TRN_VOU,
TRN_DTE,
TRN_DOC_DUE,
TRN_ACC,
TRN_VENDOR,
TRN_VNAME,
TRN_CURR,
TRN_EXCH,
TRN_BL_DATE,
TRN_PMT,
TRN_NAR,
TRN_INV,
TRN_INV_DATE,
TRN_EQNO,
TRN_PAYTERM,
TRN_LOC,
TRN_SYS,
TRN_CREATE,
TRN_WHT_CODE,
TRN_WHT_BASE,
TRN_STREET,
TRN_CITY,
TRN_TAX_CODE,
TRN_PROFIT,
VOUCHER_NO,
AP_INV,
INV_NO,
FI_DOC_NUMBER,
VENDOR_CODE,
EXPORT_TYPE,
TRN_BUS_AREA,
DC_INDICATOR
ORDER BY TRN_BNO,
TRN_SEG,
TRN_VOU,
TRN_DTE,
TRN_DOC_DUE,
TRN_ACC,
TRN_VENDOR,
TRN_VNAME,
TRN_DR_CURR,
TRN_CR_CURR,
TRN_CURR,
TRN_EXCH,
TRN_BL_DATE,
TRN_PMT,
TRN_NAR,
TRN_INV,
TRN_INV_DATE,
TRN_EQNO,
TRN_PAYTERM,
TRN_LOC,
TRN_SYS,
TRN_CREATE,
TRN_WHT_CODE,
TRN_WHT_BASE,
TRN_STREET,
TRN_CITY,
TRN_TAX_CODE,
TRN_PROFIT,
VOUCHER_NO,
AP_INV,
INV_NO,
FI_DOC_NUMBER,
VENDOR_CODE,
EXPORT_TYPE,
TRN_BUS_AREA;
