[Home] [Help]
225: set used_in_setup='Y'
226: where nvl(used_in_setup,'N')='N'
227: and segment_id in
228: (select a.segment_id
229: from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c, qp_list_headers_b h
230: where h.list_header_id BETWEEN l_min_hdr_id and l_max_hdr_id
231: and h.active_flag = 'Y'
232: and c.REQUEST_ID = child_req_rec.request_id --8418006
233: and c.list_header_id = h.list_header_id
240: set used_in_setup='Y'
241: where nvl(used_in_setup,'N')='N'
242: and segment_id in
243: (select a.segment_id
244: from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c, qp_list_headers_b h
245: where h.list_header_id BETWEEN l_min_hdr_id and l_max_hdr_id
246: and h.active_flag = 'Y'
247: and c.REQUEST_ID = child_req_rec.request_id --8418006
248: and c.list_header_id = h.list_header_id
2973: AND pa.interface_action_code IN ('INSERT','UPDATE','DELETE') )
2974: -- Bug 5208480(5208112,4188784) RAVI
2975: AND NOT EXISTS
2976: (
2977: SELECT /*+ index(qppa qp_pricing_attributes_n9) no_unnest */ 1 from qp_pricing_attributes qppa
2978: where qpip.interface_action_code = 'UPDATE'
2979: and qpip.orig_sys_header_ref = qppa.orig_sys_header_ref
2980: and qpip.orig_sys_line_ref = qppa.orig_sys_line_ref
2981: and qppa.list_line_id is not null
2984: -- Bug# 5246745 RAVI
2985: -- Should not be able to update product attribute context
2986: AND NOT EXISTS
2987: (
2988: select /*+ index(qppa qp_pricing_attributes_n9) no_unnest */ 1 from qp_pricing_attributes qppa
2989: where qpip.interface_action_code = 'UPDATE'
2990: and qpip.orig_sys_header_ref = qppa.orig_sys_header_ref
2991: and qpip.orig_sys_line_ref = qppa.orig_sys_line_ref
2992: and qppa.product_attribute_context is not null
2995: -- Bug# 5246745 RAVI
2996: -- Should not be able to update product attribute
2997: AND NOT EXISTS
2998: (
2999: select /*+ index(qppa qp_pricing_attributes_n9) no_unnest */ 1 from qp_pricing_attributes qppa
3000: where qpip.interface_action_code = 'UPDATE'
3001: and qpip.orig_sys_header_ref = qppa.orig_sys_header_ref
3002: and qpip.orig_sys_line_ref = qppa.orig_sys_line_ref
3003: and qppa.product_attribute is not null
3006: -- Bug# 5246745 RAVI
3007: -- Should not be able to update product attribute Value
3008: AND NOT EXISTS
3009: (
3010: select /*+ index(qppa qp_pricing_attributes_n9) no_unnest */ 1 from qp_pricing_attributes qppa
3011: where qpip.interface_action_code = 'UPDATE'
3012: and qpip.orig_sys_header_ref = qppa.orig_sys_header_ref
3013: and qpip.orig_sys_line_ref = qppa.orig_sys_line_ref
3014: and qppa.product_attr_value is not null
3402: QP_BULK_LOADER_PUB.G_NULL_CHAR, null,
3403: qpip.pricing_attr_value_to_disp
3404: )
3405: --Bug# 5456164 RAVI END
3406: from qp_pricing_attributes qppa,
3407: qp_list_headers_b qplh
3408: where -- CAUSES FTS AS NO INDEX ON PRICNG_ATTR_REF
3409: qpip.orig_sys_pricing_attr_ref = qppa.orig_sys_pricing_attr_ref
3410: and qppa.list_header_id = qplh.list_header_id
4366: AND pa.orig_sys_line_ref = l.orig_sys_line_ref))
4367: -- Bug 5208480(5208112,4188784) RAVI
4368: AND NOT EXISTS
4369: (
4370: select 1 from qp_pricing_attributes qppa
4371: where qpip.interface_action_code = 'UPDATE'
4372: and qpip.orig_sys_line_ref = qppa.orig_sys_line_ref
4373: and qppa.list_line_id is not null
4374: and qpip.list_line_id <> qppa.list_line_id
4650: decode(qpip.qualification_ind,QP_BULK_LOADER_PUB.G_NULL_NUMBER,
4651: null,qpip.qualification_ind
4652: )
4653: )
4654: from qp_pricing_attributes qppa,
4655: qp_list_headers_b qplh
4656: where -- CAUSES FTS AS NO INDEX ON PRICNG_ATTR_REF
4657: qpip.orig_sys_pricing_attr_ref = qppa.orig_sys_pricing_attr_ref
4658: and qppa.list_header_id = qplh.list_header_id
6851: and il.interface_action_code ='INSERT'
6852: and il.orig_sys_header_ref = l.orig_sys_header_ref
6853: and il.orig_sys_line_ref = l.orig_sys_line_ref);
6854:
6855: Delete from qp_pricing_attributes
6856: where pricing_attribute_id IN
6857: (Select pa.pricing_attribute_id
6858: from qp_pricing_attributes pa, qp_interface_pricing_attribs ipa
6859: where ipa.request_id = p_request_id
6854:
6855: Delete from qp_pricing_attributes
6856: where pricing_attribute_id IN
6857: (Select pa.pricing_attribute_id
6858: from qp_pricing_attributes pa, qp_interface_pricing_attribs ipa
6859: where ipa.request_id = p_request_id
6860: and pa.request_id = p_request_id -- changes made by rassharm 6028305
6861: and ipa.process_status_flag IS NULL
6862: and ipa.interface_action_code ='INSERT'
6899: from qp_list_lines l
6900: where l.request_id = p_request_id
6901: and not exists (select 'x' from qp_rltd_modifiers
6902: where to_rltd_modifier_id = l.list_line_id)
6903: and not exists (select 'x' from qp_pricing_attributes pa
6904: where l.list_line_id = pa.list_line_id
6905: and pa.pricing_attribute_context is null
6906: and pa.pricing_attribute is null
6907: and pa.pricing_attr_value_from is null
6913: from qp_list_lines l
6914: where l.request_id = p_request_id
6915: and exists (select 'x' from qp_rltd_modifiers
6916: where to_rltd_modifier_id = l.list_line_id)
6917: and not exists (select 'x' from qp_pricing_attributes pa
6918: where l.list_line_id = pa.list_line_id
6919: and pa.pricing_attribute_context is not null
6920: and pa.pricing_attribute is not null
6921: and (pa.pricing_attr_value_from is not null
6960: AND orig_sys_line_ref = l_orig_sys_line_ref_tbl(I)
6961: AND request_id = p_request_id; --Bug No: 6235177
6962:
6963: FORALL I in l_list_line_id_tbl.first..l_list_line_id_tbl.last
6964: DELETE FROM qp_pricing_attributes
6965: WHERE list_line_id = l_list_line_id_tbl(I);
6966:
6967: FORALL I in l_list_line_id_tbl.first..l_list_line_id_tbl.last
6968: DELETE FROM qp_list_lines
6980: FROM qp_rltd_modifiers
6981: WHERE from_rltd_modifier_id = l_list_line_id_tbl(I));
6982:
6983: FORALL I in l_list_line_id_tbl.first..l_list_line_id_tbl.last
6984: DELETE FROM qp_pricing_attributes
6985: WHERE list_line_id IN
6986: (SELECT to_rltd_modifier_id
6987: FROM qp_rltd_modifiers
6988: WHERE from_rltd_modifier_id = l_list_line_id_tbl(I));
7054: FOR l_pricing_attr_rec IN c_pricing_attr
7055: LOOP
7056: BEGIN
7057:
7058: DELETE FROM QP_PRICING_ATTRIBUTES
7059: WHERE list_line_id IN
7060: (SELECT r.to_rltd_modifier_id
7061: FROM qp_rltd_modifiers r, qp_list_lines l
7062: WHERE l.orig_sys_line_ref = l_pricing_attr_rec.orig_sys_line_ref
7076: (SELECT list_line_id FROM QP_LIST_LINES l
7077: WHERE l.orig_sys_line_ref = l_pricing_attr_rec.orig_sys_line_ref
7078: AND l.orig_sys_header_ref = l_pricing_attr_rec.orig_sys_header_ref);
7079:
7080: DELETE FROM QP_PRICING_ATTRIBUTES
7081: WHERE orig_sys_line_ref = l_pricing_attr_rec.orig_sys_line_ref
7082: AND orig_sys_header_ref = l_pricing_attr_rec.orig_sys_header_ref;
7083:
7084: DELETE FROM QP_LIST_LINES
7090: END LOOP;
7091:
7092: FOR l_pbh_cline_rec IN c_pbh_child_line
7093: LOOP
7094: DELETE FROM QP_PRICING_ATTRIBUTES
7095: WHERE list_line_id IN
7096: (SELECT to_rltd_modifier_id
7097: FROM QP_RLTD_MODIFIERS r, QP_LIST_LINES l
7098: WHERE l.orig_sys_line_ref = l_pbh_cline_rec.price_break_header_ref
7106: WHERE l.orig_sys_line_ref = l_pbh_cline_rec.price_break_header_ref
7107: AND l.orig_sys_header_ref = l_pbh_cline_rec.orig_sys_header_ref
7108: AND l.list_line_id = r.from_rltd_modifier_id);
7109:
7110: DELETE FROM QP_PRICING_ATTRIBUTES
7111: WHERE list_line_id IN
7112: (SELECT list_line_id
7113: FROM QP_LIST_LINES
7114: WHERE orig_sys_line_ref = l_pbh_cline_rec.orig_sys_line_ref