[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure to insert Price Book related error and warning messages into
qp_price_book_messages table.
*****************************************************************************/
PROCEDURE Insert_Price_Book_Messages (
p_price_book_messages_tbl IN price_book_messages_tbl)
IS
i NUMBER;
INSERT INTO qp_price_book_messages
(message_id,
message_type,
message_code,
message_text,
pb_input_header_id,
price_book_header_id,
price_book_line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_messages_s.nextval,
l_message_type_tbl(i),
l_message_code_tbl(i),
l_message_text_tbl(i),
l_pb_input_header_id_tbl(i),
l_price_book_header_id_tbl(i),
l_price_book_line_id_tbl(i),
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id);
END Insert_Price_Book_Messages;
SELECT list_header_id
INTO x_return
FROM qp_list_headers_vl
WHERE name = p_price_list_name
AND list_type_code = 'PRL'
AND rownum = 1;
SELECT agreement_id
INTO x_return
FROM oe_agreements_vl a
WHERE name = p_agreement_name
AND trunc(nvl(p_pricing_effective_date, sysdate))
between trunc(nvl(a.start_date_active,
p_pricing_effective_date))
and trunc(nvl(a.end_date_active,
p_pricing_effective_date))
AND rownum = 1;
SELECT header_id
INTO x_return
FROM oe_blanket_headers_all
WHERE order_number = p_bsa_name;
SELECT cust_account_id
INTO x_cust_account_id
FROM hz_cust_accounts
WHERE party_id = p_customer_attr_value;
SELECT template_code
INTO p_pb_input_header_rec.pub_template_code
FROM xdo_templates_vl
WHERE template_name = p_pb_input_header_rec.pub_template_name
AND application_short_name = 'QP'
AND rownum = 1;
SELECT customer_id
INTO l_customer_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT customer_id
INTO l_customer_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT party_id
INTO p_pb_input_header_rec.customer_attr_value
FROM hz_cust_accounts
WHERE cust_account_id = p_pb_input_header_rec.cust_account_id;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name =
p_pb_input_header_rec.pricing_perspective_code;
SELECT currency_code
INTO p_pb_input_header_rec.currency_code
FROM qp_list_headers_b
WHERE list_header_id = p_pb_input_header_rec.pl_agr_bsa_id;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name =
p_pb_input_header_rec.pricing_perspective_code;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
UPDATE qp_pb_input_headers_b
SET request_type_code = l_request_type_code
WHERE pb_input_header_id = p_pb_input_header_rec.pb_input_header_id;
SELECT pte_code
INTO l_pte_code
FROM qp_pte_request_types_v
WHERE request_type_code = l_request_type_code;
SELECT customer_id
INTO l_customer_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT 'Y'
INTO l_party_id_match
FROM dual
WHERE EXISTS (
SELECT 'x'
FROM hz_relationships rel, hz_parties party3,
hz_parties party4, hz_parties party5
WHERE rel.party_id = party5.party_id
AND party5.party_type = 'PARTY_RELATIONSHIP'
AND party5.status = 'A'
AND trunc(rel.start_date) <= trunc(sysdate)
AND trunc(nvl(rel.end_date, sysdate)) >= trunc(sysdate)
AND rel.subject_id = party3.party_id
AND party3.party_type = 'PERSON'
AND party3.status = 'A'
AND rel.object_id = party4.party_id
AND party4.party_type = 'ORGANIZATION'
AND party4.status = 'A'
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_id IN
(SELECT party_relationship_id
FROM hz_org_contacts org_con
WHERE rel.relationship_id =
org_con.party_relationship_id
AND org_con.status ='A' )
AND party5.party_id = l_customer_id
AND party4.party_id = p_pb_input_header_rec.customer_attr_value);
SELECT 1
INTO l_count
FROM hz_parties
WHERE party_id = p_pb_input_header_rec.customer_attr_value
AND rownum = 1;
SELECT 1
INTO l_count
FROM hz_cust_accounts
WHERE cust_account_id = p_pb_input_header_rec.cust_account_id
AND party_id = p_pb_input_header_rec.customer_attr_value;
SELECT 1
INTO l_count
FROM qp_price_book_headers_vl
WHERE price_book_name = p_pb_input_header_rec.price_book_name
AND price_book_type_code = 'F'
AND customer_id = p_pb_input_header_rec.customer_attr_value
AND rownum = 1;
SELECT 1
INTO l_count
FROM xdo_templates_vl
WHERE template_code = p_pb_input_header_rec.pub_template_code
AND application_short_name = 'QP'
AND rownum = 1;
SELECT 1
INTO l_count
FROM xdo_lobs tmpl, xdo_templates_vl t
WHERE t.APPLICATION_SHORT_NAME = 'QP'
AND t.TEMPLATE_CODE = tmpl.LOB_CODE
AND tmpl.LOB_TYPE in ('TEMPLATE','MLS_TEMPLATE')
AND tmpl.FILE_STATUS = 'E'
AND t.template_code = p_pb_input_header_rec.pub_template_code
AND lower(tmpl.LANGUAGE) = lower(p_pb_input_header_rec.pub_language)
AND upper(tmpl.TERRITORY) = upper(p_pb_input_header_rec.pub_territory);
SELECT 1
INTO l_count
FROM ecx_tp_headers eth, ecx_tp_details etd,
ecx_ext_processes eep, ecx_transactions et,
hz_parties hp, hz_party_sites hps, hz_locations hl
WHERE eth.party_id = p_pb_input_header_rec.customer_attr_value
AND eth.party_site_id = p_pb_input_header_rec.dlv_xml_site_id
AND eth.tp_header_id = etd.tp_header_id
AND etd.EXT_PROCESS_ID = eep.EXT_PROCESS_ID
AND eth.party_id = hp.party_id
AND eth.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND eep.transaction_id = et.transaction_id
AND et.transaction_type = 'QP'
AND et.transaction_subtype = 'CATSO'
AND eep.direction = 'OUT';
SELECT 1
INTO l_count
FROM qp_price_book_headers_vl
WHERE price_book_name = p_pb_input_header_rec.price_book_name
AND price_book_type_code = p_pb_input_header_rec.price_book_type_code
AND customer_id = p_pb_input_header_rec.customer_attr_value
AND rownum = 1;
SELECT 1
INTO l_count
FROM qp_price_book_headers_vl
WHERE price_book_name = p_pb_input_header_rec.price_book_name
AND price_book_type_code = p_pb_input_header_rec.price_book_type_code
AND customer_id = p_pb_input_header_rec.customer_attr_value
AND rownum = 1;
SELECT 1
INTO l_count2
FROM qp_price_book_headers_all_b b, qp_price_book_headers_tl t
WHERE b.price_book_header_id = t.price_book_header_id
AND t.language = userenv('LANG')
AND b.price_book_type_code = p_pb_input_header_rec.price_book_type_code
AND b.customer_id = p_pb_input_header_rec.customer_attr_value
AND t.price_book_name = p_pb_input_header_rec.price_book_name
AND rownum = 1;
SELECT 1
INTO l_count
FROM mtl_system_items_kfv
WHERE inventory_item_id =
to_number(p_pb_input_header_rec.product_attr_value)
AND organization_id = QP_UTIL.Get_Item_Validation_Org
AND purchasing_enabled_flag =
decode(p_pb_input_header_rec.pricing_perspective_code,
'PO', 'Y', purchasing_enabled_flag)
AND rownum = 1;
SELECT pte_code
INTO l_pte_code
FROM qp_pte_request_types_b
WHERE request_type_code = l_request_type_code;
SELECT 1
INTO l_count
FROM qp_list_headers_vl
WHERE list_type_code = 'PRL'
AND nvl(list_source_code, 'X') <> 'BSO'
AND nvl(active_flag, 'N') = 'Y'
AND (global_flag = 'Y' OR
orig_org_id = p_pb_input_header_rec.org_id)
AND source_system_code IN (SELECT application_short_name
FROM qp_pte_source_systems
WHERE pte_code = l_pte_code)
AND list_header_id = p_pb_input_header_rec.pl_agr_bsa_id
AND rownum = 1;
SELECT 1
INTO l_count
FROM qp_list_headers_vl
WHERE list_type_code = 'PRL'
AND nvl(list_source_code, 'X') <> 'BSO'
AND nvl(active_flag, 'N') = 'Y'
AND (global_flag = 'Y' OR
orig_org_id = p_pb_input_header_rec.org_id)
AND source_system_code IN (SELECT application_short_name
FROM qp_pte_source_systems
WHERE pte_code = l_pte_code)
AND list_header_id = p_pb_input_header_rec.pl_agr_bsa_id
AND rownum = 1;
SELECT 1
INTO l_count
FROM oe_agreements_vl
WHERE agreement_id = p_pb_input_header_rec.pl_agr_bsa_id
AND (sold_to_org_id = -1 OR
sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id =
p_pb_input_header_rec.customer_attr_value
AND cust_account_id =
nvl(p_pb_input_header_rec.cust_account_id, cust_account_id))
)
AND price_list_id IN (SELECT list_header_id
FROM qp_list_headers_vl
WHERE list_type_code IN ('PRL','AGR')
AND nvl(active_flag, 'N') = 'Y'
AND (global_flag = 'Y' OR
orig_org_id = p_pb_input_header_rec.org_id)
AND source_system_code IN
(SELECT application_short_name
FROM qp_pte_source_systems
WHERE pte_code = l_pte_code)
)
AND (trunc(nvl(p_pb_input_header_rec.effective_date, sysdate))
between trunc(nvl(start_date_active,
p_pb_input_header_rec.effective_date))
and trunc(nvl(end_date_active,
p_pb_input_header_rec.effective_date)))
AND rownum = 1;
SELECT 1
INTO l_count
FROM oe_blanket_headers_all a
WHERE a.header_id = p_pb_input_header_rec.pl_agr_bsa_id
AND (a.sold_to_org_id IS NULL OR
a.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id =
p_pb_input_header_rec.customer_attr_value
AND cust_account_id =
nvl(p_pb_input_header_rec.cust_account_id, cust_account_id))
)
AND a.org_id = p_pb_input_header_rec.org_id
AND EXISTS (SELECT 'x'
FROM qp_qualifiers
WHERE qualifier_context = 'ORDER'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
AND qualifier_attr_value = a.header_id)
AND rownum = 1;
SELECT 1
INTO l_count
FROM fnd_currencies_vl
WHERE currency_flag = 'Y'
AND currency_code = p_pb_input_header_rec.currency_code
AND enabled_flag = 'Y'
AND trunc(NVL(start_date_active,
p_pb_input_header_rec.effective_date)
) <= trunc(p_pb_input_header_rec.effective_date)
AND trunc(NVL(end_date_active, p_pb_input_header_rec.effective_date))
>= trunc(p_pb_input_header_rec.effective_date)
AND rownum = 1;
SELECT 1
INTO l_count
FROM qp_prc_contexts_b
WHERE prc_context_code = p_pb_input_lines_tbl(j).context
AND prc_context_type = p_pb_input_lines_tbl(j).attribute_type;
SELECT 1, nvl(user_valueset_id, seeded_valueset_id)
INTO l_count, l_valueset_id
FROM qp_segments_b s, qp_prc_contexts_b c,
qp_pte_segments ps, qp_pte_request_types_b pr
WHERE s.segment_mapping_column = p_pb_input_lines_tbl(j).attribute
AND s.prc_context_id = c.prc_context_id
AND c.prc_context_code = p_pb_input_lines_tbl(j).context
AND c.prc_context_type = p_pb_input_lines_tbl(j).attribute_type
AND c.prc_context_code <> 'ITEM'
AND NOT ((c.prc_context_code = 'CUSTOMER' AND
s.segment_code = 'PARTY_ID') OR
(c.prc_context_code = 'ASOPARTYINFO' AND
s.segment_code = 'CUSTOMER PARTY') OR
(c.prc_context_code = 'CUSTOMER' AND
s.segment_code = 'SOLD_TO_ORG_ID') OR
(c.prc_context_code = 'MODLIST' AND
s.segment_code = 'PRICE_LIST') OR
(c.prc_context_code = 'CUSTOMER' AND
s.segment_code = 'AGREEMENT_NAME') OR
(c.prc_context_code = 'ORDER' AND
s.segment_code = 'BLANKET_NUMBER') OR
(c.prc_context_code = 'ORDER' AND
s.segment_code = 'BLANKET_HEADER_ID')
)
AND s.segment_id = ps.segment_id
AND ps.pte_code = pr.pte_code
AND pr.request_type_code = l_request_type_code
AND (l_pricing_status = 'I' OR
l_pricing_status = 'S' AND
s.availability_in_basic IN ('Y','F')
)
AND (nvl(ps.user_sourcing_method, ps.seeded_sourcing_method) =
'USER ENTERED'
OR
nvl(ps.user_sourcing_method, ps.seeded_sourcing_method) =
'ATTRIBUTE MAPPING'
AND EXISTS (SELECT 'X'
FROM qp_attribute_sourcing a
WHERE a.request_type_code = pr.request_type_code
AND a.segment_id = s.segment_id
AND a.enabled_flag = 'Y'
AND a.attribute_sourcing_level <> 'LINE'
AND nvl(user_value_string,
seeded_value_string)
LIKE pr.order_level_global_struct||'%'
)
) ;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
SELECT *
INTO l_pb_input_header_rec
FROM qp_pb_input_headers_vl
WHERE pb_input_header_id = p_pb_input_header_id;
Insert_Price_Book_Messages (l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
SELECT * BULK COLLECT
INTO l_pb_input_lines_tbl
FROM qp_pb_input_lines
WHERE pb_input_header_id = p_pb_input_header_id;
Procedure to insert Price Book Header info into qp_price_book_headers_b
and _tl tables.
******************************************************************************/
PROCEDURE Insert_Price_Book_Header (
p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
x_price_book_header_id OUT NOCOPY NUMBER)
IS
l_application_id NUMBER;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name =
p_pb_input_header_rec.pricing_perspective_code;
SELECT 1
INTO l_count
FROM qp_price_book_headers_all_b b, qp_price_book_headers_tl t
WHERE b.price_book_header_id = t.price_book_header_id
AND t.language = userenv('LANG')
AND b.price_book_type_code = p_pb_input_header_rec.price_book_type_code
AND b.customer_id = p_pb_input_header_rec.customer_attr_value
AND t.price_book_name = p_pb_input_header_rec.price_book_name
AND rownum = 1;
INSERT INTO qp_price_book_headers_all_b (
price_book_header_id,
price_book_type_code,
currency_code,
effective_date,
org_id,
customer_id,
cust_account_id,
item_category,
price_based_on,
pl_agr_bsa_id,
pricing_perspective_code,
item_quantity,
request_id,
request_type_code,
pb_input_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_headers_all_b_s.nextval,
p_pb_input_header_rec.price_book_type_code,
p_pb_input_header_rec.currency_code,
p_pb_input_header_rec.effective_date,
p_pb_input_header_rec.org_id,
p_pb_input_header_rec.customer_attr_value,
p_pb_input_header_rec.cust_account_id,
decode(p_pb_input_header_rec.product_attribute,
'PRICING_ATTRIBUTE2', p_pb_input_header_rec.product_attr_value,
null),
p_pb_input_header_rec.price_based_on,
p_pb_input_header_rec.pl_agr_bsa_id,
p_pb_input_header_rec.pricing_perspective_code,
p_pb_input_header_rec.item_quantity,
null, --Will be updated with the child request id later
p_pb_input_header_rec.request_type_code,
p_pb_input_header_rec.pb_input_header_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
fnd_global.conc_login_id
) RETURNING price_book_header_id INTO l_price_book_header_id;
INSERT INTO qp_price_book_headers_tl (
price_book_header_id,
price_book_name,
pl_agr_bsa_name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
language,
source_lang
)
SELECT
l_price_book_header_id,
p_pb_input_header_rec.price_book_name,
p_pb_input_header_rec.pl_agr_bsa_name,
sysdate,
l_user_id,
sysdate,
l_user_id,
fnd_global.conc_login_id,
l.language_code,
userenv('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I','B')
AND NOT EXISTS (SELECT NULL
FROM qp_price_book_headers_tl T
WHERE t.price_book_header_id =
l_price_book_header_id
AND t.language = l.language_code);
END Insert_Price_Book_Header;
select meaning into l_meaning from qp_lookups where lookup_code = p_code and
lookup_type = p_type;
select nvl(s.user_segment_name,s.seeded_segment_name) into l_attribute_name from qp_segments_v
s,qp_prc_contexts_v p where s.segment_mapping_column = p_attribute_code and
s.prc_context_id = p.prc_context_id and p.prc_context_code = p_context_code and
p.prc_context_type = p_attribute_type;
select concatenated_segments
into l_attribute_value
from mtl_system_items_kfv
where inventory_item_id = to_number(p_attribute_value_code) and rownum = 1;
select concat_cat_parentage
into l_attribute_value
from eni_prod_den_hrchy_parents_v
where category_id = to_number(p_attribute_value_code) and rownum = 1;
select concatenated_segments
into l_attribute_value
from mtl_categories_kfv
where category_id = to_number(p_attribute_value_code);
select party_name into l_customer_name
from hz_parties
where party_id = to_number(p_attribute_value_code);
select meaning into l_customer_name from ar_lookups where lookup_code=
p_attribute_value_code and lookup_type= 'CUSTOMER CLASS';
SELECT party_name INTO l_customer_name
FROM hz_parties where party_id = to_number(p_customer_id);
select name into l_operating_unit
from HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_ID = p_orgid
AND LANGUAGE = userenv('LANG');
select nvl(user_prc_context_name,seeded_prc_context_name) into l_context_name from qp_prc_contexts_v where prc_context_code =
p_context and prc_context_type = p_attribute_type;
select description into l_item_description from mtl_system_items_tl where
language = userenv('LANG') and inventory_item_id = p_item_number and rownum =1;
select concat_cat_parentage into l_item_category from eni_prod_den_hrchy_parents_v
where category_id = p_item_category and rownum = 1;
select concatenated_segments into l_item_category from mtl_categories_kfv where
category_id = p_item_category ;
select category_desc into l_item_cat_description from eni_prod_den_hrchy_parents_v where
category_id = p_item_category and rownum = 1;
select description into l_item_cat_description from mtl_categories_kfv where
category_id = p_item_category;
select concatenated_segments into l_item_number from mtl_system_items_kfv where
inventory_item_id = p_item_number and rownum = 1;
SELECT cust_account_id
INTO l_cust_account_id
FROM qp_price_book_headers_b
WHERE price_book_header_id = p_pb_header_id;
SELECT master_organization_id
INTO l_master_org
FROM mtl_parameters
WHERE organization_id = QP_UTIL.Get_Item_Validation_Org;
SELECT ci.customer_item_number
INTO l_customer_item_number
FROM mtl_customer_item_xrefs xref, mtl_customer_items_all_v ci
WHERE xref.inventory_item_id = p_item_number
AND xref.master_organization_id = l_master_org
AND xref.inactive_flag = 'N'
AND ci.customer_item_id = xref.customer_item_id
AND ci.customer_id = l_cust_account_id
AND ci.address_id is null
AND ci.customer_category_code is null
AND ci.item_definition_level = 1;
SELECT cust_account_id
INTO l_cust_account_id
FROM qp_price_book_headers_b
WHERE price_book_header_id = p_pb_header_id;
SELECT master_organization_id
INTO l_master_org
FROM mtl_parameters
WHERE organization_id = QP_UTIL.Get_Item_Validation_Org;
SELECT MCI.CUSTOMER_ITEM_DESC
INTO l_customer_item_desc
FROM MTL_CUSTOMER_ITEMS MCI,
HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HZC, (SELECT LOC.COUNTRY,
ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
FROM HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE
WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID,- 99) = NVL(LOC_ASSIGN.ORG_ID,- 99)
AND NVL(ACCT_SITE.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1, 10))),- 99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1, 1),' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),- 99)) RAD,
MTL_COMMODITY_CODES MCC,
MTL_CUSTOMER_ITEMS MCIM, ( SELECT B.TERRITORY_CODE
FROM FND_TERRITORIES_TL T, FND_TERRITORIES B
WHERE B.TERRITORY_CODE = T.TERRITORY_CODE
AND T.LANGUAGE = USERENV('LANG')) TERR,
AR_LOOKUPS ARL,
MFG_LOOKUPS MFL, mtl_customer_item_xrefs xref
WHERE MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID
AND MCI.ADDRESS_ID = RAD.ADDRESS_ID(+)
AND MCI.COMMODITY_CODE_ID = MCC.COMMODITY_CODE_ID
AND MCI.MODEL_CUSTOMER_ITEM_ID = MCIM.CUSTOMER_ITEM_ID(+)
AND TERR.TERRITORY_CODE(+) = RAD.COUNTRY
AND MCI.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+)
AND ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY'
AND MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE
AND MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL'
AND HZC.PARTY_ID = HZP.PARTY_ID
AND xref.inventory_item_id = p_item_number
AND xref.master_organization_id = l_master_org
AND xref.inactive_flag = 'N'
AND mci.customer_item_id = xref.customer_item_id
AND mci.customer_id = l_cust_account_id
AND mci.address_id is null
AND mci.customer_category_code is null
AND mci.item_definition_level = 1;
select concat_cat_parentage
into l_attribute_value
from eni_prod_den_hrchy_parents_v
where category_id = to_number(p_attribute_value) and rownum = 1;
select concatenated_segments
into l_attribute_value
from mtl_categories_kfv
where category_id = to_number(p_attribute_value);
select name into l_list_name from qp_list_headers_tl
where list_header_id = p_list_header_id and
language = userenv('LANG');
PROCEDURE Delete_PriceBook_Info(p_price_book_header_id in number)
is
l_pb_input_header_id number := null;
SELECT CUSTOMER_ID,PB_INPUT_HEADER_ID,PRICE_BOOK_NAME,DOCUMENT_ID
into l_customer_id,l_pb_input_header_id ,l_price_book_name,l_document_id
from QP_PRICE_BOOK_HEADERS_V
WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
SELECT PB_INPUT_HEADER_ID,PRICE_BOOK_HEADER_ID,DOCUMENT_ID
into d_pb_input_header_id,d_price_book_header_id, d_document_id
FROM QP_PRICE_BOOK_HEADERS_V
WHERE PRICE_BOOK_HEADER_ID <> p_price_book_header_id AND
PRICE_BOOK_NAME = l_price_book_name and
PRICE_BOOK_TYPE_CODE = 'D' and
CUSTOMER_ID = l_customer_id;
DELETE FROM QP_PRICE_BOOK_ATTRIBUTES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PRICE_BOOK_BREAK_LINES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PRICE_BOOK_LINE_DETAILS WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PRICE_BOOK_LINES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PRICE_BOOK_HEADERS_TL WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PRICE_BOOK_HEADERS_B WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_PB_INPUT_LINES WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
DELETE FROM QP_PB_INPUT_HEADERS_TL WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
DELETE FROM QP_PB_INPUT_HEADERS_B WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
DELETE FROM QP_PRICE_BOOK_MESSAGES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
DELETE FROM QP_DOCUMENTS WHERE DOCUMENT_ID in (l_document_id,d_document_id);
PROCEDURE Delete_Input_Criteria(p_pb_input_header_id in number)
is
BEGIN
--[prarasto]Deleting the Input Header and Lines is not required as the same header_id will be
--updated in case of an error. Commenting the code.
/*
-- Commiting after each delete as it will give rollback segment error if the data is huge
DELETE FROM QP_PB_INPUT_LINES WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
DELETE FROM QP_PB_INPUT_HEADERS_TL WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
DELETE FROM QP_PB_INPUT_HEADERS_B WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
DELETE FROM QP_PRICE_BOOK_MESSAGES WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
null; --Commit will be done only after successful insertion.
select name into l_currency_name from fnd_currencies_vl where currency_code =
p_currency_code;
PROCEDURE INSERT_PB_TL_RECORDS
(
p_pb_input_header_id IN VARCHAR2,
p_price_book_name IN VARCHAR2,
p_pl_agr_bsa_name IN VARCHAR2
)
IS
BEGIN
INSERT INTO QP_PB_INPUT_HEADERS_TL (
PB_INPUT_HEADER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PRICE_BOOK_NAME,
PL_AGR_BSA_NAME,
LANGUAGE,
SOURCE_LANG
) SELECT
p_pb_input_header_id,
PBIH.CREATION_DATE,
PBIH.CREATED_BY,
PBIH.LAST_UPDATE_DATE,
PBIH.LAST_UPDATED_BY,
PBIH.LAST_UPDATE_LOGIN,
p_price_book_name,
p_pl_agr_bsa_name,
L.LANGUAGE_CODE,
userenv('LANG')
FROM FND_LANGUAGES L, QP_PB_INPUT_HEADERS_B PBIH
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND PBIH.PB_INPUT_HEADER_ID = p_pb_input_header_id
AND NOT EXISTS
(SELECT NULL
FROM QP_PB_INPUT_HEADERS_TL T
WHERE T.PB_INPUT_HEADER_ID = p_pb_input_header_id
AND T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_PB_TL_RECORDS;
SELECT inventory_item_id
INTO x_prod_attr_value
FROM mtl_system_items_vl
WHERE concatenated_segments = p_item_number
AND organization_id = p_org_id;
SELECT inventory_item_id
INTO x_prod_attr_value
FROM (
SELECT Inventory_Item_Id
FROM MTL_CUSTOMER_ITEM_XREFS x, MTL_CUSTOMER_ITEMS i
WHERE i.customer_id = p_customer_id
AND i.customer_item_number = p_item_number_cust
AND i.Customer_Item_Id = x.customer_item_id
AND x.Master_Organization_Id =
(SELECT Master_Organization_Id
FROM MTL_PARAMETERS
WHERE Organization_Id = p_org_id)
ORDER BY Preference_Number ASC)
WHERE rownum = 1;
SELECT distinct category_id
INTO x_prod_attr_value
FROM qp_item_categories_v
WHERE category_name = p_item_category_name;
SELECT c.prc_context_code
INTO x_context_code
FROM qp_prc_contexts_v c
WHERE nvl(c.user_prc_context_name,c.seeded_prc_context_name) = p_context_name
AND prc_context_type = p_attribute_type;
SELECT sb.segment_mapping_column
INTO x_attribute_code
FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
WHERE p.prc_context_code = p_context_code
AND p.prc_context_type = p_attribute_type
AND sb.prc_context_id = p.prc_context_id
AND stl.segment_id = sb.segment_id
AND stl.language = userenv('LANG')
AND nvl(stl.user_segment_name,stl.seeded_segment_name) = p_attribute_name;
SELECT segment_code
INTO l_segment_code
FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
WHERE p.prc_context_code = p_context_code
AND p.prc_context_type = p_attribute_type
AND sb.prc_context_id = p.prc_context_id
AND stl.segment_id = sb.segment_id
AND stl.language = userenv('LANG')
AND sb.segment_mapping_column = p_attribute_code;
UPDATE QP_PRICE_BOOK_HEADERS_B
SET PUB_STATUS_CODE = 'REQUESTED'
WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
SELECT PUB_STATUS_CODE
INTO l_status_code
FROM QP_PRICE_BOOK_HEADERS_B
WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
SELECT i.customer_attr_value, i.DLV_XML_SITE_ID
INTO party_id, party_site_id
FROM qp_price_book_headers_b p, qp_pb_input_headers_b i
WHERE p.price_book_header_id = p_price_book_header_id
AND p.pb_input_header_id = i.pb_input_header_id;
SELECT FND_PROFILE.VALUE('ORG_ID')
INTO l_operating_unit_id
FROM dual;
SELECT QP_XML_MESSAGES_S.NEXTVAL INTO l_syncctlg_seq FROM dual;
SELECT SYSDATE INTO l_date FROM dual;
PROCEDURE CATSO_SELECTOR
( p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
)
IS
l_user_id NUMBER;
QP_PREQ_GRP.engine_debug( 'ENTERING CATSO_SELECTOR PROCEDURE' ) ;
SELECT application_short_name
INTO l_application_code
FROM fnd_application
WHERE application_id = fnd_global.resp_appl_id; --Responsibility of user
WF_CORE.Context('QP_PRICE_BOOK_UTIL', 'CATSO_SELECTOR',
p_itemtype, p_itemkey, p_actid, p_funcmode);
END CATSO_SELECTOR;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = upper(p_user_name);
SELECT a.application_id
INTO l_pricing_perspective_appl_id
FROM fnd_application a
WHERE a.application_short_name = l_pricing_perspective_code;
SELECT a.application_id, a.application_short_name
INTO l_resp_appl_id, l_resp_appl_name
FROM fnd_responsibility r, fnd_application a
WHERE r.responsibility_id = l_resp_id
AND a.application_id = r.application_id;
PROCEDURE CATGI_UPDATE_PUBLISH_OPTIONS
(
p_price_book_name IN VARCHAR2,
p_customer_attr_value IN NUMBER,
p_effective_date IN DATE,
p_price_book_type_code IN VARCHAR2,
p_dlv_xml_site_id IN NUMBER,
p_generation_time_code IN VARCHAR2,
p_gen_schedule_date IN DATE,
x_pb_input_header_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_text IN OUT NOCOPY VARCHAR2
)
IS
l_pb_input_header_id NUMBER;
SELECT pb_input_header_id
INTO l_pb_input_header_id
FROM qp_pb_input_headers_vl
WHERE price_book_name = p_price_book_name
AND customer_attr_value = p_customer_attr_value
--AND effective_date = p_effective_date
AND price_book_type_code = p_price_book_type_code;
UPDATE QP_PB_INPUT_HEADERS_B
SET PUB_TEMPLATE_CODE = NULL,
PUB_LANGUAGE = NULL,
PUB_TERRITORY = NULL,
PUB_OUTPUT_DOCUMENT_TYPE = NULL,
DLV_XML_FLAG = 'Y',
DLV_XML_SITE_ID = p_dlv_xml_site_id,
DLV_EMAIL_FLAG = 'N',
DLV_EMAIL_ADDRESSES = NULL,
DLV_PRINTER_FLAG = 'N',
DLV_PRINTER_NAME = NULL,
PUBLISH_EXISTING_PB_FLAG = 'Y',
GENERATION_TIME_CODE = p_generation_time_code,
GEN_SCHEDULE_DATE = p_gen_schedule_date,
REQUEST_ORIGINATION_CODE = 'XML',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE pb_input_header_id = l_pb_input_header_id;
END CATGI_UPDATE_PUBLISH_OPTIONS;
PROCEDURE CATGI_POST_INSERT_PROCESSING
(
p_pb_input_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_text IN OUT NOCOPY VARCHAR2
)
IS
l_pb_input_header_id NUMBER;
SELECT *
INTO l_pb_input_header_rec
FROM qp_pb_input_headers_vl
WHERE pb_input_header_id = p_pb_input_header_id;
x_return_text := 'CATGI_POST_INSERT_PROCESSING: pb not found - ' || SQLERRM;
UPDATE QP_PB_INPUT_HEADERS_B
SET CUST_ACCOUNT_ID = l_cust_account_id
WHERE pb_input_header_id = p_pb_input_header_id;
SELECT *
INTO l_full_pb_input_header_rec
FROM qp_pb_input_headers_vl
WHERE price_book_name = l_pb_input_header_rec.price_book_name
AND customer_attr_value = l_pb_input_header_rec.customer_attr_value
AND customer_context = l_pb_input_header_rec.customer_context
AND customer_attribute = l_pb_input_header_rec.customer_attribute
AND price_book_type_code = 'F';
UPDATE qp_pb_input_headers_b
SET customer_context = l_full_pb_input_header_rec.customer_context,
customer_attribute = l_full_pb_input_header_rec.customer_attribute,
customer_attr_value = l_full_pb_input_header_rec.customer_attr_value,
cust_account_id = l_full_pb_input_header_rec.cust_account_id,
currency_code = l_full_pb_input_header_rec.currency_code,
limit_products_by = l_full_pb_input_header_rec.limit_products_by,
product_context = l_full_pb_input_header_rec.product_context,
product_attribute = l_full_pb_input_header_rec.product_attribute,
product_attr_value = l_full_pb_input_header_rec.product_attr_value,
item_quantity = l_full_pb_input_header_rec.item_quantity,
org_id = l_full_pb_input_header_rec.org_id,
price_based_on = l_full_pb_input_header_rec.price_based_on,
pl_agr_bsa_id = l_full_pb_input_header_rec.pl_agr_bsa_id,
pricing_perspective_code = l_full_pb_input_header_rec.pricing_perspective_code,
request_type_code = l_full_pb_input_header_rec.request_type_code
WHERE pb_input_header_id = p_pb_input_header_id;
UPDATE qp_pb_input_headers_tl
SET pl_agr_bsa_name = l_full_pb_input_header_rec.pl_agr_bsa_name
WHERE pb_input_header_id = p_pb_input_header_id;
SELECT context, attribute, attribute_value, attribute_type
BULK COLLECT INTO l_context_tbl, l_attribute_tbl,
l_attribute_value_tbl, l_attribute_type_tbl
FROM qp_pb_input_lines
WHERE pb_input_header_id = l_full_pb_input_header_rec.pb_input_header_id;
INSERT INTO qp_pb_input_lines
(pb_input_line_id, pb_input_header_id,
context, attribute, attribute_value,
attribute_type, creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
VALUES
(qp_pb_input_lines_s.nextval,
p_pb_input_header_id,
l_context_tbl(k), l_attribute_tbl(k),
l_attribute_value_tbl(k), l_attribute_type_tbl(k),
l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
);
x_return_text := 'CATGI_POST_INSERT_PROCESSING: error while inserting lines - ' || SQLERRM;
x_return_text := 'CATGI_POST_INSERT_PROCESSING: general error - ' || SQLERRM;
END CATGI_POST_INSERT_PROCESSING;
PROCEDURE CATGI_UPDATE_CUST_ACCOUNT_ID
(
p_pb_input_header_id IN NUMBER,
p_cust_account_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_text IN OUT NOCOPY VARCHAR2
)
IS
BEGIN
UPDATE QP_PB_INPUT_HEADERS_B
SET CUST_ACCOUNT_ID = p_cust_account_id
WHERE pb_input_header_id = p_pb_input_header_id;
x_return_text := 'CATGI_UPDATE_MISC: general error - ' || SQLERRM;
END CATGI_UPDATE_CUST_ACCOUNT_ID;
select pte_code
into l_pte_code
from qp_pte_request_types_b
where request_type_code = p_request_type_code;
l_qryCtx := DBMS_XMLQUERY.newContext('SELECT XMLElement(
"PriceBookHeadersVORow",
XMLForest( PBHDR.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.CREATION_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "CreationDate",
PBHDR.CREATED_BY "CreatedBy",
replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.LAST_UPDATE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "LastUpdateDate",
PBHDR.LAST_UPDATED_BY "LastUpdatedBy",
PBHDR.LAST_UPDATE_LOGIN "LastUpdateLogin",
PBHDR.CUSTOMER_ID "CustomerId",
PBHDR.CURRENCY_CODE "CurrencyCode",
replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.EFFECTIVE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "EffectiveDate",
PBHDR.ITEM_QUANTITY "ItemQuantity",
PBHDR.REQUEST_ID "RequestId",
PBHDR.ORG_ID "OrgId",
PBHDR.OPERATING_UNIT "OperatingUnit",
PBHDR.PRICE_BOOK_TYPE_CODE "PriceBookTypeCode",
PBHDR.REQUEST_TYPE_CODE "RequestTypeCode",
PBHDR.PRICE_BOOK_NAME "PriceBookName",
PBHDR.CUSTOMER_NAME "CustomerName",
PBHDR.ITEM_CATEGORY "ItemCategory",
PBHDR.PB_INPUT_HEADER_ID "PbInputHeaderId",
PBHDR.PRICE_BOOK_TYPE "PriceBookType",
PBHDR.CURRENCY "Currency",
PBHDR.PRICING_PERSPECTIVE_CODE "PricingPerspectiveCode",
PBHDR.PL_AGR_BSA_ID "PlAgrBsaId",
PBHDR.PL_AGR_BSA_NAME "PlAgrBsaName",
PBHDR.LANGUAGE "Language",
PBHDR.SOURCE_LANG "SourceLang",
PBHDR.PRICE_BASED_ON "PriceBasedOn",
PBHDR.CUST_ACCOUNT_ID "CustAccountId"),
XMLElement(
"PBInputHeadersVO",
(SELECT XMLAgg(
XMLElement(
"PBInputHeadersVORow",
XMLForest(PBInputHDR.PB_INPUT_HEADER_ID "PbInputHeaderId",
PBInputHDR.CUSTOMER_CONTEXT "CustomerContext",
PBInputHDR.CUSTOMER_ATTRIBUTE "CustomerAttribute",
PBInputHDR.CUSTOMER_ATTR_VALUE "CustomerAttrValue",
PBInputHDR.CURRENCY_CODE "CurrencyCode",
PBInputHDR.PRODUCT_CONTEXT "ProductContext",
PBInputHDR.PRODUCT_ATTRIBUTE "ProductAttribute",
PBInputHDR.PRODUCT_ATTR_VALUE "ProductAttrValue",
replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBInputHDR.EFFECTIVE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "EffectiveDate",
PBInputHDR.ITEM_QUANTITY "ItemQuantity",
PBInputHDR.GENERATION_TIME_CODE "GenerationTimeCode",
PBInputHDR.GEN_SCHEDULE_DATE "GenScheduleDate",
PBInputHDR.REQUEST_ID "RequestId",
PBInputHDR.ORG_ID "OrgId",
PBInputHDR.OPERATING_UNIT "OperatingUnit",
PBInputHDR.PRICE_BOOK_TYPE_CODE "PriceBookTypeCode",
PBInputHDR.PUBLISH_EXISTING_PB_FLAG "PublishExistingPbFlag",
PBInputHDR.REQUEST_TYPE_CODE "RequestTypeCode",
PBInputHDR.PRICE_BOOK_NAME "PriceBookName",
PBInputHDR.CUSTOMER_NAME "CustomerName",
PBInputHDR.PRODUCT_NAME "ProductName",
PBInputHDR.GENERATION_TIME "GenerationTime",
PBInputHDR.PRICE_BOOK_TYPE "PriceBookType",
PBInputHDR.PRODUCT_ATTRIBUTE_NAME "ProductAttributeName",
PBInputHDR.CUSTOMER_ATTRIBUTE_NAME "CustomerAttributeName",
PBInputHDR.VALIDATION_ERROR_FLAG "ValidationErrorFlag",
PBInputHDR.PRICING_PERSPECTIVE_CODE "PricingPerspectiveCode",
PBInputHDR.OVERWRITE_EXISTING_PB_FLAG "OverwriteExistingPbFlag",
PBInputHDR.CURRENCY "Currency",
PBInputHDR.LIMIT_PRODUCTS_BY "LimitProductsBy",
PBInputHDR.PRICE_BASED_ON "PriceBasedOn",
PBInputHDR.PL_AGR_BSA_ID "PlAgrBsaId",
PBInputHDR.LIMIT_PRODUCTS_BY_NAME "LimitProductsByName",
PBInputHDR.PRICE_BASED_ON_NAME "PriceBasedOnName",
PBInputHDR.PL_AGR_BSA_NAME "PlAgrBsaName",
PBInputHDR.PUB_TEMPLATE_CODE "PubTemplateCode",
PBInputHDR.PUB_LANGUAGE "PubLanguage",
PBInputHDR.PUB_TERRITORY "PubTerritory",
PBInputHDR.PUB_OUTPUT_DOCUMENT_TYPE "PubOutputDocumentType",
PBInputHDR.DLV_XML_FLAG "DlvXmlFlag",
PBInputHDR.DLV_EMAIL_FLAG "DlvEmailFlag",
PBInputHDR.DLV_EMAIL_ADDRESSES "DlvEmailAddresses",
PBInputHDR.DLV_PRINTER_FLAG "DlvPrinterFlag",
PBInputHDR.DLV_PRINTER_NAME "DlvPrinterName",
PBInputHDR.PRICING_PERSPECTIVE "PricingPerspective"),
XMLElement(
"PBInputLinesVO",
(SELECT XMLAgg(
XMLElement(
"PBInputLinesVORow",
XMLForest(PBInputLIN.PB_INPUT_LINE_ID "PbInputLineId",
PBInputLIN.PB_INPUT_HEADER_ID "PbInputHeaderId",
PBInputLIN.CONTEXT "Context",
PBInputLIN.ATTRIBUTE "Attribute",
PBInputLIN.ATTRIBUTE_VALUE "AttributeValue",
PBInputLIN.ATTRIBUTE_TYPE "AttributeType",
PBInputLIN.CONTEXT_NAME "ContextName",
PBInputLIN.ATTRIBUTE_NAME "AttributeName",
PBInputLIN.ATTRIBUTE_VALUE_NAME "AttributeValueName",
PBInputLIN.ATTRIBUTE_TYPE_VALUE "AttributeTypeValue",
QP_Price_Book_Util.value_to_meaning(''='',''COMPARISON_OPERATOR_FWK'') "OperatorCodeName")
)
)
FROM QP_PB_INPUT_LINES_V PBInputLIN
WHERE PBInputLIN.Pb_Input_Header_Id = PBInputHDR.Pb_Input_Header_Id
)
)
)
)
FROM QP_PB_INPUT_HEADERS_V PBInputHDR
WHERE PBInputHDR.Pb_Input_Header_Id = PBHDR.Pb_Input_Header_Id
)
),
XMLElement(
"PriceBookLinesVO",
(SELECT XMLAgg(
XMLElement(
"PriceBookLinesVORow",
XMLForest(PBLin.PRICE_BOOK_LINE_ID "PriceBookLineId",
PBLin.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
PBLin.ITEM_NUMBER "ItemNumber",
PBLin.PRODUCT_UOM_CODE "ProductUomCode",
PBLin.LIST_PRICE "ListPrice",
PBLin.NET_PRICE "NetPrice",
PBLin.SYNC_ACTION_CODE "SyncActionCode",
PBLin.LINE_STATUS_CODE "LineStatusCode",
PBLin.DESCRIPTION "Description",
PBLin.CUSTOMER_ITEM_NUMBER "CustomerItemNumber",
PBLin.DISPLAY_ITEM_NUMBER "DisplayItemNumber",
PBLin.SYNC_ACTION "SyncAction",
nvl(PBLin.CUSTOMER_ITEM_NUMBER,PBLin.DISPLAY_ITEM_NUMBER) "UiItemNumber",
PBLin.CUSTOMER_ITEM_DESC "CustomerItemDesc",
to_char(PBLin.LIST_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "ListPriceDisp",
to_char(PBLin.NET_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "NetPriceDisp"),
XMLElement(
"PriceBookLineDetailsVO",
(SELECT XMLAgg(
XMLElement(
"PriceBookLineDetailsVORow",
XMLForest(PBLinDet.PRICE_BOOK_LINE_ID "PriceBookLineId",
PBLinDet.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
PBLinDet.LIST_PRICE "ListPrice",
PBLinDet.ADJUSTED_NET_PRICE "AdjustedNetPrice",
PBLinDet.PRICE_BOOK_LINE_DET_ID "PriceBookLineDetId",
PBLinDet.LIST_HEADER_ID "ListHeaderId",
PBLinDet.LIST_LINE_ID "ListLineId",
PBLinDet.LIST_LINE_NO "ListLineNo",
PBLinDet.MODIFIER_OPERAND "ModifierOperand",
PBLinDet.MODIFIER_APPLICATION_METHOD "ModifierApplicationMethod",
PBLinDet.ADJUSTMENT_AMOUNT "AdjustmentAmount",
PBLinDet.LIST_LINE_TYPE_CODE "ListLineTypeCode",
PBLinDet.PRICE_BREAK_TYPE_CODE "PriceBreakTypeCode",
PBLinDet.LIST_NAME "ListName",
PBLinDet.LIST_LINE_TYPE "ListLineType",
PBLinDet.PRICE_BREAK_TYPE "PriceBreakType",
DECODE((SELECT ''X''
from dual
where exists(SELECT ''X''
from QP_PRICE_BOOK_ATTRIBUTES_V pba
where pba.PRICE_BOOK_LINE_DET_ID = PBLinDet.PRICE_BOOK_LINE_DET_ID
and pba.PRICE_BOOK_LINE_ID = PBLinDet.PRICE_BOOK_LINE_ID)),''X'',''PricingAttrEnabled'',''PricingAttrDisabled'') "PricingAttribute",
DECODE((SELECT ''X''
from dual
where exists(SELECT ''X''
from QP_PRICE_BOOK_BREAK_LINES_V pbb
where pbb.PRICE_BOOK_LINE_DET_ID= PBLinDet.PRICE_BOOK_LINE_DET_ID
and pbb.PRICE_BOOK_LINE_ID = PBLinDet.PRICE_BOOK_LINE_ID)),''X'',''BreaksEnabled'',''BreaksDisabled'') "Breaks",
''MessageCheck'' "Messages",
to_char(PBLinDet.LIST_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "ListPriceDisp",
to_char(PBLinDet.ADJUSTED_NET_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "AdjustedNetPriceDisp"),
DECODE(PBLinDet.LIST_LINE_TYPE_CODE,''PBH'',
XMLElement(
"PriceBookBreakLinesVO",
(SELECT XMLAgg(
XMLElement(
"PriceBookBreakLinesVORow",
XMLForest(pbk.PRICE_BOOK_LINE_DET_ID "PriceBookLineDetId",
pbk.COMPARISON_OPERATOR_NAME "ComparisonOperatorName",
pbk.ATTRIBUTE_NAME "AttributeName",
pbk.PRICING_ATTR_VALUE_FROM "PricingAttrValueFrom",
pbk.PRICING_ATTR_VALUE_TO "PricingAttrValueTo",
decode(LD.LIST_LINE_NO, null, fnd_message.get_string(''QP'',''QP_PRICE_BOOK_LISTPRICE''), fnd_message.get_string(''QP'',''QP_PRICE_BOOK_ADDITIONAL''))
||'' ''
||pbk.ATTRIBUTE_NAME
||'' ''
||decode(pbk.PRICING_ATTR_VALUE_TO,
999999999999999,fnd_message.get_string(''QP'',''QP_PRICE_BOOK_PBH_GREATER'')||'' ''||pbk.PRICING_ATTR_VALUE_FROM,
decode(pll.continuous_price_break_flag,
''Y'', fnd_message.get_string(''QP'',''QP_PRICE_BOOK_COMPARISON''),
pbk.COMPARISON_OPERATOR_NAME)||'' ''|| pbk.PRICING_ATTR_VALUE_FROM||'' ''
|| decode(pll.continuous_price_break_flag, ''Y'',
fnd_message.get_string(''QP'',''QP_PRICE_BOOK_MORE''),fnd_message.get_string(''QP'',''QP_PRICE_BOOK_AND''))||'' ''|| pbk.PRICING_ATTR_VALUE_TO
) "Description",
LD.LIST_LINE_NO "ModifierNumber",
pbk.OPERAND "Operand",
pbk.APPLICATION_METHOD_NAME "ApplicationMethodName",
pbk.RECURRING_VALUE "RecurringValue")
)
)
FROM QP_PRICE_BOOK_LINE_DETAILS_V LD, QP_PRICE_BOOK_BREAK_LINES_V pbk, qp_list_lines pll
WHERE LD.PRICE_BOOK_LINE_DET_ID = pbk.PRICE_BOOK_LINE_DET_ID
AND ld.list_line_id = pll.list_line_id
AND LD.PRICE_BOOK_LINE_DET_ID = PBLinDet.PRICE_BOOK_LINE_DET_ID
)
)
,
NULL)
)
)
FROM QP_PRICE_BOOK_LINE_DETAILS_V PBLinDet
WHERE PBLinDet.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
)
),
DECODE((SELECT ''X''
from dual
where exists(SELECT ''X''
from QP_PRICE_BOOK_MESSAGES_V pbm
where pbm.Price_Book_Line_Id = PBLin.Price_Book_Line_Id )),
''X'',
XMLElement(
"PriceBookMessagesVO",
(SELECT XMLAgg(
XMLElement(
"PriceBookMessagesVORow",
XMLForest(QPPBMSGS.MESSAGE_ID "MessageId",
QPPBMSGS.MESSAGE_TYPE "MessageType",
QPPBMSGS.MESSAGE_CODE "MessageCode",
QPPBMSGS.MESSAGE_TEXT "MessageText",
QPPBMSGS.PB_INPUT_HEADER_ID "PbInputHeaderId",
QPPBMSGS.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
QPPBMSGS.PRICE_BOOK_LINE_ID "PriceBookLineId")
)
)
FROM QP_PRICE_BOOK_MESSAGES_V QPPBMSGS
WHERE QPPBMSGS.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
)
)
,NULL),
XMLElement(
"PriceBookLineCatsVO",
(SELECT XMLAgg(
XMLElement(
"PriceBookLineCatsVORow",
XMLForest(QPPBATTRS.price_book_line_id "PriceBookLineId",
QPPBATTRS.attribute_value_name "CategoryName",
QPPBATTRS.PRICING_PROD_ATTR_VALUE_FROM "CategoryId")
)
)
FROM QP_PRICE_BOOK_ATTRIBUTES_V QPPBATTRS
WHERE QPPBATTRS.PRICE_BOOK_LINE_DET_ID = -1
AND QPPBATTRS.ATTRIBUTE_TYPE = ''PRODUCT''
AND QPPBATTRS.PRICING_PROD_CONTEXT = ''ITEM''
AND QPPBATTRS.PRICING_PROD_ATTRIBUTE = ''PRICING_ATTRIBUTE2''
AND QPPBATTRS.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
)
)
)
)
FROM QP_PRICE_BOOK_LINES_V PBLin
WHERE PBLin.Price_Book_Header_Id = PBHDR.Price_Book_Header_Id
)
)
) as "PriceBookHeadersVO"
FROM QP_PRICE_BOOK_HEADERS_V PBHDR
WHERE PRICE_BOOK_HEADER_ID = :PBHDRID');
DELETE FROM QP_DOCUMENTS
WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID
FROM qp_price_book_headers_b
WHERE PRICE_BOOK_HEADER_ID = p_price_book_hdr_id);
INSERT INTO QP_DOCUMENTS(
DOCUMENT_ID,
DOCUMENT_CONTENT,
DOCUMENT_CONTENT_TYPE,
DOCUMENT_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
XML_CONTENT
)
VALUES(
qp_price_book_messages_s.nextval,
EMPTY_BLOB(),
p_document_content_type,
p_document_name,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
l_result
) RETURNING DOCUMENT_ID INTO l_doc_id;
UPDATE qp_price_book_headers_b
SET document_id=l_doc_id
WHERE PRICE_BOOK_HEADER_ID = p_price_book_hdr_id;