[Home] [Help]
696:
697: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.Mini_Cache_Qualifiers';
698:
699: /*
700: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
697: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.Mini_Cache_Qualifiers';
698:
699: /*
700: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
698:
699: /*
700: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
706: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
699: /*
700: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
706: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
707:
700: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
706: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
707:
708: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
701: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
706: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
707:
708: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
709: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
702: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
703: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
704: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,SEARCH_IND,5
705: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
706: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUP_CNT,7
707:
708: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
709: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
710: INDX,QP_PREQ_GRP.mini_cache_qualifiers.l_mini_outer_qual_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
719: --Added index hints for bug#7480862
720: CURSOR l_MINI_OUTER_QUAL_CUR IS
721: -- Qualifier Only
722: select /*+ ORDERED NO_EXPAND USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6)
723: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
724: qpq.list_header_id,
725: qpq.list_line_id list_line_id,
726: qpq.qualifier_group_cnt,
727: qpq.others_group_cnt,
766: (select distinct list_header_id,list_line_id,qualifier_grouping_no
767: from qp_preq_qual_tmp
768: where pricing_status_code = G_STATUS_UNCHANGED
769: and pricing_phase_id = p_pricing_phase_id) qptq,
770: qp_qualifiers qpq ,
771: qp_npreq_line_attrs_tmp qplatq,
772: qp_list_headers_b qph,
773: qp_price_req_sources_v qprs
774: where
813: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
814: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
815: --and qptq.pricing_phase_id = p_pricing_phase_id
816: union all
817: 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
818: uter_qual_cur_mod */
819: qpq.list_header_id,
820: qpq.list_line_id list_line_id,
821: qpq.qualifier_group_cnt,
861: (select distinct list_header_id,list_line_id
862: from qp_preq_qual_tmp
863: where pricing_status_code = G_STATUS_UNCHANGED
864: and pricing_phase_id = p_pricing_phase_id) qptq,
865: qp_qualifiers qpq ,
866: qp_npreq_line_attrs_tmp qplatq,
867: qp_list_headers_b qph,
868: qp_price_req_sources_v qprs
869: where
906: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
907: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
908: --and qptq.pricing_phase_id = p_pricing_phase_id
909: union all
910: 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 */
911: qpq.list_header_id,
912: qpq.list_line_id list_line_id,
913: qpq.qualifier_group_cnt,
914: qpq.others_group_cnt,
953: (select distinct list_header_id,list_line_id,qualifier_grouping_no
954: from qp_preq_qual_tmp
955: where pricing_status_code = G_STATUS_UNCHANGED
956: and pricing_phase_id = p_pricing_phase_id) qptq,
957: qp_qualifiers qpq ,
958: qp_npreq_line_attrs_tmp qplatq,
959: qp_list_headers_b qph,
960: qp_price_req_sources_v qprs
961: where
1002: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1003: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1004: --and qptq.pricing_phase_id = p_pricing_phase_id
1005: union all
1006: 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
1007: ter_qual_cur_mod */
1008: qpq.list_header_id,
1009: qpq.list_line_id list_line_id,
1010: qpq.qualifier_group_cnt,
1050: (select distinct list_header_id,list_line_id
1051: from qp_preq_qual_tmp
1052: where pricing_status_code = G_STATUS_UNCHANGED
1053: and pricing_phase_id = p_pricing_phase_id) qptq,
1054: qp_qualifiers qpq ,
1055: qp_npreq_line_attrs_tmp qplatq,
1056: qp_list_headers_b qph,
1057: qp_price_req_sources_v qprs
1058: where
1097: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1098: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1099: --and qptq.pricing_phase_id = p_pricing_phase_id
1100: union all
1101: 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 */
1102: qpq.list_header_id,
1103: qpq.list_line_id list_line_id,
1104: qpq.qualifier_group_cnt,
1105: qpq.others_group_cnt,
1144: (select distinct list_header_id,list_line_id,qualifier_grouping_no
1145: from qp_preq_qual_tmp
1146: where pricing_status_code = G_STATUS_UNCHANGED
1147: and pricing_phase_id = p_pricing_phase_id) qptq,
1148: qp_qualifiers qpq ,
1149: qp_npreq_line_attrs_tmp qplatq,
1150: qp_list_headers_b qph,
1151: qp_price_req_sources_v qprs
1152: where
1194: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1195: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1196: --and qptq.pricing_phase_id = p_pricing_phase_id
1197: union all
1198: 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
1199: er_qual_cur_mod */
1200: qpq.list_header_id,
1201: qpq.list_line_id list_line_id,
1202: qpq.qualifier_group_cnt,
1242: (select distinct list_header_id,list_line_id
1243: from qp_preq_qual_tmp
1244: where pricing_status_code = G_STATUS_UNCHANGED
1245: and pricing_phase_id = p_pricing_phase_id) qptq,
1246: qp_qualifiers qpq ,
1247: qp_npreq_line_attrs_tmp qplatq,
1248: qp_list_headers_b qph,
1249: qp_price_req_sources_v qprs
1250: where
1291: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1292: --and qptq.pricing_phase_id = p_pricing_phase_id
1293: union all
1294: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1295: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1296: qpq.list_header_id,
1297: qpq.list_line_id list_line_id,
1298: qpq.qualifier_group_cnt,
1299: qpq.others_group_cnt,
1338: (select distinct list_header_id,list_line_id,qualifier_grouping_no
1339: from qp_preq_qual_tmp
1340: where pricing_status_code = G_STATUS_UNCHANGED
1341: and pricing_phase_id = p_pricing_phase_id) qptq,
1342: qp_qualifiers qpq ,
1343: qp_npreq_line_attrs_tmp qplatq,
1344: qp_list_headers_b qph,
1345: qp_price_req_sources_v qprs
1346: where
1386: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1387: --and qptq.pricing_phase_id = p_pricing_phase_id
1388: union all
1389: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1390: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1391: qpq.list_header_id,
1392: qpq.list_line_id list_line_id,
1393: qpq.qualifier_group_cnt,
1394: qpq.others_group_cnt,
1433: (select distinct list_header_id,list_line_id
1434: from qp_preq_qual_tmp
1435: where pricing_status_code = G_STATUS_UNCHANGED
1436: and pricing_phase_id = p_pricing_phase_id) qptq,
1437: qp_qualifiers qpq ,
1438: qp_npreq_line_attrs_tmp qplatq,
1439: qp_list_headers_b qph,
1440: qp_price_req_sources_v qprs
1441: where
1662: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,DISTINCT_QUALIFIER_FLAG,1
1663: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
1664: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1665:
1666: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1663: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
1664: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1665:
1666: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1671: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1664: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
1665:
1666: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1671: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1672:
1665:
1666: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1671: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1672:
1673: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1666: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1671: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1672:
1673: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1674: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
1667: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
1668: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
1669: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
1670: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
1671: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
1672:
1673: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
1674: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
1675:
1679: -- [julin/4396043/4471795] perform header filter before prod check, go to qp_pricing_attributes
1680: -- before qp_npreq_line_attrs_tmp during prod check.
1681: CURSOR l_OUTER_QUAL_CUR IS
1682: -- Qualifier Only
1683: 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 */
1684: qpq.list_header_id,
1685: qpq.list_line_id list_line_id,
1686: qpq.qualifier_group_cnt,
1687: qpq.others_group_cnt,
1723: qph.limit_exists_flag,
1724: qplatq.derived_qualifier_flag -- Added for TCA
1725: from
1726: qp_npreq_line_attrs_tmp qplatq,
1727: qp_qualifiers qpq ,
1728: qp_list_header_phases qplh,
1729: qp_list_headers_b qph,
1730: qp_price_req_sources_v qprs
1731: where
1794: and qph.source_system_code = qprs.source_system_code
1795: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1796: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1797: union all
1798: 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 */
1799: qpq.list_header_id,
1800: qpq.list_line_id list_line_id,
1801: qpq.qualifier_group_cnt,
1802: qpq.others_group_cnt,
1838: qph.limit_exists_flag,
1839: qplatq.derived_qualifier_flag -- Added for TCA
1840: from
1841: qp_npreq_line_attrs_tmp qplatq,
1842: qp_qualifiers qpq ,
1843: qp_list_header_phases qplh,
1844: qp_list_headers_b qph,
1845: qp_price_req_sources_v qprs
1846: where
1911: and qph.source_system_code = qprs.source_system_code
1912: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1913: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1914: union all
1915: 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 */
1916: qpq.list_header_id,
1917: qpq.list_line_id list_line_id,
1918: qpq.qualifier_group_cnt,
1919: qpq.others_group_cnt,
1955: qph.limit_exists_flag,
1956: qplatq.derived_qualifier_flag -- Added for TCA
1957: from
1958: qp_npreq_line_attrs_tmp qplatq,
1959: qp_qualifiers qpq ,
1960: qp_list_header_phases qplh,
1961: qp_list_headers_b qph,
1962: qp_price_req_sources_v qprs
1963: where
2029: and qph.source_system_code = qprs.source_system_code
2030: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2031: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2032: union all
2033: 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 */
2034: qpq.list_header_id,
2035: qpq.list_line_id list_line_id,
2036: qpq.qualifier_group_cnt,
2037: qpq.others_group_cnt,
2073: qph.limit_exists_flag,
2074: qplatq.derived_qualifier_flag -- Added for TCA
2075: from
2076: qp_npreq_line_attrs_tmp qplatq,
2077: qp_qualifiers qpq ,
2078: qp_list_header_phases qplh,
2079: qp_list_headers_b qph,
2080: qp_price_req_sources_v qprs
2081: where
2356: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,DISTINCT_QUALIFIER_FLAG,1
2357: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
2358: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2359:
2360: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2357: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,ATTRIBUTE_TYPE,2
2358: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2359:
2360: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2365: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2358: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,qp_npreq_line_attrs_tmp_N4,PRICING_STATUS_CODE,3
2359:
2360: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2365: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2366:
2359:
2360: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2365: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2366:
2367: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2360: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2365: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2366:
2367: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2368: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
2361: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
2362: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
2363: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
2364: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,ACTIVE_FLAG,5
2365: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_QUALIFIERS_N4,SEARCH_IND,6
2366:
2367: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,PRICING_PHASE_ID,1
2368: INDX,QP_PREQ_GRP.cache_header_qualifiers.l_outer_qual_cur,QP_LIST_HEADER_PHASES_U1,LIST_HEADER_ID,2
2369:
2371: */
2372: -- Changed the cursor l_outer_qual_cur for bug#3002632
2373: CURSOR l_OUTER_QUAL_CUR IS
2374: -- Qualifier Only
2375: 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 */
2376: qpq.list_header_id,
2377: qpq.list_line_id list_line_id,
2378: qpq.qualifier_group_cnt,
2379: qpq.others_group_cnt,
2415: qph.limit_exists_flag,
2416: qplatq.derived_qualifier_flag -- Added for TCA
2417: from
2418: qp_npreq_line_attrs_tmp qplatq,
2419: qp_qualifiers qpq ,
2420: qp_list_header_phases qplh,
2421: qp_list_headers_b qph,
2422: qp_price_req_sources_v qprs
2423: where
2467: and qph.source_system_code = qprs.source_system_code
2468: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2469: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2470: union all
2471: 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 */
2472: qpq.list_header_id,
2473: qpq.list_line_id list_line_id,
2474: qpq.qualifier_group_cnt,
2475: qpq.others_group_cnt,
2511: qph.limit_exists_flag,
2512: qplatq.derived_qualifier_flag -- Added for TCA
2513: from
2514: qp_npreq_line_attrs_tmp qplatq,
2515: qp_qualifiers qpq ,
2516: qp_list_header_phases qplh,
2517: qp_list_headers_b qph,
2518: qp_price_req_sources_v qprs
2519: where
2565: and qph.source_system_code = qprs.source_system_code
2566: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2567: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2568: union all
2569: 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 */
2570: qpq.list_header_id,
2571: qpq.list_line_id list_line_id,
2572: qpq.qualifier_group_cnt,
2573: qpq.others_group_cnt,
2610: qplatq.derived_qualifier_flag -- Added for TCA
2611:
2612: from
2613: qp_npreq_line_attrs_tmp qplatq,
2614: qp_qualifiers qpq ,
2615: qp_list_header_phases qplh,
2616: qp_list_headers_b qph,
2617: qp_price_req_sources_v qprs
2618: where
2665: and qph.source_system_code = qprs.source_system_code
2666: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2667: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2668: union all
2669: 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 */
2670: qpq.list_header_id,
2671: qpq.list_line_id list_line_id,
2672: qpq.qualifier_group_cnt,
2673: qpq.others_group_cnt,
2710: qplatq.derived_qualifier_flag -- Added for TCA
2711:
2712: from
2713: qp_npreq_line_attrs_tmp qplatq,
2714: qp_qualifiers qpq ,
2715: qp_list_header_phases qplh,
2716: qp_list_headers_b qph,
2717: qp_price_req_sources_v qprs
2718: where
8056: WHERE PRICING_STATUS_CODE = 'X';
8057: --added by yangli for Java Engine project
8058:
8059: -- begin declare shu fix bug 1728757
8060: l_list_header_id qp_qualifiers.list_header_id%type := NULL;
8061: --l_cascade_ask_for_profile VARCHAR2(2):= nvl(FND_PROFILE.value ('QP_CASCADE_ASK_FOR_PROMOTION'), G_NO);
8062: l_cascade_ask_for_profile VARCHAR2(2):= G_NO;
8063: l_ask_for_flag qp_list_headers_b.ask_for_flag%type := NULL;
8064:
13093: ORDER BY a.LIST_HEADER_ID, a.LINE_INDEX,a.LINE_DETAIL_INDEX,a.LIST_LINE_ID,a.GROUPING_NUMBER ;
13094:
13095: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13096: /*
13097: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13098: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13099:
13100: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13101: */
13094:
13095: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13096: /*
13097: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13098: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13099:
13100: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13101: */
13102: CURSOR hdr_lvl_setup_pl_quals_cur(p_list_header_id NUMBER) IS
13100: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13101: */
13102: CURSOR hdr_lvl_setup_pl_quals_cur(p_list_header_id NUMBER) IS
13103: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13104: FROM QP_QUALIFIERS a , QP_LIST_HEADERS_B b
13105: WHERE a.LIST_HEADER_ID = p_list_header_id
13106: AND a.list_line_id = -1
13107: AND a.QUALIFIER_CONTEXT = 'MODLIST'
13108: AND a.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4'
13110: AND b.LIST_TYPE_CODE IN ('AGR', 'PRL') ;
13111:
13112: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13113: /*
13114: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13115: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13116:
13117: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13118: */
13111:
13112: -- Get the count of records where Price list is a qualifier(for secondary price lists only)
13113: /*
13114: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13115: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13116:
13117: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_pl_quals_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
13118: */
13119:
13119:
13120: CURSOR line_level_setup_pl_quals_cur(p_list_header_id NUMBER,
13121: p_list_line_id NUMBER) IS
13122: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13123: FROM QP_QUALIFIERS a , QP_LIST_HEADERS_B b
13124: WHERE a.LIST_HEADER_ID = p_list_header_id
13125: AND a.list_line_id = p_list_line_id
13126: AND a.QUALIFIER_CONTEXT = 'MODLIST'
13127: AND a.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4'
13129: AND b.LIST_TYPE_CODE IN ('AGR', 'PRL') ;
13130:
13131:
13132: /*
13133: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13134: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13135: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13136: */
13137: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13130:
13131:
13132: /*
13133: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13134: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13135: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13136: */
13137: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13138: p_grouping_number NUMBER) IS
13131:
13132: /*
13133: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13134: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13135: INDX,QP_PREQ_GRP.perform_grouping.header_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13136: */
13137: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13138: p_grouping_number NUMBER) IS
13139: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13136: */
13137: CURSOR header_level_setup_quals_cur(p_list_header_id NUMBER,
13138: p_grouping_number NUMBER) IS
13139: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13140: FROM QP_QUALIFIERS a
13141: WHERE a.LIST_HEADER_ID = p_list_header_id
13142: AND a.list_line_id = -1
13143: AND a.qualifier_grouping_no = p_grouping_number ;
13144: --AND COMPARISON_OPERATOR_CODE <> 'NOT =';
13148: --That is, it need to present for a modifier/price list to get selected. Hence
13149: --it need to be condisered when counting number of qualifier in group
13150:
13151: /*
13152: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13153: */
13154: CURSOR header_setup_null_group_cur(p_list_header_id NUMBER) IS
13155: SELECT count(*)
13156: FROM qp_qualifiers
13152: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13153: */
13154: CURSOR header_setup_null_group_cur(p_list_header_id NUMBER) IS
13155: SELECT count(*)
13156: FROM qp_qualifiers
13157: WHERE list_header_id = p_list_header_id
13158: AND qualifier_grouping_no = -1
13159: AND list_line_id = -1;
13160: --AND comparison_operator_code <> 'NOT =';
13160: --AND comparison_operator_code <> 'NOT =';
13161:
13162: --count including null and not null group
13163: /*
13164: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13165: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13166: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13167: */
13168: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13161:
13162: --count including null and not null group
13163: /*
13164: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13165: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13166: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13167: */
13168: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13169: SELECT count(*)
13162: --count including null and not null group
13163: /*
13164: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13165: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13166: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13167: */
13168: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13169: SELECT count(*)
13170: FROM qp_qualifiers
13166: INDX,QP_PREQ_GRP.perform_grouping.header_setup_null_group_cur2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
13167: */
13168: CURSOR header_setup_null_group_cur2(p_list_header_id NUMBER) IS
13169: SELECT count(*)
13170: FROM qp_qualifiers
13171: WHERE list_header_id = p_list_header_id
13172: AND list_line_id = -1
13173: AND comparison_operator_code <> 'NOT =';
13174:
13173: AND comparison_operator_code <> 'NOT =';
13174:
13175:
13176: /*
13177: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13178: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13179: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13180: */
13181: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13174:
13175:
13176: /*
13177: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13178: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13179: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13180: */
13181: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13182: p_list_line_id NUMBER) IS
13175:
13176: /*
13177: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13178: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13179: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13180: */
13181: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13182: p_list_line_id NUMBER) IS
13183: SELECT count(*)
13180: */
13181: CURSOR line_setup_null_group_cur(p_list_header_id NUMBER,
13182: p_list_line_id NUMBER) IS
13183: SELECT count(*)
13184: FROM qp_qualifiers
13185: WHERE list_header_id = p_list_header_id
13186: AND list_line_id = p_list_line_id
13187: AND qualifier_grouping_no = -1;
13188: --AND comparison_operator_code <> 'NOT =';
13188: --AND comparison_operator_code <> 'NOT =';
13189:
13190: --count including null and not null group for line level qualifier
13191: /*
13192: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13193: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13194: */
13195: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13196: SELECT count(*)
13189:
13190: --count including null and not null group for line level qualifier
13191: /*
13192: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13193: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13194: */
13195: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13196: SELECT count(*)
13197: FROM qp_qualifiers
13193: INDX,QP_PREQ_GRP.perform_grouping.line_setup_null_group_cur2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13194: */
13195: CURSOR line_setup_null_group_cur2(p_list_header_id NUMBER,p_list_line_id NUMBER) IS
13196: SELECT count(*)
13197: FROM qp_qualifiers
13198: WHERE list_header_id = p_list_header_id
13199: AND list_line_id = p_list_line_id;
13200: --AND comparison_operator_code <> 'NOT =';
13201:
13220: GROUP BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER
13221: ORDER BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER ;
13222:
13223: /*
13224: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13225: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13226: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13227: */
13228: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13221: ORDER BY LIST_HEADER_ID,LIST_LINE_ID,LINE_INDEX,LINE_DETAIL_INDEX,GROUPING_NUMBER ;
13222:
13223: /*
13224: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13225: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13226: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13227: */
13228: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13229: p_list_line_id NUMBER,
13222:
13223: /*
13224: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13225: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13226: INDX,QP_PREQ_GRP.perform_grouping.line_level_setup_quals_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13227: */
13228: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13229: p_list_line_id NUMBER,
13230: p_grouping_no NUMBER) IS
13228: CURSOR line_level_setup_quals_cur(p_list_header_id NUMBER,
13229: p_list_line_id NUMBER,
13230: p_grouping_no NUMBER) IS
13231: SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
13232: FROM QP_QUALIFIERS a
13233: WHERE a.LIST_HEADER_ID = p_list_header_id
13234: AND a.list_line_id = p_list_line_id
13235: AND a.qualifier_grouping_no = p_grouping_no;
13236: --AND COMPARISON_OPERATOR_CODE <> 'NOT =';
13252: AND QUALIFIER_TYPE = G_HEADER_QUALIFIER
13253: AND PRICING_STATUS_CODE = G_STATUS_NEW;
13254:
13255: /*
13256: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13257: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13258: */
13259: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13260: p_list_line_id NUMBER) IS
13253: AND PRICING_STATUS_CODE = G_STATUS_NEW;
13254:
13255: /*
13256: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13257: INDX,QP_PREQ_GRP.perform_grouping.line_lvl_setup_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13258: */
13259: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13260: p_list_line_id NUMBER) IS
13261: SELECT 'X'
13258: */
13259: CURSOR line_lvl_setup_exist_cur(p_list_header_id NUMBER,
13260: p_list_line_id NUMBER) IS
13261: SELECT 'X'
13262: FROM qp_qualifiers
13263: WHERE list_header_id = p_list_header_id
13264: AND list_line_id = p_list_line_id;
13265:
13266: /*
13263: WHERE list_header_id = p_list_header_id
13264: AND list_line_id = p_list_line_id;
13265:
13266: /*
13267: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13268: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13269: */
13270: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13271: SELECT 'X'
13264: AND list_line_id = p_list_line_id;
13265:
13266: /*
13267: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13268: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13269: */
13270: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13271: SELECT 'X'
13272: FROM qp_qualifiers
13268: INDX,QP_PREQ_GRP.perform_grouping.hdr_lvl_setup_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13269: */
13270: CURSOR hdr_lvl_setup_quals_exist_cur(p_list_header_id NUMBER) IS
13271: SELECT 'X'
13272: FROM qp_qualifiers
13273: WHERE LIST_HEADER_ID = p_list_header_id
13274: AND list_line_id = -1;
13275:
13276: -- volume attributes that are sourced in engine , we do not want to look at these attrs when doing product grouping
13324: --AND a.PRICING_ATTRIBUTE_CONTEXT IS NOT NULL
13325: --GROUP BY LIST_LINE_ID,ATTRIBUTE_GROUPING_NO;
13326:
13327: /*
13328: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13329: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13330: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13331: */
13332: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13325: --GROUP BY LIST_LINE_ID,ATTRIBUTE_GROUPING_NO;
13326:
13327: /*
13328: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13329: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13330: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13331: */
13332: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13333: SELECT 'X'
13326:
13327: /*
13328: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13329: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13330: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13331: */
13332: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13333: SELECT 'X'
13334: FROM qp_qualifiers
13330: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13331: */
13332: CURSOR is_not_null_grp_exists (p_list_header_id NUMBER ,p_list_line_id NUMBER) IS
13333: SELECT 'X'
13334: FROM qp_qualifiers
13335: WHERE list_header_id = p_list_header_id
13336: AND list_line_id = p_list_line_id
13337: AND qualifier_grouping_no <> -1;
13338:
13355: AND pricing_status_code = G_STATUS_NEW -- Expect a bug with this join condition
13356: AND grouping_number <> -1;
13357:
13358: /*
13359: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13360: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13361: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13362: */
13363: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13356: AND grouping_number <> -1;
13357:
13358: /*
13359: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13360: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13361: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13362: */
13363: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13364: SELECT 'X'
13357:
13358: /*
13359: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
13360: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,LIST_LINE_ID,2
13361: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13362: */
13363: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13364: SELECT 'X'
13365: FROM qp_qualifiers
13361: INDX,QP_PREQ_GRP.perform_grouping.is_not_null_grp_exists2,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
13362: */
13363: CURSOR is_not_null_grp_exists2 (p_list_header_id NUMBER) IS
13364: SELECT 'X'
13365: FROM qp_qualifiers
13366: WHERE list_header_id = p_list_header_id
13367: AND list_line_id = -1
13368: AND qualifier_grouping_no <> -1;
13369:
14394: /*
14395: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14396: */
14397: /*
14398: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14399: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14400: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14401: */
14402: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14395: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14396: */
14397: /*
14398: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14399: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14400: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14401: */
14402: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14403: UPDATE qp_npreq_lines_tmp a
14396: */
14397: /*
14398: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14399: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14400: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14401: */
14402: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14403: UPDATE qp_npreq_lines_tmp a
14404: SET qualifiers_exist_flag = G_YES
14404: SET qualifiers_exist_flag = G_YES
14405: WHERE price_list_header_id IS NOT NULL
14406: AND validated_flag = G_NO
14407: AND EXISTS (SELECT 'x'
14408: FROM QP_QUALIFIERS
14409: WHERE LIST_HEADER_ID = a.PRICE_LIST_HEADER_ID
14410: AND QUALIFIER_CONTEXT <> 'MODLIST'
14411: AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4'
14412: AND ROWNUM = 1);
14453: /*
14454: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd1,-No Index Used-,NA,NA
14455: */
14456: /*
14457: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14458:
14459: 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
14460: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14461: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14466: UPDATE qp_npreq_lines_tmp c
14467: SET primary_qualifiers_match_flag = 'Y'
14468: WHERE EXISTS(
14469: SELECT 'x'
14470: FROM QP_QUALIFIERS a,
14471: qp_npreq_line_attrs_tmp b
14472: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14473: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14474: AND b.VALUE_FROM = a.QUALIFIER_ATTR_VALUE
14486: /*
14487: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd2,-No Index Used-,NA,NA
14488: */
14489: /*
14490: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14491: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
14492:
14493: 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
14494: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14487: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_upd2,-No Index Used-,NA,NA
14488: */
14489: /*
14490: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
14491: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
14492:
14493: 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
14494: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14495: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14499: UPDATE qp_npreq_lines_tmp c
14500: SET primary_qualifiers_match_flag = 'Y'
14501: WHERE EXISTS(
14502: SELECT 'x'
14503: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14504: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14505: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14506: AND a.COMPARISON_OPERATOR_CODE = 'BETWEEN'
14507: AND b.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
14517: UPDATE qp_npreq_lines_tmp c
14518: SET primary_qualifiers_match_flag = 'Y'
14519: WHERE EXISTS(
14520: SELECT 'x'
14521: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14522: WHERE b.CONTEXT = a.QUALIFIER_CONTEXT
14523: AND b.ATTRIBUTE = a.QUALIFIER_ATTRIBUTE
14524: AND b.VALUE_FROM <> a.QUALIFIER_ATTR_VALUE
14525: AND a.COMPARISON_OPERATOR_CODE = 'NOT ='
14553: x_status_text OUT NOCOPY VARCHAR2)
14554: AS
14555:
14556: /*
14557: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14558: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14559: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14560:
14561: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14554: AS
14555:
14556: /*
14557: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14558: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14559: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14560:
14561: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14562: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14555:
14556: /*
14557: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
14558: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
14559: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
14560:
14561: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14562: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14563: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14643: ,NULL QUALIFIER_TYPE -- 3863226
14644: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
14645: FROM
14646: qp_npreq_lines_tmp QPLINES,
14647: QP_QUALIFIERS QPQ,
14648: qp_npreq_line_attrs_tmp QPLAT_PROD,
14649: QP_PRICING_ATTRIBUTES QPA,
14650: QP_LIST_LINES QPL,
14651: QP_LIST_HEADERS_B QPH,
14764: ,NULL QUALIFIER_TYPE -- 3863226
14765: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
14766: FROM
14767: qp_npreq_lines_tmp qplines,
14768: QP_QUALIFIERS qpq,
14769: qp_npreq_line_attrs_tmp qplat_prod,
14770: QP_PRICING_ATTRIBUTES qpa,
14771: qp_npreq_line_attrs_tmp qplat_pric,
14772: QP_LIST_LINES qpl,
14891: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
14892: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
14893: FROM
14894: qp_npreq_lines_tmp QPLINES,
14895: QP_QUALIFIERS QPQ,
14896: QP_QUALIFIERS QPSQ,
14897: qp_npreq_line_attrs_tmp QPLAT_PROD,
14898: qp_npreq_line_attrs_tmp QPLAT_Q,
14899: QP_PRICING_ATTRIBUTES QPA,
14892: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
14893: FROM
14894: qp_npreq_lines_tmp QPLINES,
14895: QP_QUALIFIERS QPQ,
14896: QP_QUALIFIERS QPSQ,
14897: qp_npreq_line_attrs_tmp QPLAT_PROD,
14898: qp_npreq_line_attrs_tmp QPLAT_Q,
14899: QP_PRICING_ATTRIBUTES QPA,
14900: QP_LIST_LINES QPL,
15032: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15033: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15034: FROM
15035: qp_npreq_lines_tmp qplines,
15036: QP_QUALIFIERS qpq,
15037: QP_QUALIFIERS qpsq,
15038: qp_npreq_line_attrs_tmp qplat_prod,
15039: QP_PRICING_ATTRIBUTES qpa,
15040: qp_npreq_line_attrs_tmp qplat_pric,
15033: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15034: FROM
15035: qp_npreq_lines_tmp qplines,
15036: QP_QUALIFIERS qpq,
15037: QP_QUALIFIERS qpsq,
15038: qp_npreq_line_attrs_tmp qplat_prod,
15039: QP_PRICING_ATTRIBUTES qpa,
15040: qp_npreq_line_attrs_tmp qplat_pric,
15041: qp_npreq_line_attrs_tmp qplat_q,
15190: ,NULL QUALIFIER_TYPE -- 3863226
15191: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
15192: FROM
15193: qp_npreq_lines_tmp QPLINES,
15194: QP_QUALIFIERS QPQ,
15195: qp_npreq_line_attrs_tmp QPLAT_PROD,
15196: QP_PRICING_ATTRIBUTES QPA,
15197: QP_LIST_LINES QPL,
15198: QP_LIST_HEADERS_B QPH,
15371: ,NULL QUALIFIER_TYPE -- 3863226
15372: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
15373: FROM
15374: qp_npreq_lines_tmp qplines,
15375: QP_QUALIFIERS qpq,
15376: qp_npreq_line_attrs_tmp qplat_prod,
15377: QP_PRICING_ATTRIBUTES qpa,
15378: qp_npreq_line_attrs_tmp qplat_pric,
15379: QP_LIST_LINES qpl,
15558: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15559: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15560: FROM
15561: qp_npreq_lines_tmp QPLINES,
15562: QP_QUALIFIERS QPQ,
15563: QP_QUALIFIERS QPSQ,
15564: qp_npreq_line_attrs_tmp QPLAT_PROD,
15565: qp_npreq_line_attrs_tmp QPLAT_Q,
15566: QP_PRICING_ATTRIBUTES QPA,
15559: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15560: FROM
15561: qp_npreq_lines_tmp QPLINES,
15562: QP_QUALIFIERS QPQ,
15563: QP_QUALIFIERS QPSQ,
15564: qp_npreq_line_attrs_tmp QPLAT_PROD,
15565: qp_npreq_line_attrs_tmp QPLAT_Q,
15566: QP_PRICING_ATTRIBUTES QPA,
15567: QP_LIST_LINES QPL,
15759: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15760: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15761: FROM
15762: qp_npreq_lines_tmp qplines,
15763: QP_QUALIFIERS qpq,
15764: QP_QUALIFIERS qpsq,
15765: qp_npreq_line_attrs_tmp qplat_prod,
15766: QP_PRICING_ATTRIBUTES qpa,
15767: qp_npreq_line_attrs_tmp qplat_pric,
15760: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15761: FROM
15762: qp_npreq_lines_tmp qplines,
15763: QP_QUALIFIERS qpq,
15764: QP_QUALIFIERS qpsq,
15765: qp_npreq_line_attrs_tmp qplat_prod,
15766: QP_PRICING_ATTRIBUTES qpa,
15767: qp_npreq_line_attrs_tmp qplat_pric,
15768: qp_npreq_line_attrs_tmp qplat_q,
17366: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17367: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17371: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17372: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17373:
17374: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17367: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17371: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17372: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17373:
17374: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17375: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17371: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17372: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17373:
17374: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
17375: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
17376: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
17403: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17404: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
17405: */
17406: CURSOR L_LIST_QUAL_CUR IS
17407: 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 */
17408: QPLAT_PROD.LINE_INDEX,
17409: QPL.LIST_LINE_ID,
17410: QPL.ARITHMETIC_OPERATOR,
17411: QPL.OPERAND,
17456: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17457: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17458: FROM
17459: qp_npreq_lines_tmp QPLINES,
17460: QP_QUALIFIERS QPQ,
17461: qp_npreq_line_attrs_tmp QPLAT_Q,
17462: qp_npreq_line_attrs_tmp QPLAT_PROD,
17463: QP_PRICING_ATTRIBUTES QPA,
17464: QP_LIST_LINES QPL,
17529: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17530: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17531: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17532: UNION ALL
17533: 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)
17534: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17535: QPLAT_PROD.LINE_INDEX,
17536: QPL.LIST_LINE_ID,
17537: QPL.ARITHMETIC_OPERATOR,
17583: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17584: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17585: FROM
17586: qp_npreq_lines_tmp QPLINES,
17587: QP_QUALIFIERS QPQ,
17588: qp_npreq_line_attrs_tmp QPLAT_Q,
17589: qp_npreq_line_attrs_tmp QPLAT_PROD,
17590: QP_PRICING_ATTRIBUTES QPA,
17591: qp_npreq_line_attrs_tmp QPLAT_PRIC,
17882: ORDER BY 1,2;
17883:
17884: -- vivek
17885: CURSOR L_LIST_QUAL_CUR_CURRENCY IS
17886: 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 */
17887: QPLAT_PROD.LINE_INDEX,
17888: QPL.LIST_LINE_ID,
17889: QPL.ARITHMETIC_OPERATOR,
17890: QPL.OPERAND,
17942: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17943: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17944: FROM
17945: qp_npreq_lines_tmp QPLINES,
17946: QP_QUALIFIERS QPQ,
17947: qp_npreq_line_attrs_tmp QPLAT_Q,
17948: qp_npreq_line_attrs_tmp QPLAT_PROD,
17949: QP_PRICING_ATTRIBUTES QPA,
17950: QP_LIST_LINES QPL,
18067: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18068: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
18069: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
18070: UNION ALL
18071: 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 */
18072: QPLAT_PROD.LINE_INDEX,
18073: QPL.LIST_LINE_ID,
18074: QPL.ARITHMETIC_OPERATOR,
18075: QPL.OPERAND,
18127: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18128: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18129: FROM
18130: qp_npreq_lines_tmp QPLINES,
18131: QP_QUALIFIERS QPQ,
18132: qp_npreq_line_attrs_tmp QPLAT_Q,
18133: qp_npreq_line_attrs_tmp QPLAT_PROD,
18134: QP_PRICING_ATTRIBUTES QPA,
18135: qp_npreq_line_attrs_tmp QPLAT_PRIC,
20919: AND ROWNUM = 1;
20920:
20921:
20922: /*
20923: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
20924: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
20925: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20926: */
20927: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
20920:
20921:
20922: /*
20923: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
20924: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
20925: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20926: */
20927: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
20928: SELECT LIST_HEADER_ID
20921:
20922: /*
20923: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
20924: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
20925: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20926: */
20927: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
20928: SELECT LIST_HEADER_ID
20929: FROM QP_QUALIFIERS
20925: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_hdr_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20926: */
20927: CURSOR chk_other_hdr_quals_exist_cur(p_list_header_id NUMBER) IS
20928: SELECT LIST_HEADER_ID
20929: FROM QP_QUALIFIERS
20930: WHERE LIST_HEADER_ID = p_list_header_id
20931: AND LIST_LINE_ID = -1
20932: AND QUALIFIER_GROUPING_NO <> -1;
20933:
20931: AND LIST_LINE_ID = -1
20932: AND QUALIFIER_GROUPING_NO <> -1;
20933:
20934: /*
20935: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
20936: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20937: */
20938: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
20939: SELECT LIST_LINE_ID
20932: AND QUALIFIER_GROUPING_NO <> -1;
20933:
20934: /*
20935: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
20936: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20937: */
20938: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
20939: SELECT LIST_LINE_ID
20940: FROM QP_QUALIFIERS
20936: INDX,QP_PREQ_GRP.perform_qualifier_grouping.chk_other_line_quals_exist_cur,QP_QUALIFIERS_N1,QUALIFIER_GROUPING_NO,3
20937: */
20938: CURSOR chk_other_line_quals_exist_cur(p_list_line_id NUMBER) IS
20939: SELECT LIST_LINE_ID
20940: FROM QP_QUALIFIERS
20941: WHERE LIST_LINE_ID = p_list_line_id
20942: AND QUALIFIER_GROUPING_NO <> -1;
20943:
20944:
23055: AND c.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
23056: AND c.PRICING_PHASE_ID = p_pricing_phase_id
23057: and c.qualifier_grouping_no = -1
23058: and c.null_group_cnt = c.qualifier_group_cnt
23059: and nvl(c.others_group_cnt,0) = 0 -- to indicate that there are no other groups(coming from qp_qualifiers table)
23060: AND a.LINE_INDEX = b.LINE_INDEX
23061: AND ((c.ASK_FOR_FLAG = G_YES and
23062: (c.LIST_HEADER_ID,b.LINE_INDEX) IN (SELECT list_header_id,line_index
23063: FROM qp_npreq_line_attrs_tmp
23167: -- 3520634 start
23168: -- get rows with search ind 2
23169: CURSOR get_list_headers_with_ind2 IS
23170: SELECT/*+ ORDERED */ 'x'
23171: FROM qp_qualifiers b
23172: WHERE b.search_ind = 2
23173: and b.list_header_id in (select distinct a.list_header_id from qp_preq_qual_tmp a
23174: where a.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
23175: and a.search_ind = 1
25547: x_status_text OUT NOCOPY VARCHAR2,
25548: p_control_rec IN CONTROL_RECORD_TYPE) -- vivek
25549: AS
25550:
25551: -- [julin/4742368/4766135] Added QP_QUALIFIERS_N1 hint to prevent db from using N2
25552: --[julin/5007789] Tuned query as follows:
25553: -- 1. Performing line filter first.
25554: -- 2. Performing qualifier filter before product filter
25555: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
25557: -- Product and Qualifier Only. No pricing
25558: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25559: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25560: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25561: INDEX(QPQ QP_QUALIFIERS_N4)
25562: l_prod_qual_cur_pl */
25563: qpl.list_header_id,
25564: qpl.list_line_id,
25565: qpl.list_line_type_code,
25603: FROM qp_npreq_lines_tmp
25604: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25605: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25606: qp_npreq_line_attrs_tmp qplatq,
25607: qp_qualifiers qpq,
25608: qp_list_headers_b qph,
25609: qp_price_req_sources_v qprs,
25610: qp_npreq_line_attrs_tmp qplatpr,
25611: qp_pricing_attributes qpprod,
25665: UNION ALL
25666: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25667: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25668: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25669: INDEX(QPQ QP_QUALIFIERS_N5)
25670: l_prod_qual_cur_pl */
25671: qpl.list_header_id,
25672: qpl.list_line_id,
25673: qpl.list_line_type_code,
25711: FROM qp_npreq_lines_tmp
25712: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25713: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25714: qp_npreq_line_attrs_tmp qplatq,
25715: qp_qualifiers qpq,
25716: qp_list_headers_b qph,
25717: qp_price_req_sources_v qprs,
25718: qp_npreq_line_attrs_tmp qplatpr,
25719: qp_pricing_attributes qpprod,
25773: UNION ALL
25774: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25775: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25776: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25777: INDEX(QPQ QP_QUALIFIERS_N6)
25778: l_prod_qual_cur_pl */
25779: qpl.list_header_id,
25780: qpl.list_line_id,
25781: qpl.list_line_type_code,
25819: FROM qp_npreq_lines_tmp
25820: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25821: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25822: qp_npreq_line_attrs_tmp qplatq,
25823: qp_qualifiers qpq,
25824: qp_list_headers_b qph,
25825: qp_price_req_sources_v qprs,
25826: qp_npreq_line_attrs_tmp qplatpr,
25827: qp_pricing_attributes qpprod,
25884: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
25885: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
25886: ORDER BY 4,2;
25887:
25888: --[julin/4742368/4766135] Added QP_QUALIFIERS_N1 hint to prevent db from using N2
25889: --[julin/5007789] Tuned query as follows:
25890: -- 1. Performing line filter first.
25891: -- 2. Performing qualifier filter before product filter
25892: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
25894: -- Product and Qualifier Only. No pricing
25895: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25896: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25897: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25898: INDEX(QPQ QP_QUALIFIERS_N4)
25899: l_prod_qual_cur_pl */
25900: qpl.list_header_id,
25901: qpl.list_line_id,
25902: qpl.list_line_type_code,
25946: FROM qp_npreq_lines_tmp
25947: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25948: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25949: qp_npreq_line_attrs_tmp qplatq,
25950: qp_qualifiers qpq,
25951: qp_list_headers_b qph,
25952: qp_price_req_sources_v qprs,
25953: qp_npreq_line_attrs_tmp qplatpr,
25954: qp_pricing_attributes qpprod,
26049: UNION ALL
26050: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26051: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26052: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26053: INDEX(QPQ QP_QUALIFIERS_N5)
26054: l_prod_qual_cur_pl */
26055: qpl.list_header_id,
26056: qpl.list_line_id,
26057: qpl.list_line_type_code,
26101: FROM qp_npreq_lines_tmp
26102: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26103: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26104: qp_npreq_line_attrs_tmp qplatq,
26105: qp_qualifiers qpq,
26106: qp_list_headers_b qph,
26107: qp_price_req_sources_v qprs,
26108: qp_npreq_line_attrs_tmp qplatpr,
26109: qp_pricing_attributes qpprod,
26204: UNION ALL
26205: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26206: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26207: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26208: INDEX(QPQ QP_QUALIFIERS_N6)
26209: l_prod_qual_cur_pl */
26210: qpl.list_header_id,
26211: qpl.list_line_id,
26212: qpl.list_line_type_code,
26256: FROM qp_npreq_lines_tmp
26257: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26258: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26259: qp_npreq_line_attrs_tmp qplatq,
26260: qp_qualifiers qpq,
26261: qp_list_headers_b qph,
26262: qp_price_req_sources_v qprs,
26263: qp_npreq_line_attrs_tmp qplatpr,
26264: qp_pricing_attributes qpprod,
26371: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26372: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26373: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26374: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26375: INDEX(QPQ QP_QUALIFIERS_N4)
26376: l_prod_qual_pric_cur_pl */
26377: qpl.list_header_id,
26378: qpl.list_line_id,
26379: qpl.list_line_type_code,
26426: FROM qp_npreq_lines_tmp
26427: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26428: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26429: qp_npreq_line_attrs_tmp qplatq,
26430: qp_qualifiers qpq ,
26431: qp_list_headers_b qph,
26432: qp_price_req_sources_v qprs,
26433: qp_npreq_line_attrs_tmp qplatpr,
26434: qp_pricing_attributes qpprod ,
26499: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26500: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26501: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26502: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26503: INDEX(QPQ QP_QUALIFIERS_N5)
26504: l_prod_qual_pric_cur_pl */
26505: qpl.list_header_id,
26506: qpl.list_line_id,
26507: qpl.list_line_type_code,
26554: FROM qp_npreq_lines_tmp
26555: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26556: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26557: qp_npreq_line_attrs_tmp qplatq,
26558: qp_qualifiers qpq ,
26559: qp_list_headers_b qph,
26560: qp_price_req_sources_v qprs,
26561: qp_npreq_line_attrs_tmp qplatpr,
26562: qp_pricing_attributes qpprod ,
26627: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26628: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26629: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26630: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26631: INDEX(QPQ QP_QUALIFIERS_N6)
26632: l_prod_qual_pric_cur_pl */
26633: qpl.list_header_id,
26634: qpl.list_line_id,
26635: qpl.list_line_type_code,
26682: FROM qp_npreq_lines_tmp
26683: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26684: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26685: qp_npreq_line_attrs_tmp qplatq,
26686: qp_qualifiers qpq ,
26687: qp_list_headers_b qph,
26688: qp_price_req_sources_v qprs,
26689: qp_npreq_line_attrs_tmp qplatpr,
26690: qp_pricing_attributes qpprod ,
26769: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26770: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26771: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26772: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26773: INDEX(QPQ QP_QUALIFIERS_N4)
26774: l_prod_qual_pric_cur_pl */
26775: qpl.list_header_id,
26776: qpl.list_line_id,
26777: qpl.list_line_type_code,
26830: FROM qp_npreq_lines_tmp
26831: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26832: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26833: qp_npreq_line_attrs_tmp qplatq,
26834: qp_qualifiers qpq ,
26835: qp_list_headers_b qph,
26836: qp_price_req_sources_v qprs,
26837: qp_npreq_line_attrs_tmp qplatpr,
26838: qp_pricing_attributes qpprod ,
26944: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26945: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26946: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26947: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26948: INDEX(QPQ QP_QUALIFIERS_N5)
26949: l_prod_qual_pric_cur_pl */
26950: qpl.list_header_id,
26951: qpl.list_line_id,
26952: qpl.list_line_type_code,
27005: FROM qp_npreq_lines_tmp
27006: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
27007: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27008: qp_npreq_line_attrs_tmp qplatq,
27009: qp_qualifiers qpq ,
27010: qp_list_headers_b qph,
27011: qp_price_req_sources_v qprs,
27012: qp_npreq_line_attrs_tmp qplatpr,
27013: qp_pricing_attributes qpprod ,
27119: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
27120: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
27121: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
27122: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
27123: INDEX(QPQ QP_QUALIFIERS_N6)
27124: l_prod_qual_pric_cur_pl */
27125: qpl.list_header_id,
27126: qpl.list_line_id,
27127: qpl.list_line_type_code,
27180: FROM qp_npreq_lines_tmp
27181: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
27182: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27183: qp_npreq_line_attrs_tmp qplatq,
27184: qp_qualifiers qpq ,
27185: qp_list_headers_b qph,
27186: qp_price_req_sources_v qprs,
27187: qp_npreq_line_attrs_tmp qplatpr,
27188: qp_pricing_attributes qpprod ,