[Home] [Help]
731:
732: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.Mini_Cache_Qualifiers';
733:
734: /*
735: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
732: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.Mini_Cache_Qualifiers';
733:
734: /*
735: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
733:
734: /*
735: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
741: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
734: /*
735: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
741: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
742:
735: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
741: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
742:
743: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
736: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
741: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
742:
743: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
744: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
737: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
738: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
739: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
740: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
741: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
742:
743: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
744: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
745: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
754: --Added index hints for bug#7480862
755: CURSOR l_MINI_OUTER_QUAL_CUR IS
756: -- Qualifier Only
757: select /*+ ORDERED NO_EXPAND USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6)
758: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
759: qpq.list_header_id,
760: qpq.list_line_id list_line_id,
761: qpq.qualifier_group_cnt,
762: qpq.others_group_cnt,
801: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id,qualifier_grouping_no
802: from qp_preq_qual_tmp
803: where pricing_status_code = G_STATUS_UNCHANGED
804: and pricing_phase_id = p_pricing_phase_id) qptq,
805: qp_qualifiers qpq ,
806: qp_npreq_line_attrs_tmp qplatq,
807: qp_list_headers_b qph,
808: qp_price_req_sources_v qprs
809: where
854: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
855: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
856: --and qptq.pricing_phase_id = p_pricing_phase_id
857: union all
858: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_o
859: uter_qual_cur_mod */
860: qpq.list_header_id,
861: qpq.list_line_id list_line_id,
862: qpq.qualifier_group_cnt,
902: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id
903: from qp_preq_qual_tmp
904: where pricing_status_code = G_STATUS_UNCHANGED
905: and pricing_phase_id = p_pricing_phase_id) qptq,
906: qp_qualifiers qpq ,
907: qp_npreq_line_attrs_tmp qplatq,
908: qp_list_headers_b qph,
909: qp_price_req_sources_v qprs
910: where
954: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
955: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
956: --and qptq.pricing_phase_id = p_pricing_phase_id
957: union all
958: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
959: qpq.list_header_id,
960: qpq.list_line_id list_line_id,
961: qpq.qualifier_group_cnt,
962: qpq.others_group_cnt,
1001: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id,qualifier_grouping_no
1002: from qp_preq_qual_tmp
1003: where pricing_status_code = G_STATUS_UNCHANGED
1004: and pricing_phase_id = p_pricing_phase_id) qptq,
1005: qp_qualifiers qpq ,
1006: qp_npreq_line_attrs_tmp qplatq,
1007: qp_list_headers_b qph,
1008: qp_price_req_sources_v qprs
1009: where
1056: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1057: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1058: --and qptq.pricing_phase_id = p_pricing_phase_id
1059: union all
1060: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_ou
1061: ter_qual_cur_mod */
1062: qpq.list_header_id,
1063: qpq.list_line_id list_line_id,
1064: qpq.qualifier_group_cnt,
1104: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id
1105: from qp_preq_qual_tmp
1106: where pricing_status_code = G_STATUS_UNCHANGED
1107: and pricing_phase_id = p_pricing_phase_id) qptq,
1108: qp_qualifiers qpq ,
1109: qp_npreq_line_attrs_tmp qplatq,
1110: qp_list_headers_b qph,
1111: qp_price_req_sources_v qprs
1112: where
1157: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1158: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1159: --and qptq.pricing_phase_id = p_pricing_phase_id
1160: union all
1161: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1162: qpq.list_header_id,
1163: qpq.list_line_id list_line_id,
1164: qpq.qualifier_group_cnt,
1165: qpq.others_group_cnt,
1204: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id,qualifier_grouping_no
1205: from qp_preq_qual_tmp
1206: where pricing_status_code = G_STATUS_UNCHANGED
1207: and pricing_phase_id = p_pricing_phase_id) qptq,
1208: qp_qualifiers qpq ,
1209: qp_npreq_line_attrs_tmp qplatq,
1210: qp_list_headers_b qph,
1211: qp_price_req_sources_v qprs
1212: where
1260: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1261: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1262: --and qptq.pricing_phase_id = p_pricing_phase_id
1263: union all
1264: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out
1265: er_qual_cur_mod */
1266: qpq.list_header_id,
1267: qpq.list_line_id list_line_id,
1268: qpq.qualifier_group_cnt,
1308: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id
1309: from qp_preq_qual_tmp
1310: where pricing_status_code = G_STATUS_UNCHANGED
1311: and pricing_phase_id = p_pricing_phase_id) qptq,
1312: qp_qualifiers qpq ,
1313: qp_npreq_line_attrs_tmp qplatq,
1314: qp_list_headers_b qph,
1315: qp_price_req_sources_v qprs
1316: where
1363: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1364: --and qptq.pricing_phase_id = p_pricing_phase_id
1365: union all
1366: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1367: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1368: qpq.list_header_id,
1369: qpq.list_line_id list_line_id,
1370: qpq.qualifier_group_cnt,
1371: qpq.others_group_cnt,
1410: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id,qualifier_grouping_no
1411: from qp_preq_qual_tmp
1412: where pricing_status_code = G_STATUS_UNCHANGED
1413: and pricing_phase_id = p_pricing_phase_id) qptq,
1414: qp_qualifiers qpq ,
1415: qp_npreq_line_attrs_tmp qplatq,
1416: qp_list_headers_b qph,
1417: qp_price_req_sources_v qprs
1418: where
1464: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1465: --and qptq.pricing_phase_id = p_pricing_phase_id
1466: union all
1467: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1468: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1469: qpq.list_header_id,
1470: qpq.list_line_id list_line_id,
1471: qpq.qualifier_group_cnt,
1472: qpq.others_group_cnt,
1511: (select /*+ index(qp_preq_qual_tmp QP_PREQ_QUAL_TMP_N7) */ distinct list_header_id,list_line_id
1512: from qp_preq_qual_tmp
1513: where pricing_status_code = G_STATUS_UNCHANGED
1514: and pricing_phase_id = p_pricing_phase_id) qptq,
1515: qp_qualifiers qpq ,
1516: qp_npreq_line_attrs_tmp qplatq,
1517: qp_list_headers_b qph,
1518: qp_price_req_sources_v qprs
1519: where
1746: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,DISTINCT_QUALIFIER_FLAG,1
1747: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
1748: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1749:
1750: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1747: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
1748: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1749:
1750: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1755: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1748: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1749:
1750: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1755: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1756:
1749:
1750: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1755: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1756:
1757: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1750: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1755: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1756:
1757: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1758: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
1751: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1752: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1753: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1754: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1755: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1756:
1757: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1758: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
1759:
1763: -- [julin/4396043/4471795] perform header filter before prod check, go to qp_pricing_attributes
1764: -- before qp_npreq_line_attrs_tmp during prod check.
1765: CURSOR l_OUTER_QUAL_CUR IS
1766: -- Qualifier Only
1767: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n4) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
1768: qpq.list_header_id,
1769: qpq.list_line_id list_line_id,
1770: qpq.qualifier_group_cnt,
1771: qpq.others_group_cnt,
1807: qph.limit_exists_flag,
1808: qplatq.derived_qualifier_flag -- Added for TCA
1809: from
1810: qp_npreq_line_attrs_tmp qplatq,
1811: qp_qualifiers qpq ,
1812: qp_list_header_phases qplh,
1813: qp_list_headers_b qph,
1814: qp_price_req_sources_v qprs
1815: where
1885: and qph.source_system_code = qprs.source_system_code
1886: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1887: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1888: union all
1889: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n6) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
1890: qpq.list_header_id,
1891: qpq.list_line_id list_line_id,
1892: qpq.qualifier_group_cnt,
1893: qpq.others_group_cnt,
1929: qph.limit_exists_flag,
1930: qplatq.derived_qualifier_flag -- Added for TCA
1931: from
1932: qp_npreq_line_attrs_tmp qplatq,
1933: qp_qualifiers qpq ,
1934: qp_list_header_phases qplh,
1935: qp_list_headers_b qph,
1936: qp_price_req_sources_v qprs
1937: where
2009: and qph.source_system_code = qprs.source_system_code
2010: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2011: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2012: union all
2013: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
2014: qpq.list_header_id,
2015: qpq.list_line_id list_line_id,
2016: qpq.qualifier_group_cnt,
2017: qpq.others_group_cnt,
2053: qph.limit_exists_flag,
2054: qplatq.derived_qualifier_flag -- Added for TCA
2055: from
2056: qp_npreq_line_attrs_tmp qplatq,
2057: qp_qualifiers qpq ,
2058: qp_list_header_phases qplh,
2059: qp_list_headers_b qph,
2060: qp_price_req_sources_v qprs
2061: where
2134: and qph.source_system_code = qprs.source_system_code
2135: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2136: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2137: union all
2138: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
2139: qpq.list_header_id,
2140: qpq.list_line_id list_line_id,
2141: qpq.qualifier_group_cnt,
2142: qpq.others_group_cnt,
2178: qph.limit_exists_flag,
2179: qplatq.derived_qualifier_flag -- Added for TCA
2180: from
2181: qp_npreq_line_attrs_tmp qplatq,
2182: qp_qualifiers qpq ,
2183: qp_list_header_phases qplh,
2184: qp_list_headers_b qph,
2185: qp_price_req_sources_v qprs
2186: where
2468: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,DISTINCT_QUALIFIER_FLAG,1
2469: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
2470: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2471:
2472: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2469: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
2470: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2471:
2472: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2477: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2470: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2471:
2472: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2477: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2478:
2471:
2472: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2477: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2478:
2479: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2472: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2477: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2478:
2479: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2480: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
2473: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2474: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2475: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2476: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2477: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2478:
2479: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2480: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
2481:
2483: */
2484: -- Changed the cursor l_outer_qual_cur for bug#3002632
2485: CURSOR l_OUTER_QUAL_CUR IS
2486: -- Qualifier Only
2487: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n4) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2488: qpq.list_header_id,
2489: qpq.list_line_id list_line_id,
2490: qpq.qualifier_group_cnt,
2491: qpq.others_group_cnt,
2527: qph.limit_exists_flag,
2528: qplatq.derived_qualifier_flag -- Added for TCA
2529: from
2530: qp_npreq_line_attrs_tmp qplatq,
2531: qp_qualifiers qpq ,
2532: qp_list_header_phases qplh,
2533: qp_list_headers_b qph,
2534: qp_price_req_sources_v qprs
2535: where
2586: and qph.source_system_code = qprs.source_system_code
2587: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2588: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2589: union all
2590: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n6) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2591: qpq.list_header_id,
2592: qpq.list_line_id list_line_id,
2593: qpq.qualifier_group_cnt,
2594: qpq.others_group_cnt,
2630: qph.limit_exists_flag,
2631: qplatq.derived_qualifier_flag -- Added for TCA
2632: from
2633: qp_npreq_line_attrs_tmp qplatq,
2634: qp_qualifiers qpq ,
2635: qp_list_header_phases qplh,
2636: qp_list_headers_b qph,
2637: qp_price_req_sources_v qprs
2638: where
2691: and qph.source_system_code = qprs.source_system_code
2692: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2693: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2694: union all
2695: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2696: qpq.list_header_id,
2697: qpq.list_line_id list_line_id,
2698: qpq.qualifier_group_cnt,
2699: qpq.others_group_cnt,
2736: qplatq.derived_qualifier_flag -- Added for TCA
2737:
2738: from
2739: qp_npreq_line_attrs_tmp qplatq,
2740: qp_qualifiers qpq ,
2741: qp_list_header_phases qplh,
2742: qp_list_headers_b qph,
2743: qp_price_req_sources_v qprs
2744: where
2798: and qph.source_system_code = qprs.source_system_code
2799: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2800: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2801: union all
2802: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2803: qpq.list_header_id,
2804: qpq.list_line_id list_line_id,
2805: qpq.qualifier_group_cnt,
2806: qpq.others_group_cnt,
2843: qplatq.derived_qualifier_flag -- Added for TCA
2844:
2845: from
2846: qp_npreq_line_attrs_tmp qplatq,
2847: qp_qualifiers qpq ,
2848: qp_list_header_phases qplh,
2849: qp_list_headers_b qph,
2850: qp_price_req_sources_v qprs
2851: where
8315: WHERE PRICING_STATUS_CODE = 'X';
8316: --added by yangli for Java Engine project
8317:
8318: -- begin declare shu fix bug 1728757
8319: l_list_header_id qp_qualifiers.list_header_id%type := NULL;
8320: --l_cascade_ask_for_profile VARCHAR2(2):= nvl(FND_PROFILE.value ('QP_CASCADE_ASK_FOR_PROMOTION'), G_NO);
8321: l_cascade_ask_for_profile VARCHAR2(2):= G_NO;
8322: l_ask_for_flag qp_list_headers_b.ask_for_flag%type := NULL;
8323:
13389: ORDER BY a.LIST_HEADER_ID, a.LINE_INDEX,a.LINE_DETAIL_INDEX,a.LIST_LINE_ID,a.GROUPING_NUMBER ;
13390:
13391: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13392: /*
13393: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13394: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13395:
13396: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13397: */
13390:
13391: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13392: /*
13393: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13394: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13395:
13396: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13397: */
13398: CURSOR hdr_lvl_setup_pl_quals_cur(p_list_header_id NUMBER) IS
13395:
13396: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13397: */
13398: CURSOR hdr_lvl_setup_pl_quals_cur(p_list_header_id NUMBER) IS
13399: SELECT /*+ ORDERED index(a QP_QUALIFIERS_N1) */ COUNT(*) NO_OF_QUALIFIERS_IN_GRP --bug 12867791
13400: FROM QP_QUALIFIERS a , QP_LIST_HEADERS_B b
13401: WHERE a.LIST_HEADER_ID = p_list_header_id
13402: AND a.list_line_id = -1
13403: AND a.QUALIFIER_CONTEXT = 'MODLIST'
13396: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13397: */
13398: CURSOR hdr_lvl_setup_pl_quals_cur(p_list_header_id NUMBER) IS
13399: SELECT /*+ ORDERED index(a QP_QUALIFIERS_N1) */ COUNT(*) NO_OF_QUALIFIERS_IN_GRP --bug 12867791
13400: FROM QP_QUALIFIERS a , QP_LIST_HEADERS_B b
13401: WHERE a.LIST_HEADER_ID = p_list_header_id
13402: AND a.list_line_id = -1
13403: AND a.QUALIFIER_CONTEXT = 'MODLIST'
13404: AND a.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4'
13406: AND b.LIST_TYPE_CODE IN ('AGR', 'PRL') ;
13407:
13408: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13409: /*
13410: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13411: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13412:
13413: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13414: */
13407:
13408: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13409: /*
13410: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13411: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13412:
13413: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13414: */
13415:
13415:
13416: CURSOR line_level_setup_pl_quals_cur(p_list_header_id NUMBER,
13417: p_list_line_id NUMBER) IS
13418: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13419: FROM QP_QUALIFIERS a , QP_LIST_HEADERS_B b
13420: WHERE a.LIST_HEADER_ID = p_list_header_id
13421: AND a.list_line_id = p_list_line_id
13422: AND a.QUALIFIER_CONTEXT = 'MODLIST'
13423: AND a.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4'
13425: AND b.LIST_TYPE_CODE IN ('AGR', 'PRL') ;
13426:
13427:
13428: /*
13429: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13430: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13431: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13432: */
13433: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13426:
13427:
13428: /*
13429: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13430: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13431: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13432: */
13433: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13434: p_grouping_number NUMBER) IS
13427:
13428: /*
13429: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13430: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13431: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13432: */
13433: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13434: p_grouping_number NUMBER) IS
13435: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13432: */
13433: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13434: p_grouping_number NUMBER) IS
13435: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13436: FROM QP_QUALIFIERS a
13437: WHERE a.LIST_HEADER_ID = p_list_header_id
13438: AND a.list_line_id = -1
13439: AND a.qualifier_grouping_no = p_grouping_number ;
13440: --AND COMPARISON_OPERATOR_CODE <> 'NOT =';
13444: --That is, it need to present for a modifier/price list to get selected. Hence
13445: --it need to be condisered when counting number of qualifier in group
13446:
13447: /*
13448: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13449: */
13450: CURSOR header_setup_null_group_cur(p_list_header_id NUMBER) IS
13451: SELECT count(*)
13452: FROM qp_qualifiers
13448: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13449: */
13450: CURSOR header_setup_null_group_cur(p_list_header_id NUMBER) IS
13451: SELECT count(*)
13452: FROM qp_qualifiers
13453: WHERE list_header_id = p_list_header_id
13454: AND qualifier_grouping_no = -1
13455: AND list_line_id = -1;
13456: --AND comparison_operator_code <> 'NOT =';
13456: --AND comparison_operator_code <> 'NOT =';
13457:
13458: --count including null and not null group
13459: /*
13460: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13461: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13462: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13463: */
13464: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13457:
13458: --count including null and not null group
13459: /*
13460: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13461: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13462: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13463: */
13464: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13465: SELECT count(*)
13458: --count including null and not null group
13459: /*
13460: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13461: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13462: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13463: */
13464: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13465: SELECT count(*)
13466: FROM qp_qualifiers
13462: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13463: */
13464: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13465: SELECT count(*)
13466: FROM qp_qualifiers
13467: WHERE list_header_id = p_list_header_id
13468: AND list_line_id = -1
13469: AND comparison_operator_code <> 'NOT =';
13470:
13469: AND comparison_operator_code <> 'NOT =';
13470:
13471:
13472: /*
13473: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13474: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13475: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13476: */
13477: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13470:
13471:
13472: /*
13473: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13474: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13475: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13476: */
13477: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13478: p_list_line_id NUMBER) IS
13471:
13472: /*
13473: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13474: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13475: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13476: */
13477: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13478: p_list_line_id NUMBER) IS
13479: SELECT count(*)
13476: */
13477: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13478: p_list_line_id NUMBER) IS
13479: SELECT count(*)
13480: FROM qp_qualifiers
13481: WHERE list_header_id = p_list_header_id
13482: AND list_line_id = p_list_line_id
13483: AND qualifier_grouping_no = -1;
13484: --AND comparison_operator_code <> 'NOT =';
13484: --AND comparison_operator_code <> 'NOT =';
13485:
13486: --count including null and not null group for line level qualifier
13487: /*
13488: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13489: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13490: */
13491: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13492: SELECT count(*)
13485:
13486: --count including null and not null group for line level qualifier
13487: /*
13488: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13489: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13490: */
13491: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13492: SELECT count(*)
13493: FROM qp_qualifiers
13489: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13490: */
13491: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13492: SELECT count(*)
13493: FROM qp_qualifiers
13494: WHERE list_header_id = p_list_header_id
13495: AND list_line_id = p_list_line_id;
13496: --AND comparison_operator_code <> 'NOT =';
13497:
13516: GROUP BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER
13517: ORDER BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER ;
13518:
13519: /*
13520: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13521: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13522: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13523: */
13524: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13517: ORDER BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER ;
13518:
13519: /*
13520: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13521: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13522: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13523: */
13524: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13525: p_list_line_id NUMBER,
13518:
13519: /*
13520: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13521: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13522: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13523: */
13524: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13525: p_list_line_id NUMBER,
13526: p_grouping_no NUMBER) IS
13524: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13525: p_list_line_id NUMBER,
13526: p_grouping_no NUMBER) IS
13527: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13528: FROM QP_QUALIFIERS a
13529: WHERE a.LIST_HEADER_ID = p_list_header_id
13530: AND a.list_line_id = p_list_line_id
13531: AND a.qualifier_grouping_no = p_grouping_no;
13532: --AND COMPARISON_OPERATOR_CODE <> 'NOT =';
13548: AND QUALIFIER_TYPE = G_HEADER_QUALIFIER
13549: AND PRICING_STATUS_CODE = G_STATUS_NEW;
13550:
13551: /*
13552: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13553: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13554: */
13555: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13556: p_list_line_id NUMBER) IS
13549: AND PRICING_STATUS_CODE = G_STATUS_NEW;
13550:
13551: /*
13552: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13553: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13554: */
13555: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13556: p_list_line_id NUMBER) IS
13557: SELECT 'X'
13554: */
13555: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13556: p_list_line_id NUMBER) IS
13557: SELECT 'X'
13558: FROM qp_qualifiers
13559: WHERE list_header_id = p_list_header_id
13560: AND list_line_id = p_list_line_id;
13561:
13562: /*
13559: WHERE list_header_id = p_list_header_id
13560: AND list_line_id = p_list_line_id;
13561:
13562: /*
13563: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13564: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13565: */
13566: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13567: SELECT 'X'
13560: AND list_line_id = p_list_line_id;
13561:
13562: /*
13563: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13564: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13565: */
13566: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13567: SELECT 'X'
13568: FROM qp_qualifiers
13564: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13565: */
13566: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13567: SELECT 'X'
13568: FROM qp_qualifiers
13569: WHERE LIST_HEADER_ID = p_list_header_id
13570: AND list_line_id = -1;
13571:
13572: -- volume attributes that are sourced in engine , we do not want to look at these attrs when doing product grouping
13620: --AND a.PRICING_ATTRIBUTE_CONTEXT IS NOT NULL
13621: --GROUP BY LIST_LINE_ID,ATTRIBUTE_GROUPING_NO;
13622:
13623: /*
13624: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13625: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13626: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13627: */
13628: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13621: --GROUP BY LIST_LINE_ID,ATTRIBUTE_GROUPING_NO;
13622:
13623: /*
13624: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13625: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13626: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13627: */
13628: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13629: SELECT 'X'
13622:
13623: /*
13624: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13625: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13626: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13627: */
13628: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13629: SELECT 'X'
13630: FROM qp_qualifiers
13626: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13627: */
13628: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13629: SELECT 'X'
13630: FROM qp_qualifiers
13631: WHERE list_header_id = p_list_header_id
13632: AND list_line_id = p_list_line_id
13633: AND qualifier_grouping_no <> -1;
13634:
13651: AND pricing_status_code = G_STATUS_NEW -- Expect a bug with this join condition
13652: AND grouping_number <> -1;
13653:
13654: /*
13655: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13656: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13657: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13658: */
13659: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13652: AND grouping_number <> -1;
13653:
13654: /*
13655: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13656: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13657: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13658: */
13659: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13660: SELECT 'X'
13653:
13654: /*
13655: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13656: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13657: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13658: */
13659: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13660: SELECT 'X'
13661: FROM qp_qualifiers
13657: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13658: */
13659: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13660: SELECT 'X'
13661: FROM qp_qualifiers
13662: WHERE list_header_id = p_list_header_id
13663: AND list_line_id = -1
13664: AND qualifier_grouping_no <> -1;
13665:
14691: /*
14692: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14693: */
14694: /*
14695: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14696: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14697: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14698: */
14699: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14692: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14693: */
14694: /*
14695: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14696: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14697: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14698: */
14699: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14700: UPDATE qp_npreq_lines_tmp a
14693: */
14694: /*
14695: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14696: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14697: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14698: */
14699: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14700: UPDATE qp_npreq_lines_tmp a
14701: SET qualifiers_exist_flag = G_YES
14701: SET qualifiers_exist_flag = G_YES
14702: WHERE price_list_header_id IS NOT NULL
14703: AND validated_flag = G_NO
14704: AND EXISTS (SELECT 'x'
14705: FROM QP_QUALIFIERS
14706: WHERE LIST_HEADER_ID = a.PRICE_LIST_HEADER_ID
14707: AND QUALIFIER_CONTEXT <> 'MODLIST'
14708: AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4'
14709: AND ROWNUM = 1);
14750: /*
14751: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd1,-No Index Used-,NA,NA
14752: */
14753: /*
14754: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14755:
14756: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14757: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14758: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14762: */
14763: UPDATE qp_npreq_lines_tmp c
14764: SET primary_qualifiers_match_flag = 'Y'
14765: WHERE EXISTS(
14766: SELECT /*+ index(a QP_QUALIFIERS_N7) */ 'x' --bug#11924681
14767: FROM QP_QUALIFIERS a,
14768: qp_npreq_line_attrs_tmp b
14769: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14770: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14763: UPDATE qp_npreq_lines_tmp c
14764: SET primary_qualifiers_match_flag = 'Y'
14765: WHERE EXISTS(
14766: SELECT /*+ index(a QP_QUALIFIERS_N7) */ 'x' --bug#11924681
14767: FROM QP_QUALIFIERS a,
14768: qp_npreq_line_attrs_tmp b
14769: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14770: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14771: AND b.VALUE_FROM = a.QUALIFIER_ATTR_VALUE
14783: /*
14784: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd2,-No Index Used-,NA,NA
14785: */
14786: /*
14787: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14788: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
14789:
14790: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14791: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14784: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd2,-No Index Used-,NA,NA
14785: */
14786: /*
14787: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14788: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
14789:
14790: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14791: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14792: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14796: UPDATE qp_npreq_lines_tmp c
14797: SET primary_qualifiers_match_flag = 'Y'
14798: WHERE EXISTS(
14799: SELECT 'x'
14800: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14801: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14802: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14803: AND a.COMPARISON_OPERATOR_CODE = 'BETWEEN'
14804: AND b.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
14814: UPDATE qp_npreq_lines_tmp c
14815: SET primary_qualifiers_match_flag = 'Y'
14816: WHERE EXISTS(
14817: SELECT 'x'
14818: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14819: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14820: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14821: AND b.VALUE_FROM <> a.QUALIFIER_ATTR_VALUE
14822: AND a.COMPARISON_OPERATOR_CODE = 'NOT ='
14850: x_status_text OUT NOCOPY VARCHAR2)
14851: AS
14852:
14853: /*
14854: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14855: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14856: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14857:
14858: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14851: AS
14852:
14853: /*
14854: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14855: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14856: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14857:
14858: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14859: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14852:
14853: /*
14854: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14855: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14856: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14857:
14858: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14859: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14860: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14940: ,NULL QUALIFIER_TYPE -- 3863226
14941: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
14942: FROM
14943: qp_npreq_lines_tmp QPLINES,
14944: QP_QUALIFIERS QPQ,
14945: qp_npreq_line_attrs_tmp QPLAT_PROD,
14946: QP_PRICING_ATTRIBUTES QPA,
14947: QP_LIST_LINES QPL,
14948: QP_LIST_HEADERS_B QPH,
15061: ,NULL QUALIFIER_TYPE -- 3863226
15062: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
15063: FROM
15064: qp_npreq_lines_tmp qplines,
15065: QP_QUALIFIERS qpq,
15066: qp_npreq_line_attrs_tmp qplat_prod,
15067: QP_PRICING_ATTRIBUTES qpa,
15068: qp_npreq_line_attrs_tmp qplat_pric,
15069: QP_LIST_LINES qpl,
15188: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15189: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15190: FROM
15191: qp_npreq_lines_tmp QPLINES,
15192: QP_QUALIFIERS QPQ,
15193: QP_QUALIFIERS QPSQ,
15194: qp_npreq_line_attrs_tmp QPLAT_PROD,
15195: qp_npreq_line_attrs_tmp QPLAT_Q,
15196: QP_PRICING_ATTRIBUTES QPA,
15189: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15190: FROM
15191: qp_npreq_lines_tmp QPLINES,
15192: QP_QUALIFIERS QPQ,
15193: QP_QUALIFIERS QPSQ,
15194: qp_npreq_line_attrs_tmp QPLAT_PROD,
15195: qp_npreq_line_attrs_tmp QPLAT_Q,
15196: QP_PRICING_ATTRIBUTES QPA,
15197: QP_LIST_LINES QPL,
15329: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15330: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15331: FROM
15332: qp_npreq_lines_tmp qplines,
15333: QP_QUALIFIERS qpq,
15334: QP_QUALIFIERS qpsq,
15335: qp_npreq_line_attrs_tmp qplat_prod,
15336: QP_PRICING_ATTRIBUTES qpa,
15337: qp_npreq_line_attrs_tmp qplat_pric,
15330: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15331: FROM
15332: qp_npreq_lines_tmp qplines,
15333: QP_QUALIFIERS qpq,
15334: QP_QUALIFIERS qpsq,
15335: qp_npreq_line_attrs_tmp qplat_prod,
15336: QP_PRICING_ATTRIBUTES qpa,
15337: qp_npreq_line_attrs_tmp qplat_pric,
15338: qp_npreq_line_attrs_tmp qplat_q,
15487: ,NULL QUALIFIER_TYPE -- 3863226
15488: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
15489: FROM
15490: qp_npreq_lines_tmp QPLINES,
15491: QP_QUALIFIERS QPQ,
15492: qp_npreq_line_attrs_tmp QPLAT_PROD,
15493: QP_PRICING_ATTRIBUTES QPA,
15494: QP_LIST_LINES QPL,
15495: QP_LIST_HEADERS_B QPH,
15668: ,NULL QUALIFIER_TYPE -- 3863226
15669: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
15670: FROM
15671: qp_npreq_lines_tmp qplines,
15672: QP_QUALIFIERS qpq,
15673: qp_npreq_line_attrs_tmp qplat_prod,
15674: QP_PRICING_ATTRIBUTES qpa,
15675: qp_npreq_line_attrs_tmp qplat_pric,
15676: QP_LIST_LINES qpl,
15855: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15856: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15857: FROM
15858: qp_npreq_lines_tmp QPLINES,
15859: QP_QUALIFIERS QPQ,
15860: QP_QUALIFIERS QPSQ,
15861: qp_npreq_line_attrs_tmp QPLAT_PROD,
15862: qp_npreq_line_attrs_tmp QPLAT_Q,
15863: QP_PRICING_ATTRIBUTES QPA,
15856: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15857: FROM
15858: qp_npreq_lines_tmp QPLINES,
15859: QP_QUALIFIERS QPQ,
15860: QP_QUALIFIERS QPSQ,
15861: qp_npreq_line_attrs_tmp QPLAT_PROD,
15862: qp_npreq_line_attrs_tmp QPLAT_Q,
15863: QP_PRICING_ATTRIBUTES QPA,
15864: QP_LIST_LINES QPL,
16056: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
16057: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
16058: FROM
16059: qp_npreq_lines_tmp qplines,
16060: QP_QUALIFIERS qpq,
16061: QP_QUALIFIERS qpsq,
16062: qp_npreq_line_attrs_tmp qplat_prod,
16063: QP_PRICING_ATTRIBUTES qpa,
16064: qp_npreq_line_attrs_tmp qplat_pric,
16057: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
16058: FROM
16059: qp_npreq_lines_tmp qplines,
16060: QP_QUALIFIERS qpq,
16061: QP_QUALIFIERS qpsq,
16062: qp_npreq_line_attrs_tmp qplat_prod,
16063: QP_PRICING_ATTRIBUTES qpa,
16064: qp_npreq_line_attrs_tmp qplat_pric,
16065: qp_npreq_line_attrs_tmp qplat_q,
17673: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17674: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17678: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17679: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17680:
17681: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17674: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17678: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17679: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17680:
17681: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17682: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17678: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17679: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17680:
17681: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17682: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
17683: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
17710: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17711: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
17712: */
17713: CURSOR L_LIST_QUAL_CUR IS
17714: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_Q QPLAT_PROD QPA QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_qual_cur */
17715: QPLAT_PROD.LINE_INDEX,
17716: QPL.LIST_LINE_ID,
17717: QPL.ARITHMETIC_OPERATOR,
17718: QPL.OPERAND,
17763: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17764: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17765: FROM
17766: qp_npreq_lines_tmp QPLINES,
17767: QP_QUALIFIERS QPQ,
17768: qp_npreq_line_attrs_tmp QPLAT_Q,
17769: qp_npreq_line_attrs_tmp QPLAT_PROD,
17770: QP_PRICING_ATTRIBUTES QPA,
17771: QP_LIST_LINES QPL,
17836: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17837: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17838: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17839: UNION ALL
17840: SELECT /*+ ORDERED USE_NL(QPLINES QPQ QPLAT_Q QPLAT_PROD QPA QPLAT_PRIC QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2)
17841: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17842: QPLAT_PROD.LINE_INDEX,
17843: QPL.LIST_LINE_ID,
17844: QPL.ARITHMETIC_OPERATOR,
17890: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17891: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17892: FROM
17893: qp_npreq_lines_tmp QPLINES,
17894: QP_QUALIFIERS QPQ,
17895: qp_npreq_line_attrs_tmp QPLAT_Q,
17896: qp_npreq_line_attrs_tmp QPLAT_PROD,
17897: QP_PRICING_ATTRIBUTES QPA,
17898: qp_npreq_line_attrs_tmp QPLAT_PRIC,
18189: ORDER BY 1,2;
18190:
18191: -- vivek
18192: CURSOR L_LIST_QUAL_CUR_CURRENCY IS
18193: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_Q QPLAT_PROD QPA QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) l_list_qual_cur */
18194: QPLAT_PROD.LINE_INDEX,
18195: QPL.LIST_LINE_ID,
18196: QPL.ARITHMETIC_OPERATOR,
18197: QPL.OPERAND,
18249: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18250: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18251: FROM
18252: qp_npreq_lines_tmp QPLINES,
18253: QP_QUALIFIERS QPQ,
18254: qp_npreq_line_attrs_tmp QPLAT_Q,
18255: qp_npreq_line_attrs_tmp QPLAT_PROD,
18256: QP_PRICING_ATTRIBUTES QPA,
18257: QP_LIST_LINES QPL,
18374: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18375: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
18376: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
18377: UNION ALL
18378: SELECT /*+ ORDERED USE_NL(QPLINES QPQ QPLAT_Q QPLAT_PROD QPA QPLAT_PRIC QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) l_list_qual_cur */
18379: QPLAT_PROD.LINE_INDEX,
18380: QPL.LIST_LINE_ID,
18381: QPL.ARITHMETIC_OPERATOR,
18382: QPL.OPERAND,
18434: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18435: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18436: FROM
18437: qp_npreq_lines_tmp QPLINES,
18438: QP_QUALIFIERS QPQ,
18439: qp_npreq_line_attrs_tmp QPLAT_Q,
18440: qp_npreq_line_attrs_tmp QPLAT_PROD,
18441: QP_PRICING_ATTRIBUTES QPA,
18442: qp_npreq_line_attrs_tmp QPLAT_PRIC,
21097: atrgrps.descendents_quals_exist
21098: FROM
21099: qp_npreq_lines_tmp qplines,
21100: qp_npreq_line_patrns_tmp qppattmp,
21101: QP_QUALIFIERS qpq,
21102: qp_secu_attr_groups_v atrgrps,
21103: --qp_attribute_groups atrgrps,
21104: --QP_PRICE_REQ_SOURCES_V qprs /* yangli */
21105: qp_pte_source_systems qprs
21244: atrgrps.descendents_quals_exist
21245: FROM
21246: qp_npreq_lines_tmp qplines,
21247: qp_npreq_line_patrns_tmp qppattmp,
21248: QP_QUALIFIERS qpq,
21249: qp_secu_attr_groups_v atrgrps,
21250: --qp_attribute_groups atrgrps,
21251: --QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
21252: qp_pte_source_systems qprs,
21445: qpq.qualifier_precedence,
21446: 'N' descendents_quals_exist
21447: FROM
21448: qp_npreq_lines_tmp qplines,
21449: QP_QUALIFIERS qpq,
21450: qp_list_headers_b qph,
21451: qp_pte_source_systems qprs,
21452: --QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
21453: qp_currency_details qcdt
21629: qpq.qualifier_precedence,
21630: 'N' descendents_quals_exist
21631: FROM
21632: qp_npreq_lines_tmp qplines,
21633: QP_QUALIFIERS qpq,
21634: qp_list_headers_b qph,
21635: qp_pte_source_systems qprs
21636: --QP_PRICE_REQ_SOURCES_V qprs /* yangli */
21637: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
21726: /****************************/
21727: CURSOR L_SEC_SRCH_BOTH_P2_QLFY_N IS
21728:
21729: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_U1)
21730: index(QPQ QP_QUALIFIERS_N4)
21731: index(qpl QP_LIST_LINES_N17)
21732: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
21733: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
21734: index(qprs qp_pte_source_systems_u2)
21773: qpq.qualifier_precedence,
21774: 'N' descendents_quals_exist,
21775: qpl.PRICE_BREAK_TYPE_CODE
21776: FROM qp_npreq_lines_tmp qplines,
21777: QP_QUALIFIERS QPQ,
21778: qp_npreq_line_patrns_tmp qppattmp1,
21779: qp_list_lines qpl,
21780: qp_secu_attr_groups_v qpatrgrp,
21781: --qp_attribute_groups qpatrgrp,
21830:
21831: CURSOR L_SEC_SRCH_BOTH_P2_QLFY_N_CURR IS
21832:
21833: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_U1)
21834: index(QPQ QP_QUALIFIERS_N4)
21835: index(qpl QP_LIST_LINES_N16)
21836: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
21837: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
21838: index(qcdt QP_CURRENCY_DETAILS_N2)
21878: qpq.qualifier_precedence,
21879: 'N' descendents_quals_exist,
21880: qpl.PRICE_BREAK_TYPE_CODE
21881: FROM qp_npreq_lines_tmp qplines,
21882: QP_QUALIFIERS QPQ,
21883: qp_npreq_line_patrns_tmp qppattmp1,
21884: qp_list_lines qpl,
21885: qp_secu_attr_groups_v qpatrgrp,
21886: --qp_attribute_groups qpatrgrp,
22702: and a.pricing_phase_id = G_PRICE_LIST_PHASE_ID
22703: and a.stage = l_stage
22704: AND EXISTS (
22705: SELECT 1
22706: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
22707: WHERE b.list_header_id = a.list_header_id
22708: AND b.list_line_id = a.list_line_id
22709: AND (b.qualifier_grouping_no = a.grouping_no
22710: or
23312: and a.pricing_phase_id = G_PRICE_LIST_PHASE_ID
23313: and a.stage = l_stage
23314: AND EXISTS (
23315: SELECT 1
23316: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
23317: WHERE b.list_header_id = a.list_header_id
23318: AND b.list_line_id = a.list_line_id
23319: AND (b.qualifier_grouping_no = a.grouping_no
23320: or
24362: and a.pricing_phase_id = p_pricing_phase_id
24363: and a.stage = G_STAGE_CACHE_LST_HDR_LIN_SRCH
24364: AND EXISTS (
24365: SELECT 1
24366: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
24367: WHERE b.list_header_id = a.list_header_id
24368: AND b.list_line_id = a.list_line_id
24369: AND (b.qualifier_grouping_no = a.grouping_no
24370: or
24740: AND ROWNUM = 1;
24741:
24742:
24743: /*
24744: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
24745: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
24746: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24747: */
24748: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
24741:
24742:
24743: /*
24744: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
24745: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
24746: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24747: */
24748: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
24749: SELECT LIST_HEADER_ID
24742:
24743: /*
24744: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
24745: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
24746: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24747: */
24748: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
24749: SELECT LIST_HEADER_ID
24750: FROM QP_QUALIFIERS
24746: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24747: */
24748: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
24749: SELECT LIST_HEADER_ID
24750: FROM QP_QUALIFIERS
24751: WHERE LIST_HEADER_ID = p_list_header_id
24752: AND LIST_LINE_ID = -1
24753: AND QUALIFIER_GROUPING_NO <> -1;
24754:
24752: AND LIST_LINE_ID = -1
24753: AND QUALIFIER_GROUPING_NO <> -1;
24754:
24755: /*
24756: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
24757: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24758: */
24759: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
24760: SELECT LIST_LINE_ID
24753: AND QUALIFIER_GROUPING_NO <> -1;
24754:
24755: /*
24756: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
24757: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24758: */
24759: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
24760: SELECT LIST_LINE_ID
24761: FROM QP_QUALIFIERS
24757: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
24758: */
24759: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
24760: SELECT LIST_LINE_ID
24761: FROM QP_QUALIFIERS
24762: WHERE LIST_LINE_ID = p_list_line_id
24763: AND QUALIFIER_GROUPING_NO <> -1;
24764:
24765:
26894: AND c.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
26895: AND c.PRICING_PHASE_ID = p_pricing_phase_id
26896: and c.qualifier_grouping_no = -1
26897: and c.null_group_cnt = c.qualifier_group_cnt
26898: and nvl(c.others_group_cnt,0) = 0 -- to indicate that there are no other groups(coming from qp_qualifiers table)
26899: AND a.LINE_INDEX = b.LINE_INDEX
26900: AND ((c.ASK_FOR_FLAG = G_YES and
26901: (c.LIST_HEADER_ID,b.LINE_INDEX) IN (SELECT list_header_id,line_index
26902: FROM qp_npreq_line_attrs_tmp
27006: -- 3520634 start
27007: -- get rows with search ind 2
27008: CURSOR get_list_headers_with_ind2 IS
27009: SELECT/*+ ORDERED */ 'x'
27010: FROM qp_qualifiers b
27011: WHERE b.search_ind = 2
27012: and b.list_header_id in (select distinct a.list_header_id from qp_preq_qual_tmp a
27013: where a.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
27014: and a.search_ind = 1
30379: and a.pricing_phase_id = p_pricing_phase_id
30380: and a.stage = G_STAGE_CACHE_PL_LIN2
30381: AND EXISTS (
30382: SELECT 1
30383: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
30384: WHERE b.list_header_id = a.list_header_id
30385: AND b.list_line_id = a.list_line_id
30386: AND (b.qualifier_grouping_no = a.grouping_no
30387: or
30636: and a.pricing_phase_id = p_pricing_phase_id
30637: and a.stage = G_STAGE_CACHE_PL_HDR
30638: AND EXISTS (
30639: SELECT 1
30640: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
30641: WHERE b.list_header_id = a.list_header_id
30642: AND b.list_line_id = a.list_line_id
30643: AND (b.qualifier_grouping_no = a.grouping_no
30644: or
30883: and a.pricing_phase_id = p_pricing_phase_id
30884: and a.stage = G_STAGE_CACHE_PL_LIN
30885: AND EXISTS (
30886: SELECT 1
30887: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
30888: WHERE b.list_header_id = a.list_header_id
30889: AND b.list_line_id = a.list_line_id
30890: AND (b.qualifier_grouping_no = a.grouping_no
30891: or
31091: x_status_text OUT NOCOPY VARCHAR2,
31092: p_control_rec IN CONTROL_RECORD_TYPE) -- vivek
31093: AS
31094:
31095: -- [julin/4742368/4766135] Added QP_QUALIFIERS_N1 hint to prevent db from using N2
31096: --[julin/5007789] Tuned query as follows:
31097: -- 1. Performing line filter first.
31098: -- 2. Performing qualifier filter before product filter
31099: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
31103: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31104: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31105: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31106: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31107: INDEX(QPQ QP_QUALIFIERS_N4)
31108: INDEX(QPL QP_LIST_LINES_PK)
31109: l_prod_qual_cur_pl */
31110: qpl.list_header_id,
31111: qpl.list_line_id,
31150: FROM qp_npreq_lines_tmp
31151: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31152: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31153: qp_npreq_line_attrs_tmp qplatq,
31154: qp_qualifiers qpq,
31155: qp_list_headers_b qph,
31156: qp_price_req_sources_v qprs,
31157: qp_npreq_line_attrs_tmp qplatpr,
31158: qp_pricing_attributes qpprod,
31213: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31214: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31215: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31216: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31217: INDEX(QPQ QP_QUALIFIERS_N5)
31218: INDEX(QPL QP_LIST_LINES_PK)
31219: l_prod_qual_cur_pl */
31220: qpl.list_header_id,
31221: qpl.list_line_id,
31260: FROM qp_npreq_lines_tmp
31261: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31262: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31263: qp_npreq_line_attrs_tmp qplatq,
31264: qp_qualifiers qpq,
31265: qp_list_headers_b qph,
31266: qp_price_req_sources_v qprs,
31267: qp_npreq_line_attrs_tmp qplatpr,
31268: qp_pricing_attributes qpprod,
31323: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31324: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31325: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31326: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31327: INDEX(QPQ QP_QUALIFIERS_N6)
31328: INDEX(QPL QP_LIST_LINES_PK)
31329: l_prod_qual_cur_pl */
31330: qpl.list_header_id,
31331: qpl.list_line_id,
31370: FROM qp_npreq_lines_tmp
31371: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31372: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31373: qp_npreq_line_attrs_tmp qplatq,
31374: qp_qualifiers qpq,
31375: qp_list_headers_b qph,
31376: qp_price_req_sources_v qprs,
31377: qp_npreq_line_attrs_tmp qplatpr,
31378: qp_pricing_attributes qpprod,
31435: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
31436: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31437: ORDER BY 4,2;
31438:
31439: --[julin/4742368/4766135] Added QP_QUALIFIERS_N1 hint to prevent db from using N2
31440: --[julin/5007789] Tuned query as follows:
31441: -- 1. Performing line filter first.
31442: -- 2. Performing qualifier filter before product filter
31443: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
31446: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31447: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31448: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31449: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31450: INDEX(QPQ QP_QUALIFIERS_N4)
31451: INDEX(QPL QP_LIST_LINES_PK)
31452: l_prod_qual_cur_pl */
31453: qpl.list_header_id,
31454: qpl.list_line_id,
31499: FROM qp_npreq_lines_tmp
31500: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31501: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31502: qp_npreq_line_attrs_tmp qplatq,
31503: qp_qualifiers qpq,
31504: qp_list_headers_b qph,
31505: qp_price_req_sources_v qprs,
31506: qp_npreq_line_attrs_tmp qplatpr,
31507: qp_pricing_attributes qpprod,
31603: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31604: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31605: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31606: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31607: INDEX(QPQ QP_QUALIFIERS_N5)
31608: INDEX(QPL QP_LIST_LINES_PK)
31609: l_prod_qual_cur_pl */
31610: qpl.list_header_id,
31611: qpl.list_line_id,
31656: FROM qp_npreq_lines_tmp
31657: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31658: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31659: qp_npreq_line_attrs_tmp qplatq,
31660: qp_qualifiers qpq,
31661: qp_list_headers_b qph,
31662: qp_price_req_sources_v qprs,
31663: qp_npreq_line_attrs_tmp qplatpr,
31664: qp_pricing_attributes qpprod,
31760: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31761: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31762: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31763: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31764: INDEX(QPQ QP_QUALIFIERS_N6)
31765: INDEX(QPL QP_LIST_LINES_PK)
31766: l_prod_qual_cur_pl */
31767: qpl.list_header_id,
31768: qpl.list_line_id,
31813: FROM qp_npreq_lines_tmp
31814: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31815: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31816: qp_npreq_line_attrs_tmp qplatq,
31817: qp_qualifiers qpq,
31818: qp_list_headers_b qph,
31819: qp_price_req_sources_v qprs,
31820: qp_npreq_line_attrs_tmp qplatpr,
31821: qp_pricing_attributes qpprod,
31928: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
31929: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
31930: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31931: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31932: INDEX(QPQ QP_QUALIFIERS_N4)
31933: l_prod_qual_pric_cur_pl */
31934: qpl.list_header_id,
31935: qpl.list_line_id,
31936: qpl.list_line_type_code,
31983: FROM qp_npreq_lines_tmp
31984: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31985: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31986: qp_npreq_line_attrs_tmp qplatq,
31987: qp_qualifiers qpq ,
31988: qp_list_headers_b qph,
31989: qp_price_req_sources_v qprs,
31990: qp_npreq_line_attrs_tmp qplatpr,
31991: qp_pricing_attributes qpprod ,
32056: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32057: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32058: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32059: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32060: INDEX(QPQ QP_QUALIFIERS_N5)
32061: l_prod_qual_pric_cur_pl */
32062: qpl.list_header_id,
32063: qpl.list_line_id,
32064: qpl.list_line_type_code,
32111: FROM qp_npreq_lines_tmp
32112: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32113: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32114: qp_npreq_line_attrs_tmp qplatq,
32115: qp_qualifiers qpq ,
32116: qp_list_headers_b qph,
32117: qp_price_req_sources_v qprs,
32118: qp_npreq_line_attrs_tmp qplatpr,
32119: qp_pricing_attributes qpprod ,
32184: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32185: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32186: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32187: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32188: INDEX(QPQ QP_QUALIFIERS_N6)
32189: l_prod_qual_pric_cur_pl */
32190: qpl.list_header_id,
32191: qpl.list_line_id,
32192: qpl.list_line_type_code,
32239: FROM qp_npreq_lines_tmp
32240: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32241: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32242: qp_npreq_line_attrs_tmp qplatq,
32243: qp_qualifiers qpq ,
32244: qp_list_headers_b qph,
32245: qp_price_req_sources_v qprs,
32246: qp_npreq_line_attrs_tmp qplatpr,
32247: qp_pricing_attributes qpprod ,
32326: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32327: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32328: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32329: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32330: INDEX(QPQ QP_QUALIFIERS_N4)
32331: l_prod_qual_pric_cur_pl */
32332: qpl.list_header_id,
32333: qpl.list_line_id,
32334: qpl.list_line_type_code,
32387: FROM qp_npreq_lines_tmp
32388: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32389: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32390: qp_npreq_line_attrs_tmp qplatq,
32391: qp_qualifiers qpq ,
32392: qp_list_headers_b qph,
32393: qp_price_req_sources_v qprs,
32394: qp_npreq_line_attrs_tmp qplatpr,
32395: qp_pricing_attributes qpprod ,
32501: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32502: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32503: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32504: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32505: INDEX(QPQ QP_QUALIFIERS_N5)
32506: l_prod_qual_pric_cur_pl */
32507: qpl.list_header_id,
32508: qpl.list_line_id,
32509: qpl.list_line_type_code,
32562: FROM qp_npreq_lines_tmp
32563: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32564: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32565: qp_npreq_line_attrs_tmp qplatq,
32566: qp_qualifiers qpq ,
32567: qp_list_headers_b qph,
32568: qp_price_req_sources_v qprs,
32569: qp_npreq_line_attrs_tmp qplatpr,
32570: qp_pricing_attributes qpprod ,
32676: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32677: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32678: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32679: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32680: INDEX(QPQ QP_QUALIFIERS_N6)
32681: l_prod_qual_pric_cur_pl */
32682: qpl.list_header_id,
32683: qpl.list_line_id,
32684: qpl.list_line_type_code,
32737: FROM qp_npreq_lines_tmp
32738: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32739: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32740: qp_npreq_line_attrs_tmp qplatq,
32741: qp_qualifiers qpq ,
32742: qp_list_headers_b qph,
32743: qp_price_req_sources_v qprs,
32744: qp_npreq_line_attrs_tmp qplatpr,
32745: qp_pricing_attributes qpprod ,
38436: and a.pricing_phase_id = p_pricing_phase_id
38437: and a.stage = G_STAGE_CACHE_MOD_HDR
38438: AND EXISTS (
38439: SELECT 1
38440: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
38441: WHERE b.list_header_id = a.list_header_id
38442: AND b.list_line_id = a.list_line_id
38443: AND (b.qualifier_grouping_no = a.grouping_no
38444: or
38600: and a.pricing_phase_id = p_pricing_phase_id
38601: and a.stage = G_STAGE_CACHE_MOD_LIN
38602: AND EXISTS (
38603: SELECT 1
38604: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
38605: WHERE b.list_header_id = a.list_header_id
38606: AND b.list_line_id = a.list_line_id
38607: AND (b.qualifier_grouping_no = a.grouping_no
38608: or
39115: and a.pricing_phase_id = p_pricing_phase_id
39116: and a.stage IN (G_STAGE_CACHE_MOD_HDR,G_STAGE_CACHE_MOD_LIN)
39117: AND EXISTS (
39118: SELECT 1
39119: FROM qp_qualifiers b, qp_npreq_line_attrs_tmp c
39120: WHERE b.list_header_id = a.list_header_id
39121: AND b.list_line_id = a.list_line_id
39122: AND (b.qualifier_grouping_no = a.grouping_no
39123: or
39849: PRIMARY_UOM_FLAG,
39850: --SEGMENT_ID,
39851: derived_qualifier_flag
39852: )
39853: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
39854: qpldets.LINE_INDEX, --LINE_INDEX,
39855: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
39856: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
39857: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
39884: qplines.derived_qualifier_flag
39885: FROM
39886: qp_npreq_ldets_tmp qpldets,
39887: QP_PREQ_PATRN_QUAL_TMP qppatq,
39888: qp_qualifiers qpq,
39889: qp_npreq_line_attrs_tmp qplines
39890: WHERE
39891: qpldets.pricing_phase_id = p_pricing_phase_id
39892: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
39901: AND qplines.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
39902: AND qplines.LINE_INDEX = qppatq.LINE_INDEX
39903: UNION
39904: -- -1 qualifiers if other group exist
39905: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
39906: qpldets.LINE_INDEX, --LINE_INDEX,
39907: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
39908: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
39909: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
39936: qplines.derived_qualifier_flag
39937: FROM
39938: qp_npreq_ldets_tmp qpldets,
39939: QP_PREQ_PATRN_QUAL_TMP qppatq,
39940: qp_qualifiers qpq,
39941: qp_npreq_line_attrs_tmp qplines
39942: WHERE
39943: qpldets.pricing_phase_id = p_pricing_phase_id
39944: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
40013: PRIMARY_UOM_FLAG,
40014: --SEGMENT_ID,
40015: derived_qualifier_flag
40016: )
40017: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40018: qpldets.LINE_INDEX, --LINE_INDEX,
40019: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
40020: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40021: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
40048: qplines.derived_qualifier_flag
40049: FROM
40050: qp_npreq_ldets_tmp qpldets,
40051: QP_PREQ_PATRN_QUAL_TMP qppatq,
40052: qp_qualifiers qpq,
40053: qp_npreq_line_attrs_tmp qplines
40054: WHERE
40055: qpldets.pricing_phase_id = p_pricing_phase_id
40056: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
40064: AND qplines.SEGMENT_ID = qpq.SEGMENT_ID
40065: AND qplines.LINE_INDEX = qppatq.LINE_INDEX
40066: UNION all
40067: -- -1 qualifiers if other group exist
40068: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40069: qpldets.LINE_INDEX, --LINE_INDEX,
40070: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
40071: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40072: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
40099: qplines.derived_qualifier_flag
40100: FROM
40101: qp_npreq_ldets_tmp qpldets,
40102: QP_PREQ_PATRN_QUAL_TMP qppatq,
40103: qp_qualifiers qpq,
40104: qp_npreq_line_attrs_tmp qplines
40105: WHERE
40106: qpldets.pricing_phase_id = p_pricing_phase_id
40107: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
40240: MODIFIER_LEVEL_CODE,
40241: --SEGMENT_ID,
40242: derived_qualifier_flag
40243: )
40244: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40245: qppatq.LINE_INDEX, --LINE_INDEX,
40246: NULL, -- LINE_DETAIL_INDEX,
40247: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40248: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40271: --qplines.SEGMENT_ID,
40272: qplines.derived_qualifier_flag
40273: FROM
40274: QP_PREQ_PATRN_QUAL_TMP qppatq,
40275: qp_qualifiers qpq,
40276: qp_npreq_line_attrs_tmp qplines
40277: WHERE
40278: qppatq.pricing_phase_id = p_pricing_phase_id
40279: AND qppatq.stage = p_stage
40288: AND ((nvl(qplines.DERIVED_QUALIFIER_FLAG,'N')='N')
40289: OR (qplines.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
40290: UNION all
40291: -- -1 qualifiers if other group exist
40292: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40293: qppatq.LINE_INDEX, --LINE_INDEX,
40294: null, -- LINE_DETAIL_INDEX,
40295: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40296: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40319: --qplines.SEGMENT_ID,
40320: qplines.derived_qualifier_flag
40321: FROM
40322: QP_PREQ_PATRN_QUAL_TMP qppatq,
40323: qp_qualifiers qpq,
40324: qp_npreq_line_attrs_tmp qplines
40325: WHERE
40326: qppatq.pricing_phase_id = p_pricing_phase_id
40327: AND qppatq.stage = p_stage
40491: MODIFIER_LEVEL_CODE,
40492: --SEGMENT_ID,
40493: derived_qualifier_flag
40494: )
40495: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40496: qppatq.LINE_INDEX, --LINE_INDEX,
40497: NULL, -- LINE_DETAIL_INDEX,
40498: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40499: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40522: --qplines.SEGMENT_ID,
40523: qplines.derived_qualifier_flag
40524: FROM
40525: QP_PREQ_PATRN_QUAL_TMP qppatq,
40526: qp_qualifiers qpq,
40527: qp_npreq_line_attrs_tmp qplines
40528: WHERE
40529: qppatq.pricing_phase_id = p_pricing_phase_id
40530: AND qppatq.stage = p_stage
40539: AND ((nvl(qplines.DERIVED_QUALIFIER_FLAG,'N')='N')
40540: OR (qplines.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
40541: UNION all
40542: -- -1 qualifiers if other group exist
40543: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1)
40544: index(qplines qp_preq_line_attrs_tmp_n8)
40545: index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40546: DISTINCT qppatq.LINE_INDEX, --LINE_INDEX,
40547: null, -- LINE_DETAIL_INDEX,
40572: --qplines.SEGMENT_ID,
40573: qplines.derived_qualifier_flag
40574: FROM
40575: QP_PREQ_PATRN_QUAL_TMP qppatq,
40576: qp_qualifiers qpq,
40577: qp_npreq_line_attrs_tmp qplines
40578: WHERE
40579: qppatq.pricing_phase_id = p_pricing_phase_id
40580: AND qppatq.stage = p_stage