CREATE VIEW
PTIVSTOCKTRANSACTION
(
ANO,
MES,
FECHA,
NUM_TRANSACCION,
LINEA_TRANSACCION,
ALMACEN_LOGICO,
DESC_ALMACEN_LOGICO,
ALMACEN_CONTABLE,
FULL_LLAVE,
TIPOA,
CLAVE1,
CLAVE2,
CLAVE3,
CLAVE4,
CLAVE5,
CLAVE6,
CLAVE7,
CLAVE8,
CLAVE9,
CLAVE10,
DESCRIPCION,
CALIDAD,
PLANTILLA_COD,
PLANTILLA_DESC,
CENTRO_COSTOS,
DESC_CENTRO_COSTOS,
UM_PRODUCTO,
UM_MOVIMIENTO,
LOTE,
ELEMENTO,
CONT_DOCUMENTO,
NUM_DOCUMENTO,
COD_MAQ_DOCU_INTERNO,
FACT_PROVEEDOR,
COD_RECEPCION,
TRANSACC_CONTABLE,
NIT_SOCIO_ORDEN,
SOCIO_ORDEN,
NOMBRE_SOCIO_ORDEN,
LINEA_DOCUMENTO,
COD_ORDEN,
SALDO_INI_CANT,
COSTO_TOTAL_SALDO_INI,
COSTO_UNIT_SALDO_INI,
CANT_ENTRADA,
CANT_SALIDA,
CANT__SECUND_ENTRADA,
CANT__SECUND_SALIDA,
CANT_KG_ENTRADA,
CANT_KG_SALIDA,
COSTO_ACTUAL_ENTRADA,
COSTO_ACTUAL_SALIDA,
COSTO_UNIT_PROV_ENTRADA,
COSTO_UNIT_PROV_SALIDA,
COSTO_UNIT_DEFINITIVO_ENTRADA,
COSTO_UNIT_DEFINITIVO_SALIDA,
COSTO_TOTAL_PROV_ENTRADA,
COSTO_TOTAL_PROV_SALIDA,
COSTO_TOTAL_DEFINITIVO_ENTRADA,
COSTO_TOTAL_DEFINITIVO_SALIDA,
CUENTA_DEBITO,
TIPO_DEBITO_CONTABLE,
CUENTA_CREDITO,
TIPO_CREDITO_CONTABLE,
MES_ANTERIOR,
GRUPO_CONTABLE,
ALMACEN_LOGICO_ORIGEN,
DESCR_ALM_LOGICO_ORIGEN,
GRUPO_CONTABLE_ORIGEN,
ALMACEN_LOGICO_DESTINO,
DESCR_ALM_LOGICO_DESTINO,
GRUPO_CONTABLE_DESTINO,
CONTADOR_OC_CONSIGNA,
OC_CONSIGNACION,
USARIO_CREA,
CANT_EMPAQUE,
UM_EMPAQUE
)
AS
SELECT
YEAR(STOCKTRANSACTION.TRANSACTIONDATE) ANO,
MONTH(STOCKTRANSACTION.TRANSACTIONDATE) MES,
STOCKTRANSACTION.TRANSACTIONDATE FECHA,
STOCKTRANSACTION.TRANSACTIONNUMBER NUM_TRANSACCION,
STOCKTRANSACTION.TRANSACTIONDETAILNUMBER LINEA_TRANSACCION,
STOCKTRANSACTION.LOGICALWAREHOUSECODE ALMACEN_LOGICO,
LOGICALWAREHOUSE.LONGDESCRIPTION AS DESC_ALMACEN_LOGICO,
LOGICALWAREHOUSE.WAREHOUSEACCOUNTINGGROUPCODE AS ALMACEN_CONTABLE,
(TRIM(STOCKTRANSACTION.ITEMTYPECODE) ||' '|| TRIM(STOCKTRANSACTION.DECOSUBCODE01)||' '|| TRIM
(STOCKTRANSACTION.DECOSUBCODE02)||' '|| TRIM(STOCKTRANSACTION.DECOSUBCODE03)||' '|| TRIM
(STOCKTRANSACTION.DECOSUBCODE04)||' '|| TRIM(STOCKTRANSACTION.DECOSUBCODE05)||' '|| TRIM
(STOCKTRANSACTION.DECOSUBCODE06)||' '|| TRIM(STOCKTRANSACTION.DECOSUBCODE07)||' '|| TRIM
(STOCKTRANSACTION.DECOSUBCODE08)||' '|| TRIM(STOCKTRANSACTION.DECOSUBCODE09)||' '|| TRIM
(STOCKTRANSACTION.DECOSUBCODE10)) FULL_LLAVE,
STOCKTRANSACTION.ITEMTYPECODE TIPOA,
STOCKTRANSACTION.DECOSUBCODE01 CLAVE1,
STOCKTRANSACTION.DECOSUBCODE02 CLAVE2,
STOCKTRANSACTION.DECOSUBCODE03 CLAVE3,
STOCKTRANSACTION.DECOSUBCODE04 CLAVE4,
STOCKTRANSACTION.DECOSUBCODE05 CLAVE5,
STOCKTRANSACTION.DECOSUBCODE06 CLAVE6,
STOCKTRANSACTION.DECOSUBCODE07 CLAVE7,
STOCKTRANSACTION.DECOSUBCODE08 CLAVE8,
STOCKTRANSACTION.DECOSUBCODE09 CLAVE9,
STOCKTRANSACTION.DECOSUBCODE10 CLAVE10,
FULLITEMKEYDECODER.SUMMARIZEDDESCRIPTION DESCRIPCION,
STOCKTRANSACTION.QUALITYLEVELCODE CALIDAD,
STOCKTRANSACTION.TEMPLATECODE PLANTILLA_COD,
STOCKTRANSACTIONTEMPLATE.LONGDESCRIPTION PLANTILLA_DESC,
CASE
WHEN STOCKTRANSACTION.COSTCENTERCODE IS NULL
THEN INTERNALDOCUMENTLINE.COSTCENTERCODE
ELSE STOCKTRANSACTION.COSTCENTERCODE
END CENTRO_COSTOS,
COSTCENTER.LONGDESCRIPTION DESC_CENTRO_COSTOS,
PRODUCT.BASEPRIMARYUNITCODE UM_PRODUCTO,
STOCKTRANSACTION.BASEPRIMARYUOMCODE UM_MOVIMIENTO,
STOCKTRANSACTION.LOTCODE LOTE,
STOCKTRANSACTION.ITEMELEMENTCODE ELEMENTO,
STOCKTRANSACTION.ORDERCOUNTERCODE CONT_DOCUMENTO,
STOCKTRANSACTION.ORDERCODE NUM_DOCUMENTO,
SUBSTR(ADSTORAGEMQ.VALUESTRING,1,24) COD_MAQ_DOCU_INTERNO,
STOCKTRANSACTION.INVOICECODE FACT_PROVEEDOR,
STOCKTRANSACTION.BILLCODE COD_RECEPCION,
STOCKTRANSACTION.ACCOUNTTRANSACTIONNUMBER TRANSACC_CONTABLE,
COALESCE(CUSTOMER.BUSINESSPARTNERSEARCHNAME,SUPPLIER.BUSINESSPARTNERSEARCHNAME)
NIT_SOCIO_ORDEN,
COALESCE(STOCKTRANSACTION.CUSTOMERCODE,STOCKTRANSACTION.SUPPLIERCODE) SOCIO_ORDEN,
COALESCE(CUSTOMER.BUSINESSPARTNERLEGALNAME1,SUPPLIER.BUSINESSPARTNERLEGALNAME1)
NOMBRE_SOCIO_ORDEN,
STOCKTRANSACTION.ORDERLINE LINEA_DOCUMENTO,
STOCKTRANSACTION.PRODUCTIONORDERCODE COD_ORDEN,
COALESCE(WAREHOUSEITEMPERIODIZEDCOST.DYNAMICAVERAGECOSTTOTALQTY,0) SALDO_INI_CANT,
COALESCE(WAREHOUSEITEMPERIODIZEDCOST.DYNAMICAVERAGECOSTTOTALVALUE,0) COSTO_TOTAL_SALDO_INI,
COALESCE(WAREHOUSEITEMPERIODIZEDCOST.DYNAMICAVERAGECOSTUNITVALUE,0) COSTO_UNIT_SALDO_INI,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN STOCKTRANSACTION.BASEPRIMARYQUANTITY
WHEN 2
THEN 0
END CANT_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN STOCKTRANSACTION.BASEPRIMARYQUANTITY
END CANT_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN STOCKTRANSACTION.BASESECONDARYQUANTITY
WHEN 2
THEN 0
END CANT__SECUND_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN STOCKTRANSACTION.BASESECONDARYQUANTITY
END CANT__SECUND_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN
CASE
WHEN STOCKTRANSACTION.BASEPRIMARYUOMCODE = 'kg'
THEN STOCKTRANSACTION.BASEPRIMARYQUANTITY
WHEN STOCKTRANSACTION.BASESECONDARYUOMCODE = 'kg'
THEN STOCKTRANSACTION.BASESECONDARYQUANTITY
WHEN STOCKTRANSACTION.BASEPRIMARYUOMCODE = 'un'
THEN (STOCKTRANSACTION.BASEPRIMARYQUANTITY * ADSTORAGE.VALUEDECIMAL)
END
WHEN 2
THEN 0
END CANT_KG_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 2
THEN
CASE
WHEN STOCKTRANSACTION.BASEPRIMARYUOMCODE = 'kg'
THEN STOCKTRANSACTION.BASEPRIMARYQUANTITY
WHEN STOCKTRANSACTION.BASESECONDARYUOMCODE = 'kg'
THEN STOCKTRANSACTION.BASESECONDARYQUANTITY
WHEN STOCKTRANSACTION.BASEPRIMARYUOMCODE = 'un'
THEN (STOCKTRANSACTION.BASEPRIMARYQUANTITY * ADSTORAGE.VALUEDECIMAL)
END
WHEN 1
THEN 0
END CANT_KG_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN STOCKTRANSACTION.ACTUALBASECOST
WHEN 2
THEN 0
END COSTO_ACTUAL_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN STOCKTRANSACTION.ACTUALBASECOST
END COSTO_ACTUAL_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN STOCKTRANSACTION.PROVISIONALBASECOST
WHEN 2
THEN 0
END COSTO_UNIT_PROV_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN STOCKTRANSACTION.PROVISIONALBASECOST
END COSTO_UNIT_PROV_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN STOCKTRANSACTION.CLOSINGBASECOST
WHEN 2
THEN 0
END COSTO_UNIT_DEFINITIVO_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN STOCKTRANSACTION.CLOSINGBASECOST
END COSTO_UNIT_DEFINITIVO_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN (STOCKTRANSACTION.PROVISIONALBASECOST * STOCKTRANSACTION.BASEPRIMARYQUANTITY)
WHEN 2
THEN 0
END COSTO_TOTAL_PROV_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN (STOCKTRANSACTION.PROVISIONALBASECOST * STOCKTRANSACTION.BASEPRIMARYQUANTITY)
END COSTO_TOTAL_PROV_SALIDA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN (STOCKTRANSACTION.CLOSINGBASECOST * STOCKTRANSACTION.BASEPRIMARYQUANTITY)
WHEN 2
THEN 0
END COSTO_TOTAL_DEFINITIVO_ENTRADA,
CASE STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE
WHEN 1
THEN 0
WHEN 2
THEN (STOCKTRANSACTION.CLOSINGBASECOST * STOCKTRANSACTION.BASEPRIMARYQUANTITY)
END COSTO_TOTAL_DEFINITIVO_SALIDA,
PROSTACCOUNTING.ACCOUNTCODE CUENTA_DEBITO,
PROSTACCOUNTING.ACCOUNTINGTYPE TIPO_DEBITO_CONTABLE,
PROSTACCOUNTING2.ACCOUNTCODE CUENTA_CREDITO,
PROSTACCOUNTING2.ACCOUNTINGTYPE TIPO_CREDITO_CONTABLE,
(WAREHOUSEITEMPERIODIZEDCOST.PERIODPERIODIZEDCALENDARYEAR ||'-' ||
WAREHOUSEITEMPERIODIZEDCOST.PERIODCODE) MES_ANTERIOR,
TABAVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE AS GRUPO_CONTABLE,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 1
THEN STOCKTRANSACTION.LOGICALWAREHOUSECODE
ELSE STOCKTRANSACTION1.LOGICALWAREHOUSECODE
END ALMACEN_LOGICO_ORIGEN,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 1
THEN LOGICALWAREHOUSE.LONGDESCRIPTION
ELSE LOGICALWAREHOUSE1.LONGDESCRIPTION
END DESCR_ALM_LOGICO_ORIGEN,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 1
THEN TABAVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE
ELSE TABAVLWAREHOUSEGROUPDETAIL2.AVAILABILITYWAREHOUSEGROUPCODE
END GRUPO_CONTABLE_ORIGEN,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 2
THEN STOCKTRANSACTION.LOGICALWAREHOUSECODE
ELSE STOCKTRANSACTION1.LOGICALWAREHOUSECODE
END ALMACEN_LOGICO_DESTINO,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 2
THEN LOGICALWAREHOUSE.LONGDESCRIPTION
ELSE LOGICALWAREHOUSE1.LONGDESCRIPTION
END DESCR_ALM_LOGICO_DESTINO ,
CASE STOCKTRANSACTION.DETAILTYPE
WHEN 2
THEN TABAVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE
ELSE TABAVLWAREHOUSEGROUPDETAIL2.AVAILABILITYWAREHOUSEGROUPCODE
END GRUPO_CONTABLE_DESTINO ,
PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCNTCODE CONTADOR_OC_CONSIGNA,
PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCODE OC_CONSIGNACION,
STOCKTRANSACTION.CREATIONUSER USARIO_CREA,
STOCKTRANSACTION.USERPACKAGINGQUANTITY CANT_EMPAQUE,
STOCKTRANSACTION.USERPACKAGINGUOMCODE UM_EMPAQUE
FROM
STOCKTRANSACTION STOCKTRANSACTION
LEFT OUTER JOIN
WAREHOUSEITEMPERIODIZEDCOST
ON
WAREHOUSEITEMPERIODIZEDCOST.PERIODPERIODIZEDCALENDARYEAR = YEAR(ADD_MONTHS
(STOCKTRANSACTION.TRANSACTIONDATE,-1))
AND WAREHOUSEITEMPERIODIZEDCOST.PERIODCODE = MONTH(ADD_MONTHS(STOCKTRANSACTION.TRANSACTIONDATE,-1)
)
AND WAREHOUSEITEMPERIODIZEDCOST.ITEMTYPEAFICODE = STOCKTRANSACTION.ITEMTYPECODE
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE01 = STOCKTRANSACTION.DECOSUBCODE01
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE02 = STOCKTRANSACTION.DECOSUBCODE02
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE03 = STOCKTRANSACTION.DECOSUBCODE03
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE04 = STOCKTRANSACTION.DECOSUBCODE04
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE05 = STOCKTRANSACTION.DECOSUBCODE05
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE06 = STOCKTRANSACTION.DECOSUBCODE06
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE07 = STOCKTRANSACTION.DECOSUBCODE07
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE08 = STOCKTRANSACTION.DECOSUBCODE08
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE09 = STOCKTRANSACTION.DECOSUBCODE09
AND WAREHOUSEITEMPERIODIZEDCOST.SUBCODE10 = STOCKTRANSACTION.DECOSUBCODE10
AND WAREHOUSEITEMPERIODIZEDCOST.COMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND WAREHOUSEITEMPERIODIZEDCOST.PERPERIODIZEDCALENDARTYPECODE = 'MO1'
LEFT OUTER JOIN
STOCKTRANSACTIONTEMPLATE
ON
STOCKTRANSACTIONTEMPLATE.CODE = STOCKTRANSACTION.TEMPLATECODE
AND STOCKTRANSACTIONTEMPLATE.ONHANDUPDATE IN (1,2)
LEFT OUTER JOIN
LOGICALWAREHOUSE
ON
LOGICALWAREHOUSE.OWNINGCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND LOGICALWAREHOUSE.COMPANYCODE = STOCKTRANSACTION.LOGICALWAREHOUSECOMPANYCODE
AND LOGICALWAREHOUSE.CODE = STOCKTRANSACTION.LOGICALWAREHOUSECODE
LEFT OUTER JOIN
FULLITEMKEYDECODER
ON
FULLITEMKEYDECODER.ITEMTYPECODE = STOCKTRANSACTION.ITEMTYPECODE
AND FULLITEMKEYDECODER.SUBCODE01 = STOCKTRANSACTION.DECOSUBCODE01
AND FULLITEMKEYDECODER.SUBCODE02 = STOCKTRANSACTION.DECOSUBCODE02
AND FULLITEMKEYDECODER.SUBCODE03 = STOCKTRANSACTION.DECOSUBCODE03
AND FULLITEMKEYDECODER.SUBCODE04 = STOCKTRANSACTION.DECOSUBCODE04
AND FULLITEMKEYDECODER.SUBCODE05 = STOCKTRANSACTION.DECOSUBCODE05
AND FULLITEMKEYDECODER.SUBCODE06 = STOCKTRANSACTION.DECOSUBCODE06
AND FULLITEMKEYDECODER.SUBCODE07 = STOCKTRANSACTION.DECOSUBCODE07
AND FULLITEMKEYDECODER.SUBCODE08 = STOCKTRANSACTION.DECOSUBCODE08
AND FULLITEMKEYDECODER.SUBCODE09 = STOCKTRANSACTION.DECOSUBCODE09
AND FULLITEMKEYDECODER.SUBCODE10 = STOCKTRANSACTION.DECOSUBCODE10
LEFT OUTER JOIN
PRODUCT
ON
PRODUCT.COMPANYCODE = FULLITEMKEYDECODER.COMPANYCODE
AND PRODUCT.ITEMTYPECODE = FULLITEMKEYDECODER.ITEMTYPECODE
AND PRODUCT.SUBCODE01 = FULLITEMKEYDECODER.SUBCODE01
AND PRODUCT.SUBCODE02 = FULLITEMKEYDECODER.SUBCODE02
AND
(
(
PRODUCT.SUBCODE03 = FULLITEMKEYDECODER.SUBCODE03)
OR
(
PRODUCT.SUBCODE03 = ''))
AND
(
(
PRODUCT.SUBCODE04 = FULLITEMKEYDECODER.SUBCODE04)
OR
(
PRODUCT.SUBCODE04 = ''))
AND
(
(
PRODUCT.SUBCODE05 = FULLITEMKEYDECODER.SUBCODE05)
OR
(
PRODUCT.SUBCODE05 = ''))
AND
(
(
PRODUCT.SUBCODE06 = FULLITEMKEYDECODER.SUBCODE06)
OR
(
PRODUCT.SUBCODE06 = ''))
AND
(
(
PRODUCT.SUBCODE07 = FULLITEMKEYDECODER.SUBCODE07)
OR
(
PRODUCT.SUBCODE07 = ''))
AND
(
(
PRODUCT.SUBCODE08 = FULLITEMKEYDECODER.SUBCODE08)
OR
(
PRODUCT.SUBCODE08 = ''))
AND
(
(
PRODUCT.SUBCODE09 = FULLITEMKEYDECODER.SUBCODE09)
OR
(
PRODUCT.SUBCODE09 = ''))
AND
(
(
PRODUCT.SUBCODE10 = FULLITEMKEYDECODER.SUBCODE10)
OR
(
PRODUCT.SUBCODE10 = ''))
LEFT OUTER JOIN
PROSTACCOUNTING PROSTACCOUNTING
ON
PROSTACCOUNTING.COMPANY = STOCKTRANSACTION.COMPANYCODE
AND PROSTACCOUNTING.STOCKTRANSACTION = STOCKTRANSACTION.TRANSACTIONNUMBER
AND PROSTACCOUNTING.TRANSACTIONDETAILNUMBER = STOCKTRANSACTION.TRANSACTIONDETAILNUMBER
AND PROSTACCOUNTING.LINETYPE = 'D'
LEFT OUTER JOIN
PROSTACCOUNTING PROSTACCOUNTING2
ON
PROSTACCOUNTING2.COMPANY = STOCKTRANSACTION.COMPANYCODE
AND PROSTACCOUNTING2.STOCKTRANSACTION = STOCKTRANSACTION.TRANSACTIONNUMBER
AND PROSTACCOUNTING2.TRANSACTIONDETAILNUMBER = STOCKTRANSACTION.TRANSACTIONDETAILNUMBER
AND PROSTACCOUNTING2.LINETYPE = 'C'
LEFT OUTER JOIN
ADSTORAGE ADSTORAGE
ON
ADSTORAGE.UNIQUEID = FULLITEMKEYDECODER.ABSUNIQUEID
AND ADSTORAGE.NAMEENTITYNAME = 'FullItemKeyDecoder'
AND ADSTORAGE.NAMENAME = '20900'
AND ADSTORAGE.FIELDNAME = '20900'
LEFT OUTER JOIN
PTIRVORDERPARTNER CUSTOMER
ON
CUSTOMER.CUSTOMERSUPPLIERCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND CUSTOMER.CUSTOMERSUPPLIERTYPE = STOCKTRANSACTION.CUSTOMERTYPE
AND CUSTOMER.CUSTOMERSUPPLIERCODE = STOCKTRANSACTION.CUSTOMERCODE
LEFT OUTER JOIN
PTIRVORDERPARTNER SUPPLIER
ON
SUPPLIER.CUSTOMERSUPPLIERCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND SUPPLIER.CUSTOMERSUPPLIERTYPE = STOCKTRANSACTION.SUPPLIERTYPE
AND SUPPLIER.CUSTOMERSUPPLIERCODE = STOCKTRANSACTION.SUPPLIERCODE
LEFT OUTER JOIN
( SELECT
AVLWAREHOUSEGROUPDETAIL.OWNINGCOMPANYCODE OWNINGCOMPANYCODE,
AVLWAREHOUSEGROUPDETAIL.LOGICALWAREHOUSECODE LOGICALWAREHOUSECODE,
AVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE AVAILABILITYWAREHOUSEGROUPCODE
FROM
AVLWAREHOUSEGROUPDETAIL AVLWAREHOUSEGROUPDETAIL
INNER JOIN
AVAILABILITYWAREHOUSEGROUP AVAILABILITYWAREHOUSEGROUP
ON
AVAILABILITYWAREHOUSEGROUP.OWNINGCOMPANYCODE =
AVLWAREHOUSEGROUPDETAIL.OWNINGCOMPANYCODE
AND AVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE =
AVAILABILITYWAREHOUSEGROUP.CODE
AND AVAILABILITYWAREHOUSEGROUP.LONGDESCRIPTION LIKE '**%' ) TABAVLWAREHOUSEGROUPDETAIL
ON
TABAVLWAREHOUSEGROUPDETAIL.OWNINGCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND TABAVLWAREHOUSEGROUPDETAIL.LOGICALWAREHOUSECODE = STOCKTRANSACTION.LOGICALWAREHOUSECODE
LEFT OUTER JOIN
STOCKTRANSACTION STOCKTRANSACTION1
ON
STOCKTRANSACTION1.COMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND STOCKTRANSACTION1.TRANSACTIONDATE = STOCKTRANSACTION.TRANSACTIONDATE
AND STOCKTRANSACTION1.TRANSACTIONNUMBER = STOCKTRANSACTION.TRANSACTIONNUMBER
AND STOCKTRANSACTION1.ITEMTYPECODE = STOCKTRANSACTION.ITEMTYPECODE
AND STOCKTRANSACTION1.DECOSUBCODE01 = STOCKTRANSACTION.DECOSUBCODE01
AND STOCKTRANSACTION1.DECOSUBCODE02 = STOCKTRANSACTION.DECOSUBCODE02
AND STOCKTRANSACTION1.DECOSUBCODE03 = STOCKTRANSACTION.DECOSUBCODE03
AND STOCKTRANSACTION1.DECOSUBCODE04 = STOCKTRANSACTION.DECOSUBCODE04
AND STOCKTRANSACTION1.DECOSUBCODE05 = STOCKTRANSACTION.DECOSUBCODE05
AND STOCKTRANSACTION1.DECOSUBCODE06 = STOCKTRANSACTION.DECOSUBCODE06
AND STOCKTRANSACTION1.DECOSUBCODE07 = STOCKTRANSACTION.DECOSUBCODE07
AND STOCKTRANSACTION1.DECOSUBCODE08 = STOCKTRANSACTION.DECOSUBCODE08
AND STOCKTRANSACTION1.DECOSUBCODE09 = STOCKTRANSACTION.DECOSUBCODE09
AND STOCKTRANSACTION1.DECOSUBCODE10 = STOCKTRANSACTION.DECOSUBCODE10
AND STOCKTRANSACTION1.FULLITEMIDENTIFIER = STOCKTRANSACTION.FULLITEMIDENTIFIER
AND STOCKTRANSACTION1.DETAILTYPE =
CASE STOCKTRANSACTION.DETAILTYPE
WHEN '1'
THEN '2'
ELSE '1'
END
AND STOCKTRANSACTION1.ABSUNIQUEID =
( SELECT
CASE STOCKTRANSACTION.DETAILTYPE
WHEN '1'
THEN MIN(STOCKTRANSACTION2.ABSUNIQUEID)
ELSE MAX(STOCKTRANSACTION2.ABSUNIQUEID)
END
FROM
STOCKTRANSACTION STOCKTRANSACTION2
WHERE
STOCKTRANSACTION2.COMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND STOCKTRANSACTION2.TRANSACTIONDATE = STOCKTRANSACTION.TRANSACTIONDATE
AND STOCKTRANSACTION2.TRANSACTIONNUMBER = STOCKTRANSACTION.TRANSACTIONNUMBER
AND STOCKTRANSACTION2.ITEMTYPECODE = STOCKTRANSACTION.ITEMTYPECODE
AND STOCKTRANSACTION2.DECOSUBCODE01 = STOCKTRANSACTION.DECOSUBCODE01
AND STOCKTRANSACTION2.DECOSUBCODE02 = STOCKTRANSACTION.DECOSUBCODE02
AND STOCKTRANSACTION2.DECOSUBCODE03 = STOCKTRANSACTION.DECOSUBCODE03
AND STOCKTRANSACTION2.DECOSUBCODE04 = STOCKTRANSACTION.DECOSUBCODE04
AND STOCKTRANSACTION2.DECOSUBCODE05 = STOCKTRANSACTION.DECOSUBCODE05
AND STOCKTRANSACTION2.DECOSUBCODE06 = STOCKTRANSACTION.DECOSUBCODE06
AND STOCKTRANSACTION2.DECOSUBCODE07 = STOCKTRANSACTION.DECOSUBCODE07
AND STOCKTRANSACTION2.DECOSUBCODE08 = STOCKTRANSACTION.DECOSUBCODE08
AND STOCKTRANSACTION2.DECOSUBCODE09 = STOCKTRANSACTION.DECOSUBCODE09
AND STOCKTRANSACTION2.DECOSUBCODE10 = STOCKTRANSACTION.DECOSUBCODE10
AND STOCKTRANSACTION2.FULLITEMIDENTIFIER = STOCKTRANSACTION.FULLITEMIDENTIFIER
AND STOCKTRANSACTION2.DETAILTYPE =
CASE STOCKTRANSACTION.DETAILTYPE
WHEN '1'
THEN '2'
ELSE '1'
END
AND
(
(
(
STOCKTRANSACTION2.LOTCODE = STOCKTRANSACTION.LOTCODE
OR NVL(STOCKTRANSACTION2.LOTCODE,'')= '' )
AND
(
STOCKTRANSACTION2.ITEMELEMENTCODE = STOCKTRANSACTION.ITEMELEMENTCODE
OR NVL(STOCKTRANSACTION2.ITEMELEMENTCODE,'')= '' ))
OR STOCKTRANSACTION.DETAILTYPE = '2' )
AND
(
(
(
STOCKTRANSACTION2.LOTCODE = STOCKTRANSACTION.LOTCODE
OR NVL(STOCKTRANSACTION.LOTCODE,'')= '' )
AND
(
STOCKTRANSACTION2.ITEMELEMENTCODE = STOCKTRANSACTION.ITEMELEMENTCODE
OR NVL(STOCKTRANSACTION.ITEMELEMENTCODE,'')= '' ))
OR STOCKTRANSACTION.DETAILTYPE = '1')
AND
(
(
STOCKTRANSACTION2.ABSUNIQUEID > STOCKTRANSACTION.ABSUNIQUEID
OR STOCKTRANSACTION.DETAILTYPE = '2')
AND
(
STOCKTRANSACTION2.ABSUNIQUEID < STOCKTRANSACTION.ABSUNIQUEID
OR STOCKTRANSACTION.DETAILTYPE = '1')) )
LEFT OUTER JOIN
LOGICALWAREHOUSE LOGICALWAREHOUSE1
ON
LOGICALWAREHOUSE1.OWNINGCOMPANYCODE = STOCKTRANSACTION1.COMPANYCODE
AND LOGICALWAREHOUSE1.COMPANYCODE = STOCKTRANSACTION1.LOGICALWAREHOUSECOMPANYCODE
AND LOGICALWAREHOUSE1.CODE = STOCKTRANSACTION1.LOGICALWAREHOUSECODE
LEFT OUTER JOIN
( SELECT
AVLWAREHOUSEGROUPDETAIL.OWNINGCOMPANYCODE OWNINGCOMPANYCODE,
AVLWAREHOUSEGROUPDETAIL.LOGICALWAREHOUSECODE LOGICALWAREHOUSECODE,
AVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE AVAILABILITYWAREHOUSEGROUPCODE
FROM
AVLWAREHOUSEGROUPDETAIL AVLWAREHOUSEGROUPDETAIL
INNER JOIN
AVAILABILITYWAREHOUSEGROUP AVAILABILITYWAREHOUSEGROUP
ON
AVAILABILITYWAREHOUSEGROUP.OWNINGCOMPANYCODE =
AVLWAREHOUSEGROUPDETAIL.OWNINGCOMPANYCODE
AND AVLWAREHOUSEGROUPDETAIL.AVAILABILITYWAREHOUSEGROUPCODE =
AVAILABILITYWAREHOUSEGROUP.CODE
AND AVAILABILITYWAREHOUSEGROUP.LONGDESCRIPTION LIKE '**%' ) TABAVLWAREHOUSEGROUPDETAIL2
ON
TABAVLWAREHOUSEGROUPDETAIL2.OWNINGCOMPANYCODE = STOCKTRANSACTION1.COMPANYCODE
AND TABAVLWAREHOUSEGROUPDETAIL2.LOGICALWAREHOUSECODE = STOCKTRANSACTION1.LOGICALWAREHOUSECODE
LEFT OUTER JOIN
( SELECT
DISTINCT PROLCCDOCLINEPROLCCDOCCMYCODE,
PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCNTCODE,
PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCODE,
PROLCCDOCLINETRANSACTIONS.TRANSACTIONNUMBER
FROM
PROLCCDOCUMENT
INNER JOIN
PROLCCDOCLINETRANSACTIONS
ON
PROLCCDOCUMENT.COMPANYCODE = PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCMYCODE
AND PROLCCDOCUMENT.COUNTERCODE = PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCNTCODE
AND PROLCCDOCUMENT.CODE = PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCODE
WHERE
PROLCCDOCUMENT.PROGRESSSTATUS IN('0', '2') ) PROLCCDOCLINETRANSACTIONS
ON
PROLCCDOCLINETRANSACTIONS.PROLCCDOCLINEPROLCCDOCCMYCODE = STOCKTRANSACTION.COMPANYCODE
AND PROLCCDOCLINETRANSACTIONS.TRANSACTIONNUMBER = STOCKTRANSACTION.TRANSACTIONNUMBER
LEFT OUTER JOIN
INTERNALDOCUMENTLINE
ON
INTERNALDOCUMENTLINE.INTERNALDOCUMENTCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND INTERNALDOCUMENTLINE.INTDOCPROVISIONALCOUNTERCODE = STOCKTRANSACTION.ORDERCOUNTERCODE
AND INTERNALDOCUMENTLINE.INTDOCUMENTPROVISIONALCODE = STOCKTRANSACTION.ORDERCODE
AND INTERNALDOCUMENTLINE.ORDERLINE = STOCKTRANSACTION.ORDERLINE
LEFT OUTER JOIN
INTERNALDOCUMENT
ON
INTERNALDOCUMENT.COMPANYCODE = INTERNALDOCUMENTLINE.INTERNALDOCUMENTCOMPANYCODE
AND INTERNALDOCUMENT.PROVISIONALCOUNTERCODE = INTERNALDOCUMENTLINE.INTDOCPROVISIONALCOUNTERCODE
AND INTERNALDOCUMENT.PROVISIONALCODE = INTERNALDOCUMENTLINE.INTDOCUMENTPROVISIONALCODE
LEFT OUTER JOIN
COSTCENTER
ON
COSTCENTER.OWNINGCOMPANYCODE = STOCKTRANSACTION.COMPANYCODE
AND COSTCENTER.CODE = (
CASE
WHEN STOCKTRANSACTION.COSTCENTERCODE IS NULL
THEN INTERNALDOCUMENTLINE.COSTCENTERCODE
ELSE STOCKTRANSACTION.COSTCENTERCODE
END )
LEFT OUTER JOIN
ADSTORAGE ADSTORAGEMQ
ON
ADSTORAGEMQ.UNIQUEID = INTERNALDOCUMENT.ABSUNIQUEID
AND ADSTORAGEMQ.NAMEENTITYNAME = 'InternalDocument'
AND ADSTORAGEMQ.NAMENAME = 'PMMachine'
AND ADSTORAGEMQ.FIELDNAME = 'PMMachineCode';
