[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_clvl_selections_tbl clvl_selections_tbl;
SELECT unit_selling_price
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT name, description
FROM OKX_SYSTEM_ITEMS_V
WHERE id1 = p_inv_id
AND TRUNC(SYSDATE) BETWEEN trunc(nvl(start_date_active, SYSDATE)) AND trunc(nvl(end_date_active, SYSDATE)) ;
SELECT id1, name
FROM okx_parties_v
WHERE id1 = p_party_id;
SELECT P.id1, P.name
FROM OKX_PARTIES_V P,
OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE P.id1 = CA1.party_id
AND CA1.id1 IN(SELECT A.related_cust_account_id
FROM OKX_CUST_ACCT_RELATE_ALL_V A,
OKX_CUSTOMER_ACCOUNTS_V B
WHERE B.ID1 = A.CUST_ACCOUNT_ID
AND B.party_id = p_party_id
AND B.status = 'A'
AND A.status = 'A'
AND A.org_id = p_org_id)
AND CA1.status = 'A';
SELECT id1, name
FROM okx_parties_v
WHERE id1 = p_party_id
UNION
SELECT P.id1, p.name
FROM OKX_PARTIES_V P,
OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE P.id1 = CA1.party_id
AND CA1.id1 IN(SELECT A.related_cust_account_id
FROM OKX_CUST_ACCT_RELATE_ALL_V A,
OKX_CUSTOMER_ACCOUNTS_V B
WHERE B.ID1 = A.CUST_ACCOUNT_ID
AND B.party_id = p_party_id
AND B.status = 'A'
AND A.status = 'A'
AND A.org_id = p_org_id)
AND CA1.status = 'A';
SELECT CA1.Id1, CA1.name
FROM OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE CA1.party_id = p_party_id
AND CA1.status = 'A'
ORDER BY ca1.name ;
SELECT CA2.id1, CA2.name
FROM OKX_CUSTOMER_ACCOUNTS_V CA2
WHERE CA2.id1 IN(SELECT A.RELATED_CUST_ACCOUNT_ID
FROM OKX_CUST_ACCT_RELATE_ALL_V A,
OKX_CUSTOMER_ACCOUNTS_V B
WHERE B.ID1 = A.CUST_ACCOUNT_ID
AND B.party_id = p_party_id
AND B.status = 'A'
AND A.status = 'A'
AND A.org_id = p_org_id)
AND CA2.status = 'A'
ORDER BY ca2.name ;
SELECT CA1.Id1, CA1.name
FROM OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE CA1.party_id = p_party_id
AND CA1.status = 'A'
UNION
SELECT CA2.id1, CA2.name
FROM OKX_CUSTOMER_ACCOUNTS_V CA2
WHERE CA2.id1 IN(SELECT A.RELATED_CUST_ACCOUNT_ID
FROM OKX_CUST_ACCT_RELATE_ALL_V A,
OKX_CUSTOMER_ACCOUNTS_V B
WHERE B.ID1 = A.CUST_ACCOUNT_ID
AND B.party_id = p_party_id
AND B.status = 'A'
AND A.status = 'A'
AND A.org_id = p_org_id)
AND CA2.status = 'A'
ORDER BY 2 ;
/* Select Products for a single customer or for all customers belonging to a Party */
-- This cursor is not used --
CURSOR l_csr_party_products(p_party_id IN NUMBER,
p_organization_id IN NUMBER)
IS
SELECT CII.instance_id id1,
CII.install_location_id,
CII.quantity,
CII.instance_number,
CII.unit_of_measure,
0,
CII.inventory_item_id,
CII.serial_number,
'#' id2,
CII.last_oe_order_line_id
FROM CSI_ITEM_INSTANCES CII,
CSI_INSTANCE_STATUSES CIS
WHERE CII.owner_party_account_id IN
(SELECT id1 FROM okx_customer_accounts_v
WHERE party_id = p_party_id)
AND CII.Instance_status_id = CIS.instance_status_id
AND CIS.service_order_allowed_flag = 'Y';
get_prod_sql := ' SELECT CII.instance_id id1 '
||', CII.install_location_id install_location_id '
||', CII.quantity quantity '
||', CII.instance_number instance_number '
||', CII.unit_of_measure unit_of_measure '
||', 0 '
||', CII.inventory_item_id inventory_item_id'
||', CII.serial_number serial_number '
||', ''#'' id2 '
||', CII.last_oe_order_line_id '
||', CII.external_reference ' -- new bug 4372877
||' FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES CIS '
||' WHERE '
||' CIS.instance_status_id = CII.instance_status_id '
||' AND CIS.service_order_allowed_flag = ''Y''' ;
get_prod_sql_cust := ' SELECT CII.instance_id id1 '
||', CII.install_location_id install_location_id '
||', CII.quantity quantity '
||', CII.instance_number instance_number '
||', CII.unit_of_measure unit_of_measure '
||', 0 unit_selling_price '
||', CII.inventory_item_id inventory_item_id'
||', CII.serial_number serial_number '
||', ''#'' id2 '
||', CII.last_oe_order_line_id '
||', CII.external_reference ' -- new bug 4372877
||' FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES CIS , MTL_SYSTEM_ITEMS_KFV IT'
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :p_organization_id '
||' AND CIS.instance_status_id = CII.instance_status_id '
||' AND CIS.service_order_allowed_flag = ''Y'''
||' AND CII.owner_party_account_id in (select id1 '
||' from okx_customer_accounts_v '
||' where party_id = :l_party_id ) ';
get_prod_sql_rel := ' SELECT CII.instance_id id1 '
||', CII.install_location_id install_location_id '
||', CII.quantity quantity '
||', CII.instance_number instance_number '
||', CII.unit_of_measure unit_of_measure '
||', 0 unit_selling_price '
||', CII.inventory_item_id inventory_item_id'
||', CII.serial_number serial_number '
||', ''#'' id2 '
||', last_oe_order_line_id '
||', CII.external_reference ' -- new bug 4372877
||' FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES CIS , MTL_SYSTEM_ITEMS_KFV IT'
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :p_organization_id '
||' AND '
||' CIS.instance_status_id = CII.instance_status_id '
||' AND CIS.service_order_allowed_flag = ''Y'''
||' AND CII.owner_party_account_id in '
||' (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :l_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :l_org_id '
||' ) ' ;
get_prod_sql_both := ' SELECT CII.instance_id id1 '
||', CII.install_location_id install_location_id '
||', CII.quantity quantity '
||', CII.instance_number instance_number '
||', CII.unit_of_measure unit_of_measure '
||',0 unit_selling_price '
||', CII.inventory_item_id inventory_item_id'
||', CII.serial_number serial_number '
||', ''#'' id2 '
||', last_oe_order_line_id '
||', CII.external_reference ' -- new bug 4372877
||' FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES CIS , MTL_SYSTEM_ITEMS_KFV IT'
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :p_organization_id '
||' AND '
||' CIS.instance_status_id = CII.instance_status_id '
||' AND CIS.service_order_allowed_flag = ''Y'''
||' AND CII.owner_party_account_id in (select id1 '
||' from okx_customer_accounts_v '
||' where party_id = to_char(:l_party_id) '
||' UNION '
||' select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :l_party_id'
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :l_org_id '
||' ) ';
get_prod_sql_all := ' SELECT CII.instance_id id1 '
||', CII.install_location_id install_location_id '
||', CII.quantity quantity '
||', CII.instance_number instance_number '
||', CII.unit_of_measure unit_of_measure '
||', 0 unit_selling_price '
||', CII.inventory_item_id inventory_item_id'
||', CII.serial_number serial_number '
||', ''#'' id2 '
||', CII.last_oe_order_line_id '
||', CII.external_reference ' -- new bug 4372877
||' FROM CSI_ITEM_INSTANCES CII, CSI_INSTANCE_STATUSES CIS , MTL_SYSTEM_ITEMS_KFV IT'
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :p_organization_id '
||' AND '
||' CIS.instance_status_id = CII.instance_status_id '
||' AND CIS.service_order_allowed_flag = ''Y''';
SELECT S.DESCRIPTION
FROM OKX_PARTY_SITES_V S
WHERE S.ID1 = p_install_location_id;
SELECT
SUBSTR(replace(arp_addr_label_pkg.format_address
(NULL,HZ.ADDRESS1,HZ.ADDRESS2,HZ.ADDRESS3,HZ.ADDRESS4,HZ.CITY,HZ.COUNTY,HZ.STATE,HZ.PROVINCE,HZ.POSTAL_CODE,NULL,HZ.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),fnd_global.local_chr(10)),1,80)
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID=p_install_location_id;
SELECT
SUBSTR(replace(arp_addr_label_pkg.format_address
(NULL,vs.address_line1,vs.address_line2,vs.address_line3,vs.address_line4,vs.city,VS.COUNTY,vs.state,VS.PROVINCE, vs.zip,NULL,VS.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),fnd_global.local_chr(10)),1,80)
FROM PO_VENDOR_SITES_ALL vs
WHERE vs.VENDOR_SITE_ID =p_install_location_id;
SELECT
SUBSTR(replace(arp_addr_label_pkg.format_address
(NULL,hr.address_line_1,hr.address_line_2,hr.address_line_3,null,hr.town_or_city,null,hr.region_2,null,hr.postal_code,NULL,hr.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),fnd_global.local_chr(10)),1,80)
FROM hr_locations hr
WHERE hr.LOCATION_ID =p_install_location_id;
SELECT INSTALL_LOCATION_TYPE_CODE INTO l_install_loc_type FROM csi_item_instances WHERE instance_id=p_instance_id;
x_prod_selections_tbl IN OUT NOCOPY prod_selections_tbl )
IS
l_price NUMBER;
SELECT S.DESCRIPTION
FROM OKX_PARTY_SITES_V S
WHERE S.ID1 = l_install_site_id;
SELECT unit_of_measure_tl
FROM OKX_UNITS_OF_MEASURE_V
WHERE uom_code = p_uom_code ;
x_prod_selections_tbl(rowcount).rec_type := 'C';
x_prod_selections_tbl(rowcount).rec_name := p_filter;
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id := p_prod_tbl(j).id1;
x_prod_selections_tbl(rowcount).config_parent_id := p_prod_tbl(j).config_parent_id;
x_prod_selections_tbl(rowcount).model_level := p_prod_tbl(j).model_level;
x_prod_selections_tbl(rowcount).cp_id2 := p_prod_tbl(j).id2;
x_prod_selections_tbl(rowcount).ser_number := p_prod_tbl(j).serial_number ;
x_prod_selections_tbl(rowcount).ref_number := p_prod_tbl(j).instance_number ;
x_prod_selections_tbl(rowcount).quantity := p_prod_tbl(j).quantity;
x_prod_selections_tbl(rowcount).orig_net_amt := p_prod_tbl(j).unit_selling_price;
x_prod_selections_tbl(rowcount).ext_reference := p_prod_tbl(j).external_reference;
x_prod_selections_tbl(rowcount).price := l_price;
x_prod_selections_tbl(rowcount).inventory_item_id := p_prod_tbl(j).inventory_item_id;
x_prod_selections_tbl(rowcount).site_id := p_prod_tbl(j).install_location_id;
x_prod_selections_tbl(rowcount).uom_code := p_prod_tbl(j).unit_of_measure;
x_prod_selections_tbl(rowcount).orig_net_amt := get_selling_price(p_prod_tbl(j).oe_line_id);
x_prod_selections_tbl(rowcount).name := l_gen_name;
x_prod_selections_tbl(rowcount).description := l_gen_desc;
x_prod_selections_tbl(rowcount).name := l_gen_desc;
x_prod_selections_tbl(rowcount).description := l_gen_name;
x_prod_selections_tbl(rowcount).site_name := l_install_site_name;
l_display_name := x_prod_selections_tbl(rowcount).name||'; '||g_serial_number ||
x_prod_selections_tbl(rowcount).display_name := l_display_name;
x_prod_selections_tbl IN OUT NOCOPY OKS_AUTH_UTIL_PVT.prod_selections_tbl)
IS
/* Cursor to select all Model having Covered Products for a given Party ID */
CURSOR l_csr_all_models(p_party_id IN NUMBER,
p_organization_id IN NUMBER)
IS
SELECT DISTINCT id1,
name,
description
FROM okx_system_items_v it
WHERE id1 IN
(SELECT inventory_item_id FROM
csi_item_instances
WHERE instance_id IN (SELECT PR.object_id
FROM csi_ii_relationships PR
WHERE relationship_type_code = 'COMPONENT-OF'
AND NOT EXISTS (SELECT cp.subject_id FROM csi_ii_relationships cp
WHERE pr.object_id = cp.subject_id )))
AND IT.serviceable_product_flag = 'Y'
--And IT.organization_id = p_organization_id
AND SYSDATE BETWEEN nvl(it.start_date_active, SYSDATE) AND nvl(it.end_date_active, SYSDATE) ;
SELECT ciir.object_id config_parent_id,
ciir.subject_id cp_id,
LEVEL
FROM csi_ii_relationships ciir
WHERE active_end_date IS NULL
START WITH subject_id
IN (SELECT ciir_pr.subject_id
FROM csi_ii_relationships ciir_pr
WHERE object_id IN
(SELECT instance_id
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_id
AND owner_party_account_id IN (SELECT id1
FROM okx_customer_accounts_v
WHERE party_id = p_party_id)))
CONNECT BY ciir.object_id = PRIOR ciir.subject_id ;
/** Cursor to get product + system item details for select instance id ***/
CURSOR l_csr_model_products(p_instance_id IN NUMBER,
p_organization_id IN NUMBER
)
IS
SELECT CII.instance_id
, IT.name
, IT.description
, CII.install_location_id
, CII.quantity
, CII.instance_number
, CII.unit_of_measure
, CII.inventory_item_id
, CII.serial_number
, OOL.unit_selling_price
, '#' id2
, CII.external_reference -- bug 4372877
FROM CSI_ITEM_INSTANCES CII
, CSI_INSTANCE_STATUSES CIS
, OE_ORDER_LINES_ALL OOL
, OKX_SYSTEM_ITEMS_V IT
WHERE CII.INSTANCE_ID = p_instance_id
AND CII.inventory_item_id = IT.id1
AND IT.serviceable_product_flag = 'Y'
AND IT.organization_id = p_organization_id
AND SYSDATE BETWEEN nvl(it.start_date_active, SYSDATE) AND nvl(it.end_date_active, SYSDATE)
AND CII.last_oe_order_line_id = OOL.line_id ( + )
AND CII.instance_status_id = CIS.instance_status_id
AND CIS.service_order_allowed_flag = 'Y' ;
l_config_cust := 'SELECT ciir.object_id config_parent_id,'
||'ciir.subject_id cp_id,'
||'level '
||' FROM csi_ii_relationships ciir '
||' WHERE active_end_date IS NULL '
||' START WITH subject_id '
||' IN (SELECT ciir_pr.subject_id '
||' FROM csi_ii_relationships ciir_pr '
||' WHERE object_id IN '
||' ( SELECT instance_id '
||' FROM csi_item_instances '
||' WHERE inventory_item_id = to_char(:l_id)'
||' AND owner_party_account_id in (select id1 '
||' from okx_customer_accounts_v '
||' where party_id = to_char(:p_party_id) ))) '
||'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ' ;
l_config_rel := 'SELECT ciir.object_id config_parent_id,'
||'ciir.subject_id cp_id,'
||'level '
||' FROM csi_ii_relationships ciir '
||' WHERE active_end_date IS NULL '
||' START WITH subject_id '
||' IN (SELECT ciir_pr.subject_id '
||' FROM csi_ii_relationships ciir_pr '
||' WHERE object_id IN '
||' ( SELECT instance_id '
||' FROM csi_item_instances '
||' WHERE inventory_item_id = to_char(:l_id ) '
||' AND owner_party_account_id in '
||' (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = to_char(:p_party_id) '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = to_char(:p_org_id) '
||' ))) '
||'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ' ;
l_config_both := 'SELECT ciir.object_id config_parent_id,'
||'ciir.subject_id cp_id,'
||'level '
||' FROM csi_ii_relationships ciir '
||' WHERE active_end_date IS NULL '
||' START WITH subject_id '
||' IN (SELECT ciir_pr.subject_id '
||' FROM csi_ii_relationships ciir_pr '
||' WHERE object_id IN '
||' ( SELECT instance_id '
||' FROM csi_item_instances '
||' WHERE inventory_item_id = to_char(:l_id)'
||' AND owner_party_account_id in (select id1 '
||' from okx_customer_accounts_v '
||' where party_id = to_char(:p_party_id)) '
||' UNION '
||' (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = to_char(:p_party_id)'
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = to_Char(:p_org_id) '
||' ))) '
||'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ' ;
l_config_all := 'SELECT ciir.object_id config_parent_id,'
||'ciir.subject_id cp_id,'
||'level '
||' FROM csi_ii_relationships ciir '
||' WHERE active_end_date IS NULL '
||' START WITH subject_id '
||' IN (SELECT ciir_pr.subject_id '
||' FROM csi_ii_relationships ciir_pr '
||' WHERE object_id IN '
||' ( SELECT instance_id '
||' FROM csi_item_instances '
||' WHERE inventory_item_id = to_char(:l_id))) '
||'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ' ;
/* Select all Items for given party first */
FOR l_get_all_models_rec IN l_csr_all_models(p_party_id, p_organization_id)
LOOP /** 1st loop **/
l_id := l_get_all_models_rec.id1;
x_prod_selections_tbl);
l_prod_tbl.DELETE;
/* select product details for config parent */
/** Build parent **/
OPEN l_csr_model_products(l_config_rec.config_parent_id,
p_organization_id);
x_prod_selections_tbl(rowcount).cp_id := l_model_prod_rec.instance_id;
x_prod_selections_tbl(rowcount).config_parent_id := '';
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Model';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).model_level := l_config_rec.LEVEL;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).model_level := - 1;
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
l_prod_tbl.DELETE;
/* select product details for config parent */
/** Build parent **/
OPEN l_csr_model_products(l_config_rec.config_parent_id,
p_organization_id);
x_prod_selections_tbl(rowcount).cp_id := l_model_prod_rec.instance_id;
x_prod_selections_tbl(rowcount).config_parent_id := '';
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Model';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).model_level := l_config_rec.LEVEL;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).model_level := - 1;
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl IN OUT NOCOPY OKS_AUTH_UTIL_PVT.prod_selections_tbl)
IS
/* Cursor to select all Model having Covered Products for a given Party ID */
CURSOR l_csr_all_models(p_party_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT DISTINCT id1, name, description
FROM okx_system_items_v it
WHERE id1 IN
(SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id IN
(SELECT PR.object_id
FROM csi_ii_relationships PR
WHERE relationship_type_code = 'COMPONENT-OF'
AND NOT EXISTS
(SELECT cp.subject_id
FROM csi_ii_relationships cp
WHERE pr.object_id = cp.subject_id)))
AND IT.serviceable_product_flag = 'Y'
--And IT.organization_id = p_organization_id
AND SYSDATE BETWEEN nvl(it.start_date_active, SYSDATE) AND
nvl(it.end_date_active, SYSDATE);
SELECT ciir.object_id config_parent_id, ciir.subject_id cp_id, LEVEL
FROM csi_ii_relationships ciir
WHERE active_end_date IS NULL
START WITH subject_id IN
(SELECT ciir_pr.subject_id
FROM csi_ii_relationships ciir_pr
WHERE object_id IN
(SELECT instance_id
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_id
AND owner_party_account_id IN
(SELECT id1
FROM okx_customer_accounts_v
WHERE party_id = p_party_id)))
CONNECT BY ciir.object_id = PRIOR ciir.subject_id;
/** Cursor to get product + system item details for select instance id ***/
CURSOR l_csr_model_products(p_instance_id IN NUMBER, p_organization_id IN NUMBER) IS
SELECT CII.instance_id,
IT.name,
IT.description,
CII.install_location_id,
CII.quantity,
CII.instance_number,
CII.unit_of_measure,
CII.inventory_item_id,
CII.serial_number,
OOL.unit_selling_price,
'#' id2,
CII.external_reference -- bug 4372877
FROM CSI_ITEM_INSTANCES CII,
CSI_INSTANCE_STATUSES CIS,
OE_ORDER_LINES_ALL OOL,
OKX_SYSTEM_ITEMS_V IT
WHERE CII.INSTANCE_ID = p_instance_id
AND CII.inventory_item_id = IT.id1
AND IT.serviceable_product_flag = 'Y'
AND IT.organization_id = p_organization_id
AND SYSDATE BETWEEN nvl(it.start_date_active, SYSDATE) AND
nvl(it.end_date_active, SYSDATE)
AND CII.last_oe_order_line_id = OOL.line_id(+)
AND CII.instance_status_id = CIS.instance_status_id
AND CIS.service_order_allowed_flag = 'Y';
SELECT a.customer_product_id
FROM oks_ib_config_v a
WHERE a.config_parent_id IS NULL
START WITH a.customer_product_id = p_customer_prod_id
CONNECT BY PRIOR a.config_parent_id = a.customer_product_id;
SELECT S.DESCRIPTION
FROM OKX_PARTY_SITES_V S
WHERE S.ID1 = l_install_site_id;
SELECT unit_of_measure_tl
FROM OKX_UNITS_OF_MEASURE_V
WHERE uom_code = p_uom_code ;
l_config_cust := 'SELECT ciir.object_id config_parent_id,' ||
'ciir.subject_id cp_id,' || 'level ' ||
' FROM csi_ii_relationships ciir ' ||
' WHERE active_end_date IS NULL ' ||
' START WITH subject_id ' ||
' IN (SELECT ciir_pr.subject_id ' ||
' FROM csi_ii_relationships ciir_pr ' ||
' WHERE object_id IN ' || ' ( SELECT instance_id ' ||
' FROM csi_item_instances ' ||
' WHERE instance_id = to_char(:l_id)' ||
' AND owner_party_account_id in (select id1 ' ||
' from okx_customer_accounts_v ' ||
' where party_id = to_char(:p_party_id) ))) ' ||
'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ';
l_config_rel := 'SELECT ciir.object_id config_parent_id,' ||
'ciir.subject_id cp_id,' || 'level ' ||
' FROM csi_ii_relationships ciir ' ||
' WHERE active_end_date IS NULL ' ||
' START WITH subject_id ' ||
' IN (SELECT ciir_pr.subject_id ' ||
' FROM csi_ii_relationships ciir_pr ' ||
' WHERE object_id IN ' || ' ( SELECT instance_id ' ||
' FROM csi_item_instances ' ||
' WHERE instance_id = to_char(:l_id ) ' ||
' AND owner_party_account_id in ' ||
' (select A.RELATED_CUST_ACCOUNT_ID ' ||
' FROM OKX_CUST_ACCT_RELATE_ALL_V A, ' ||
' OKX_CUSTOMER_ACCOUNTS_V B ' ||
' WHERE B.ID1 = A.CUST_ACCOUNT_ID ' ||
' AND B.party_id = to_char(:p_party_id) ' ||
' AND B.status = ''A''' ||
' AND A.status = ''A''' ||
' AND A.org_id = to_char(:p_org_id) ' || ' ))) ' ||
'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ';
l_config_both := 'SELECT ciir.object_id config_parent_id,' ||
'ciir.subject_id cp_id,' || 'level ' ||
' FROM csi_ii_relationships ciir ' ||
' WHERE active_end_date IS NULL ' ||
' START WITH subject_id ' ||
' IN (SELECT ciir_pr.subject_id ' ||
' FROM csi_ii_relationships ciir_pr ' ||
' WHERE object_id IN ' || ' ( SELECT instance_id ' ||
' FROM csi_item_instances ' ||
' WHERE instance_id = to_char(:l_id)' ||
' AND owner_party_account_id in ((select id1 ' ||
' from okx_customer_accounts_v ' ||
' where party_id = to_char(:p_party_id)) ' || ' UNION ' ||
' (select A.RELATED_CUST_ACCOUNT_ID ' ||
' FROM OKX_CUST_ACCT_RELATE_ALL_V A, ' ||
' OKX_CUSTOMER_ACCOUNTS_V B ' ||
' WHERE B.ID1 = A.CUST_ACCOUNT_ID ' ||
' AND B.party_id = to_char(:p_party_id)' ||
' AND B.status = ''A''' ||
' AND A.status = ''A''' ||
' AND A.org_id = to_Char(:p_org_id)) ' || ' ))) ' ||
'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ';
l_config_all := 'SELECT ciir.object_id config_parent_id,' ||
'ciir.subject_id cp_id,' || 'level ' ||
' FROM csi_ii_relationships ciir ' ||
' WHERE active_end_date IS NULL ' ||
' START WITH subject_id ' ||
' IN (SELECT ciir_pr.subject_id ' ||
' FROM csi_ii_relationships ciir_pr ' ||
' WHERE object_id IN ' || ' ( SELECT instance_id ' ||
' FROM csi_item_instances ' ||
' WHERE instance_id = to_char(:l_id))) ' ||
'CONNECT BY ciir.object_id = PRIOR ciir.subject_id ';
x_prod_selections_tbl);
l_prod_tbl.DELETE;
x_prod_selections_tbl);
l_prod_tbl.DELETE;
/* select product details for config parent */
/** Build parent **/
OPEN l_csr_model_products(l_config_rec.config_parent_id,
p_organization_id);
x_prod_selections_tbl(rowcount).cp_id := l_model_prod_rec.instance_id;
x_prod_selections_tbl(rowcount).config_parent_id := '';
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).description := l_model_prod_rec.name;
x_prod_selections_tbl(rowcount).name := l_model_prod_rec.description;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Configuration';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).model_level := l_config_rec.LEVEL;
x_prod_selections_tbl(rowcount).cp_id2 := l_model_prod_rec.id2;
x_prod_selections_tbl(rowcount).ser_number := l_model_prod_rec.serial_number;
x_prod_selections_tbl(rowcount).ref_number := l_model_prod_rec.instance_number;
x_prod_selections_tbl(rowcount).quantity := l_model_prod_rec.quantity;
x_prod_selections_tbl(rowcount).orig_net_amt := l_model_prod_rec.unit_selling_price;
x_prod_selections_tbl(rowcount).price := 0;
x_prod_selections_tbl(rowcount).price := l_model_prod_rec.unit_selling_price;
x_prod_selections_tbl(rowcount).inventory_item_id := l_model_prod_rec.inventory_item_id;
x_prod_selections_tbl(rowcount).site_id := l_model_prod_rec.install_location_id;
x_prod_selections_tbl(rowcount).uom_code := l_model_prod_rec.unit_of_measure;
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).model_level := 0;
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl(rowcount).name := l_gen_name;
x_prod_selections_tbl(rowcount).description := l_gen_desc;
x_prod_selections_tbl(rowcount).name := l_gen_desc;
x_prod_selections_tbl(rowcount).description := l_gen_name;
x_prod_selections_tbl(rowcount).site_name := l_install_site_name;
l_display_name := x_prod_selections_tbl(rowcount).name||'; '||g_serial_number ||
x_prod_selections_tbl(rowcount).display_name := l_display_name;
PROCEDURE get_product_selection(p_clvl_filter_rec IN clvl_filter_rec,
x_prod_selections_tbl OUT NOCOPY prod_selections_tbl)
IS
l_param_organization_id NUMBER := okc_context.get_okc_organization_id;
l_select_for_name VARCHAR2(2000);
l_select_for_desc VARCHAR2(2000);
l_prod_selections_tbl prod_selections_tbl;
select inventory_item_id, concatenated_segments name , description description */
l_select_for_name := 'select inventory_item_id, concatenated_segments name , description description ';
l_select_for_desc := 'select inventory_item_id, description description , concatenated_segments name ';
||' where inventory_item_id in ( select inventory_item_id from csi_item_instances '
||' where owner_party_account_id in (select id1 '
||' FROM okx_customer_accounts_v '
||' WHERE party_id = :l_party_id)) '
||' and organization_id = :l_organization_id '
||' and serviceable_product_flag = ''Y''';
||' where inventory_item_id in ( select inventory_item_id from csi_item_instances '
||' where owner_party_account_id in (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :l_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :l_org_id '
||' )) '
||' and organization_id = :l_organization_id '
||' and serviceable_product_flag = ''Y''';
||' where inventory_item_id in ( select inventory_item_id from csi_item_instances '
||' where owner_party_account_id in (select id1 '
||' FROM okx_customer_accounts_v '
||' WHERE party_id = :p_party_id '
||' UNION '
||' select A.RELATED_CUST_ACCOUNT_ID '
||' FROM OKX_CUST_ACCT_RELATE_ALL_V A, '
||' OKX_CUSTOMER_ACCOUNTS_V B '
||' WHERE B.ID1 = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :l_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :l_org_id '
||' )) '
||' and organizatioN_id = :l_organization_id '
||' and serviceable_product_flag = ''Y''';
/* Select all Items for given party first */
IF l_default = 'CUSTOMER' THEN
IF l_display_pref = 'NAME' THEN
l_item_cust := l_select_for_name ||' '|| l_item_cust ||' '|| l_order_by_name;
l_item_cust := l_select_for_desc ||' '|| l_item_cust ||' '|| l_order_by_desc;
l_item_rel := l_select_for_name ||' '|| l_item_rel ||' '|| l_order_by_name;
l_item_rel := l_select_for_desc ||' '|| l_item_rel ||' '|| l_order_by_desc;
l_item_both := l_select_for_name ||' '|| l_item_both ||' '|| l_order_by_name;
l_item_both := l_select_for_desc ||' '|| l_item_both ||' '|| l_order_by_desc;
l_item_all := l_select_for_name ||' '|| l_item_all ||' '|| l_order_by_name;
l_item_all := l_select_for_desc ||' '|| l_item_all ||' '|| l_order_by_desc;
x_prod_selections_tbl(rowcount).cp_id := l_items_rec.inventory_item_id;
x_prod_selections_tbl(rowcount).name := l_items_rec.name;
x_prod_selections_tbl(rowcount).description := l_items_rec.description;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Item';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
x_prod_selections_tbl(rowcount).cp_id := l_clvl_id;
x_prod_selections_tbl(rowcount).name := rpad(p_clvl_filter_rec.clvl_name, 30,' ') || rpad(p_clvl_filter_rec.clvl_description, 40,' ');
x_prod_selections_tbl(rowcount).description := p_clvl_filter_rec.clvl_description;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Item';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
l_get_all_systems_sql_c := ' SELECT CSB.system_Id Id1 , CST.name name , CST.description description '
|| ' FROM CSI_SYSTEMS_B CSB, '
|| ' CSI_SYSTEMS_TL CST '
|| ' WHERE CSB.system_id = CST.system_id '
|| ' AND CSB.system_id IN (Select CII.system_id '
|| ' From CSI_ITEM_INSTANCES CII, '
|| ' CSI_INSTANCE_STATUSES CIS '
|| ' , MTL_SYSTEM_ITEMS_B IT '
|| ' WHERE IT.inventory_item_id = CII.inventory_item_id '
|| ' AND IT.serviceable_product_flag = ''Y'''
|| ' AND IT.organization_id = :l_organization_id '
|| ' AND CII.owner_party_account_id in (select cust_account_id '
||' from hz_cust_accounts '
||' where party_id = :p_party_id ) '
|| ' And CIS.instance_status_id = CII.instance_status_id '
|| ' And CIS.service_order_allowed_flag = ''Y'''
|| ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
|| ' And CII.system_id is not null ) '
|| ' And sysdate between Nvl(CSB.start_date_active, sysdate) and Nvl(CSB.end_date_active, sysdate) '
|| ' Order by CST.name ';
l_get_all_systems_sql_b := ' SELECT CSB.system_Id Id1 , CST.name name , CST.description description '
|| ' FROM CSI_SYSTEMS_B CSB, '
|| ' CSI_SYSTEMS_TL CST '
|| ' WHERE CSB.system_id = CST.system_id '
|| ' AND CSB.system_id IN '
||' (SELECT CII.system_id '
||' FROM CSI_ITEM_INSTANCES CII, '
||' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_B IT '
|| ' WHERE IT.inventory_item_id = CII.inventory_item_id '
|| ' AND IT.serviceable_product_flag = ''Y'''
|| ' AND IT.organization_id = :l_organization_id '
|| ' AND CII.owner_party_account_id in (select cust_account_id '
||' from HZ_CUST_ACCOUNTS '
||' where party_id = :p_party_id '
||' UNION ALL'
||' select A.RELATED_CUST_ACCOUNT_ID '
||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
||' HZ_CUST_ACCOUNTS B '
||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :p_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :p_org_id '
||' ) '
|| ' And CIS.instance_status_id = CII.instance_status_id '
|| ' And CIS.service_order_allowed_flag = ''Y'''
|| ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
|| ' And CII.system_id is not null ) '
|| ' And sysdate between Nvl(CSB.start_date_active, sysdate) and Nvl(CSB.end_date_active, sysdate) '
|| ' Order by CST.name ';
l_get_all_systems_sql_r := ' SELECT CSB.system_Id Id1 , CST.name name , CST.description description '
|| ' FROM CSI_SYSTEMS_B CSB, '
|| ' CSI_SYSTEMS_TL CST '
|| ' WHERE CSB.system_id = CST.system_id '
|| ' AND CSB.system_id IN (Select CII.system_id '
|| ' From CSI_ITEM_INSTANCES CII, '
|| ' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_B IT '
|| ' WHERE IT.inventory_item_id = CII.inventory_item_id '
|| ' AND IT.serviceable_product_flag = ''Y'''
|| ' AND IT.organization_id = :l_organization_id '
|| ' AND CII.owner_party_account_id in '
||' (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
||' HZ_CUST_ACCOUNTS B '
||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :p_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :p_org_id '
||' ) '
|| ' And CIS.instance_status_id = CII.instance_status_id '
|| ' And CIS.service_order_allowed_flag = ''Y'''
|| ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
|| ' And system_id is not null ) '
|| ' And sysdate between Nvl(CSB.start_date_active, sysdate) and Nvl(CSB.end_date_active, sysdate) '
|| ' Order by CST.name ';
l_get_all_systems_sql_a := ' SELECT CSB.system_Id Id1 , CST.name name , CST.description description '
|| ' FROM CSI_SYSTEMS_B CSB, '
|| ' CSI_SYSTEMS_TL CST '
|| ' WHERE CSB.system_id = CST.system_id '
|| ' AND CSB.system_id IN (Select CII.system_id '
|| ' From CSI_ITEM_INSTANCES CII, '
|| ' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_B IT '
|| ' WHERE IT.inventory_item_id = CII.inventory_item_id '
|| ' AND IT.serviceable_product_flag = ''Y'''
|| ' AND IT.organization_id = :l_organization_id '
|| ' AND CIS.instance_status_id = CII.instance_status_id '
|| ' And CIS.service_order_allowed_flag = ''Y'''
|| ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
|| ' And CII.system_id is not null ) '
|| ' And sysdate between Nvl(CSB.start_date_active, sysdate) and Nvl(CSB.end_date_active, sysdate) '
|| ' Order by CST.name ';
/* Select all Items for given party first */
IF l_default = 'CUSTOMER' THEN
------------------------------errorout_an(l_get_all_systems_sql_c);
FOR id_counter IN 1 .. x_prod_selections_tbl.COUNT LOOP
IF x_prod_selections_tbl(id_counter).rec_type = 'P' THEN
IF x_prod_selections_tbl(id_counter).cp_id = l_id THEN
l_chk_id_flag := 'Y';
x_prod_selections_tbl(rowcount).cp_id := l_get_all_systems_rec.id1;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).description := l_get_all_systems_rec.description;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'System';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
x_prod_selections_tbl(rowcount).cp_id := l_clvl_id;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).description := p_clvl_filter_rec.clvl_description;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'System';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
/* Select all Items for given party first */
-- Bug 5004778 --
-- Modified Query to reduce shared memory Usage
-- Replaced views to base tables where ever possible
l_get_all_sites_sql_c := ' SELECT SI.Id1 id1 , SI.name name , SI.Party_Site_Number party_site_number , SI.description description '
|| ' FROM OKX_PARTY_SITES_V SI '
|| ' WHERE exists (SELECT CII.install_location_Id '
||' FROM CSI_ITEM_INSTANCES CII, '
||' CSI_INSTANCE_STATUSES CIS, '
||' MTL_SYSTEM_ITEMS_B IT '
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :l_organization_id '
||' AND CII.install_location_id = SI.id1 '
||' AND CII.owner_party_account_id in '
||'(select cust_account_id '
||' from hz_cust_accounts '
||' where party_id = :p_party_id ) '
||' And CIS.instance_Status_id = CII.instance_status_id '
||' And CIS.service_order_allowed_flag = ''Y'''
||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
||' Nvl(CIS.end_date_active, sysdate)) '
-- TCA Changes --
/* ||' And sysdate between Nvl(SI.start_date_active, sysdate) and '
||' Nvl(SI.end_date_active, sysdate) ' */
|| 'AND SI.STATUS = ''A'''
-- TCA Changes --
||' ORDER BY si.party_site_number, si.name ';
l_get_all_sites_sql_r := ' SELECT SI.Id1 id1 , SI.name name , SI.Party_Site_Number party_site_number , SI.description description '
|| ' FROM OKX_PARTY_SITES_V SI '
|| ' WHERE exists (SELECT CII.install_location_Id '
||' FROM CSI_ITEM_INSTANCES CII, '
||' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_B IT '
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :l_organization_id '
||' AND CII.install_location_id = SI.id1 '
||' AND CII.owner_party_account_id in '
||' (select A.RELATED_CUST_ACCOUNT_ID '
||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
||' HZ_CUST_ACCOUNTS B '
||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :l_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :p_org_id '
||' ) '
||' And CIS.instance_Status_id = CII.instance_status_id '
||' And CIS.service_order_allowed_flag = ''Y'''
||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
||' Nvl(CIS.end_date_active, sysdate)) '
-- TCA Changes --
/* ||' And sysdate between Nvl(SI.start_date_active, sysdate) and '
||' Nvl(SI.end_date_active, sysdate) ' */
|| 'AND SI.STATUS = ''A'''
-- TCA Changes --
||' ORDER BY si.party_site_number, si.name ';
l_get_all_sites_sql_b := ' SELECT SI.Id1 id1 , SI.name name , SI.Party_Site_Number party_site_number , SI.description description '
|| ' FROM OKX_PARTY_SITES_V SI '
|| ' WHERE exists (SELECT CII.install_location_Id '
|| ' FROM CSI_ITEM_INSTANCES CII, '
||' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_B IT '
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :l_organization_id '
||' AND CII.install_location_id = SI.id1 '
||' AND CII.owner_party_account_id in '
||' (select cust_account_id '
||' from hz_cust_accounts '
||' where party_id = :p_party_id '
||' UNION '
||' select A.RELATED_CUST_ACCOUNT_ID '
||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
||' HZ_CUST_ACCOUNTS B '
||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
||' AND B.party_id = :p_party_id '
||' AND B.status = ''A'''
||' AND A.status = ''A'''
||' AND A.org_id = :p_org_id '
||' ) '
||' And CIS.instance_Status_id = CII.instance_status_id '
||' And CIS.service_order_allowed_flag = ''Y'''
||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
||' Nvl(CIS.end_date_active, sysdate)) '
-- TCA Changes --
/* ||' And sysdate between Nvl(SI.start_date_active, sysdate) and '
||' Nvl(SI.end_date_active, sysdate) ' */
|| 'AND SI.STATUS = ''A'''
-- TCA Changes --
||' ORDER BY si.party_site_number, si.name ';
l_get_all_sites_sql_a := ' SELECT SI.Id1 id1 , SI.name name , SI.Party_Site_Number party_site_number , SI.description description '
|| ' FROM OKX_PARTY_SITES_V SI '
|| ' WHERE exists (SELECT CII.install_location_Id '
|| ' FROM CSI_ITEM_INSTANCES CII, '
||' CSI_INSTANCE_STATUSES CIS '
||' , MTL_SYSTEM_ITEMS_KFV IT '
||' WHERE IT.inventory_item_id = CII.inventory_item_id '
||' AND IT.serviceable_product_flag = ''Y'''
||' AND IT.organization_id = :l_organization_id '
||' AND CII.install_location_id = SI.id1 '
||' AND CIS.instance_Status_id = CII.instance_status_id '
||' And CIS.service_order_allowed_flag = ''Y'''
||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
||' Nvl(CIS.end_date_active, sysdate)) '
-- TCA Changes --
/* ||' And sysdate between Nvl(SI.start_date_active, sysdate) and '
||' Nvl(SI.end_date_active, sysdate) ' */
|| 'AND SI.STATUS = ''A''';
FOR id_counter IN 1 .. x_prod_selections_tbl.COUNT LOOP
IF x_prod_selections_tbl(id_counter).rec_type = 'P' THEN
IF x_prod_selections_tbl(id_counter).cp_id = l_id THEN
l_chk_id_flag := 'Y';
x_prod_selections_tbl(rowcount).cp_id := l_get_all_sites_rec.id1;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).description := l_get_all_sites_rec.description;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Site';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
x_prod_selections_tbl(rowcount).cp_id := l_clvl_id;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).description := p_clvl_filter_rec.clvl_description;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Site';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
/* Select all Items for given party first */
get_customer_id(p_default => l_default,
p_party_id => l_party_id,
p_org_id => l_org_id,
x_cust_id_tbl => l_cust_id_tbl);
x_prod_selections_tbl(rowcount).cp_id := l_id;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Customer';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
x_prod_selections_tbl(rowcount).cp_id := l_customer_id;
x_prod_selections_tbl(rowcount).name := p_clvl_filter_rec.clvl_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Customer';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
x_prod_selections_tbl );
x_prod_selections_tbl );
x_prod_selections_tbl(rowcount).cp_id := l_party_id;
x_prod_selections_tbl(rowcount).name := l_name;
x_prod_selections_tbl(rowcount).description := l_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Party';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
/* Select all Items for given party first */
get_party_id(p_default => l_default,
p_party_id => l_party_id,
p_org_id => l_org_id,
x_party_id_tbl => l_party_id_tbl );
x_prod_selections_tbl(rowcount).cp_id := l_party_id_tbl(i).party_id;
x_prod_selections_tbl(rowcount).name := l_party_id_tbl(i).party_name;
x_prod_selections_tbl(rowcount).description := l_party_id_tbl(i).party_name;
x_prod_selections_tbl(rowcount).rec_type := 'P';
x_prod_selections_tbl(rowcount).rec_name := 'Party';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id2 := '';
x_prod_selections_tbl(rowcount).ser_number := '';
x_prod_selections_tbl(rowcount).ref_number := '' ;
x_prod_selections_tbl(rowcount).quantity := '';
x_prod_selections_tbl(rowcount).orig_net_amt := '';
x_prod_selections_tbl(rowcount).price := '';
x_prod_selections_tbl(rowcount).inventory_item_id := '';
x_prod_selections_tbl(rowcount).site_id := '';
x_prod_selections_tbl(rowcount).uom_code := '';
x_prod_selections_tbl(rowcount).display_name := '';
x_prod_selections_tbl(rowcount).site_name := '';
x_prod_selections_tbl(rowcount).ext_reference := '';
x_prod_selections_tbl);
PROCEDURE Get_customer_selections(p_clvl_filter_rec IN clvl_filter_rec,
x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
IS
CURSOR l_csr_get_customer(p_party_id IN NUMBER ) IS
SELECT cust_acc.party_id party_id, parties.name party_name, parties.description description,
cust_acc.id1 id1, cust_acc.name name, cust_acc.id2 id2, cust_acc.description account_number
FROM OKX_CUSTOMER_ACCOUNTS_V cust_acc,
OKX_PARTIES_V parties
WHERE cust_acc.party_id = p_party_id
AND cust_acc.party_id = parties.id1
AND cust_acc.status = 'A';
SELECT parties.id1 party_id, parties.name party_name
FROM OKX_PARTIES_V parties
WHERE parties.id1 = p_party_id ;
SELECT cust_acc.party_id party_id, parties.name party_name, parties.description description,
cust_acc.id1 id1, cust_acc.name name, cust_acc.id2 id2, cust_acc.description account_number
FROM OKX_CUSTOMER_ACCOUNTS_V cust_acc,
OKX_PARTIES_V parties
WHERE cust_acc.party_id = parties.id1
AND cust_acc.status = 'A';
l_party_selected OKX_PARTIES_V.ID1%TYPE;
l_party_selected := p_clvl_filter_rec.clvl_find_id;
l_party_id := NVL(l_party_selected, l_party_id);
IF l_party_selected IS NOT NULL THEN
OPEN l_csr_get_party_name(l_party_selected);
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_csr_party_rec.party_id;
x_clvl_selections_tbl(rowcount).name := l_csr_party_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
FOR l_cust_csr_rec IN l_csr_get_customer(l_party_selected) LOOP
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_cust_csr_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_cust_csr_rec.id2;
x_clvl_selections_tbl(rowcount).name := l_cust_csr_rec.name;
x_clvl_selections_tbl(rowcount).clvl_id := l_cust_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_cust_csr_rec.name;
x_clvl_selections_tbl(rowcount).display_name := l_cust_csr_rec.name || ',' || l_cust_csr_rec.account_number;
x_clvl_selections_tbl(rowcount).party_id := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).party_name := l_cust_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).description := l_cust_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
l_party_selected := l_party_id_tbl(i).party_id;
FOR l_cust_csr_rec IN l_csr_get_customer(l_party_selected) LOOP
l_party_id := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).name := l_cust_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_cust_csr_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_cust_csr_rec.id2;
x_clvl_selections_tbl(rowcount).name := l_cust_csr_rec.name;
x_clvl_selections_tbl(rowcount).clvl_id := l_cust_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_cust_csr_rec.name;
x_clvl_selections_tbl(rowcount).display_name := l_cust_csr_rec.name || ',' || l_cust_csr_rec.account_number;
x_clvl_selections_tbl(rowcount).party_id := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).party_name := l_cust_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).description := l_cust_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).name := l_cust_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Customer';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).clvl_id := l_cust_csr_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_cust_csr_rec.id2;
x_clvl_selections_tbl(rowcount).clvl_name := l_cust_csr_rec.name;
x_clvl_selections_tbl(rowcount).display_name := l_cust_csr_rec.name || ',' || l_cust_csr_rec.account_number;
x_clvl_selections_tbl(rowcount).party_id := l_cust_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).party_name := l_cust_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).description := l_cust_csr_Rec.account_number;
x_clvl_selections_tbl(rowcount).lse_id := 35;
x_clvl_selections_tbl(rowcount).lse_name := 'Customer';
END get_customer_selections;
PROCEDURE Get_party_selections(p_clvl_filter_rec IN clvl_filter_rec,
x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
IS
CURSOR l_csr_party_customer(p_party_id IN NUMBER ) IS
SELECT id1, id2, Name, Description
FROM OKX_PARTIES_V
WHERE id1 = p_party_id
AND status = 'A';
SELECT P1.id1, P1.id2, P1.name, P1.description
FROM OKX_PARTIES_V P1, OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE P1.id1 = CA1.party_id
AND CA1.id1 IN (SELECT rel_acc.cust_account_id
FROM OKX_CUSTOMER_ACCOUNTS_V cust_acc,
OKX_CUST_ACCT_RELATE_ALL_V rel_acc
WHERE rel_acc.related_cust_account_id = cust_acc.id1
AND cust_acc.party_id = p_party_id
AND cust_acc.status = 'A'
AND rel_acc.org_id = p_org_id
AND rel_acc.status = 'A')
AND P1.status = 'A'
AND CA1.status = 'A';
SELECT id1, id2, Name, Description
FROM OKX_PARTIES_V
WHERE id1 = p_party_id
AND status = 'A'
UNION
SELECT P1.id1, P1.id2, P1.name, P1.description
FROM OKX_PARTIES_V P1, OKX_CUSTOMER_ACCOUNTS_V CA1
WHERE P1.id1 = CA1.party_id
AND CA1.id1 IN (SELECT rel_acc.cust_account_id
FROM OKX_CUSTOMER_ACCOUNTS_V cust_acc,
OKX_CUST_ACCT_RELATE_ALL_V rel_acc
WHERE rel_acc.related_cust_account_id = cust_acc.id1
AND cust_acc.party_id = p_party_id
AND cust_acc.status = 'A'
AND rel_acc.org_id = p_org_id
AND rel_acc.status = 'A')
AND P1.status = 'A'
AND CA1.status = 'A';
SELECT id1, id2, Name, Description
FROM OKX_PARTIES_V
WHERE status = 'A';
l_party_selected NUMBER;
l_party_selected := p_clvl_filter_rec.clvl_find_id;
IF l_default = 'CUSTOMER' OR l_party_selected IS NOT NULL THEN
IF l_party_selected IS NOT NULL THEN
l_party_id := l_party_selected ;
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).rec_name := 'Party';
x_clvl_selections_tbl(rowcount).clvl_id := l_csr_party_rec_c.id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_csr_party_rec_c.name;
x_clvl_selections_tbl(rowcount).id2 := l_csr_party_rec_c.id2;
x_clvl_selections_tbl(rowcount).name := l_csr_party_rec_c.name;
x_clvl_selections_tbl(rowcount).display_name := l_csr_party_rec_c.name || ',' || l_csr_party_Rec_c.description;
x_clvl_selections_tbl(rowcount).party_id := l_csr_party_rec_c.id1;
x_clvl_selections_tbl(rowcount).party_name := l_csr_party_rec_c.name;
x_clvl_selections_tbl(rowcount).description := l_csr_party_Rec_c.description;
x_clvl_selections_tbl(rowcount).lse_id := 8;
x_clvl_selections_tbl(rowcount).lse_name := 'Party' ;
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).rec_name := 'Party';
x_clvl_selections_tbl(rowcount).id1 := l_csr_party_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_csr_party_rec.id2;
x_clvl_selections_tbl(rowcount).name := l_csr_party_rec.name;
x_clvl_selections_tbl(rowcount).display_name := l_csr_party_rec.name || ',' || l_csr_party_Rec.description;
x_clvl_selections_tbl(rowcount).clvl_id := l_csr_party_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_csr_party_rec.name;
x_clvl_selections_tbl(rowcount).party_id := l_csr_party_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_csr_party_rec.name;
x_clvl_selections_tbl(rowcount).description := l_csr_party_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 8;
x_clvl_selections_tbl(rowcount).lse_name := 'Party' ;
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).rec_name := 'Party';
x_clvl_selections_tbl(rowcount).id1 := l_csr_party_rec_r.id1;
x_clvl_selections_tbl(rowcount).id2 := l_csr_party_rec_r.id2;
x_clvl_selections_tbl(rowcount).name := l_csr_party_rec_r.name;
x_clvl_selections_tbl(rowcount).clvl_id := l_csr_party_rec_r.id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_csr_party_rec_r.name;
x_clvl_selections_tbl(rowcount).display_name := l_csr_party_rec_r.name || ',' || l_csr_party_Rec.description;
x_clvl_selections_tbl(rowcount).party_id := l_csr_party_rec_r.id1;
x_clvl_selections_tbl(rowcount).party_name := l_csr_party_rec_r.name;
x_clvl_selections_tbl(rowcount).description := l_csr_party_Rec_r.description;
x_clvl_selections_tbl(rowcount).lse_id := 8;
x_clvl_selections_tbl(rowcount).lse_name := 'Party' ;
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).rec_name := 'Party';
x_clvl_selections_tbl(rowcount).id1 := l_csr_party_rec_b.id1;
x_clvl_selections_tbl(rowcount).id2 := l_csr_party_rec_b.id2;
x_clvl_selections_tbl(rowcount).name := l_csr_party_rec_b.name;
x_clvl_selections_tbl(rowcount).clvl_id := l_csr_party_rec_b.id1;
x_clvl_selections_tbl(rowcount).id2 := l_csr_party_rec_b.id2;
x_clvl_selections_tbl(rowcount).clvl_name := l_csr_party_rec_b.name;
x_clvl_selections_tbl(rowcount).display_name := l_csr_party_rec_b.name || ',' || l_csr_party_Rec.description;
x_clvl_selections_tbl(rowcount).party_id := l_csr_party_rec_b.id1;
x_clvl_selections_tbl(rowcount).party_name := l_csr_party_rec_b.name;
x_clvl_selections_tbl(rowcount).description := l_csr_party_Rec_b.description;
x_clvl_selections_tbl(rowcount).lse_id := 8;
x_clvl_selections_tbl(rowcount).lse_name := 'Party' ;
END get_party_selections;
PROCEDURE Get_site_selections(p_clvl_filter_rec IN clvl_filter_rec,
x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
IS
CURSOR l_csr_get_site(p_party_id IN NUMBER ) IS
SELECT DISTINCT parties.name party_name, parties.id1 party_id,
party_site.id1 id1, party_site.party_site_number, party_site.id2,
party_site.name party_site_name, party_site.description
FROM OKX_PARTIES_V parties,
OKX_PARTY_SITES_V party_site
WHERE parties.id1 = p_party_id
AND party_site.party_id = parties.id1
AND party_site.status = 'A'
ORDER BY parties.name ;
SELECT DISTINCT parties.name party_name, parties.id1 party_id,
party_site.id1 id1, party_site.party_site_number, party_site.id2,
party_site.name party_site_name, party_site.description
FROM OKX_PARTIES_V parties,
OKX_PARTY_SITES_V party_site
WHERE party_site.party_id = parties.id1
AND party_site.status = 'A'
ORDER BY parties.name ;
l_party_selected VARCHAR2(15);
l_party_selected := l_party_Id;
IF p_clvl_filter_rec.clvl_find_id IS NOT NULL THEN /* If default = CUSTOMER, or find id has a value then select covered sites only for given party id */
-- BUG 4915711 --
-- FOR l_site_csr_rec IN l_csr_get_site(l_party_id) LOOP
OPEN l_csr_get_site(l_party_id);
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_id1;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).id2 := l_get_all_sites_rec.party_sites_id2;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).display_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name ||' '|| l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).clvl_id := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).description := l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_id1;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).id2 := l_get_all_sites_rec.party_sites_id2;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).display_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name ||' '|| l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).clvl_id := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).clvl_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).description := l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_site_csr_rec.party_id;
x_clvl_selections_tbl(rowcount).name := l_site_csr_rec.party_name;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_id1;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_name;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'Site';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).clvl_id := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).id2 := l_get_all_sites_rec.party_sites_id2;
x_clvl_selections_tbl(rowcount).clvl_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).display_name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name ||' '|| l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).id1 := l_get_all_sites_rec.party_sites_id1;
x_clvl_selections_tbl(rowcount).name := l_get_all_sites_rec.party_sites_number || '-' || l_get_all_sites_rec.party_sites_name;
x_clvl_selections_tbl(rowcount).description := l_get_all_sites_rec.party_desc;
x_clvl_selections_tbl(rowcount).lse_id := 10;
x_clvl_selections_tbl(rowcount).lse_name := 'Site';
END get_site_selections;
PROCEDURE Get_system_selections(p_clvl_filter_rec IN clvl_filter_rec,
x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
IS
-- BUG 4171350 --
-- Added check for Language used for Session
/** Covered Systems by customer */
CURSOR l_csr_get_cust_system(p_customer_id IN NUMBER ) IS
SELECT cust_acc.id1, cust_acc.name,
CSB.system_id, '#' id2, CSB.system_number,
CST.description, CST.name system_name
FROM CSI_SYSTEMS_B CSB, CSI_SYSTEMS_TL CST, OKX_CUSTOMER_ACCOUNTS_V cust_acc
WHERE cust_acc.id1 = p_customer_id
AND CSB.system_id = CST.system_id
AND CSB.Customer_id = cust_acc.id1
AND CST.language = userenv('lang') -- new
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE)
ORDER BY cust_acc.id1, CSB.system_id;
SELECT cust_acc.id1, cust_acc.name,
CSB.system_id, '#' id2, CSB.system_number,
CST.description, CST.name system_name
FROM CSI_SYSTEMS_B CSB, CSI_SYSTEMS_TL CST, OKX_CUSTOMER_ACCOUNTS_V cust_acc
WHERE CSB.system_id = CST.system_id
AND CSB.Customer_id = cust_acc.id1
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE)
AND CST.language = userenv('lang') -- new
ORDER BY cust_acc.id1, CSB.system_id;
SELECT parties.id1, parties.name, CSB.system_id, parties.id2 id2,
CST.name system_name, CST.description, CSB.system_number
FROM CSI_SYSTEMS_B CSB,
CSI_SYSTEMS_TL CST,
OKX_PARTIES_V parties,
OKX_CUSTOMER_ACCOUNTS_V cust_acc
WHERE parties.id1 = p_party_id
AND cust_acc.party_id = parties.id1
AND CSB.system_id = CST.system_id
AND CSB.customer_id = cust_acc.id1
AND CST.language = userenv('lang') -- new
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE);
SELECT parties.id1, parties.name, CSB.system_id, parties.id2 id2,
CST.name system_name, CST.description,
CSB.system_number
FROM CSI_SYSTEMS_B CSB,
CSI_SYSTEMS_TL CST,
OKX_PARTIES_V parties,
OKX_CUSTOMER_ACCOUNTS_V cust_acc
WHERE cust_acc.party_id = parties.id1
AND CSB.system_id = CST.system_id
AND CSB.customer_id = cust_acc.id1
AND CST.language = userenv('lang') -- new
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE);
SELECT party_site.id1, party_site.party_site_number || '-' || party_site.name party_site_name, CSB.system_id,
'#', CST.name system_name, CST.description,
CSB.system_number, party_site.id2 id2
FROM CSI_SYSTEMS_B CSB, CSI_SYSTEMS_TL CST, OKX_PARTY_SITES_V party_site
WHERE party_site.id1 = p_party_site_id
AND CSB.system_id = CST.system_id
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE)
AND CSB.install_site_use_id = party_site.id1
AND CST.language = userenv('lang') -- new
ORDER BY party_site.id1, CSB.system_id;
SELECT party_site.id1, party_site.party_site_number || '-' || party_site.name party_site_name, CSB.system_id,
'#', CST.name system_name, CST.description,
CSB.system_number, party_site.id2 id2
FROM CSI_SYSTEMS_B CSB, CSI_SYSTEMS_TL CST, OKX_PARTY_SITES_V party_site
WHERE party_site.party_id = p_party_id
AND CSB.system_id = CST.system_id
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE)
AND CSB.install_site_use_id = party_site.id1
AND CST.language = userenv('lang') -- new
ORDER BY party_site.id1, CSB.system_id;
SELECT party_site.id1, party_site.party_site_number || '-' || party_site.name party_site_name,
CSB.system_id, party_site.id2 id2, CST.name system_name, CST.description,
CSB.system_number
FROM CSI_SYSTEMS_B CSB, CSI_SYSTEMS_TL CST, OKX_PARTY_SITES_V party_site
WHERE CSB.system_id = CST.system_id
AND SYSDATE BETWEEN NVL(CSB.start_date_active, SYSDATE) AND NVL(CSB.end_date_active, SYSDATE)
AND CSB.install_site_use_id = party_site.id1
AND CST.language = userenv('lang') -- new
ORDER BY party_site.id1, CSB.system_id;
l_party_selected NUMBER;
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1 /** Customer id */ ;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name /* customer name */ ;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1; /** customer id **/
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name; /** customer name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** customer id **/
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name; /** customer name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /* Select systems for given customer id */
END LOOP; /* Select customer id */
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1; /** customer id **/
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name; /** customer name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /** Select systems for all customers **/
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** party id **/
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** party id **/
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /* Select systems for given customer id */
END LOOP; /* Select customer id */
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.party_site_name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** party id **/
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.party_site_name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /** Select systems for all customers **/
l_party_selected := l_party_id;
l_party_selected := p_clvl_filter_rec.clvl_find_id;
IF l_party_selected IS NOT NULL THEN
FOR l_system_csr_rec IN l_csr_get_party_system(l_party_selected) LOOP
IF rowcount = 1 THEN
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** party id **/
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
l_party_selected := l_party_id_tbl(i).party_id;
FOR l_system_csr_rec IN l_csr_get_party_system(l_party_selected) LOOP
IF rowcount = 1 THEN
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /* Select systems for given customer id */
END LOOP; /* Select customer id */
x_clvl_selections_tbl(rowcount).rec_type := 'P';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
x_clvl_selections_tbl(rowcount).rec_type := 'C';
x_clvl_selections_tbl(rowcount).rec_name := 'System';
x_clvl_selections_tbl(rowcount).rec_no := rowcount;
x_clvl_selections_tbl(rowcount).id2 := l_system_csr_rec.id2;
x_clvl_selections_tbl(rowcount).id1 := l_system_csr_rec.id1;
x_clvl_selections_tbl(rowcount).clvl_id := l_system_csr_rec.system_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_system_csr_rec.system_name;
x_clvl_selections_tbl(rowcount).display_name := l_system_csr_rec.system_name ||' , '|| l_system_csr_rec.description ||' , '|| l_system_csr_rec.system_number;
x_clvl_selections_tbl(rowcount).party_id := l_system_csr_rec.id1; /** party id **/
x_clvl_selections_tbl(rowcount).party_name := l_system_csr_rec.name; /** party name **/
x_clvl_selections_tbl(rowcount).description := l_system_csr_Rec.description;
x_clvl_selections_tbl(rowcount).lse_id := 11;
x_clvl_selections_tbl(rowcount).lse_name := 'System';
END LOOP; /** Select systems for all customers **/
END get_system_selections;
PROCEDURE GetSelections_Prod(p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_clvl_filter_rec IN clvl_filter_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_prod_selections_tbl OUT NOCOPY prod_selections_tbl)
IS
lf_prod_selections_tbl prod_selections_tbl;
get_product_selection(p_clvl_filter_rec,
lf_prod_selections_tbl);
FOR rowcount IN 1 .. lf_prod_selections_tbl.COUNT LOOP
x_prod_selections_tbl(rowcount).rec_type := lf_prod_selections_tbl(rowcount).rec_type;
x_prod_selections_tbl(rowcount).rec_name := 'Item';
x_prod_selections_tbl(rowcount).rec_no := rowcount;
x_prod_selections_tbl(rowcount).cp_id := lf_prod_selections_tbl(rowcount).cp_id;
x_prod_selections_tbl(rowcount).cp_id2 := lf_prod_selections_tbl(rowcount).cp_id2;
x_prod_selections_tbl(rowcount).ser_number := lf_prod_selections_tbl(rowcount).ser_number;
x_prod_selections_tbl(rowcount).ref_number := lf_prod_selections_tbl(rowcount).ref_number ;
x_prod_selections_tbl(rowcount).quantity := lf_prod_selections_tbl(rowcount).quantity;
x_prod_selections_tbl(rowcount).orig_net_amt := lf_prod_selections_tbl(rowcount).orig_net_amt;
x_prod_selections_tbl(rowcount).price := lf_prod_selections_tbl(rowcount).price;
x_prod_selections_tbl(rowcount).inventory_item_id := lf_prod_selections_tbl(rowcount).inventory_item_id;
x_prod_selections_tbl(rowcount).site_id := lf_prod_selections_tbl(rowcount).site_id;
x_prod_selections_tbl(rowcount).uom_code := lf_prod_selections_tbl(rowcount).uom_code;
x_prod_selections_tbl(rowcount).name := lf_prod_selections_tbl(rowcount).name;
x_prod_selections_tbl(rowcount).display_name := lf_prod_selections_tbl(rowcount).display_name;
x_prod_selections_tbl(rowcount).description := lf_prod_selections_tbl(rowcount).description;
x_prod_selections_tbl(rowcount).model_level := lf_prod_selections_tbl(rowcount).model_level;
x_prod_selections_tbl(rowcount).site_name := lf_prod_selections_tbl(rowcount).site_name;
x_prod_selections_tbl(rowcount).model_level := lf_prod_selections_tbl(rowcount).model_level;
x_prod_selections_tbl(rowcount).ext_reference := lf_prod_selections_tbl(rowcount).ext_reference;
END GetSelections_Prod;
PROCEDURE GetSelections_other(p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_clvl_filter_rec IN clvl_filter_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
IS
l_clvl_selections_tbl clvl_selections_tbl ;
get_party_selections(p_clvl_filter_rec,
l_clvl_selections_tbl);
FOR rowcount IN 1 .. l_clvl_selections_tbl.COUNT LOOP
x_clvl_selections_tbl(rowcount).rec_no := l_clvl_selections_tbl(rowcount).rec_no;
x_clvl_selections_tbl(rowcount).rec_name := l_clvl_selections_tbl(rowcount).rec_name;
x_clvl_selections_tbl(rowcount).rec_type := l_clvl_selections_tbl(rowcount).rec_type;
x_clvl_selections_tbl(rowcount).id1 := l_clvl_selections_tbl(rowcount).id1;
x_clvl_selections_tbl(rowcount).name := l_clvl_selections_tbl(rowcount).name;
x_clvl_selections_tbl(rowcount).id2 := l_clvl_selections_tbl(rowcount).id2;
x_clvl_selections_tbl(rowcount).Party_id := l_clvl_selections_tbl(rowcount).party_id;
x_clvl_selections_tbl(rowcount).party_name := l_clvl_selections_tbl(rowcount).party_name;
x_clvl_selections_tbl(rowcount).description := l_clvl_selections_tbl(rowcount).description;
x_clvl_selections_tbl(rowcount).display_name := l_clvl_selections_tbl(rowcount).display_name;
x_clvl_selections_tbl(rowcount).clvl_id := l_clvl_selections_tbl(rowcount).clvl_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_clvl_selections_tbl(rowcount).clvl_name;
x_clvl_selections_tbl(rowcount).lse_id := l_clvl_selections_tbl(rowcount).lse_id;
x_clvl_selections_tbl(rowcount).lse_name := l_clvl_selections_tbl(rowcount).lse_name;
get_customer_selections(p_clvl_filter_rec,
l_clvl_selections_tbl);
FOR rowcount IN 1 .. l_clvl_selections_tbl.COUNT LOOP
x_clvl_selections_tbl(rowcount).rec_no := l_clvl_selections_tbl(rowcount).rec_no;
x_clvl_selections_tbl(rowcount).rec_name := l_clvl_selections_tbl(rowcount).rec_name;
x_clvl_selections_tbl(rowcount).rec_type := l_clvl_selections_tbl(rowcount).rec_type;
x_clvl_selections_tbl(rowcount).id1 := l_clvl_selections_tbl(rowcount).id1;
x_clvl_selections_tbl(rowcount).name := l_clvl_selections_tbl(rowcount).name;
x_clvl_selections_tbl(rowcount).id2 := l_clvl_selections_tbl(rowcount).id2;
x_clvl_selections_tbl(rowcount).Party_id := l_clvl_selections_tbl(rowcount).party_id;
x_clvl_selections_tbl(rowcount).party_name := l_clvl_selections_tbl(rowcount).party_name;
x_clvl_selections_tbl(rowcount).description := l_clvl_selections_tbl(rowcount).description;
x_clvl_selections_tbl(rowcount).display_name := l_clvl_selections_tbl(rowcount).display_name;
x_clvl_selections_tbl(rowcount).clvl_id := l_clvl_selections_tbl(rowcount).clvl_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_clvl_selections_tbl(rowcount).clvl_name;
x_clvl_selections_tbl(rowcount).lse_id := l_clvl_selections_tbl(rowcount).lse_id;
x_clvl_selections_tbl(rowcount).lse_name := l_clvl_selections_tbl(rowcount).lse_name;
get_site_selections(p_clvl_filter_rec,
l_clvl_selections_tbl);
FOR rowcount IN 1 .. l_clvl_selections_tbl.COUNT LOOP
x_clvl_selections_tbl(rowcount).rec_no := l_clvl_selections_tbl(rowcount).rec_no;
x_clvl_selections_tbl(rowcount).rec_name := l_clvl_selections_tbl(rowcount).rec_name;
x_clvl_selections_tbl(rowcount).rec_type := l_clvl_selections_tbl(rowcount).rec_type;
x_clvl_selections_tbl(rowcount).id1 := l_clvl_selections_tbl(rowcount).id1;
x_clvl_selections_tbl(rowcount).name := l_clvl_selections_tbl(rowcount).name;
x_clvl_selections_tbl(rowcount).id2 := l_clvl_selections_tbl(rowcount).id2;
x_clvl_selections_tbl(rowcount).Party_id := l_clvl_selections_tbl(rowcount).party_id;
x_clvl_selections_tbl(rowcount).party_name := l_clvl_selections_tbl(rowcount).party_name;
x_clvl_selections_tbl(rowcount).description := l_clvl_selections_tbl(rowcount).description;
x_clvl_selections_tbl(rowcount).display_name := l_clvl_selections_tbl(rowcount).display_name;
x_clvl_selections_tbl(rowcount).clvl_id := l_clvl_selections_tbl(rowcount).clvl_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_clvl_selections_tbl(rowcount).clvl_name;
x_clvl_selections_tbl(rowcount).lse_id := l_clvl_selections_tbl(rowcount).lse_id;
x_clvl_selections_tbl(rowcount).lse_name := l_clvl_selections_tbl(rowcount).lse_name;
get_system_selections(p_clvl_filter_rec,
l_clvl_selections_tbl);
FOR rowcount IN 1 .. l_clvl_selections_tbl.COUNT LOOP
x_clvl_selections_tbl(rowcount).rec_no := l_clvl_selections_tbl(rowcount).rec_no;
x_clvl_selections_tbl(rowcount).rec_name := l_clvl_selections_tbl(rowcount).rec_name;
x_clvl_selections_tbl(rowcount).rec_type := l_clvl_selections_tbl(rowcount).rec_type;
x_clvl_selections_tbl(rowcount).id1 := l_clvl_selections_tbl(rowcount).id1;
x_clvl_selections_tbl(rowcount).name := l_clvl_selections_tbl(rowcount).name;
x_clvl_selections_tbl(rowcount).id2 := l_clvl_selections_tbl(rowcount).id2;
x_clvl_selections_tbl(rowcount).Party_id := l_clvl_selections_tbl(rowcount).party_id;
x_clvl_selections_tbl(rowcount).party_name := l_clvl_selections_tbl(rowcount).party_name;
x_clvl_selections_tbl(rowcount).description := l_clvl_selections_tbl(rowcount).description;
x_clvl_selections_tbl(rowcount).display_name := l_clvl_selections_tbl(rowcount).display_name;
x_clvl_selections_tbl(rowcount).clvl_id := l_clvl_selections_tbl(rowcount).clvl_id;
x_clvl_selections_tbl(rowcount).clvl_name := l_clvl_selections_tbl(rowcount).clvl_name;
x_clvl_selections_tbl(rowcount).lse_id := l_clvl_selections_tbl(rowcount).lse_id;
x_clvl_selections_tbl(rowcount).lse_name := l_clvl_selections_tbl(rowcount).lse_name;
END GetSelections_other;
SELECT name
FROM OKX_SYSTEM_ITEMS_V
WHERE id1 = p_inventory_item_id
AND TRUNC(SYSDATE) BETWEEN trunc(nvl(start_date_active, SYSDATE)) AND trunc(nvl(end_date_active, SYSDATE))
AND ROWNUM < 2;
SELECT description
FROM OKX_SYSTEM_ITEMS_V
WHERE id1 = p_inventory_item_id
AND TRUNC(SYSDATE) BETWEEN trunc(nvl(start_date_active, SYSDATE)) AND trunc(nvl(end_date_active, SYSDATE))
AND ROWNUM < 2;
SELECT description
FROM OKX_SYSTEM_ITEMS_V
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND TRUNC(SYSDATE) BETWEEN trunc(nvl(start_date_active, SYSDATE)) AND trunc(nvl(end_date_active, SYSDATE))
AND ROWNUM < 2;
SELECT concatenated_segments name
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND TRUNC(SYSDATE) BETWEEN trunc(nvl(start_date_active, SYSDATE)) AND trunc(nvl(end_date_active, SYSDATE))
AND ROWNUM < 2;
SELECT chr_id
, cle_id
, account_class
, code_combination_id
, PERCENT
FROM oks_rev_distributions
WHERE cle_id = p_cle_id;
x_rev_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
x_rev_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
x_rev_tbl(i).last_update_login := OKC_API.G_MISS_NUM;
OKS_REV_DISTR_PUB.insert_Revenue_Distr
(p_api_version => l_api_version
, x_return_status => x_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rdsv_tbl => p_rev_tbl
, x_rdsv_tbl => l_rev_tbl);
SELECT
PERCENT,
chr_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2
FROM OKS_K_SALES_CREDITS
WHERE cle_id = p_cle_id;
x_scrv_tbl(i).last_updated_by := OKC_API.G_MISS_NUM;
x_scrv_tbl(i).last_update_date := OKC_API.G_MISS_DATE;
OKS_SALES_CREDIT_PUB.insert_Sales_credit(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => p_scrv_tbl,
x_scrv_tbl => l_scrv_tbl);
PROCEDURE update_line_item(p_cle_id IN NUMBER,
p_item_id IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2) IS
l_api_version NUMBER := 1.0;
SELECT id
FROM okc_k_items_v
WHERE cle_id = p_cle_id;
OKC_CONTRACT_ITEM_PUB.update_contract_item(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_rec => l_cimv_rec_in,
x_cimv_rec => l_cimv_rec_out);
END update_line_item;
SELECT COUNT( * )
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 35, 18, 25);
SELECT id, price_negotiated
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 35, 18, 25);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
x_return_status => x_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
SELECT inv_rule_id
FROM okc_k_lines_b
WHERE id = p_id;
SELECT id
, chr_id
, cle_id
, dnz_chr_id
, sequence_no
, uom_code
, start_date
, end_date
, level_periods
, uom_per_period
, advance_periods
, level_amount
, invoice_offset_days
, interface_offset_days
, comments
, due_arr_yn
, amount
, lines_detailed_yn
FROM oks_stream_levels_b
WHERE cle_id = p_cle_id;
SELECT billing_schedule_type
FROM OKS_K_LINES_V
WHERE cle_id = p_cle_id;
SELECT COVERAGE_SCHEDULE_ID
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = nvl(l_org_id, -99) ;
SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id IN(2, 20);
PROCEDURE UpdateIRTRule(p_chr_id IN NUMBER
, p_cle_id IN NUMBER
, p_invoice_text IN VARCHAR2
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_tax_value OUT NOCOPY NUMBER
, x_AMOUNT_INCLUDES_TAX_FLAG OUT NOCOPY VARCHAR2
, x_total OUT NOCOPY NUMBER)
IS
l_khrv_tbl OKS_CONTRACT_HDR_PUB.khrv_tbl_type;
SELECT id, object_version_number --BUG#4066428 hkamdar 01/21/05 added object_version_number
FROM OKS_K_HEADERS_V
WHERE chr_id = p_chr_id;
SELECT id, object_version_number
FROM OKS_K_LINES_V
WHERE dnz_chr_id = p_dnz_chr_id
AND cle_id = p_cle_id;
SELECT cle.id, SUM(kln.tax_amount)
FROM OKS_K_LINES_V kln, OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.cle_id IS NULL
AND kln.cle_id = cle.id;
SELECT MAX(kln.tax_amount)
FROM OKS_K_LINES_V kln, OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.cle_id = p_cle_id
AND kln.cle_id = cle.id;
SELECT nvl(SUM(nvl(tax_amount, 0)), 0)
FROM okc_k_lines_b cle, oks_k_lines_b sle
WHERE cle.cle_id = p_cle_id
AND cle.lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
AND cle.id = sle.cle_id
AND cle.dnz_chr_id = sle.dnz_chr_id
AND cle.date_cancelled IS NULL;
SELECT nvl(SUM(nvl(tax_amount, 0)), 0)
FROM okc_k_lines_b cle, oks_k_lines_b sle
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id IN (1, 12, 14, 19, 46)
AND cle.cle_id IS NULL
AND cle.id = sle.cle_id
AND cle.dnz_chr_id = sle.dnz_chr_id;
OKS_CONTRACT_LINE_PUB.update_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl,
x_klnv_tbl => lx_klnv_tbl,
p_validate_yn => l_validate_yn);
OKS_CONTRACT_HDR_PUB.update_header(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_tbl => l_khrv_tbl,
x_khrv_tbl => lx_khrv_tbl,
p_validate_yn => l_validate_yn);
END UpdateIRTRule;
PROCEDURE UpdateIRTRule_Subline(p_cle_id IN NUMBER
, p_item_desc IN VARCHAR2
, p_start_date IN DATE
, p_end_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_klnv_tbl_in OKS_CONTRACT_LINE_PUB.klnv_tbl_type;
SELECT cle.lse_id
, cle.id line_id
, cle.start_date
, cle.end_date
, cle.price_negotiated amt
, cle.dnz_chr_id chr_id
, kln.id kln_id
, kln.object_version_number
FROM okc_k_lines_b cle,
oks_k_lines_b kln
WHERE cle.cle_id = p_cle_id
AND kln.cle_id = cle.id
AND lse_id NOT IN (2, 15, 20, 46);
SELECT jtot_object1_code
, object1_id1
, object1_id2
, number_of_items
FROM okc_k_items
WHERE cle_id = p_cle_id;
oks_contract_line_pub.update_line (
p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => lx_klnv_tbl,
p_validate_yn => l_validate_yn) ;
END UpdateIRTRule_Subline;
PROCEDURE update_header_amount(p_cle_id IN NUMBER,
x_status OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT nvl(SUM(price_negotiated), 0) SUM
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND lse_id IN (7, 8, 9, 10, 11, 35, 13, 18, 25)
AND date_cancelled IS NULL; --ignore cancelled sublines
okc_contract_pub.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out );
END update_header_amount;
SELECT dnz_chr_id, start_date, end_date, object_version_number, lse_id
FROM okc_k_lines_b
WHERE id = p_source_rec.cle_id;
SELECT contract_number_modifier
FROM okc_k_headers_b
WHERE id = p_chr_id;
l_select_renewal_flag BOOLEAN;
SELECT id
FROM okc_rule_groups_b
WHERE cle_id = p_cle_id; */
SELECT object1_id1, object1_id2, jtot_object1_code
FROM okc_k_items
WHERE cle_id = p_cle_id;
SELECT nvl(SUM(price_negotiated), 0) amt
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND date_cancelled IS NULL; ---Added condition to exclude cancelled sublines
SELECT nvl(SUM(price_negotiated), 0) amt
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND date_cancelled IS NULL; ---Added condition to exclude cancelled lines
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT SUM(nvl(cancelled_amount, 0))
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
SELECT nvl(SUM(nvl(price_negotiated, 0)), 0) --BUG FIX 4758886 --Forced to return this query 0 value
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND date_cancelled IS NOT NULL; --Condition to consider only the cancelled sublines
SELECT pm_program_id,pm_conf_req_yn,pm_sch_exists_yn
FROM oks_k_lines_b WHERE cle_id=p_covtemp_id;
SELECT start_date,end_date
FROM okc_k_lines_b WHERE id=p_cle_id;
SELECT nvl(MAX(to_number(line_number)), 0)
INTO l_top_line_number
FROM OKC_K_LINES_B
WHERE dnz_chr_id = g_chr_id
AND cle_id IS NULL;
UPDATE okc_k_lines_b SET line_number = l_top_line_number
WHERE id = l_target_tbl(idx).cle_id;
update_line_item(l_target_tbl(idx).cle_id, l_target_tbl(idx).item_id, l_return_status);
select_renewal_info
(p_chr_id => g_chr_id,
x_operation_lines_tbl => l_operation_lines_tbl
);
l_select_renewal_flag := TRUE;
l_select_renewal_flag := FALSE;
IF l_select_renewal_flag
THEN
IF l_operation_lines_tbl.COUNT > 0
THEN
update_renewal_info
(p_operation_lines_tbl => l_operation_lines_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE oks_k_lines_b
SET coverage_id = l_covtemp_id,
standard_cov_yn = 'Y',
pm_program_id =l_pm_prg_id ,
pm_conf_req_yn =l_pm_conf_req ,
pm_sch_exists_yn =l_pm_sch_exists
WHERE cle_id = l_target_tbl(idx).cle_id;
UpdateIRTRule_Subline(p_cle_id => l_target_tbl(idx).cle_id
, p_item_desc => l_target_tbl(idx).item_desc
, p_start_date => l_start_date
, p_end_date => l_end_date
, x_return_status => l_return_status);
UpdateIRTRule(p_chr_id => g_chr_id
, p_cle_id => l_target_tbl(idx).cle_id --LLC ealier: p_source_rec.cle_id
, p_invoice_text => GetFormattedInvoiceText
(l_target_tbl(idx).item_desc, l_start_date, l_end_date)
, p_api_version => 1.0
, p_init_msg_list => l_init_msg_list
, x_return_status => l_return_status
, x_tax_value => l_tax_value
, x_AMOUNT_INCLUDES_TAX_FLAG => l_AMOUNT_INCLUDES_TAX_FLAG
, x_total => l_total);
UPDATE okc_k_lines_b
SET price_negotiated = l_curr_price_negotiated_amt,
cancelled_amount = l_curr_cancelled_amt
WHERE id = l_target_tbl(idx).cle_id;
UPDATE okc_k_headers_b
SET estimated_amount = l_curr_sum_negotiated_amt,
cancelled_amount = l_curr_sum_cancelled_amt
WHERE id = g_chr_id;
update_header_amount(p_source_rec.cle_id, l_return_status);
PROCEDURE Update_Contact_Points
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
P_commit IN VARCHAR2,
P_contact_point_rec IN contact_point_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_object_Version_number NUMBER := 1 ;
SELECT last_update_date, object_version_number, contact_point_type
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = P_contact_point_rec.contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point (
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => l_contact_point_rec,
p_email_rec => l_email_rec_type,
p_phone_rec => l_phone_rec_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_version_number => l_object_version_number
);
END Update_Contact_Points;
SELECT id
FROM okc_class_operations
WHERE CLS_CODE = 'SERVICE'
AND OPN_CODE = 'SPLIT';
SELECT object1_id1, object1_id2, dnz_chr_id, uom_code
FROM okc_k_items
WHERE cle_id = p_cle_id;
SELECT party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = p_party_id
AND identifying_address_flag = 'Y';
SELECT cust_acct_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT party_id
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = p_cust_acct_id;
SELECT revision_qty_control_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT revision
FROM MTL_ITEM_REVISIONS_VL
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT ship_to_site_use_id, bill_to_site_use_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT party_site_id
FROM okx_cust_site_uses_v
WHERE id1 = p_shipto_or_billto ;
/* UPDATE csi_item_instances
SET owner_party_source_table = 'OKC_K_ITEMS_B',
security_group_id = p_cle_id
WHERE instance_id = p_instance_rec.instance_id;
PROCEDURE DELETE_CII_FOR_SUBSCRIPTION
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_instance_id IN NUMBER
)
IS
BEGIN
NULL;
SELECT FROM_TABLE, WHERE_CLAUSE
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_object_code;
SELECT description
FROM okx_cust_sites_v
WHERE id1 = p_cust_acct_site_id;
l_sql_stmt := 'SELECT name, cust_acct_site_id FROM ' || l_from_table ||
' WHERE ID1 = :id_1 AND ID2 = :id2';
PROCEDURE select_renewal_info
(p_chr_id IN NUMBER,
x_operation_lines_tbl OUT NOCOPY opn_lines_tbl
)
IS
--select operation id for renewal of 'SERVICE'
CURSOR class_operation_cur IS
SELECT id
FROM okc_class_operations
WHERE opn_code = 'RENEWAL'
AND cls_code = 'SERVICE';
SELECT id
FROM okc_operation_instances
WHERE target_chr_id = p_chr_id
AND cop_id = p_cop_id;
SELECT creation_date, subject_chr_id, object_chr_id,
subject_cle_id, object_cle_id
FROM okc_operation_lines
WHERE oie_id = p_oie_id;
x_operation_lines_tbl.DELETE;
END select_renewal_info;
PROCEDURE update_renewal_info
(p_operation_lines_tbl IN opn_lines_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF p_operation_lines_tbl.COUNT > 0
THEN
FOR i IN p_operation_lines_tbl.FIRST..p_operation_lines_tbl.LAST
LOOP
IF p_operation_lines_tbl(i).object_cle_id IS NULL
THEN
UPDATE okc_k_headers_b
SET date_renewed = TRUNC(p_operation_lines_tbl(i).creation_date)
WHERE id = p_operation_lines_tbl(i).object_chr_id;
UPDATE okc_k_lines_b
SET date_renewed = TRUNC(p_operation_lines_tbl(i).creation_date)
WHERE id = p_operation_lines_tbl(i).object_cle_id;
END update_renewal_info;
SELECT cle_id, line_number
FROM okc_k_lines_b
WHERE id = c_cle_id;
SELECT 'x'
FROM okc_price_adjustments
WHERE list_line_id = c_list_line_id
AND cle_id = c_cle_id;
SELECT 'x'
FROM okc_price_adjustments
WHERE list_line_id = c_list_line_id
AND chr_id = c_chr_id;
SELECT id, line_number
FROM okc_k_lines_b
WHERE cle_id = c_cle_id
AND lse_id IN(9, 25);
PROCEDURE Update_Line_Amount
(p_line_id IN NUMBER,
p_new_service_amount IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_clev_rec OKC_CONTRACT_PUB.clev_rec_type;
SELECT nvl(SUM(nvl(price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE cle_id = p_line_id
AND date_cancelled IS NULL; -- line Level Cancellation
SELECT SUM(nvl(tax_amount, 0)) amount
FROM okc_k_lines_b cle, oks_k_lines_b kln
WHERE cle.cle_id = p_cle_id
AND cle.id = kln.cle_id
AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
AND cle.date_cancelled IS NULL;
SELECT id, object_version_number, dnz_chr_id
FROM oks_k_lines_b
WHERE cle_id = p_cle_id ;
OKC_CONTRACT_PUB.Update_Contract_Line
(
p_api_version => l_api_version,
p_init_msg_lISt => l_init_msg_lISt,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => 'F',
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
oks_contract_line_pub.update_line
(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N'
);
END Update_Line_Amount;
PROCEDURE Update_Coverage_Levels
(p_clvl_rec IN Clvl_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_clev_rec OKC_CONTRACT_PUB.clev_rec_type;
OKC_CONTRACT_PUB.Update_Contract_Line
(
p_api_version => l_api_version,
p_init_msg_lISt => l_init_msg_lISt,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => 'F',
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
END Update_Coverage_Levels;
PROCEDURE UPDATE_CONTRACT_AMOUNT(p_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT nvl(SUM(price_negotiated), 0) SUM
FROM okc_k_lines_b
WHERE dnz_chr_id = p_header_id
AND cle_id IS NULL
AND date_cancelled IS NULL; -- line Level Cancellation
SELECT SUM(kln.tax_amount) amount
FROM okc_k_lines_b cle, oks_k_lines_b kln
WHERE cle.dnz_chr_id = p_header_id
AND cle.id = kln.cle_id
AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
AND cle.date_cancelled IS NULL;
SELECT id, object_version_number
FROM OKS_K_HEADERS_B
WHERE chr_id = p_header_id ;
l_chrv_tbl_in.DELETE;
okc_contract_pub.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out
);
oks_contract_hdr_pub.update_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_tbl => l_khrv_tbl_type_in,
x_khrv_tbl => l_khrv_tbl_type_out,
p_validate_yn => 'N');
END UPDATE_CONTRACT_AMOUNT;
SELECT NVL(lines.price_negotiated, 0) price_negotiated,
lines.start_date,
lines.end_date,
lines.date_terminated,
lines.price_unit,
lines.price_unit_percent,
lines.id
FROM okc_k_lines_b lines
WHERE lines.cle_id = p_contract_line_id
AND lines.lse_id IN(7, 8, 9, 10, 11, 18, 25, 35);
SELECT nvl(SUM(NVL(price_negotiated, 0)), 0) service_amount
, COUNT(price_negotiated)
FROM okc_k_lines_b lines
WHERE lines.cle_id = p_contract_line_id
AND lines.lse_id IN(7, 8, 9, 10, 11, 18, 25, 35)
AND lines.date_cancelled IS NULL; -- line Level Cancellation
SELECT okc.id,
price_negotiated,
price_unit,
price_unit_percent,
lse_id,
okc.dnz_chr_id,
currency_code,
oks.id oks_id,
oks.object_version_number
FROM okc_k_lines_b okc, oks_k_lines_b oks
WHERE okc.Cle_id = p_contract_line_id
AND OKS.cle_id = okc.id
AND (okc.date_terminated IS NULL OR okc.date_terminated > TRUNC(SYSDATE))
AND lse_id NOT IN(2, 15, 20)
AND okc.date_cancelled IS NULL; -- line Level Cancellation
SELECT okc.id,
price_negotiated,
price_unit,
price_unit_percent,
lse_id,
okc.dnz_chr_id,
currency_code,
oks.id oks_id,
oks.object_version_number
FROM okc_k_lines_b okc, oks_k_lines_b oks
WHERE okc.Cle_id = p_contract_line_id
AND OKS.cle_id = okc.id
AND (okc.date_terminated IS NULL OR okc.date_terminated > TRUNC(SYSDATE))
AND lse_id NOT IN(2, 15, 20)
AND Nvl(price_negotiated, 0) <> 0
ORDER BY To_Number(line_number) DESC;
SELECT id, object_version_number
FROM oks_k_lines_b
WHERE cle_id = p_cle_id;
SELECT Isa_Agreement_Id
FROM OKC_GOVERNANCES_V
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_contract_line_id;
Update_Coverage_Levels
(p_clvl_rec => l_clvl_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* select price_negotiated into cp_price
from okc_k_lines_b
where id = clvl_rec.id; */
OKS_CONTRACT_LINE_PUB.update_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl,
x_klnv_tbl => lx_klnv_tbl,
p_validate_yn => l_validate_yn);
END IF; /* Calculate tax - update IRT rule for non cov item , cov prod */
Update_Coverage_Levels(p_clvl_rec => l_clvl_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/*** Calculate tax - update IRT rule for residual value **/
-- Bug 5228352 --
IF l_old_lse_id IN (8, 10, 11, 12, 35 )
OR ( l_old_lse_id IN (7, 9, 18, 25)
AND
NVL(fnd_profile.VALUE('OKS_USE_QP_FOR_MANUAL_ADJ'), 'NO') = 'NO')
THEN
-- Bug 5228352 --
-- --------errorout_ad(' before calculating tax ');
OKS_CONTRACT_LINE_PUB.update_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl,
x_klnv_tbl => lx_klnv_tbl,
p_validate_yn => l_validate_yn);
/* oks_qp_int_pvt.UPDATE_RULE ( p_rule_rec => l_rule_rec,
p_line_id => l_old_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END IF; /* Calculate tax - update IRT rule for non cov item , cov prod */
UPDATE_LINE_AMOUNT(p_contract_line_id,
p_new_service_price,
x_return_status,
x_msg_count,
x_msg_data );
UPDATE_CONTRACT_AMOUNT(p_header_id => Get_Header_Id,
x_return_status => x_return_status);
PROCEDURE update_quantity(p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR get_all_instances_csr IS
SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id IN (9, 25);
SELECT object1_id1, number_of_items
FROM okc_k_items
WHERE cle_id = p_item_cle_id;
SELECT cii.quantity
FROM CSI_ITEM_INSTANCES CII
WHERE instance_id = TO_NUMBER(p_object1_id1);
UPDATE okc_k_items
SET number_of_items = get_csi_item_qty_rec.quantity
WHERE cle_id = get_all_instances_rec.id;
END update_quantity;
PROCEDURE update_person (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_person_tbl IN PERSON_TBL_TYPE,
p_party_object_version_number IN NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_person_rec HZ_PARTY_V2PUB.person_rec_type ;
HZ_PARTY_V2PUB.update_person(
p_init_msg_list => l_init_msg_list,
p_person_rec => l_person_rec,
p_party_object_version_number => l_party_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_org_contact (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_org_contact_tbl IN ORG_CONTACT_TBL_TYPE,
p_relationship_tbl_type IN relationship_tbl_type,
p_cont_object_version_number IN OUT NOCOPY NUMBER,
p_rel_object_version_number IN OUT NOCOPY NUMBER,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
HZ_PARTY_CONTACT_V2PUB.update_org_contact(
p_init_msg_list => l_init_msg_list,
p_org_contact_rec => org_contact_rec,
p_cont_object_version_number => l_cont_object_version_number,
p_rel_object_version_number => l_rel_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_party_site (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_party_site_tbl IN PARTY_SITE_TBL_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
party_site_rec hz_party_site_v2pub.PARTY_SITE_REC_TYPE;
HZ_PARTY_SITE_V2PUB.update_party_site(
p_init_msg_list => l_init_msg_list,
p_party_site_rec => party_site_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_cust_account_role (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_cust_account_role_tbl IN CUST_ACCOUNT_ROLE_tbl_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
Hz_cust_account_role_v2pub.update_cust_account_role(
p_init_msg_list => l_init_msg_list,
p_cust_account_role_rec => cust_account_role_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_cust_acct_site (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_cust_acct_site_tbl IN CUST_ACCT_SITE_TBL_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
Hz_cust_account_site_v2pub.update_cust_acct_site(
p_init_msg_list => l_init_msg_list,
p_cust_acct_site_rec => cust_acct_site_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT 'Y' FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_contract_hdr_id
AND id = p_contract_line_id
AND(date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
AND date_cancelled IS NULL
AND end_date > NVL(oks_bill_util_pub.get_billed_upto(id, p_intent), end_date - 1);
SELECT ID, LSE_ID FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_contract_hdr_id
AND cle_id IS NULL
AND(date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
AND date_cancelled IS NULL
AND end_date > NVL(oks_bill_util_pub.get_billed_upto(id, 'T'), end_date - 1);
SELECT locked_price_list_id,
locked_price_list_line_id
FROM OKS_k_LINES_B
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id ;
UPDATE okc_k_lines_b
SET price_list_id = p_price_list_id
WHERE dnz_chr_id = p_contract_hdr_id
AND cle_id IS NULL
AND ID = CUR_REC.id;
oks_qp_pkg.delete_locked_pricebreaks(l_api_version,
l_source_price_list_line_id,
l_init_msg_list,
l_return_status,
l_msg_count,
l_msg_data);
UPDATE OKS_K_LINES_B SET
locked_price_list_id = NULL,
locked_price_list_line_id = NULL
WHERE dnz_chr_id = p_contract_hdr_id
AND cle_id = cur_rec.id ;
It updates amount for a topline if and only if topline amount
does not euqal to the sum of subline amounts. Similarly it
updates the header amount if and only if, the header amount does
not equal the sum of line amounts.
This avoids unncessary updates to lines and prevents record locking
when multiple users access the same contract. The example given below
illustrates record locking in authoring and how it can be avoided in most cases
User 1 Action User 2 Action
Time A Open Contract K1 Open same Contract K1
Time B Modify contract header Modify topline 1
-Header record locked -Top line record locked.
-No amount changes required. -No amount changes required.
Time C Try to save contract K1 No action
-Application hangs as -User 2 continues to hold lock
-unconditional update of topline -on topline 1.
-amount waits for lock on topline
-1 to be released by User 2.
-Header and contract version number
-updated.
Time D No action Try to save contract K1
-Deadlock detected
-User 1 is waiting for lock on
-topline 1 held by user 2.
-User 2 is waiting for lock on
-version number held by user 1.
To avoid, the above record locking and deadlock, we one needs to
1. Conditionally update the topline amount and header amount. Do not
update if amounts are in sync whith child entities.
2. Do not update contract version as part of header/line or any other
enitity update. Defer contract version update till all other updates
are successful.
Please note that if two users attempt to modify the same line or same entity
in a contract, record locking cannot be and should not be prevented.
*/
PROCEDURE CHECK_UPDATE_AMOUNTS
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_chr_id IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_UPDATE_AMOUNTS';
SELECT a.id, nvl(a.price_negotiated, 0) topline_amt,
SUM(nvl(b.price_negotiated,0)) sum_subline_amt,
nvl(c.tax_amount,0) topline_tax_amt,
SUM(nvl(d.tax_amount,0)) sum_subline_tax_amt
FROM okc_k_lines_b a, okc_k_lines_b b,
oks_k_lines_b c, oks_k_lines_b d
WHERE a.dnz_chr_id = cp_chr_id AND a.lse_id IN (1,12,14,19)
AND b.dnz_chr_id = a.dnz_chr_id AND b.cle_id = a.id
AND a.id=c.cle_id AND b.id=d.cle_id
AND b.lse_id IN (7,8,9,10,11,35,13,18,25)
AND a.date_cancelled is null
AND b.date_cancelled is null
GROUP BY a.id, a.price_negotiated,c.tax_amount
HAVING nvl(a.price_negotiated, 0) <> SUM(nvl(b.price_negotiated,0))
OR nvl(c.tax_amount, 0) <> SUM(nvl(d.tax_amount,0));
SELECT a.id, nvl(a.price_negotiated, 0) topline_amt,
0 sum_subline_amt,
nvl(c.tax_amount,0) topline_tax_amt,
0 sum_subline_tax_amt
FROM okc_k_lines_b a,
oks_k_lines_b c
WHERE a.dnz_chr_id = cp_chr_id
AND a.lse_id IN (1,12,14,19)
AND a.id=c.cle_id
AND a.date_cancelled is null
and not exists( select 1 from okc_k_lines_b b
WHERE b.lse_id IN (7,8,9,10,11,35,13,18,25)
AND b.date_cancelled is null
AND b.dnz_chr_id=a.dnz_chr_id
AND b.cle_id=a.id);
SELECT price_negotiated
FROM okc_k_lines_b WHERE id = cp_id
FOR UPDATE OF price_negotiated NOWAIT;
SELECT substr(RTRIM(RTRIM(line_number) || ', ' || RTRIM(lsev.name) || ' ' || RTRIM(clev.name)), 1, 2000) line_name
FROM okc_line_styles_v lsev, okc_k_lines_v clev
WHERE lsev.id = clev.lse_id
AND clev.id = cp_id;
SELECT a.contract_number ||' '|| a.contract_number_modifier,
nvl(a.estimated_amount, 0) hdr_amt,
SUM(nvl(b.price_negotiated,0)) sum_tl_line_amt,
nvl(c.tax_amount,0) hdr_tax_amt,
SUM(nvl(d.tax_amount,0)) sum_tl_line_amt_tax
FROM okc_k_headers_all_b a, okc_k_lines_b b,
oks_k_headers_b c, oks_k_lines_b d
WHERE a.id = cp_chr_id
AND b.dnz_chr_id = a.id AND b.cle_id IS NULL
AND d.dnz_chr_id = c.chr_id AND d.cle_id=b.id
AND b.date_cancelled is null
AND b.lse_id IN (1,12,14,19,46)
GROUP BY a.contract_number, a.contract_number_modifier, a.estimated_amount,c.tax_amount;
SELECT a.contract_number ||' '|| a.contract_number_modifier,
nvl(a.estimated_amount, 0) hdr_amt,
0 sum_tl_line_amt,
nvl(c.tax_amount,0) hdr_tax_amt,
0 sum_tl_line_amt_tax
FROM okc_k_headers_all_b a,
oks_k_headers_b c
WHERE a.id = cp_chr_id
AND c.chr_id = a.id
AND NOT EXISTS (SELECT 1 from okc_k_lines_b b
WHERE b.dnz_chr_id = a.id
AND b.cle_id IS NULL
AND b.date_cancelled is null
AND b.lse_id IN (1,12,14,19,46));
SELECT estimated_amount
FROM okc_k_headers_all_b WHERE id = cp_chr_id
FOR UPDATE OF estimated_amount NOWAIT;
SELECT a.id, nvl(a.cancelled_amount, 0) topline_canceled_amt, /*modified for bug:6765336*/
SUM(nvl(b.cancelled_amount,0)) sum_subline_cancelled_amt
FROM okc_k_lines_b a, okc_k_lines_b b
WHERE a.dnz_chr_id = cp_chr_id AND a.lse_id IN (1,12,14,19)
AND b.dnz_chr_id = a.dnz_chr_id AND b.cle_id = a.id
AND a.date_cancelled is null
AND b.date_cancelled is not null
GROUP BY a.id, a.cancelled_amount
HAVING nvl(a.cancelled_amount, 0) <> SUM(nvl(b.cancelled_amount,0)); /*modified for bug:6765336*/
SELECT
nvl(a.cancelled_amount, 0) hdr_cancelled_amt,
SUM(nvl(b.cancelled_amount,0)) sum_tl_line_cancelled_amt
FROM okc_k_headers_all_b a, okc_k_lines_b b
WHERE a.id = cp_chr_id
AND b.dnz_chr_id = a.id AND b.cle_id IS NULL
AND b.lse_id IN (1,12,14,19,46)
GROUP BY a.cancelled_amount;
SAVEPOINT check_update_amounts_PVT;
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
l_tl_amt_tax_tbl.delete;
l_sl_amt_tax_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'able to lock all fetched toplines - updating amount');
UPDATE okc_k_lines_b
SET price_negotiated = l_sl_amt_tbl(j)
WHERE id = l_tl_id_tbl(j);
UPDATE oks_k_lines_b
SET tax_amount = l_sl_amt_tax_tbl(j)
WHERE cle_id = l_tl_id_tbl(j);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'amounts updated');
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
l_tl_amt_tax_tbl.delete;
l_sl_amt_tax_tbl.delete;
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
l_tl_amt_tax_tbl.delete;
l_sl_amt_tax_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'able to lock all fetched toplines - updating amount');
UPDATE okc_k_lines_b
SET price_negotiated = l_sl_amt_tbl(j)
WHERE id = l_tl_id_tbl(j);
UPDATE oks_k_lines_b
SET tax_amount = l_sl_amt_tax_tbl(j)
WHERE cle_id = l_tl_id_tbl(j);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'amounts updated');
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
l_tl_amt_tax_tbl.delete;
l_sl_amt_tax_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_hdr', 'db hdr amt='||l_hdr_amt||' , sum of toplines='||l_tl_amt);
UPDATE okc_k_headers_all_b
SET estimated_amount = l_tl_amt
WHERE id = p_chr_id;
UPDATE oks_k_headers_b
SET tax_amount = l_tl_amt_tax
where chr_id = p_chr_id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_hdr', 'db hdr amt='||l_hdr_amt||' , sum of toplines='||l_tl_amt);
--update the header only if required
IF (l_hdr_amt <> l_tl_amt) OR(l_hdr_amt_tax <> l_tl_amt_tax) THEN
--before updating the header amount, first try to obtain a lock on the header
--if the lock fails, another user is holding the lock, exit with appropriate
--error message. If the lock succeeds, update the header amount
BEGIN
IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.lock_hdr', 'checking lock for header id='||p_chr_id);
UPDATE okc_k_headers_all_b
SET estimated_amount = l_tl_amt
WHERE id = p_chr_id;
UPDATE oks_k_headers_b
SET tax_amount = l_tl_amt_tax
where chr_id = p_chr_id;
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'able to lock all fetched toplines - updating canceled amount');
update okc_k_lines_b
set cancelled_amount =l_sl_amt_tbl(j)
where id=l_tl_id_tbl(j);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_line', 'cancelled amounts updated');
l_tl_id_tbl.delete;
l_tl_amt_tbl.delete;
l_sl_amt_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_header', 'to update header cancelled amount not equal to sum of toplines.');
update okc_k_headers_all_b
set cancelled_amount = l_topline_cancelled
where id=p_chr_id;
ROLLBACK TO check_update_amounts_PVT;
ROLLBACK TO check_update_amounts_PVT;
ROLLBACK TO check_update_amounts_PVT;
END CHECK_UPDATE_AMOUNTS;
SELECT counter_value_id
FROM CSI_COUNTER_READINGS
WHERE counter_id = p_counter_id
AND value_timestamp in
(select max(value_timestamp) from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and disabled_flag='N');
SELECT max(request_id)
FROM fnd_concurrent_requests
WHERE program_application_id = l_prog_appl_id
AND concurrent_program_id = l_prg_id
AND argument2=to_char(l_chr_id)
AND phase_code NOT IN ('C'); --- IN ('P','R'); SKUCHIMA
SELECT concurrent_program_id, p.application_id
INTO program_id, prog_appl_id
FROM fnd_concurrent_programs P,
fnd_application A
WHERE concurrent_program_name = 'OKSREPCON'
AND P.application_id = A.Application_ID
AND A.application_short_name = 'OKS';
SELECT max(request_id)
FROM fnd_concurrent_requests
WHERE program_application_id = l_prog_appl_id
AND concurrent_program_id = l_prg_id
AND argument1=to_char(l_chr_id)
AND phase_code NOT IN ('C');
SELECT concurrent_program_id, p.application_id
INTO program_id, prog_appl_id
FROM fnd_concurrent_programs P,
fnd_application A
WHERE concurrent_program_name = 'OKSCASCR'
AND P.application_id = A.Application_ID
AND A.application_short_name = 'OKS';
SELECT ID, start_date, end_date, lse_id
FROM okc_k_lines_b
WHERE chr_id = p_chr_id
AND lse_id IN (1, 14, 19, 46)
AND date_cancelled IS NULL
AND date_terminated IS NULL
ORDER BY line_number;
SELECT ID, start_date, end_date, lse_id ,price_negotiated
FROM okc_k_lines_b
WHERE cle_id = p_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
AND date_cancelled IS NULL
AND date_terminated IS NULL
ORDER BY line_number;
SELECT SUM(price_negotiated) amount
FROM okc_k_lines_b
WHERE cle_id = p_line_id
AND chr_id IS NULL
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
AND date_cancelled IS NULL;
SELECT SUM(price_negotiated) amount
FROM okc_k_lines_b
WHERE chr_id = p_chr_id
AND cle_id IS NULL
AND date_cancelled IS NULL;
SELECT ID, object_version_number
FROM okc_k_headers_all_b
WHERE ID = p_id;
SELECT ID, object_version_number FROM okc_k_lines_b WHERE ID = p_id;
SELECT ID, object_version_number
FROM oks_k_headers_b
WHERE chr_id = p_id;
SELECT ID, object_version_number
FROM oks_k_lines_b
WHERE cle_id = p_id;
SELECT Nvl(conversion_rate, 1)
INTO l_old_conversion_rate
FROM okc_k_headers_all_b
WHERE id = p_chr_id;*/
UPDATE okc_k_lines_b okl
SET okl.price_negotiated = ((okl.price_negotiated *
l_old_conversion_rate) /
Nvl(p_new_conversion_rate, 1))
WHERE okl.id = l_topline_rec.id;
okc_contract_pub.update_contract_line(p_api_version => l_api_version,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_rec => l_clev_rec_in,
x_clev_rec => l_clev_rec_out);
UPDATE okc_k_lines_b okl
SET okl.price_negotiated = ((okl.price_negotiated *
l_old_conversion_rate) /
Nvl(p_new_conversion_rate, 1))
WHERE okl.id = l_subline_rec.id;
okc_contract_pub.update_contract_line(p_api_version => l_api_version,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_rec => l_clev_rec_in,
x_clev_rec => l_clev_rec_out);
okc_contract_pub.update_contract_header(p_api_version => l_api_version,
p_init_msg_list => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_false,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
| Purpose: Check to see if there is any active request runnning in the background for Mass Update
|
| In Parameters: p_chr_id the contract id
| Out Parameters: x_return_status standard return status
|
+============================================================================*/
PROCEDURE check_massupd_request(
p_chr_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_MASSUPD_REQUEST';
This will check whether there is any active mass update request running in the background.
If mass update request is running ,it will issue an error message
*/
Program_ID NUMBER;
SELECT max(request_id)
FROM fnd_concurrent_requests
WHERE program_application_id = l_prog_appl_id
AND concurrent_program_id = l_prg_id
AND argument1=to_char(l_chr_id)
AND phase_code NOT IN ('C');
SELECT concurrent_program_id, p.application_id
INTO program_id, prog_appl_id
FROM fnd_concurrent_programs P,
fnd_application A
WHERE concurrent_program_name = 'OKSLAUNCHMASSUPD'
AND P.application_id = A.Application_ID
AND A.application_short_name = 'OKS';