[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(sum(lines.price_negotiated),0)
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_chr_id
AND lines.cle_id IS NULL;
SELECT nvl(shdr.tax_amount,0) AS tax_amount
FROM oks_k_headers_b shdr
WHERE shdr.chr_id = p_chr_id;
SELECT
NVL(SUM(btl.trx_amount), 0) + NVL(SUM(btl.trx_line_tax_amount), 0) trx_amount
FROM
oks_bill_transactions btr
, oks_bill_txn_lines btl
, oks_bill_cont_lines bcl
WHERE
bcl.cle_id = l_line_id
and btr.ID = bcl.BTN_ID
and btl.btn_id = btr.id
and btl.BCL_ID = bcl.id
AND bcl.bill_action = 'RI'
and (btl.trx_number <> -99 OR btr.trx_number <> -99)
GROUP BY bcl.cle_id
UNION
SELECT
(nvl (sum (decode(raTrxLineSelect.r, 1, lineamt, 0)), 0) + nvl (sum (taxamt), 0)) trx_amount
FROM
(
select uniqueOrderLineSelect.id,
ra.trx_number,
ra.trx_date,
ratax.extended_amount taxamt,
ral.extended_amount lineamt,
ral.customer_trx_id,
uniqueOrderLineSelect.bill_action,
uniqueOrderLineSelect.bill_from_date,
uniqueOrderLineSelect.bill_to_date,
rank() over (partition by ral.customer_trx_line_id order by ratax.customer_trx_line_id) r
from (
select /*+ no_merge */ distinct rel.object1_id1, subline.cle_id id,
bcl.bill_action,
bcl.date_billed_from bill_from_date,
bcl.date_billed_to bill_to_date
from okc_k_rel_objs rel,
okc_k_lines_b subline,
oks_bill_cont_lines bcl
where subline.cle_id = l_line_id
and subline.lse_id in (9,18,25)
and subline.cle_id = bcl.cle_id
AND bcl.btn_id = - 44
AND bcl.bill_action = 'RI'
and subline.id = rel.cle_id
and rel.jtot_object1_code = 'OKX_ORDERLINE') uniqueOrderLineSelect,
oe_order_lines_all oel,
oe_order_headers_all oe,
ra_customer_trx_lines_all ral,
ra_customer_trx_all ra,
ra_customer_trx_lines_all ratax
where
uniqueOrderLineSelect.object1_id1 = oel.line_id
and oel.header_id = oe.header_id
and to_char (oe.order_number) = ral.sales_order
and to_char (oel.line_id) = ral.interface_line_attribute6
and ral.customer_trx_id = ra.customer_trx_id
and ratax.link_to_cust_trx_line_id (+) = ral.customer_trx_line_id
and ratax.line_type (+) = 'TAX') raTrxLineSelect
GROUP BY raTrxLineSelect.id;
SELECT
NVL(SUM(btl.trx_amount), 0) + NVL(SUM(btl.trx_line_tax_amount), 0) trx_amount
FROM
oks_bill_txn_lines btl
, oks_bill_cont_lines bcl
, oks_bill_sub_lines bsl
, oks_bill_transactions btr
WHERE
bsl.cle_id = l_line_id
AND btl.BSL_ID = bsl.id
AND btl.btn_id = btr.id
AND bcl.id = bsl.bcl_id
AND bcl.BTN_ID = btr.ID
AND bcl.bill_action = 'RI'
AND (btl.trx_number <> -99 OR btr.trx_number <> -99)
GROUP BY bsl.cle_id
UNION
SELECT (nvl (sum (decode(raTrxLineSelect.r, 1, lineamt, 0)), 0) + nvl (sum (taxamt), 0)) trx_amount
FROM
(
select subline.cle_id id,
ra.trx_number,
ra.trx_date,
ratax.extended_amount taxamt,
ral.extended_amount lineamt,
ral.customer_trx_id,
rank() over (partition by ral.customer_trx_line_id order by ratax.customer_trx_line_id) r
from
okc_k_lines_b subline,
okc_k_rel_objs rel,
oe_order_lines_all oel,
oe_order_headers_all oe,
ra_customer_trx_lines_all ral,
ra_customer_trx_all ra,
ra_customer_trx_lines_all ratax
where subline.id = l_line_id
and subline.lse_id in (9,18,25)
and subline.id = rel.cle_id
and rel.jtot_object1_code = 'OKX_ORDERLINE'
and rel.object1_id1 = oel.line_id
and oel.header_id = oe.header_id
and to_char (oe.order_number) = ral.sales_order
and to_char (oel.line_id) = ral.interface_line_attribute6
and ral.customer_trx_id = ra.customer_trx_id
and ratax.link_to_cust_trx_line_id (+) = ral.customer_trx_line_id
and ratax.line_type (+) = 'TAX') raTrxLineSelect,
oks_bill_cont_lines bcl
WHERE raTrxLineSelect.id = bcl.cle_id
AND bcl.btn_id = - 44
AND bcl.bill_action = 'RI'
GROUP BY raTrxLineSelect.id;
SELECT
(
NVL(KLINES.price_negotiated, 0)
+
NVL(KSLINES.credit_amount, 0)
+
NVL(KSLINES.suppressed_credit, 0)
)
-
(NVL(OKS_BILLED_LINES.BILLED_AMOUNT,0) + NVL(OM_ORIGINATED_BILLED_LINES.BILLED_AMOUNT,0)) unbilled_amount
FROM
OKC_K_LINES_B KLINES
,OKS_K_LINES_B KSLINES
,
(
SELECT
NVL(SUM(btl.trx_amount), 0) BILLED_AMOUNT
FROM
oks_bill_transactions btr
,oks_bill_txn_lines btl
,OKS_BILL_CONT_LINES BCL
WHERE
BCL.cle_id = l_line_id
AND BCL.bill_action = 'RI'
AND btr.ID = bcl.BTN_ID
AND btl.btn_id = btr.id
AND btl.BCL_ID = bcl.id
AND (btl.trx_number <> -99 OR btr.trx_number <> -99)
) OKS_BILLED_LINES
,
(
select NVL(SUM(ral.extended_amount), 0) BILLED_AMOUNT
from (
select /*+ no_merge */ distinct rel.object1_id1
from okc_k_rel_objs rel,
okc_k_lines_b subline,
oks_bill_cont_lines bcl
where subline.cle_id = l_line_id
and subline.lse_id in (9,18,25)
and subline.cle_id = bcl.cle_id
AND bcl.btn_id = - 44
AND bcl.bill_action = 'RI'
and subline.id = rel.cle_id
and rel.jtot_object1_code = 'OKX_ORDERLINE') uniqueOrderLineSelect,
oe_order_lines_all oel,
oe_order_headers_all oe,
ra_customer_trx_lines_all ral
where
uniqueOrderLineSelect.object1_id1 = oel.line_id
and oel.header_id = oe.header_id
and to_char (oe.order_number) = ral.sales_order
and to_char (oel.line_id) = ral.interface_line_attribute6
) OM_ORIGINATED_BILLED_LINES
WHERE
KLINES.id = l_line_id
AND KSLINES.cle_id = KLINES.id
AND (KSLINES.usage_type IS NULL OR KSLINES.usage_type = 'NPR');
SELECT
(
NVL(KLINES.price_negotiated, 0)
+
NVL(KSLINES.credit_amount, 0)
+
NVL(KSLINES.suppressed_credit, 0)
)
-
(NVL(OKS_BILLED_LINES.BILLED_AMOUNT,0) + NVL(OM_ORIGINATED_BILLED_LINES.BILLED_AMOUNT,0)) unbilled_amount
FROM
OKC_K_LINES_B KLINES
,OKS_K_LINES_B KSLINES
,
(
SELECT
NVL(SUM(btl.trx_amount), 0) BILLED_AMOUNT
FROM
OKS_BILL_SUB_LINES BSL
,oks_bill_txn_lines btl
,oks_bill_transactions btr
,OKS_BILL_CONT_LINES BCL
WHERE
BSL.cle_id = l_line_id
AND BCL.id = BSL.bcl_id
AND BCL.bill_action = 'RI'
AND btl.BSL_ID = bsl.id
AND btl.btn_id = btr.id
AND bcl.BTN_ID = btr.ID
AND (btl.trx_number <> -99 OR btr.trx_number <> -99) ) OKS_BILLED_LINES
,
(
SELECT
NVL(SUM(ral.extended_amount), 0) BILLED_AMOUNT
FROM
ra_customer_trx_lines_all ral
,oks_bill_cont_lines bcl
,oe_order_headers_all oe
,oe_order_lines_all oel
,okc_k_rel_objs rel
,okc_k_lines_b subline
WHERE
subline.id = l_line_id
AND subline.lse_id in (9,18,25)
AND rel.cle_id = subline.id
AND rel.jtot_object1_code = 'OKX_ORDERLINE'
AND oel.line_id = rel.object1_id1
AND oe.header_id = oel.header_id
AND ral.sales_order = TO_CHAR(oe.order_number)
AND ral.interface_line_attribute1 = TO_CHAR(oe.order_number)
AND ral.interface_line_attribute6 = TO_CHAR(oel.line_id)
AND bcl.cle_id = subline.cle_id
AND bcl.btn_id = -44
AND bcl.bill_action = 'RI'
) OM_ORIGINATED_BILLED_LINES
WHERE
KLINES.id = l_line_id
AND KSLINES.cle_id = KLINES.id;
SELECT (
(SELECT SUM(
NVL(KLINES.PRICE_NEGOTIATED, 0) +
NVL(KSLINES.CREDIT_AMOUNT, 0) +
NVL(KSLINES.SUPPRESSED_CREDIT, 0)
)
FROM
OKC_K_LINES_B KLINES
,OKS_K_LINES_B KSLINES
WHERE
KLINES.CHR_ID = P_CHR_ID
AND KLINES.ID = KSLINES.CLE_ID
AND (KSLINES.USAGE_TYPE IS NULL OR KSLINES.USAGE_TYPE = 'NPR')
)
-
(
( SELECT
NVL(SUM(BTL.TRX_AMOUNT),0) BILLED_AMOUNT
FROM
OKC_K_LINES_B LINE
,OKS_K_LINES_B KSLINES
,OKS_BILL_TRANSACTIONS BTR
,OKS_BILL_TXN_LINES BTL
,OKS_BILL_CONT_LINES BCL
WHERE
LINE.CHR_ID = P_CHR_ID
AND LINE.ID = KSLINES.CLE_ID
AND (KSLINES.USAGE_TYPE IS NULL OR KSLINES.USAGE_TYPE = 'NPR') -- Bug 5484219 Filter out non NPR lines
AND LINE.ID = BCL.CLE_ID
AND BCL.bill_action = 'RI'
AND BTR.ID = BCL.BTN_ID
AND BTL.BTN_ID = BTR.ID
AND BTL.BCL_ID = BCL.ID
AND (BTL.TRX_NUMBER <> -99 OR BTR.TRX_NUMBER <> -99)
)
+
(
SELECT
NVL(SUM(RAL.EXTENDED_AMOUNT), 0) BILLED_AMOUNT
FROM (
SELECT /*+ NO_MERGE */ DISTINCT REL.OBJECT1_ID1, SUBLINE.CHR_ID
FROM OKC_K_REL_OBJS REL,
OKC_K_LINES_B SUBLINE,
OKS_BILL_CONT_LINES BCL
WHERE
SUBLINE.DNZ_CHR_ID = P_CHR_ID
AND SUBLINE.LSE_ID IN (9,18,25)
AND SUBLINE.CLE_ID = BCL.CLE_ID
AND BCL.BTN_ID = - 44
AND BCL.BILL_ACTION = 'RI'
AND SUBLINE.ID = REL.CLE_ID
AND REL.JTOT_OBJECT1_CODE = 'OKX_ORDERLINE') UNIQUEORDERLINESELECT,
OE_ORDER_LINES_ALL OEL,
OE_ORDER_HEADERS_ALL OE,
RA_CUSTOMER_TRX_LINES_ALL RAL
WHERE
UNIQUEORDERLINESELECT.OBJECT1_ID1 = OEL.LINE_ID
AND OEL.HEADER_ID = OE.HEADER_ID
AND TO_CHAR (OE.ORDER_NUMBER) = RAL.SALES_ORDER
AND TO_CHAR (OEL.LINE_ID) = RAL.INTERFACE_LINE_ATTRIBUTE6
)
)
) UNBILLED_AMOUNT FROM DUAL;
SELECT
NVL(SUM(BTL.TRX_AMOUNT), 0) + NVL(SUM(BTL.TRX_LINE_TAX_AMOUNT), 0) TRX_AMOUNT
FROM
OKC_K_LINES_B LINE
,OKS_BILL_TRANSACTIONS BTR
,OKS_BILL_TXN_LINES BTL
,OKS_BILL_CONT_LINES BCL
WHERE
LINE.CHR_ID = P_CHR_ID
AND LINE.ID = BCL.CLE_ID
AND BTR.ID = BCL.BTN_ID
AND BTL.BTN_ID = BTR.ID
AND BTL.BCL_ID = BCL.ID
AND BCL.bill_action = 'RI'
AND (BTL.TRX_NUMBER <> -99 OR BTR.TRX_NUMBER <> -99)
GROUP BY LINE.CHR_ID
UNION
SELECT
(NVL (SUM (DECODE(RATRXLINESELECT.R, 1, LINEAMT, 0)), 0) + NVL (SUM (TAXAMT), 0)) TRX_AMOUNT
FROM
(
SELECT UNIQUEORDERLINESELECT.ID,
UNIQUEORDERLINESELECT.CHR_ID,
RA.TRX_NUMBER,
RA.TRX_DATE,
RATAX.EXTENDED_AMOUNT TAXAMT,
RAL.EXTENDED_AMOUNT LINEAMT,
RAL.CUSTOMER_TRX_ID,
UNIQUEORDERLINESELECT.BILL_ACTION,
UNIQUEORDERLINESELECT.BILL_FROM_DATE,
UNIQUEORDERLINESELECT.BILL_TO_DATE,
RANK() OVER (PARTITION BY RAL.CUSTOMER_TRX_LINE_ID ORDER BY RATAX.CUSTOMER_TRX_LINE_ID) R
FROM (
SELECT /*+ NO_MERGE */ DISTINCT REL.OBJECT1_ID1,
SUBLINE.CLE_ID ID,
SUBLINE.DNZ_CHR_ID CHR_ID,
BCL.BILL_ACTION,
BCL.DATE_BILLED_FROM BILL_FROM_DATE,
BCL.DATE_BILLED_TO BILL_TO_DATE
FROM OKC_K_REL_OBJS REL,
OKC_K_LINES_B SUBLINE,
OKS_BILL_CONT_LINES BCL
WHERE
SUBLINE.DNZ_CHR_ID = P_CHR_ID
AND SUBLINE.LSE_ID IN (9,18,25)
AND SUBLINE.CLE_ID = BCL.CLE_ID
AND BCL.BTN_ID = - 44
AND BCL.BILL_ACTION = 'RI'
AND SUBLINE.ID = REL.CLE_ID
AND REL.JTOT_OBJECT1_CODE = 'OKX_ORDERLINE') UNIQUEORDERLINESELECT,
OE_ORDER_LINES_ALL OEL,
OE_ORDER_HEADERS_ALL OE,
RA_CUSTOMER_TRX_LINES_ALL RAL,
RA_CUSTOMER_TRX_ALL RA,
RA_CUSTOMER_TRX_LINES_ALL RATAX
WHERE
UNIQUEORDERLINESELECT.OBJECT1_ID1 = OEL.LINE_ID
AND OEL.HEADER_ID = OE.HEADER_ID
AND TO_CHAR (OE.ORDER_NUMBER) = RAL.SALES_ORDER
AND TO_CHAR (OEL.LINE_ID) = RAL.INTERFACE_LINE_ATTRIBUTE6
AND RAL.CUSTOMER_TRX_ID = RA.CUSTOMER_TRX_ID
AND RATAX.LINK_TO_CUST_TRX_LINE_ID (+) = RAL.CUSTOMER_TRX_LINE_ID
AND RATAX.LINE_TYPE (+) = 'TAX') RATRXLINESELECT
GROUP BY RATRXLINESELECT.CHR_ID;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_tl
WHERE uom_code = p_unit_of_measure
AND language = userenv('LANG');
SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC'
, mtl.description,mtl.concatenated_segments)
FROM mtl_system_items_kfv mtl
,okc_k_items itm
,csi_item_instances csi
WHERE itm.object1_id1 = p_object1_id1
AND itm.jtot_object1_code = p_jtot_object1_code
AND csi.instance_id = itm.object1_id1
AND csi.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = csi.inv_master_organization_id
AND rownum < 2;
SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC'
, mtl.description,mtl.concatenated_segments)
FROM mtl_system_items_kfv mtl
WHERE mtl.inventory_item_id = to_number(p_object1_id1)
AND mtl.organization_id = p_object1_id2;
SELECT party.party_name
FROM hz_parties party
WHERE party.party_id = p_object1_id1;
SELECT systl.name
FROM csi_systems_tl systl
WHERE systl.system_id = to_number(p_object1_id1)
AND systl.language = userenv('LANG');
SELECT decode (ca.account_name, null, p.party_name,ca.account_name)
FROM hz_cust_accounts ca
,hz_parties p
WHERE ca.cust_account_id = to_number(p_object1_id1)
AND p.party_id = ca.party_id;
SELECT DECODE(site.party_site_name,NULL,site.party_site_number
,site.party_site_number || '-' ||
site.party_site_name ) NAME
FROM hz_party_sites site
WHERE site.party_site_id = to_number(p_object1_id1);
SELECT jtot_object1_code,
object1_id1,
object1_id2
FROM OKC_K_ITEMS
WHERE CLE_ID = p_line_id;
SELECT fnd_flex_server.get_kfv_concat_segs_by_rowid('COMPACT', 401, 'SERV', 101, sysitems.rowid)name
FROM MTL_SYSTEM_ITEMS_B sysitems
WHERE sysitems.inventory_item_id = to_number(p_object1_id1)
AND rownum < 2;
SELECT trx_number
FROM ra_customer_trx_all rah
WHERE rah.customer_trx_id = p_commitment_id
AND nvl(rah.org_id,-99) = p_org_id;
SELECT
(CASE WHEN oel1.inventory_item_id = csi.inventory_item_id THEN
'N'
ELSE
'Y'
END) isComponentFlag
FROM okc_k_items itm
,csi_item_instances csi
,oe_order_lines_all oel
,oe_order_lines_all oel1
,(SELECT rel.object1_id1,
rel.cle_id
FROM okc_k_rel_objs rel
WHERE rel.cle_id = l_line_id
AND rel.jtot_object1_code = 'OKX_ORDERLINE' ) x
WHERE itm.cle_id = x.cle_id
AND itm.object1_id1 = csi.instance_id
AND x.object1_id1 = oel.line_id
AND oel.service_reference_line_id = oel1.line_id (+)
AND oel.service_reference_type_code = 'ORDER';