[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor l_sname_csr (p_resid number) is select name from jtf_rs_salesreps where resource_id = p_resid;
SELECT header_id
FROM okx_order_lines_v ol
WHERE ol.id1 = p_order_line_id;
SELECT chr_id
FROM okc_k_rel_objs
WHERE object1_id1 = TO_CHAR (p_order_hdr_id);
Select object1_id1
from okc_rules_v
where rgp_id = (select id from okc_rule_groups_v where cle_id = p_cle_id)
And rule_information_category = p_category;
Select object1_id1
From okc_rules_v rul
,okc_rule_groups_v rgp
Where rul.rgp_id = rgp.id
And rule_information_category = p_category
And jtot_object1_Code = p_object_Code
And rgp.dnz_chr_id = p_chr_id
And rgp.cle_id Is Null;
Cursor which selects id i.e gets all covered levels for a given
Contract line (Warr or ExtWar)from COntract lines.
*/
CURSOR l_serv_csr
IS
SELECT ID
FROM okc_k_lines_v
WHERE cle_id = p_service_line_id;
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_line_id AND jtot_object1_code = g_jtf_cusprod;
SELECT ki.cle_id line_id
FROM okc_k_items ki
WHERE ki.dnz_chr_id = p_k_hdr_id
AND ki.object1_id1 = TO_CHAR (p_object_id)
AND ki.jtot_object1_code = g_jtf_warr;
/*Select Kl.id
From OKC_K_LINES_B kl
,OKC_K_ITEMS ki
Where kl.dnz_chr_id = p_K_hdr_Id
And kl.lse_id in (14,19)
And ki.cle_id = kl.id
And ki.object1_id1 = to_char(p_object_id)
And ki.jtot_object1_code in (G_JTF_Warr,G_JTF_Extwar)
And trunc(p_service_startdate) >= trunc(kl.Start_Date)
And trunc(p_service_enddate) <= trunc(kl.end_date) ;
SELECT 'x'
FROM okc_k_lines_b kl, okc_k_items ki
WHERE ki.cle_id = kl.ID
AND ki.jtot_object1_code = 'OKX_CUSTPROD'
AND kl.lse_id IN (25, 18)
AND kl.cle_id = p_line_id
AND ki.object1_id1 = TO_CHAR (p_object_id);
SELECT chr_id, renewal_type, po_required_yn, renewal_pricing_type,
markup_percent, price_list_id1, line_renewal_type,
link_chr_id, contact_id, site_id, email_id, phone_id, fax_id,
billing_profile_id --new parameter added -vigandhi (May29-02)
,RENEWAL_APPROVAL_FLAG --Bug# 5173373
FROM oks_k_order_details_v
WHERE order_line_id1 = TO_CHAR (p_order_line_id);
SELECT Id
FROM OKC_K_HEADERS_B CHR
,OKC_STATUSES_B CST
WHERE CHR.STS_CODE = CST.CODE
AND CHR.DATE_TERMINATED IS NULL
AND CST.STE_CODE IN ( 'ACTIVE', 'SIGNED')
AND CHR.ID = c_CHR_ID;
SELECT minimum_accountable_unit, PRECISION, extended_precision
FROM fnd_currencies
WHERE currency_code = p_currency_code;
SELECT LENGTH (p_credit_card_num)
INTO len_credit_card_num
FROM DUAL;
SELECT SUBSTR (p_credit_card_num, i, 1)
INTO l_cc_num_char (i)
FROM DUAL;
SELECT chr_id
FROM okc_k_rel_objs
WHERE object1_id1 = TO_CHAR (p_order_hdr_id)
AND jtot_object1_code = 'OKX_ORDERHEAD';
SELECT /*+ leading (kii) use_nl (kii rel kl kiw)
index(kiw okc_k_items_n1) */ 'D'
FROM Okc_k_items kii
,Okc_k_rel_objs rel
,Okc_k_lines_b kl
,Okc_k_items kiw
WHERE rel.object1_id1 = c_order_hdr_id
AND rel.jtot_object1_code = 'OKX_ORDERHEAD'
AND kiw.dnz_chr_id = rel.chr_id
AND kiw.object1_id1 = c_warr_item_id
AND kiw.jtot_object1_code = G_JTF_WARR
AND kl.cle_id = kiw.cle_id
AND kl.lse_id IN (18,25)
AND kii.cle_id = kl.id
AND kii.jtot_object1_code = 'OKX_CUSTPROD'
AND kii.object1_id1 = c_instance_id
AND kii.dnz_chr_id = rel.chr_id;
SELECT bic.component_item_id war_item_id
FROM bom_inventory_components bic --OKX_INV_COMPONENTS_V bic
WHERE bic.bill_sequence_id = c_bill_seq_id
AND EXISTS (
SELECT 'Component is a Warranty'
FROM okx_system_items_v mtl
WHERE mtl.id2 = okc_context.get_okc_organization_id
AND mtl.id1 = bic.component_item_id
AND mtl.vendor_warranty_flag = 'Y')
AND TRUNC (p_datec) >= TRUNC (bic.effectivity_date)
AND TRUNC (p_datec) <=
NVL (TRUNC (bic.disable_date), TRUNC (p_datec))
ORDER BY bic.component_item_id;
SELECT common_bill_sequence_id
FROM bom_bill_of_materials --OKX_BILL_OF_MATERIALS_V
WHERE organization_id = okc_context.get_okc_organization_id
AND assembly_item_id = p_inventory_item_id
AND alternate_bom_designator IS NULL;
SELECT bic.component_quantity, b.primary_uom_code,
b.coverage_schedule_id coverage_template_id
FROM mtl_system_items_b_kfv b, --okx_system_items_v mtl,
bom_inventory_components bic --okx_inv_components_v bic
WHERE bic.component_item_id = b.inventory_item_id
AND b.organization_id = okc_context.get_okc_organization_id
AND bic.bill_sequence_id = p_comm_bill_seq_id
AND bic.component_item_id = p_war_inv_item_id
AND TRUNC (l_war_date) >= TRUNC (bic.effectivity_date)
AND TRUNC (l_war_date) <=
NVL (TRUNC (bic.disable_date), TRUNC (l_war_date) + 1)
-- fix bug 2458473
AND b.vendor_warranty_flag = 'Y'
AND ROWNUM < 2;
SELECT service_starting_delay
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_prod_item_id
AND organization_id = okc_context.get_okc_organization_id;
SELECT DECODE (ol.actual_shipment_date,
NULL, 'N',
'Y'
) shipped_flag
FROM csi_item_instances csi, oe_order_lines_all ol
WHERE csi.instance_id = p_customer_product_id
AND csi.last_oe_order_line_id = ol.line_id;
Select bic.component_item_id war_item_id,bic.effectivity_date,bic.disable_date
From BOM_INVENTORY_COMPONENTS bic --OKX_INV_COMPONENTS_V bic
Where bic.bill_sequence_id = C_bill_seq_id
And exists
(
Select 'Component is a Warranty'
From OKX_SYSTEM_ITEMS_V mtl
Where mtl.id2 = okc_context.get_okc_organization_id
And mtl.id1 = bic.component_item_id
And mtl.vendor_warranty_flag = 'Y'
)
Order by bic.component_item_id;
Select common_bill_sequence_id
From BOM_BILL_OF_MATERIALS --OKX_BILL_OF_MATERIALS_V
Where organization_id = okc_context.get_okc_organization_id
And assembly_item_id = P_prod_item_id
And alternate_bom_designator is Null;
SELECT oh.order_number,
oh.org_id,
oh.ordered_date,
oh.price_list_id,
oh.agreement_id,
oh.cust_po_number,
oh.invoicing_rule_id,
oh.accounting_rule_id,
oh.payment_term_id,
oh.sold_to_org_id,
oh.ship_to_org_id,
oh.invoice_to_org_id,
oh.invoice_to_contact_id,
oh.sold_to_contact_id,
oh.deliver_to_contact_id,
oh.payment_amount,
oh.transactional_curr_code,
oh.tax_exempt_number,
oh.tax_exempt_flag,
oh.conversion_rate,
oh.conversion_type_code,
oh.conversion_rate_date,
oh.salesrep_id,
-- Modified for 120 CC Extn Projct
NULL credit_card_expiration_date, -- OH.CREDIT_CARD_EXPIRATION_DATE
NULL credit_card_number, -- OH.CREDIT_CARD_NUMBER
--
--Added in R12 by rsu
oh.tax_exempt_reason_code,
oh.tax_point_code
--
FROM oe_order_headers_all oh
WHERE oh.header_id = p_hdr_id;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_id;
SELECT ca.cust_account_id
FROM hz_cust_acct_sites_all ca, hz_cust_site_uses_all cs
WHERE ca.cust_acct_site_id = cs.cust_acct_site_id
AND cs.site_use_id = l_inv_org_id;
SELECT tax_exemption_id id1
FROM zx_exemptions
WHERE exempt_certificate_number = p_no
AND cust_account_id = p_cust_id
AND exemption_status_code IN ('MANUAL', 'PRIMARY', 'UNAPPROVED');
SELECT lv.lookup_code
FROM fnd_lookup_values lv
WHERE lv.LANGUAGE = USERENV ('LANG')
AND security_group_id =
fnd_global.lookup_security_group (lv.lookup_type,
lv.view_application_id
)
AND lv.lookup_type = 'TAX_CONTROL_FLAG'
AND lv.lookup_code = p_id2;
SELECT ship_to_contact_id
FROM oe_order_headers_all
WHERE header_id = p_hdr_id;
SELECT ol.org_id,
ol.line_number,
ol.sold_to_org_id,
ol.ship_to_org_id,
ol.invoice_to_org_id,
ol.commitment_id,
-- added in R12 by rsu
ol.tax_exempt_number,
ol.tax_exempt_reason_code,
ol.tax_point_code,
ol.tax_exempt_flag,
ol.header_id
--
FROM oe_order_lines_all ol
WHERE ol.line_id = l_line_id;
SELECT t.description NAME,
b.concatenated_segments description
FROM mtl_system_items_b_kfv b,
mtl_system_items_tl t
WHERE
b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.inventory_item_id = p_product_item
AND b.organization_id = okc_context.get_okc_organization_id;
SELECT ol.inventory_item_id,
ol.service_start_date,
ol.service_end_date,
ol.service_duration,
ol.service_period,
ol.org_id, ol.line_number,
ol.invoice_to_org_id,
ol.ship_to_org_id,
ol.unit_selling_price,
ol.unit_selling_percent,
ol.fulfilled_quantity,
ol.invoice_to_contact_id, --Bug#1696511
ol.service_reference_type_code,
ol.service_reference_line_id,
--end bug
--Ordered_Quantity
ol.order_quantity_uom,
--22-NOV-2005 mchoudha added for PPC
pricing_quantity,
pricing_quantity_uom, --End PPC
ol.sold_to_org_id,
ol.invoicing_rule_id,
ol.accounting_rule_id,
ol.commitment_id,
ol.tax_value,
ol.price_list_id,
t.description NAME,
b.concatenated_segments description,
b.service_starting_delay,
b.coverage_schedule_id coverage_template_id,
ol.header_id,
ol.tax_exempt_number, -- Bug#5008188 - vjramali
ol.tax_exempt_reason_code,
ol.tax_code, ol.tax_exempt_flag -- End Bug#5008188
FROM oe_order_lines_all ol,
mtl_system_items_b_kfv b,
mtl_system_items_tl t
WHERE ol.line_id = p_order_line_id
AND b.inventory_item_id = ol.inventory_item_id
AND b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.organization_id = okc_context.get_okc_organization_id;
SELECT actual_shipment_date
FROM oe_order_lines_all
WHERE line_id = p_service_line_id;
SELECT MAX (ol.end_date)
FROM okc_k_items_v ot, okc_k_lines_v ol
WHERE ot.object1_id1 = TO_CHAR (p_cp_id)
AND ol.ID = ot.cle_id
AND ol.lse_id = 18;
SELECT line_id, inventory_item_id, service_start_date,
service_end_date, service_reference_line_id
FROM oe_order_lines_all
WHERE header_id = p_ord_hdr_id
AND service_reference_line_id = p_order_line_id
AND service_reference_type_code IS NOT NULL;
Select line_id
,Service_Start_Date
,Service_End_Date
,Inventory_Item_Id
,Service_Reference_Line_Id
From OE_ORDER_LINES_ALL
Where not HEADER_ID = p_ord_hdr_id
And Service_reference_type_code = 'ORDER'
And Service_Reference_line_id = p_order_line_id
And Service_Duration is Not Null;
SELECT line_id, service_start_date, service_end_date,
inventory_item_id, service_reference_line_id
FROM oe_order_lines_all
WHERE header_id = p_ord_hdr_id
AND service_reference_type_code = 'CUSTOMER_PRODUCT'
AND service_reference_line_id = p_customer_product_id
AND service_duration IS NOT NULL;
SELECT ki.ID, ki.number_of_items, ki.dnz_chr_id, ki.cle_id,
kl.sts_code, kl.start_date, kl.end_date, kl.lse_id,
kl.date_terminated, kl.upg_orig_system_ref,
kl.upg_orig_system_ref_id, kl.price_unit,
kl.price_negotiated, kl.NAME, kl.item_description,
kl.line_renewal_type_code, kh.start_date hdr_sdt,
kh.end_date hdr_edt, kh.sts_code hdr_sts, kh.price_list_id,
kh.payment_term_id, kh.inv_rule_id, ks.acct_rule_id,
kh.inv_organization_id, ks.payment_type, ks.inv_trx_type,
ks.ar_interface_yn, ks.hold_billing, ks.summary_trx_yn,
kh.authoring_org_id, kh.contract_number, kh.cust_po_number,
kh.currency_code, kh.conversion_type, kh.conversion_rate,
kh.conversion_rate_date, kh.conversion_euro_rate,
kh.scs_code, okl.tax_amount -- bug 3736860
,
party.object1_id1 party_id
FROM okc_k_items_v ki,
okc_k_headers_b kh,
oks_k_headers_b ks,
okc_k_lines_v kl,
oks_k_lines_b okl,
okc_statuses_v st,
okc_k_party_roles_b party
WHERE ki.object1_id1 = p_id
AND ki.jtot_object1_code = p_code
AND ki.dnz_chr_id = kh.ID
AND ks.chr_id(+) = kh.ID -- Vigandhi 06-jan-2004
AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
-- support to subscription category contracts
AND ki.cle_id = kl.ID
AND okl.cle_id(+) = kl.ID
AND kl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
AND kl.date_terminated IS NULL
AND kh.template_yn = 'N'
-- And PARTY.chr_id = KH.Id
AND party.dnz_chr_id = kh.ID -- vigandhi 16-mar-05
AND party.chr_id IS NOT NULL
-- Added for performance issue 4223824
AND party.cle_id IS NULL
AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND party.jtot_object1_code = 'OKX_PARTY';
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_cleid
AND jtot_object1_code IN (g_jtf_warr, g_jtf_extwar, g_jtf_usage);
SELECT ol.ID, ol.cle_id, ol.start_date, ol.end_date, ol.NAME,
ol.item_description, ol.price_negotiated, ol.currency_code,
ol.line_number, ol.lse_id, ol.inv_rule_id,
ol.bill_to_site_use_id, ol.ship_to_site_use_id,
ol.cust_acct_id
-- ,OL.unit_price
--,OL.cle_id_renewed
, ol.sts_code, oh.contract_number_modifier, kl.acct_rule_id,
kl.tax_code --Bug#4121175
FROM okc_k_lines_v ol, oks_k_lines_b kl, okc_k_headers_b oh
WHERE ol.dnz_chr_id = p_hdr_id
AND oh.ID = ol.dnz_chr_id
AND kl.cle_id(+) = ol.ID -- Vigandhi 06-Jan-2004
AND oh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
-- support to subscription category contracts
AND ol.ID IN (SELECT okl.cle_id
FROM okc_k_lines_v okl
WHERE okl.ID = p_line_id AND okl.cle_id IS NOT NULL);
Select COVERAGE_SCHEDULE_ID coverage_template_id
From MTL_SYSTEM_ITEMS_B_KFV -- OKX_SYSTEM_ITEMS_V
WHere INVENTORY_ITEM_ID = p_serv_id;
SELECT t.description NAME, b.concatenated_segments description,
b.coverage_schedule_id coverage_template_id
FROM mtl_system_items_b_kfv b, mtl_system_items_tl t
WHERE b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.inventory_item_id = p_serv_id
AND ROWNUM < 2;
SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
FROM oks_k_sales_credits_v
WHERE cle_id = p_cle_id;
SELECT object1_id1
FROM okc_k_rel_objs_v
WHERE cle_id = p_line_id
AND chr_id = p_hdr_id
AND jtot_object1_code = p_object_code;
SELECT resource_id, GROUP_ID, access_level
FROM okc_k_accesses_v
WHERE chr_id = p_hdr_id;
SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
FROM oks_k_sales_credits_v
WHERE chr_id = p_chr_id AND cle_id IS NULL;
SELECT date_billed_from, date_billed_to, creation_date
FROM oks_bill_cont_lines_v
WHERE cle_id = p_cle_id;
SELECT tce_code, quantity
FROM okc_time_code_units_v
WHERE uom_code = p_code;
SELECT shippable_item_flag, service_starting_delay
FROM okx_system_items_v
WHERE id1 = p_prod_item_id
AND organization_id = okc_context.get_okc_organization_id;
SELECT csi.last_oe_order_line_id original_order_line_id,
csi.inventory_item_id, csi.quantity,
csi.unit_of_measure uom_code, t.description NAME -- mtl.name
,
b.concatenated_segments description --mtl.description
,
b.coverage_schedule_id coverage_template_id
-- mtl.coverage_template_id
FROM csi_item_instances csi,
mtl_system_items_b_kfv b,
mtl_system_items_tl t --okx_system_items_v mtl
WHERE csi.instance_id = p_cpid
AND csi.inventory_item_id = b.inventory_item_id
AND b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.organization_id = okc_context.get_okc_organization_id;
SELECT ol.header_id, oh.transactional_curr_code, oh.cust_po_number,
oh.invoice_to_contact_id, oh.agreement_id,
oh.invoicing_rule_id, oh.accounting_rule_id
FROM oe_order_lines_all ol -- OKX_ORDER_LINES_V OL
,
oe_order_headers_all oh -- OKX_ORDER_HEADERS_V OH
WHERE ol.line_id = p_line_id AND ol.header_id = oh.header_id;
PROCEDURE update_timestamp (
p_counter_group_id IN NUMBER,
p_service_start_date IN DATE,
p_service_line_id IN NUMBER,
x_status OUT NOCOPY VARCHAR2
)
IS
--------------------
CURSOR c_ctr_timestamp (cp_counter_group_id NUMBER)
IS
SELECT counter_value_id, counter_id
FROM okx_counters_v
WHERE counter_group_id = cp_counter_group_id AND TYPE = 'TIME';
END update_timestamp;
SELECT salesrep_id, sales_credit_type_id, PERCENT, sales_group_id
FROM oe_sales_credits
WHERE line_id = p_order_line_id;
SELECT DISTINCT grp.group_name, grp.group_id
FROM jtf_rs_group_members mem
,jtf_rs_groups_vl grp
,jtf_rs_salesreps srp
,jtf_rs_group_usages usg
,jtf_rs_role_relations rrl
WHERE srp.resource_id = mem.resource_id
AND mem.group_id = grp.group_id
AND mem.group_id = usg.group_id
AND usg.usage = 'SALES'
AND mem.delete_flag = 'N'
AND mem.group_member_id = rrl.role_resource_id
AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
AND rrl.delete_flag = 'N'
--AND nvl(rrl.end_date_active,TO_DATE('01/01/4713','MM/DD/RRRR')) >=
--:NAME_IN('OKS_HEADER_CONTACTS.START_DATE')
-- AND rrl.start_date_active <= :NAME_IN('OKS_HEADER_CONTACTS END_DATE ')
AND srp.salesrep_id = p_salesrep_id
AND srp.org_id = okc_context.get_okc_
-- AND :END_DATE BETWEEN grp.start_date_active AND
-- NVL(grp.end_date_active,TO_DATE('01/01/4713','MM/DD/RRRR'))
UNION ALL
SELECT group_name, group_id
FROM jtf_rs_groups_tl
WHERE group_id = -1
AND LANGUAGE = USERENV('LANG');
SELECT salesrep_id, sales_credit_type_id, PERCENT, sales_group_id
FROM oe_sales_credits
WHERE header_id = p_order_hdr_id AND line_id IS NULL;
PROCEDURE update_contract_details (
p_hdr_id NUMBER,
p_order_line_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR l_link_csr1
IS
SELECT NVL (link_ord_line_id1, order_line_id1)
FROM oks_k_order_details
WHERE order_line_id1 = TO_CHAR (p_order_line_id);
SELECT ID, order_line_id1, object_version_number
FROM oks_k_order_details
WHERE link_ord_line_id1 = l_link_ord_id AND chr_id IS NULL;
SELECT ID, order_line_id1, object_version_number
FROM oks_k_order_details
WHERE order_line_id1 = l_link_ord_id AND chr_id IS NULL;
|| '.UPDATE_CONTRACT_DETAILS.ERROR',
'l_line_csr1 Not Found for Line Id = '
|| p_order_line_id
);
oks_cod_pvt.update_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_codv_tbl => l_codv_tbl_in,
x_codv_tbl => l_codv_tbl_out
);
|| '.UPDATE_CONTRACT_DETAILS.External_call.after',
'OKS_COD_PVT.update_row(Return Status = '
|| l_return_status
|| ')'
);
oks_cod_pvt.update_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_codv_tbl => l_codv_tbl_in,
x_codv_tbl => l_codv_tbl_out
);
|| '.UPDATE_CONTRACT_DETAILS.External_call.after',
'OKS_COD_PVT.update_row(Return Status = '
|| l_return_status
|| ')'
);
|| '.UPDATE_CONTRACT_DETAILS.UNEXPECTED',
'sqlcode = ' || SQLCODE || ', sqlerrm = '
|| SQLERRM
);
SELECT pricing_context, pricing_attribute1, pricing_attribute2,
pricing_attribute3, pricing_attribute4, pricing_attribute5,
pricing_attribute6, pricing_attribute7, pricing_attribute8,
pricing_attribute9, pricing_attribute10, pricing_attribute11,
pricing_attribute12, pricing_attribute13,
pricing_attribute14, pricing_attribute15,
pricing_attribute16, pricing_attribute17,
pricing_attribute18, pricing_attribute19,
pricing_attribute20, pricing_attribute21,
pricing_attribute22, pricing_attribute23,
pricing_attribute24, pricing_attribute25,
pricing_attribute26, pricing_attribute27,
pricing_attribute28, pricing_attribute29,
pricing_attribute30, pricing_attribute31,
pricing_attribute32, pricing_attribute33,
pricing_attribute34, pricing_attribute35,
pricing_attribute36, pricing_attribute37,
pricing_attribute38, pricing_attribute39,
pricing_attribute40, pricing_attribute41,
pricing_attribute42, pricing_attribute43,
pricing_attribute44, pricing_attribute45,
pricing_attribute46, pricing_attribute47,
pricing_attribute48, pricing_attribute49,
pricing_attribute50, pricing_attribute51,
pricing_attribute52, pricing_attribute53,
pricing_attribute54, pricing_attribute55,
pricing_attribute56, pricing_attribute57,
pricing_attribute58, pricing_attribute59,
pricing_attribute60, pricing_attribute61,
pricing_attribute62, pricing_attribute63,
pricing_attribute64, pricing_attribute65,
pricing_attribute66, pricing_attribute67,
pricing_attribute68, pricing_attribute69,
pricing_attribute70, pricing_attribute71,
pricing_attribute72, pricing_attribute73,
pricing_attribute74, pricing_attribute75,
pricing_attribute76, pricing_attribute77,
pricing_attribute78, pricing_attribute79,
pricing_attribute80, pricing_attribute81,
pricing_attribute82, pricing_attribute83,
pricing_attribute84, pricing_attribute85,
pricing_attribute86, pricing_attribute87,
pricing_attribute88, pricing_attribute89,
pricing_attribute90, pricing_attribute91,
pricing_attribute92, pricing_attribute93,
pricing_attribute94, pricing_attribute95,
pricing_attribute96, pricing_attribute97,
pricing_attribute98, pricing_attribute99,
pricing_attribute100
FROM oe_order_price_attribs_v okx
WHERE okx.line_id = p_order_line_id;
SELECT pricing_context, pricing_attribute1, pricing_attribute2,
pricing_attribute3, pricing_attribute4, pricing_attribute5,
pricing_attribute6, pricing_attribute7, pricing_attribute8,
pricing_attribute9, pricing_attribute10, pricing_attribute11,
pricing_attribute12, pricing_attribute13,
pricing_attribute14, pricing_attribute15,
pricing_attribute16, pricing_attribute17,
pricing_attribute18, pricing_attribute19,
pricing_attribute20, pricing_attribute21,
pricing_attribute22, pricing_attribute23,
pricing_attribute24, pricing_attribute25,
pricing_attribute26, pricing_attribute27,
pricing_attribute28, pricing_attribute29,
pricing_attribute30, pricing_attribute31,
pricing_attribute32, pricing_attribute33,
pricing_attribute34, pricing_attribute35,
pricing_attribute36, pricing_attribute37,
pricing_attribute38, pricing_attribute39,
pricing_attribute40, pricing_attribute41,
pricing_attribute42, pricing_attribute43,
pricing_attribute44, pricing_attribute45,
pricing_attribute46, pricing_attribute47,
pricing_attribute48, pricing_attribute49,
pricing_attribute50, pricing_attribute51,
pricing_attribute52, pricing_attribute53,
pricing_attribute54, pricing_attribute55,
pricing_attribute56, pricing_attribute57,
pricing_attribute58, pricing_attribute59,
pricing_attribute60, pricing_attribute61,
pricing_attribute62, pricing_attribute63,
pricing_attribute64, pricing_attribute65,
pricing_attribute66, pricing_attribute67,
pricing_attribute68, pricing_attribute69,
pricing_attribute70, pricing_attribute71,
pricing_attribute72, pricing_attribute73,
pricing_attribute74, pricing_attribute75,
pricing_attribute76, pricing_attribute77,
pricing_attribute78, pricing_attribute79,
pricing_attribute80, pricing_attribute81,
pricing_attribute82, pricing_attribute83,
pricing_attribute84, pricing_attribute85,
pricing_attribute86, pricing_attribute87,
pricing_attribute88, pricing_attribute89,
pricing_attribute90, pricing_attribute91,
pricing_attribute92, pricing_attribute93,
pricing_attribute94, pricing_attribute95,
pricing_attribute96, pricing_attribute97,
pricing_attribute98, pricing_attribute99,
pricing_attribute100
FROM okc_price_att_values_v okx
WHERE okx.cle_id = p_k_line_id;
SELECT object1_id1
FROM okc_k_party_roles_v
WHERE chr_id = p_chr_id AND rle_code = p_rle_code;
Select object1_id1
From okc_rules_v rul
,okc_rule_groups_v rgp
Where rul.rgp_id = rgp.id
And rule_information_category = p_category
And jtot_object1_Code = p_object_Code
And rgp.dnz_chr_id = p_chr_id
And cle_id Is Null;
SELECT object1_id1
FROM okc_rules_v rul, okc_rule_groups_v rgp
WHERE rul.rgp_id = rgp.ID
AND rule_information_category = p_category
AND jtot_object1_code = p_object_code
AND cle_id = p_cle_id;
SELECT object1_id1
FROM okc_k_items kit
WHERE cle_id = p_cle_id;
SELECT PERCENT, ctc_id, sales_credit_type_id1,
sales_credit_type_id2, sales_group_id
FROM oks_k_sales_credits_v
WHERE chr_id = p_header_id AND cle_id IS NULL;
oks_sales_credit_pub.insert_sales_credit
(p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => l_scrv_tbl_in,
x_scrv_tbl => l_scrv_tbl_out
);
'OKS_SALES_CREDIT_PUB.Insert_Sales_credit(x_return_status = '
|| x_return_status
|| ')'
);
SELECT application_column_name
FROM fnd_id_flex_segments_vl
WHERE application_id = 401 AND id_flex_code = p_code
ORDER BY segment_num;
SELECT concatenated_segment_delimiter
FROM fnd_id_flex_structures_vl
WHERE application_id = 401
AND id_flex_code = p_code
AND id_flex_structure_code = l_structure_code;
l_select_stmt VARCHAR2 (4000);
l_select_stmt :=
'Select '
|| l_sel_column
|| ' From Mtl_system_items_b
Where Inventory_item_id = :d1 And Organization_id = :d2 ';
DBMS_SQL.parse (l_cursor_id, l_select_stmt, DBMS_SQL.native);
SELECT application_column_name
FROM fnd_id_flex_segments_vl
WHERE application_id = 401 AND id_flex_code = p_code
ORDER BY segment_num;
SELECT concatenated_segment_delimiter
FROM fnd_id_flex_structures_vl
WHERE application_id = 401
AND id_flex_code = p_code
AND id_flex_structure_code = l_structure_code;
l_select_stmt VARCHAR2 (4000);
l_select_stmt :=
'Select '
|| l_sel_column
|| ' ,Description '
|| ' From Mtl_system_items_b
Where Inventory_item_id = :d1 And Organization_id = :d2 ';
DBMS_SQL.parse (l_cursor_id, l_select_stmt, DBMS_SQL.native);
SELECT source_id
FROM jtf_rs_resource_extns re, jtf_rs_salesreps sr
WHERE sr.resource_id = re.resource_id
AND sr.salesrep_id = p_salesrep_id
AND (sr.org_id = p_authorg_id OR p_authorg_id IS NULL);
SELECT ppl.employee_number employee_number, ppl.full_name full_name,
ppl.work_telephone phone, NULL fax,
LOWER (ppl.email_address) email, job.NAME job_title,
loc.address_line_1 address1, loc.address_line_2 address2,
loc.address_line_3 address3, NULL concatenated_address,
loc.town_or_city city, loc.postal_code postal_code,
loc.region_2 state, NULL province, NULL county,
loc.country country, asg.supervisor_id mgr_id,
emp.full_name mgr_name, ppl.business_group_id org_id,
org.NAME org_name, ppl.first_name first_name,
ppl.last_name last_name, ppl.middle_names middle_name,
LOWER (ppl.attribute26) new_email
FROM per_all_people_f ppl,
hr_all_organization_units org,
per_all_assignments_f asg,
per_jobs job,
hr_locations loc,
per_all_people_f emp
WHERE ppl.person_id = p_source_id
AND TRUNC (SYSDATE) BETWEEN ppl.effective_start_date
AND ppl.effective_end_date
AND ppl.employee_number IS NOT NULL
AND ppl.business_group_id = org.organization_id
AND ppl.person_id = asg.person_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'E'
AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.job_id = job.job_id(+)
AND asg.location_id = loc.location_id(+)
AND asg.supervisor_id = emp.person_id(+)
AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
AND NOT EXISTS (
SELECT pep.person_id
FROM per_all_people_f pep, per_all_assignments_f asg1
WHERE pep.person_id = ppl.person_id
AND TRUNC (SYSDATE) BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND pep.employee_number IS NOT NULL
AND pep.person_id = asg1.person_id
AND asg1.primary_flag = 'Y'
AND asg1.assignment_type = 'E'
AND TRUNC (SYSDATE) BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
GROUP BY pep.person_id
HAVING COUNT (pep.person_id) > 1);
SELECT phone_number
FROM per_phones
WHERE parent_id = p_source_id
AND phone_type = p_phone_type
AND parent_table = 'PER_ALL_PEOPLE_F'
AND TRUNC (SYSDATE) BETWEEN date_from AND NVL (date_to, SYSDATE);
SELECT vc.object1_id1, kh.authoring_org_id
FROM okc_k_headers_b kh, okc_k_party_roles_b pr, okc_contacts vc
WHERE kh.ID = p_contract_id
AND pr.chr_id = p_contract_id
AND vc.dnz_chr_id = p_contract_id
AND vc.cpl_id = pr.ID
AND pr.rle_code = 'VENDOR'
AND vc.jtot_object1_code = 'OKX_SALEPERS'
AND vc.ID =
oks_extwar_util_pvt.active_salesrep (kh.ID,
pr.ID,
kh.authoring_org_id
);
SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
FROM okc_k_headers_b kh,
okc_statuses_b st,
oks_k_headers_b rul,
(SELECT included_chr_id
FROM okc_k_grpings
START WITH cgp_parent_id = p_contract_group
CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
WHERE kh.authoring_org_id = p_orgid
AND kh.scs_code IN ('SERVICE', 'WARRANTY')
AND kh.sts_code = st.code
AND st.ste_code = 'ENTERED'
AND rul.chr_id(+) = kh.ID
AND kh.ID = grp.included_chr_id
AND rul.est_rev_date >= p_forfdate
AND rul.est_rev_date <= p_fortdate
AND kh.currency_code = p_curr
ORDER BY kh.currency_code, kh.sts_code;
SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
FROM okc_k_headers_b kh,
okc_statuses_b st,
oks_k_headers_b rul,
(SELECT included_chr_id
FROM okc_k_grpings
START WITH cgp_parent_id = p_contract_group
CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
WHERE kh.scs_code IN ('SERVICE', 'WARRANTY')
AND kh.sts_code = st.code
AND st.ste_code = 'ENTERED'
AND rul.chr_id(+) = kh.ID
AND kh.ID = grp.included_chr_id
AND rul.est_rev_date >= p_forfdate
AND rul.est_rev_date <= p_fortdate
AND kh.currency_code = p_curr
ORDER BY kh.currency_code, kh.sts_code;
SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
FROM okc_k_headers_b kh,
okc_statuses_b st,
oks_k_headers_b rul,
(SELECT included_chr_id
FROM okc_k_grpings
START WITH cgp_parent_id = p_contract_group
CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
WHERE kh.authoring_org_id = p_orgid
AND kh.scs_code IN ('SERVICE', 'WARRANTY')
AND kh.sts_code = st.code
AND st.ste_code = 'ENTERED'
AND rul.chr_id(+) = kh.ID
AND kh.ID = grp.included_chr_id
AND rul.est_rev_date >= p_forfdate
AND rul.est_rev_date <= p_fortdate
ORDER BY kh.currency_code, kh.sts_code;
SELECT kh.currency_code, kh.sts_code, kh.ID contract_id,
NVL (rul.est_rev_percent, 0) PERCENT, st.ste_code
FROM okc_k_headers_b kh,
okc_statuses_b st,
oks_k_headers_b rul,
(SELECT included_chr_id
FROM okc_k_grpings
START WITH cgp_parent_id = p_contract_group
CONNECT BY cgp_parent_id = PRIOR included_cgp_id) grp
WHERE kh.scs_code IN ('SERVICE', 'WARRANTY')
AND kh.sts_code = st.code
AND st.ste_code = 'ENTERED'
AND rul.chr_id(+) = kh.ID
AND kh.ID = grp.included_chr_id
AND rul.est_rev_date >= p_forfdate
AND rul.est_rev_date <= p_fortdate
ORDER BY kh.currency_code, kh.sts_code;
SELECT SUM (price_negotiated)
FROM okc_k_lines_b kl
WHERE kl.dnz_chr_id = p_kid
AND kl.lse_id IN (25, 7, 9, 10, 8, 35, 11)
AND kl.price_negotiated BETWEEN 0 AND 90999999
HAVING SUM (price_negotiated) BETWEEN NVL (p_min, 0)
AND NVL (p_max, 99999999999999999);
SELECT NVL (SUM ( ( kl.price_negotiated
/ CEIL (DECODE (SIGN (end_date - start_date),
0, 1,
(MONTHS_BETWEEN (kl.end_date,
kl.start_date
)
)
)
)
)
* CEIL (DECODE (SIGN (p_regz_date - kl.start_date),
0, 1,
MONTHS_BETWEEN (p_regz_date,
kl.start_date
)
)
)
),
0
)
FROM okc_k_lines_b kl
WHERE kl.dnz_chr_id = p_kid
AND kl.lse_id IN (25, 7, 9, 10, 8, 35, 11)
AND kl.price_negotiated BETWEEN 0 AND 90999999
AND kl.start_date <= p_regz_date;
SELECT ven.object1_id1, ven.ID, cust.object1_id1
FROM okc_k_party_roles_b ven, okc_k_party_roles_b cust
WHERE ven.dnz_chr_id = cust.dnz_chr_id
AND ven.dnz_chr_id = l_chr_id
AND ven.cle_id IS NULL
AND ven.rle_code = 'VENDOR'
AND cust.dnz_chr_id = l_chr_id
AND cust.cle_id IS NULL
AND cust.rle_code = 'CUSTOMER';
SELECT 'Y'
FROM okc_contacts con
WHERE con.cpl_id = l_cpl_id
AND con.jtot_object1_code = 'OKX_SALEPERS'
AND con.cro_code IN ('SUP_SALES', 'SALESPERSON')
AND TRUNC (SYSDATE) BETWEEN NVL (con.start_date, SYSDATE - 1)
AND NVL (con.end_date, SYSDATE);
DELETE FROM oks_status_forecast
WHERE conc_request_id = p_conc_request_id;
INSERT INTO oks_status_forecast
(conc_request_id, run_flag, run_time, status_type,
status_code, number_of_contracts, contract_value,
rev_rec_value, forecast_value,
booking_forecast, currency
)
VALUES (p_conc_request_id, 1, SYSDATE, l_ste_prev,
--'ENTERED',
l_status_prev, l_number_k, l_kh_amount,
l_revrec_amount, l_forecast_amount,
l_booking_forecast, l_curncy_prev
);
INSERT INTO oks_status_forecast
(conc_request_id, run_flag, run_time, status_type,
status_code, number_of_contracts, contract_value,
rev_rec_value, forecast_value, booking_forecast,
currency
)
VALUES (p_conc_request_id, 1, SYSDATE, l_ste_curr, --'ENTERED',
l_status_curr, l_number_k, l_kh_amount,
l_revrec_amount, l_forecast_amount, l_booking_forecast,
l_curncy_curr
);
SELECT con1.ID
FROM okc_contacts con1, jtf_rs_salesreps salesrep
WHERE con1.dnz_chr_id = p_contract_id
AND jtot_object1_code = 'OKX_SALEPERS'
AND con1.cpl_id = p_party_id
AND TRUNC (SYSDATE) BETWEEN NVL (con1.start_date, SYSDATE - 1)
AND NVL (con1.end_date, SYSDATE)
AND NVL (con1.start_date, TO_DATE (1, 'J')) IN (
SELECT MAX (NVL (con2.start_date, TO_DATE (1, 'J')))
FROM okc_contacts con2
WHERE con2.dnz_chr_id = p_contract_id
AND jtot_object1_code = 'OKX_SALEPERS'
AND con2.cpl_id = p_party_id
AND TRUNC (SYSDATE) BETWEEN NVL (con2.start_date,
SYSDATE - 1
)
AND NVL (con2.end_date,
SYSDATE
))
AND salesrep.salesrep_id = TO_NUMBER (con1.object1_id1)
AND salesrep.org_id = p_org_id
ORDER BY con1.creation_date DESC, salesrep.NAME ASC;
SELECT TRUNC (MAX (date_billed_to))
FROM oks_bill_cont_lines
WHERE cle_id IN (
SELECT ID
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND lse_id IN (1, 12, 14, 19));
SELECT TRUNC (MAX (date_billed_to))
FROM oks_bill_cont_lines
WHERE cle_id = p_cle_id;
SELECT TRUNC (MAX (date_billed_to))
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
okc_api.set_message (g_app_name, 'OKS_BA_UPDATE_NOT_ALLOWED');