[Home] [Help]
456: FROM ASO_SHIPMENTS
457: WHERE quote_header_id = p_quote_header_id;
458:
459: CURSOR c_hdr_tsn_count IS
460: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
461: B.SUBSTITUTION_VALUE,
462: b.substitution_attribute, lines.line_id,
463: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
464: a.modifier_level_code,a.process_code
461: B.SUBSTITUTION_VALUE,
462: b.substitution_attribute, lines.line_id,
463: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
464: a.modifier_level_code,a.process_code
465: FROM QP_PREQ_LINES_TMP_T lines,
466: QP_PREQ_LDETS_TMP_T A,
467: QP_LIST_LINES B
468: WHERE lines.line_id = p_quote_header_id
469: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
479: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
480:
481:
482: CURSOR c_hdr_pyt_tsn IS
483: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
484: B.SUBSTITUTION_VALUE
485: FROM QP_PREQ_LINES_TMP_T lines,
486: QP_PREQ_LDETS_TMP_T A,
487: QP_LIST_LINES B,
481:
482: CURSOR c_hdr_pyt_tsn IS
483: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
484: B.SUBSTITUTION_VALUE
485: FROM QP_PREQ_LINES_TMP_T lines,
486: QP_PREQ_LDETS_TMP_T A,
487: QP_LIST_LINES B,
488: RA_TERMS_B ratv
489: WHERE lines.line_id = p_quote_header_id
503: AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ratv.start_date_active),TRUNC(sysdate))
504: AND NVL(TRUNC(ratv.end_date_active ), TRUNC(sysdate)));
505:
506: CURSOR c_hdr_smc_tsn IS
507: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
508: B.SUBSTITUTION_VALUE
509: FROM QP_PREQ_LINES_TMP_T lines,
510: QP_PREQ_LDETS_TMP_T A,
511: QP_LIST_LINES B
505:
506: CURSOR c_hdr_smc_tsn IS
507: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
508: B.SUBSTITUTION_VALUE
509: FROM QP_PREQ_LINES_TMP_T lines,
510: QP_PREQ_LDETS_TMP_T A,
511: QP_LIST_LINES B
512: WHERE lines.line_id = p_quote_header_id
513: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
524: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
525:
526:
527: CURSOR c_hdr_ftc_tsn IS
528: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
529: B.SUBSTITUTION_VALUE
530: FROM QP_PREQ_LINES_TMP_T lines,
531: QP_PREQ_LDETS_TMP_T A,
532: QP_LIST_LINES B,
526:
527: CURSOR c_hdr_ftc_tsn IS
528: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
529: B.SUBSTITUTION_VALUE
530: FROM QP_PREQ_LINES_TMP_T lines,
531: QP_PREQ_LDETS_TMP_T A,
532: QP_LIST_LINES B,
533: fnd_lookup_values olk
534: WHERE lines.line_id = p_quote_header_id
966: WHERE quote_header_id = p_quote_header_id
967: AND nvl(pricing_line_type_indicator,'X') = G_FREE_LINE_FLAG;
968:
969: CURSOR c_ln_tsn_count(p_quote_line_id NUMBER) IS
970: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
971: B.SUBSTITUTION_VALUE,
972: b.substitution_attribute, lines.line_id,
973: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
974: a.modifier_level_code,a.process_code
971: B.SUBSTITUTION_VALUE,
972: b.substitution_attribute, lines.line_id,
973: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
974: a.modifier_level_code,a.process_code
975: FROM QP_PREQ_LINES_TMP_T lines,
976: QP_PREQ_LDETS_TMP_T A,
977: QP_LIST_LINES B
978: WHERE lines.line_id = p_quote_line_id
979: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
989: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
990:
991:
992: CURSOR c_ln_smc_tsn (p_quote_line_id NUMBER) IS
993: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
994: B.SUBSTITUTION_VALUE
995: FROM QP_PREQ_LINES_TMP_T lines,
996: QP_PREQ_LDETS_TMP_T A,
997: QP_LIST_LINES B
991:
992: CURSOR c_ln_smc_tsn (p_quote_line_id NUMBER) IS
993: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
994: B.SUBSTITUTION_VALUE
995: FROM QP_PREQ_LINES_TMP_T lines,
996: QP_PREQ_LDETS_TMP_T A,
997: QP_LIST_LINES B
998: WHERE lines.line_id = p_quote_line_id
999: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
1010: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
1011:
1012:
1013: CURSOR c_ln_ftc_tsn (p_quote_line_id NUMBER) IS
1014: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
1015: B.SUBSTITUTION_VALUE
1016: FROM QP_PREQ_LINES_TMP_T lines,
1017: QP_PREQ_LDETS_TMP_T A,
1018: QP_LIST_LINES B,
1012:
1013: CURSOR c_ln_ftc_tsn (p_quote_line_id NUMBER) IS
1014: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
1015: B.SUBSTITUTION_VALUE
1016: FROM QP_PREQ_LINES_TMP_T lines,
1017: QP_PREQ_LDETS_TMP_T A,
1018: QP_LIST_LINES B,
1019: fnd_lookup_values olk
1020: WHERE lines.line_id = p_quote_line_id
1058: WHERE quote_header_id = p_quote_header_id
1059: and quote_line_id = p_quote_line_id;
1060:
1061: CURSOR c_line_pyt_tsn(p_quote_line_id NUMBER) IS
1062: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
1063: B.SUBSTITUTION_VALUE
1064: FROM QP_PREQ_LINES_TMP_T lines,
1065: QP_PREQ_LDETS_TMP_T A,
1066: QP_LIST_LINES B,
1060:
1061: CURSOR c_line_pyt_tsn(p_quote_line_id NUMBER) IS
1062: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
1063: B.SUBSTITUTION_VALUE
1064: FROM QP_PREQ_LINES_TMP_T lines,
1065: QP_PREQ_LDETS_TMP_T A,
1066: QP_LIST_LINES B,
1067: RA_TERMS_B ratv
1068: WHERE lines.line_id = p_quote_line_id
9285: CURSOR C_status_code IS
9286: SELECT line_id,
9287: pricing_status_code,
9288: pricing_status_text
9289: FROM qp_preq_lines_tmp lines
9290: WHERE lines.line_type_code='LINE'
9291: AND lines.pricing_status_code in(
9292: QP_PREQ_GRP.g_status_invalid_price_list,
9293: QP_PREQ_GRP.g_sts_lhs_not_found,
9306:
9307: CURSOR C_status_code_GSA IS
9308: SELECT pricing_status_code,
9309: pricing_status_text
9310: FROM qp_preq_lines_tmp lines
9311: WHERE lines.line_type_code='LINE'
9312: AND lines.pricing_status_code = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
9313:
9314: /*This cursor is just for debugging purpose*/
9337: rltd.REQUEST_ID,
9338: lines.line_id,
9339: lines.process_status
9340: FROM QP_PREQ_RLTD_LINES_TMP rltd,
9341: QP_PREQ_LINES_TMP lines
9342: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9343: AND RLTD.Relationship_Type_Code in (QP_PREQ_GRP.G_PBH_LINE ,QP_PREQ_GRP.G_GENERATED_LINE )
9344: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9345: AND lines.line_type_code = 'LINE'
9476: ,lines.priced_uom_code
9477: ,sysdate
9478: ,G_USER_ID
9479: ,G_LOGIN_ID
9480: FROM qp_preq_lines_tmp lines
9481: WHERE lines.line_id=l.quote_line_id
9482: AND lines.line_type_code='LINE'
9483: )
9484: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9483: )
9484: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9485: AND l.quote_line_id IN
9486: (SELECT line_id
9487: FROM qp_preq_lines_tmp lines
9488: WHERE lines.pricing_status_code
9489: IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9490: AND lines.line_type_code='LINE');
9491:
9527: ,G_USER_ID
9528: ,G_LOGIN_ID
9529: ,'N'--recalculate_flag - reset back to the default value
9530: ,'N'--selling_price_change - reset back to the default value
9531: FROM qp_preq_lines_tmp lines
9532: WHERE lines.line_id=l.quote_line_id
9533: AND lines.line_type_code='LINE'
9534: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9535: AND lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9536: )
9537: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9538: AND l.quote_line_id IN
9539: (SELECT line_id
9540: FROM qp_preq_lines_tmp lines
9541: WHERE lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9542: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9543: AND lines.line_type_code='LINE');
9544:
9647: ldets_v.change_reason_text,
9648: ldets_v.OVERRIDE_FLAG,
9649: ldets_v.operand_value,
9650: ldets_v.adjustment_amount
9651: FROM QP_PREQ_LINES_TMP lines,
9652: QP_LDETS_V ldets_v
9653: WHERE lines.line_index = ldets_v.line_index
9654: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9655: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
9720: ,ADJ.PRICE_ADJUSTMENT_ID
9721: ,RADJ.PRICE_ADJUSTMENT_ID
9722: FROM
9723: QP_PREQ_RLTD_LINES_TMP RLTD,
9724: QP_PREQ_LINES_TMP LINE,
9725: QP_PREQ_LINES_TMP RLTD_LINE,
9726: ASO_PRICE_ADJUSTMENTS ADJ,
9727: ASO_PRICE_ADJUSTMENTS RADJ
9728: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9721: ,RADJ.PRICE_ADJUSTMENT_ID
9722: FROM
9723: QP_PREQ_RLTD_LINES_TMP RLTD,
9724: QP_PREQ_LINES_TMP LINE,
9725: QP_PREQ_LINES_TMP RLTD_LINE,
9726: ASO_PRICE_ADJUSTMENTS ADJ,
9727: ASO_PRICE_ADJUSTMENTS RADJ
9728: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9729: AND RLTD_LINE.line_index = RLTD.related_line_index
9783: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
9784: 'QP_ATTR_DEFNS_PRICING')
9785: FROM
9786: ASO_PRICE_ADJUSTMENTS ADJ,
9787: QP_PREQ_LINES_TMP LINES ,
9788: QP_PREQ_LDETS_TMP LDETS,
9789: QP_PREQ_LINE_ATTRS_TMP QPLAT
9790: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
9791: AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
9834: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
9835: 'QP_ATTR_DEFNS_PRICING')
9836: FROM
9837: ASO_PRICE_ADJUSTMENTS ADJ,
9838: QP_PREQ_LINES_TMP LINES ,
9839: QP_PREQ_LDETS_TMP LDETS,
9840: QP_PREQ_LINE_ATTRS_TMP QPLAT
9841: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
9842: AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
9965: ldets_v.change_reason_text,
9966: ldets_v.OVERRIDE_FLAG,
9967: ldets_v.operand_value,
9968: ldets_v.adjustment_amount
9969: FROM QP_PREQ_LINES_TMP lines,
9970: QP_LDETS_V ldets_v
9971: WHERE lines.line_index = ldets_v.line_index
9972: AND ldets_v.modifier_level_code = 'LINE'
9973: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
10040: ,ADJ.PRICE_ADJUSTMENT_ID
10041: ,RADJ.PRICE_ADJUSTMENT_ID
10042: FROM
10043: QP_PREQ_RLTD_LINES_TMP RLTD,
10044: QP_PREQ_LINES_TMP LINE,
10045: QP_PREQ_LINES_TMP RLTD_LINE,
10046: ASO_PRICE_ADJUSTMENTS ADJ,
10047: ASO_PRICE_ADJUSTMENTS RADJ
10048: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
10041: ,RADJ.PRICE_ADJUSTMENT_ID
10042: FROM
10043: QP_PREQ_RLTD_LINES_TMP RLTD,
10044: QP_PREQ_LINES_TMP LINE,
10045: QP_PREQ_LINES_TMP RLTD_LINE,
10046: ASO_PRICE_ADJUSTMENTS ADJ,
10047: ASO_PRICE_ADJUSTMENTS RADJ
10048: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
10049: AND RLTD_LINE.line_index = RLTD.related_line_index
10101: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
10102: 'QP_ATTR_DEFNS_PRICING')
10103: FROM
10104: ASO_PRICE_ADJUSTMENTS ADJ,
10105: QP_PREQ_LINES_TMP LINES ,
10106: QP_PREQ_LDETS_TMP LDETS,
10107: QP_PREQ_LINE_ATTRS_TMP QPLAT
10108: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
10109: AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
10220: ldets_v.change_reason_text,
10221: ldets_v.OVERRIDE_FLAG,
10222: ldets_v.operand_value,
10223: ldets_v.adjustment_amount
10224: FROM QP_PREQ_LINES_TMP lines,
10225: QP_LDETS_V ldets_v
10226: WHERE lines.line_index = ldets_v.line_index
10227: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
10228: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
10267: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
10268: 'QP_ATTR_DEFNS_PRICING')
10269: FROM
10270: ASO_PRICE_ADJUSTMENTS ADJ,
10271: QP_PREQ_LINES_TMP LINES ,
10272: QP_PREQ_LDETS_TMP LDETS,
10273: QP_PREQ_LINE_ATTRS_TMP QPLAT
10274: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
10275: AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
10386: ldets_v.change_reason_text,
10387: ldets_v.OVERRIDE_FLAG,
10388: ldets_v.operand_value,
10389: ldets_v.adjustment_amount
10390: FROM QP_PREQ_LINES_TMP lines,
10391: QP_LDETS_V ldets_v
10392: WHERE lines.line_index = ldets_v.line_index
10393: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
10394: AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
10801: lines.ACTUAL_ORDER_QUANTITY ACTUAL_ORDER_QUANTITY,
10802: attrs.ATTRIBUTE ATTRIBUTE,
10803: attrs.CONTEXT CONTEXT,
10804: attrs.VALUE_FROM VALUE_FROM
10805: FROM QP_PREQ_LINES_TMP lines,
10806: QP_PREQ_LINE_ATTRS_TMP attrs
10807: --WHERE lines.PROCESSED_CODE = QP_PREQ_PUB.G_BY_ENGINE
10808: WHERE lines.process_status in (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_DELETED)
10809: AND lines.line_index = attrs.line_index
11143: ||C_PRG_RLTD_INFO_REC.line_index,1,'Y');
11144: END IF;
11145: SELECT LINE_ID
11146: INTO l_parent_line_id
11147: FROM QP_PREQ_LINES_TMP
11148: WHERE LINE_INDEX = C_PRG_RLTD_INFO_REC.line_index;
11149: IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
11150: aso_debug_pub.add('ASO_PRICING_CORE_PVT: l_parent_line_id:'||l_parent_line_id,1,'Y');
11151: END IF;