VIEW: APPS.OKL_CS_ASSET_LOC_CHANGE_UV

Source

View Text - Preformatted

SELECT TRX.ID, TXL.KLE_ID, TXL.DNZ_CLE_ID, TXL.OBJECT_ID1_OLD, TXL.OBJECT_ID2_OLD, TXL.OBJECT_ID1_NEW, TXL.OBJECT_ID2_NEW, TRX. DATE_TRANS_OCCURRED DATE_EFFECTIVE, substr(arp_addr_label_pkg.format_address(null,hzo.address1,hzo.address2,hzo.address3, hzo.address4,hzo.city,hzo.county,hzo.state,hzo.province,hzo.postal_code,null,hzo.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) OLD_LOCATION, substr(arp_addr_label_pkg.format_address(null,hzn.address1,hzn.address2,hzn.address3, hzn.address4,hzn.city,hzn.county,hzn.state,hzn.province,hzn.postal_code,null,hzn.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) NEW_LOCATION, sum(txs.total_Tax) TAX_AMOUNT, chr.currency_code, trx.tsu_code, flk.meaning STATUS , TRX.REQ_ASSET_ID, csi.serial_number, sum(txs.line_amt) line_amt FROM OKL_TRX_ASSETS TRX, OKL_TXL_ITM_INSTS TXL, OKL_TRX_TYPES_V TRY, HZ_PARTY_SITE_USES psuo, HZ_PARTY_SITE_USES psun, hz_party_sites pso, hz_party_sites psn, hz_locations hzo, hz_locations hzn, OKC_K_LINES_B CLE, OKC_K_HEADERS_B CHR, fnd_lookups flk , OKL_TAX_SOURCES TXS, CSI_ITEM_INSTANCES CSI, OKC_K_ITEMS CIM WHERE TRX.ID = TXL.TAS_ID AND TRX.TRY_ID = TRY.ID and try.id = (select s.id from okl_trx_types_tl s where s.name= 'Asset Relocation') AND TXL.OBJECT_ID1_OLD = psuo.PARTY_SITE_USE_ID AND TXL.OBJECT_ID1_NEW = psun.PARTY_SITE_USE_ID AND psuo.party_site_id = pso.party_site_id AND psun.party_site_id = psn.party_site_id AND pso.location_id = hzo.location_id AND psn.location_id = hzn.location_id AND TXL.KLE_ID = CLE.ID AND CLE.DNZ_CHR_ID = CHR.ID AND CIM.CLE_ID = cle.Id AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID AND CIM.OBJECT1_ID2 = '#' AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM' and flk.lookup_code = trx.tsu_code and flk.lookup_type = 'OKL_REQUEST_STATUS' AND TXS.TRX_ID(+) = TRX.ID AND TXS.TAX_CALL_TYPE_CODE(+) = 'UPFRONT_TAX' AND TXS.ENTITY_CODE(+) = 'ASSETS' AND TXS.EVENT_CLASS_CODE(+) = 'ASSET_RELOCATION' AND TXS.TRX_LEVEL_TYPE(+) = 'LINE' AND TXS.APPLICATION_ID(+) = 540 group by TRX.ID, TXL.KLE_ID, TXL.DNZ_CLE_ID, TXL.OBJECT_ID1_OLD, TXL.OBJECT_ID2_OLD, TXL.OBJECT_ID1_NEW, TXL.OBJECT_ID2_NEW, TRX. DATE_TRANS_OCCURRED , substr(arp_addr_label_pkg.format_address(null,hzo.address1,hzo.address2,hzo.address3, hzo.address4,hzo.city,hzo.county,hzo.state,hzo.province,hzo.postal_code,null,hzo.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) , substr(arp_addr_label_pkg.format_address(null,hzn.address1,hzn.address2,hzn.address3, hzn.address4,hzn.city,hzn.county,hzn.state,hzn.province,hzn.postal_code,null,hzn.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) , chr.currency_code, trx.tsu_code, flk.meaning , TRX.REQ_ASSET_ID, csi.serial_number order by TRX. DATE_TRANS_OCCURRED DESC

View Text - HTML Formatted

SELECT TRX.ID
, TXL.KLE_ID
, TXL.DNZ_CLE_ID
, TXL.OBJECT_ID1_OLD
, TXL.OBJECT_ID2_OLD
, TXL.OBJECT_ID1_NEW
, TXL.OBJECT_ID2_NEW
, TRX. DATE_TRANS_OCCURRED DATE_EFFECTIVE
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HZO.ADDRESS1
, HZO.ADDRESS2
, HZO.ADDRESS3
, HZO.ADDRESS4
, HZO.CITY
, HZO.COUNTY
, HZO.STATE
, HZO.PROVINCE
, HZO.POSTAL_CODE
, NULL
, HZO.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) OLD_LOCATION
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HZN.ADDRESS1
, HZN.ADDRESS2
, HZN.ADDRESS3
, HZN.ADDRESS4
, HZN.CITY
, HZN.COUNTY
, HZN.STATE
, HZN.PROVINCE
, HZN.POSTAL_CODE
, NULL
, HZN.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) NEW_LOCATION
, SUM(TXS.TOTAL_TAX) TAX_AMOUNT
, CHR.CURRENCY_CODE
, TRX.TSU_CODE
, FLK.MEANING STATUS
, TRX.REQ_ASSET_ID
, CSI.SERIAL_NUMBER
, SUM(TXS.LINE_AMT) LINE_AMT
FROM OKL_TRX_ASSETS TRX
, OKL_TXL_ITM_INSTS TXL
, OKL_TRX_TYPES_V TRY
, HZ_PARTY_SITE_USES PSUO
, HZ_PARTY_SITE_USES PSUN
, HZ_PARTY_SITES PSO
, HZ_PARTY_SITES PSN
, HZ_LOCATIONS HZO
, HZ_LOCATIONS HZN
, OKC_K_LINES_B CLE
, OKC_K_HEADERS_B CHR
, FND_LOOKUPS FLK
, OKL_TAX_SOURCES TXS
, CSI_ITEM_INSTANCES CSI
, OKC_K_ITEMS CIM
WHERE TRX.ID = TXL.TAS_ID
AND TRX.TRY_ID = TRY.ID
AND TRY.ID = (SELECT S.ID
FROM OKL_TRX_TYPES_TL S
WHERE S.NAME= 'ASSET RELOCATION')
AND TXL.OBJECT_ID1_OLD = PSUO.PARTY_SITE_USE_ID
AND TXL.OBJECT_ID1_NEW = PSUN.PARTY_SITE_USE_ID
AND PSUO.PARTY_SITE_ID = PSO.PARTY_SITE_ID
AND PSUN.PARTY_SITE_ID = PSN.PARTY_SITE_ID
AND PSO.LOCATION_ID = HZO.LOCATION_ID
AND PSN.LOCATION_ID = HZN.LOCATION_ID
AND TXL.KLE_ID = CLE.ID
AND CLE.DNZ_CHR_ID = CHR.ID
AND CIM.CLE_ID = CLE.ID
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM.OBJECT1_ID2 = '#'
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND FLK.LOOKUP_CODE = TRX.TSU_CODE
AND FLK.LOOKUP_TYPE = 'OKL_REQUEST_STATUS'
AND TXS.TRX_ID(+) = TRX.ID
AND TXS.TAX_CALL_TYPE_CODE(+) = 'UPFRONT_TAX'
AND TXS.ENTITY_CODE(+) = 'ASSETS'
AND TXS.EVENT_CLASS_CODE(+) = 'ASSET_RELOCATION'
AND TXS.TRX_LEVEL_TYPE(+) = 'LINE'
AND TXS.APPLICATION_ID(+) = 540 GROUP BY TRX.ID
, TXL.KLE_ID
, TXL.DNZ_CLE_ID
, TXL.OBJECT_ID1_OLD
, TXL.OBJECT_ID2_OLD
, TXL.OBJECT_ID1_NEW
, TXL.OBJECT_ID2_NEW
, TRX. DATE_TRANS_OCCURRED
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HZO.ADDRESS1
, HZO.ADDRESS2
, HZO.ADDRESS3
, HZO.ADDRESS4
, HZO.CITY
, HZO.COUNTY
, HZO.STATE
, HZO.PROVINCE
, HZO.POSTAL_CODE
, NULL
, HZO.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80)
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HZN.ADDRESS1
, HZN.ADDRESS2
, HZN.ADDRESS3
, HZN.ADDRESS4
, HZN.CITY
, HZN.COUNTY
, HZN.STATE
, HZN.PROVINCE
, HZN.POSTAL_CODE
, NULL
, HZN.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80)
, CHR.CURRENCY_CODE
, TRX.TSU_CODE
, FLK.MEANING
, TRX.REQ_ASSET_ID
, CSI.SERIAL_NUMBER ORDER BY TRX. DATE_TRANS_OCCURRED DESC