VIEW: APPS.OKL_AM_QTE_UNITS_UV

Source

View Text - Preformatted

SELECT qte.quote_number quote_number, qte.qst_code qst_code, qte.qtp_code qtp_code, tql.id tql_id, to_number(null) tqd_id, tql.asset_quantity asset_quantity, tql.quote_quantity quote_quantity, cle_ib.id ib_line_id, cle_fin.id fin_line_id, cle_fin.dnz_chr_id dnz_chr_id, csi.serial_number, csi.quantity instance_quantity, csi.instance_number, clet_fin.name asset_number, clet_fin.item_description asset_description, substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) location_description, qte.id qte_id FROM HZ_LOCATIONS HL,HZ_PARTY_SITES HPS,HZ_PARTY_SITE_USES HPSU,CSI_ITEM_INSTANCES CSI,OKC_K_ITEMS CIM_IB,OKC_LINE_STYLES_B LSE_IB,OKC_K_LINES_B CLE_IB,OKC_LINE_STYLES_B LSE_INST,OKC_K_LINES_B CLE_INST,OKC_LINE_STYLES_B LSE_FIN,OKL_TXL_QTE_LINES_ALL_B TQL,OKL_TRX_QUOTES_B QTE,OKC_K_LINES_TL CLET_FIN,OKC_K_LINES_B CLE_FIN WHERE cle_fin.cle_id is null and cle_fin.chr_id = cle_fin.dnz_chr_id and cle_fin.id = clet_fin.id and clet_fin.language = userenv('LANG') and cle_fin.id = tql.kle_id and tql.qlt_code = 'AMCFIA' and lse_fin.id = cle_fin.lse_id and lse_fin.lty_code = 'FREE_FORM1' and cle_inst.cle_id = cle_fin.id and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id and cle_inst.lse_id = lse_inst.id and lse_inst.lty_code = 'FREE_FORM2' and cle_ib.cle_id = cle_inst.id and cle_ib.dnz_chr_id = cle_fin.dnz_chr_id and cle_ib.lse_id = lse_ib.id and lse_ib.lty_code = 'INST_ITEM' and cim_ib.cle_id = cle_ib.id and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id and cim_ib.object1_id1 = csi.instance_id and cim_ib.object1_id2 = '#' and cim_ib.jtot_object1_code = 'OKX_IB_ITEM' and csi.install_location_id = hpsu.party_site_id and hpsu.site_use_type = 'INSTALL_AT' and hpsu.party_site_id = hps.party_site_id and hps.location_id = hl.location_id and tql.qte_id = qte.id and not exists (select '1' from okl_txd_quote_line_dtls tqd where tqd.kle_id = cle_ib.id and tqd.tql_id = tql.id) union SELECT qte.quote_number quote_number, qte.qst_code qst_code, qte.qtp_code qtp_code, tql.id tql_id, tqd.id tqd_id, tql.asset_quantity asset_quantity, tql.quote_quantity quote_quantity, cle_ib.id ib_line_id, cle_fin.id fin_line_id, cle_fin.dnz_chr_id dnz_chr_id, csi.serial_number, csi.quantity instance_quantity, csi.instance_number, clet_fin.name asset_number, clet_fin.item_description asset_description, substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) location_description, qte.id qte_id FROM HZ_LOCATIONS HL, HZ_PARTY_SITES HPS, HZ_PARTY_SITE_USES HPSU, CSI_ITEM_INSTANCES CSI, OKC_K_ITEMS CIM_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_LINES_B CLE_IB, OKC_LINE_STYLES_B LSE_INST, OKC_K_LINES_B CLE_INST, OKC_LINE_STYLES_B LSE_FIN, OKL_TXD_QUOTE_LINE_DTLS TQD, OKL_TXL_QUOTE_LINES_B TQL, OKL_TRX_QUOTES_B QTE, OKC_K_LINES_TL CLET_FIN, OKC_K_LINES_B CLE_FIN WHERE cle_fin.cle_id is null and cle_fin.chr_id = cle_fin.dnz_chr_id and cle_fin.id = clet_fin.id and clet_fin.language = userenv('LANG') and cle_fin.id = tql.kle_id and tql.qlt_code = 'AMCFIA' and tqd.tql_id = tql.id and tqd.kle_id = cle_ib.id and lse_fin.id = cle_fin.lse_id and lse_fin.lty_code = 'FREE_FORM1' and cle_inst.cle_id = cle_fin.id and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id and cle_inst.lse_id = lse_inst.id and lse_inst.lty_code = 'FREE_FORM2' and cle_ib.cle_id = cle_inst.id and cle_ib.dnz_chr_id = cle_fin.dnz_chr_id and cle_ib.lse_id = lse_ib.id and lse_ib.lty_code = 'INST_ITEM' and cim_ib.cle_id = cle_ib.id and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id and cim_ib.object1_id1 = csi.instance_id and cim_ib.object1_id2 = '#' and cim_ib.jtot_object1_code = 'OKX_IB_ITEM' and csi.install_location_id = hpsu.party_site_id and hpsu.site_use_type = 'INSTALL_AT' and hpsu.party_site_id = hps.party_site_id and hps.location_id = hl.location_id and tql.qte_id = qte.id

View Text - HTML Formatted

SELECT QTE.QUOTE_NUMBER QUOTE_NUMBER
, QTE.QST_CODE QST_CODE
, QTE.QTP_CODE QTP_CODE
, TQL.ID TQL_ID
, TO_NUMBER(NULL) TQD_ID
, TQL.ASSET_QUANTITY ASSET_QUANTITY
, TQL.QUOTE_QUANTITY QUOTE_QUANTITY
, CLE_IB.ID IB_LINE_ID
, CLE_FIN.ID FIN_LINE_ID
, CLE_FIN.DNZ_CHR_ID DNZ_CHR_ID
, CSI.SERIAL_NUMBER
, CSI.QUANTITY INSTANCE_QUANTITY
, CSI.INSTANCE_NUMBER
, CLET_FIN.NAME ASSET_NUMBER
, CLET_FIN.ITEM_DESCRIPTION ASSET_DESCRIPTION
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.COUNTY
, HL.STATE
, HL.PROVINCE
, HL.POSTAL_CODE
, NULL
, HL.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) LOCATION_DESCRIPTION
, QTE.ID QTE_ID
FROM HZ_LOCATIONS HL
, HZ_PARTY_SITES HPS
, HZ_PARTY_SITE_USES HPSU
, CSI_ITEM_INSTANCES CSI
, OKC_K_ITEMS CIM_IB
, OKC_LINE_STYLES_B LSE_IB
, OKC_K_LINES_B CLE_IB
, OKC_LINE_STYLES_B LSE_INST
, OKC_K_LINES_B CLE_INST
, OKC_LINE_STYLES_B LSE_FIN
, OKL_TXL_QTE_LINES_ALL_B TQL
, OKL_TRX_QUOTES_B QTE
, OKC_K_LINES_TL CLET_FIN
, OKC_K_LINES_B CLE_FIN
WHERE CLE_FIN.CLE_ID IS NULL
AND CLE_FIN.CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_FIN.ID = CLET_FIN.ID
AND CLET_FIN.LANGUAGE = USERENV('LANG')
AND CLE_FIN.ID = TQL.KLE_ID
AND TQL.QLT_CODE = 'AMCFIA'
AND LSE_FIN.ID = CLE_FIN.LSE_ID
AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
AND CLE_INST.CLE_ID = CLE_FIN.ID
AND CLE_INST.DNZ_CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_INST.LSE_ID = LSE_INST.ID
AND LSE_INST.LTY_CODE = 'FREE_FORM2'
AND CLE_IB.CLE_ID = CLE_INST.ID
AND CLE_IB.DNZ_CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_IB.LSE_ID = LSE_IB.ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND CIM_IB.CLE_ID = CLE_IB.ID
AND CIM_IB.DNZ_CHR_ID = CLE_IB.DNZ_CHR_ID
AND CIM_IB.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM_IB.OBJECT1_ID2 = '#'
AND CIM_IB.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = 'INSTALL_AT'
AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND TQL.QTE_ID = QTE.ID
AND NOT EXISTS (SELECT '1'
FROM OKL_TXD_QUOTE_LINE_DTLS TQD
WHERE TQD.KLE_ID = CLE_IB.ID
AND TQD.TQL_ID = TQL.ID) UNION SELECT QTE.QUOTE_NUMBER QUOTE_NUMBER
, QTE.QST_CODE QST_CODE
, QTE.QTP_CODE QTP_CODE
, TQL.ID TQL_ID
, TQD.ID TQD_ID
, TQL.ASSET_QUANTITY ASSET_QUANTITY
, TQL.QUOTE_QUANTITY QUOTE_QUANTITY
, CLE_IB.ID IB_LINE_ID
, CLE_FIN.ID FIN_LINE_ID
, CLE_FIN.DNZ_CHR_ID DNZ_CHR_ID
, CSI.SERIAL_NUMBER
, CSI.QUANTITY INSTANCE_QUANTITY
, CSI.INSTANCE_NUMBER
, CLET_FIN.NAME ASSET_NUMBER
, CLET_FIN.ITEM_DESCRIPTION ASSET_DESCRIPTION
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.COUNTY
, HL.STATE
, HL.PROVINCE
, HL.POSTAL_CODE
, NULL
, HL.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) LOCATION_DESCRIPTION
, QTE.ID QTE_ID
FROM HZ_LOCATIONS HL
, HZ_PARTY_SITES HPS
, HZ_PARTY_SITE_USES HPSU
, CSI_ITEM_INSTANCES CSI
, OKC_K_ITEMS CIM_IB
, OKC_LINE_STYLES_B LSE_IB
, OKC_K_LINES_B CLE_IB
, OKC_LINE_STYLES_B LSE_INST
, OKC_K_LINES_B CLE_INST
, OKC_LINE_STYLES_B LSE_FIN
, OKL_TXD_QUOTE_LINE_DTLS TQD
, OKL_TXL_QUOTE_LINES_B TQL
, OKL_TRX_QUOTES_B QTE
, OKC_K_LINES_TL CLET_FIN
, OKC_K_LINES_B CLE_FIN
WHERE CLE_FIN.CLE_ID IS NULL
AND CLE_FIN.CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_FIN.ID = CLET_FIN.ID
AND CLET_FIN.LANGUAGE = USERENV('LANG')
AND CLE_FIN.ID = TQL.KLE_ID
AND TQL.QLT_CODE = 'AMCFIA'
AND TQD.TQL_ID = TQL.ID
AND TQD.KLE_ID = CLE_IB.ID
AND LSE_FIN.ID = CLE_FIN.LSE_ID
AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
AND CLE_INST.CLE_ID = CLE_FIN.ID
AND CLE_INST.DNZ_CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_INST.LSE_ID = LSE_INST.ID
AND LSE_INST.LTY_CODE = 'FREE_FORM2'
AND CLE_IB.CLE_ID = CLE_INST.ID
AND CLE_IB.DNZ_CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_IB.LSE_ID = LSE_IB.ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND CIM_IB.CLE_ID = CLE_IB.ID
AND CIM_IB.DNZ_CHR_ID = CLE_IB.DNZ_CHR_ID
AND CIM_IB.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM_IB.OBJECT1_ID2 = '#'
AND CIM_IB.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = 'INSTALL_AT'
AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND TQL.QTE_ID = QTE.ID