[Home] [Help]
187: )
188: ;
189:
190: /* Cursor gives line that got atleast one duplicate pricing attribute line
191: * in qp_pricing_attributes table */
192: CURSOR c_dup_line_rec1 IS
193: SELECT DISTINCT a_attr.orig_sys_header_ref,
194: a_attr.orig_sys_line_ref,
195: b_attr.orig_sys_line_ref
193: SELECT DISTINCT a_attr.orig_sys_header_ref,
194: a_attr.orig_sys_line_ref,
195: b_attr.orig_sys_line_ref
196: FROM qp_interface_pricing_attribs a_attr,
197: qp_pricing_attributes b_attr
198: WHERE a_attr.request_id = p_request_id
199: AND a_attr.process_status_flag = 'P'
200: AND a_attr.interface_action_code IN ('INSERT', 'UPDATE')
201: AND b_attr.orig_sys_header_ref = a_attr.orig_sys_header_ref
231: )
232: ;
233:
234: /* gives all pricing attribute of a line
235: * union b/w qp_interface_pricing_attribs and qp_pricing_attributes
236: */
237: CURSOR c_pricing_attr(l_orig_sys_line_ref VARCHAR2) IS
238: /* take attibute from qp_interface_pricing_attribs */
239: SELECT pricing_attribute_context,
253: pricing_attribute,
254: pricing_attr_value_from,
255: pricing_attr_value_to,
256: comparison_operator_code
257: FROM qp_pricing_attributes qpa
258: WHERE qpa.orig_sys_line_ref = l_orig_sys_line_ref
259: AND qpa.pricing_attribute_context IS NOT NULL
260: AND qpa.pricing_attribute IS NOT NULL
261: AND NOT EXISTS ( /* do not take an attribute from */
258: WHERE qpa.orig_sys_line_ref = l_orig_sys_line_ref
259: AND qpa.pricing_attribute_context IS NOT NULL
260: AND qpa.pricing_attribute IS NOT NULL
261: AND NOT EXISTS ( /* do not take an attribute from */
262: SELECT 1 /* qp_pricing_attributes if that going to */
263: /* be updated or deleted */
264: FROM qp_interface_pricing_attribs c
265: WHERE c.orig_sys_line_ref = l_orig_sys_line_ref
266: AND c.interface_action_code IN ('UPDATE', 'DELETE')
307:
308: l_routine VARCHAR2(230) := 'DUP_LINE_CHECK ';
309: BEGIN
310: /* First we check for duplicates in qp_interface_pricing_attribs(i_mode = 1)
311: * then in qp_pricing_attributes(i_mode = 2) */
312: qp_bulk_loader_pub.write_log('In ' || l_routine);
313: FOR i_mode IN 1..2
314: LOOP
315: qp_bulk_loader_pub.write_log('i_mode = ' || i_mode);
1880:
1881: if l_exist=0 then
1882: begin
1883: select count(*) into l_exist
1884: from qp_pricing_attributes qpa, mtl_system_items_vl msiv
1885: where qpa.orig_sys_line_ref = p_line_rec.orig_sys_line_ref(I)
1886: -- Bug 5246745 Use Composite Index for Ref columns
1887: and qpa.orig_sys_header_ref = p_line_rec.orig_sys_header_ref(I)
1888: and qpa.product_attribute_context = 'ITEM'
3349: FROM qp_list_lines la,
3350: qp_rltd_modifiers ra,
3351: qp_rltd_modifiers rb,
3352: qp_interface_pricing_attribs pa,
3353: qp_pricing_attributes pb
3354: WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
3355: -- Bug 5246745 Use Composite Index for Ref columns
3356: AND la.orig_sys_header_ref = p_orig_sys_header_ref
3357: AND ra.to_rltd_modifier_id = la.list_line_id
3409: FROM qp_list_lines la,
3410: qp_rltd_modifiers ra,
3411: qp_rltd_modifiers rb,
3412: qp_interface_pricing_attribs pa,
3413: qp_pricing_attributes pb
3414: WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
3415: -- Bug 5246745 Use Composite Index for Ref columns
3416: AND la.orig_sys_header_ref = p_orig_sys_header_ref
3417: AND ra.to_rltd_modifier_id = la.list_line_id
3463: FROM qp_list_lines la,
3464: qp_rltd_modifiers ra,
3465: qp_rltd_modifiers rb,
3466: qp_interface_pricing_attribs pa,
3467: qp_pricing_attributes pb
3468: WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
3469: -- Bug 5246745 Use Composite Index for Ref columns
3470: AND la.orig_sys_header_ref = p_orig_sys_header_ref
3471: AND ra.to_rltd_modifier_id = la.list_line_id
3517: FROM qp_list_lines la,
3518: qp_rltd_modifiers ra,
3519: qp_rltd_modifiers rb,
3520: qp_interface_pricing_attribs pa,
3521: qp_pricing_attributes pb
3522: WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
3523: -- Bug 5246745 Use Composite Index for Ref columns
3524: AND la.orig_sys_header_ref = p_orig_sys_header_ref
3525: AND ra.to_rltd_modifier_id = la.list_line_id
3724: -- Check for the uniqueness of orig_sys_pricing_attr_ref
3725: l_exist:=null;
3726: BEGIN
3727: SELECT 1,pricing_attribute_id INTO l_exist,l_pricing_attribute_id
3728: FROM qp_pricing_attributes
3729: WHERE orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
3730: AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
3731: AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
3732: EXCEPTION
3837: OR p_pricing_attr_rec.pricing_attribute(I) IS NULL THEN
3838: l_exist := 0;
3839: BEGIN
3840: SELECT count(*) INTO l_exist
3841: FROM qp_pricing_attributes
3842: WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
3843: AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
3844: AND pricing_attribute_context IS NULL
3845: AND pricing_attribute IS NULL
3887: AND p_pricing_attr_rec.pricing_attribute(I) IS NOT NULL THEN
3888: l_exist := 0;
3889: BEGIN
3890: SELECT count(*) INTO l_exist
3891: FROM qp_pricing_attributes
3892: WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
3893: AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
3894: AND pricing_attribute_context <> p_pricing_attr_rec.pricing_attribute_context(I)
3895: AND orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I);
3966:
3967: BEGIN
3968: l_exist := NULL;
3969: SELECT count(*) INTO l_exist
3970: FROM qp_pricing_attributes
3971: WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
3972: AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
3973: AND product_attribute_context <> p_pricing_attr_rec.product_attribute_context(I)
3974: AND product_attribute <> p_pricing_attr_rec.product_attribute(I)
4249: -- Bug# 5228284 RAVI
4250: -- Do not consider the line if it's already the primary flag
4251: SELECT count(*)
4252: INTO l_count
4253: FROM qp_list_lines l, qp_pricing_attributes a
4254: WHERE l.list_line_id = a.list_line_id
4255: AND a.list_header_id = p_PRICING_ATTR_rec.list_header_id(I)
4256: AND a.product_attribute_context=p_PRICING_ATTR_rec.product_attribute_context(I)
4257: AND a.product_attribute = p_PRICING_ATTR_rec.product_attribute(I)
4288: l_count:=null;
4289: for c1 in
4290: (
4291: select b.list_line_id col2
4292: from qp_pricing_attributes b, qp_list_lines c
4293: where b.list_line_id <> p_PRICING_ATTR_rec.list_line_id(I)
4294: and b.list_line_id=c.list_line_id
4295: and c.primary_uom_flag='Y'
4296: and b.product_attribute_context = p_PRICING_ATTR_rec.product_attribute_context(I)
4487: /* SELECT COUNT(1) INTO l_count
4488: FROM
4489: (SELECT /*+ ordered leading(la) index(pb QP_INTERFACE_PRCNG_ATTRIBS_N4) */ /*DISTINCT pb.pricing_attribute
4490: FROM qp_list_lines la,
4491: qp_pricing_attributes pb,
4492: qp_rltd_modifiers ra
4493: WHERE ra.to_rltd_modifier_id = la.list_line_id
4494: AND pb.list_line_id = ra.to_rltd_modifier_id
4495: AND ra.from_rltd_modifier_id = (select from_rltd_modifier_id
4537: rb.rltd_modifier_grp_type = 'PRICE BREAK'
4538: UNION
4539: SELECT /*+ leading(la) */ DISTINCT pb.pricing_attribute
4540: FROM qp_list_lines la,
4541: qp_pricing_attributes pb,
4542: qp_rltd_modifiers ra,
4543: qp_rltd_modifiers rb
4544: WHERE la.orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I) AND
4545: la.list_line_type_code = 'PLL' AND
4829: IF l_pricing_attr_flex_enabled = 'Y' THEN
4830: qp_bulk_loader_pub.write_log( 'Pricing Attr Flex enabled ');
4831:
4832: IF NOT Init_Desc_Flex
4833: (p_flex_name =>'QP_PRICING_ATTRIBUTES'
4834: ,p_context => p_pricing_attr_rec.context(i)
4835: ,p_attribute1 => p_pricing_attr_rec.attribute1(i)
4836: ,p_attribute2 => p_pricing_attr_rec.attribute2(i)
4837: ,p_attribute3 => p_pricing_attr_rec.attribute3(i)