[Home] [Help]
441: FROM ASO_SHIPMENTS
442: WHERE quote_header_id = p_quote_header_id;
443:
444: CURSOR c_hdr_tsn_count IS
445: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
446: B.SUBSTITUTION_VALUE,
447: b.substitution_attribute, lines.line_id,
448: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
449: a.modifier_level_code,a.process_code
446: B.SUBSTITUTION_VALUE,
447: b.substitution_attribute, lines.line_id,
448: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
449: a.modifier_level_code,a.process_code
450: FROM QP_PREQ_LINES_TMP_T lines,
451: QP_PREQ_LDETS_TMP_T A,
452: QP_LIST_LINES B
453: WHERE lines.line_id = p_quote_header_id
454: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
464: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
465:
466:
467: CURSOR c_hdr_pyt_tsn IS
468: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
469: B.SUBSTITUTION_VALUE
470: FROM QP_PREQ_LINES_TMP_T lines,
471: QP_PREQ_LDETS_TMP_T A,
472: QP_LIST_LINES B,
466:
467: CURSOR c_hdr_pyt_tsn IS
468: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
469: B.SUBSTITUTION_VALUE
470: FROM QP_PREQ_LINES_TMP_T lines,
471: QP_PREQ_LDETS_TMP_T A,
472: QP_LIST_LINES B,
473: RA_TERMS_B ratv
474: WHERE lines.line_id = p_quote_header_id
488: AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ratv.start_date_active),TRUNC(sysdate))
489: AND NVL(TRUNC(ratv.end_date_active ), TRUNC(sysdate)));
490:
491: CURSOR c_hdr_smc_tsn IS
492: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
493: B.SUBSTITUTION_VALUE
494: FROM QP_PREQ_LINES_TMP_T lines,
495: QP_PREQ_LDETS_TMP_T A,
496: QP_LIST_LINES B
490:
491: CURSOR c_hdr_smc_tsn IS
492: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
493: B.SUBSTITUTION_VALUE
494: FROM QP_PREQ_LINES_TMP_T lines,
495: QP_PREQ_LDETS_TMP_T A,
496: QP_LIST_LINES B
497: WHERE lines.line_id = p_quote_header_id
498: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
509: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
510:
511:
512: CURSOR c_hdr_ftc_tsn IS
513: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
514: B.SUBSTITUTION_VALUE
515: FROM QP_PREQ_LINES_TMP_T lines,
516: QP_PREQ_LDETS_TMP_T A,
517: QP_LIST_LINES B,
511:
512: CURSOR c_hdr_ftc_tsn IS
513: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
514: B.SUBSTITUTION_VALUE
515: FROM QP_PREQ_LINES_TMP_T lines,
516: QP_PREQ_LDETS_TMP_T A,
517: QP_LIST_LINES B,
518: fnd_lookup_values olk
519: WHERE lines.line_id = p_quote_header_id
951: WHERE quote_header_id = p_quote_header_id
952: AND nvl(pricing_line_type_indicator,'X') = G_FREE_LINE_FLAG;
953:
954: CURSOR c_ln_tsn_count(p_quote_line_id NUMBER) IS
955: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
956: B.SUBSTITUTION_VALUE,
957: b.substitution_attribute, lines.line_id,
958: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
959: a.modifier_level_code,a.process_code
956: B.SUBSTITUTION_VALUE,
957: b.substitution_attribute, lines.line_id,
958: lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
959: a.modifier_level_code,a.process_code
960: FROM QP_PREQ_LINES_TMP_T lines,
961: QP_PREQ_LDETS_TMP_T A,
962: QP_LIST_LINES B
963: WHERE lines.line_id = p_quote_line_id
964: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
974: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
975:
976:
977: CURSOR c_ln_smc_tsn (p_quote_line_id NUMBER) IS
978: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
979: B.SUBSTITUTION_VALUE
980: FROM QP_PREQ_LINES_TMP_T lines,
981: QP_PREQ_LDETS_TMP_T A,
982: QP_LIST_LINES B
976:
977: CURSOR c_ln_smc_tsn (p_quote_line_id NUMBER) IS
978: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
979: B.SUBSTITUTION_VALUE
980: FROM QP_PREQ_LINES_TMP_T lines,
981: QP_PREQ_LDETS_TMP_T A,
982: QP_LIST_LINES B
983: WHERE lines.line_id = p_quote_line_id
984: AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
995: and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
996:
997:
998: CURSOR c_ln_ftc_tsn (p_quote_line_id NUMBER) IS
999: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
1000: B.SUBSTITUTION_VALUE
1001: FROM QP_PREQ_LINES_TMP_T lines,
1002: QP_PREQ_LDETS_TMP_T A,
1003: QP_LIST_LINES B,
997:
998: CURSOR c_ln_ftc_tsn (p_quote_line_id NUMBER) IS
999: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
1000: B.SUBSTITUTION_VALUE
1001: FROM QP_PREQ_LINES_TMP_T lines,
1002: QP_PREQ_LDETS_TMP_T A,
1003: QP_LIST_LINES B,
1004: fnd_lookup_values olk
1005: WHERE lines.line_id = p_quote_line_id
1043: WHERE quote_header_id = p_quote_header_id
1044: and quote_line_id = p_quote_line_id;
1045:
1046: CURSOR c_line_pyt_tsn(p_quote_line_id NUMBER) IS
1047: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
1048: B.SUBSTITUTION_VALUE
1049: FROM QP_PREQ_LINES_TMP_T lines,
1050: QP_PREQ_LDETS_TMP_T A,
1051: QP_LIST_LINES B,
1045:
1046: CURSOR c_line_pyt_tsn(p_quote_line_id NUMBER) IS
1047: SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
1048: B.SUBSTITUTION_VALUE
1049: FROM QP_PREQ_LINES_TMP_T lines,
1050: QP_PREQ_LDETS_TMP_T A,
1051: QP_LIST_LINES B,
1052: RA_TERMS_B ratv
1053: WHERE lines.line_id = p_quote_line_id
9269: CURSOR C_status_code IS
9270: SELECT line_id,
9271: pricing_status_code,
9272: pricing_status_text
9273: FROM qp_preq_lines_tmp lines
9274: WHERE lines.line_type_code='LINE'
9275: AND lines.pricing_status_code in(
9276: QP_PREQ_GRP.g_status_invalid_price_list,
9277: QP_PREQ_GRP.g_sts_lhs_not_found,
9290:
9291: CURSOR C_status_code_GSA IS
9292: SELECT pricing_status_code,
9293: pricing_status_text
9294: FROM qp_preq_lines_tmp lines
9295: WHERE lines.line_type_code='LINE'
9296: AND lines.pricing_status_code = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
9297:
9298: /*This cursor is just for debugging purpose*/
9321: rltd.REQUEST_ID,
9322: lines.line_id,
9323: lines.process_status
9324: FROM QP_PREQ_RLTD_LINES_TMP rltd,
9325: QP_PREQ_LINES_TMP lines
9326: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9327: AND RLTD.Relationship_Type_Code in (QP_PREQ_GRP.G_PBH_LINE ,QP_PREQ_GRP.G_GENERATED_LINE )
9328: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9329: AND lines.line_type_code = 'LINE'
9459: ,lines.priced_uom_code
9460: ,sysdate
9461: ,G_USER_ID
9462: ,G_LOGIN_ID
9463: FROM qp_preq_lines_tmp lines
9464: WHERE lines.line_id=l.quote_line_id
9465: AND lines.line_type_code='LINE'
9466: )
9467: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9466: )
9467: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9468: AND l.quote_line_id IN
9469: (SELECT line_id
9470: FROM qp_preq_lines_tmp lines
9471: WHERE lines.pricing_status_code
9472: IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9473: AND lines.line_type_code='LINE');
9474:
9510: ,G_USER_ID
9511: ,G_LOGIN_ID
9512: ,'N'--recalculate_flag - reset back to the default value
9513: ,'N'--selling_price_change - reset back to the default value
9514: FROM qp_preq_lines_tmp lines
9515: WHERE lines.line_id=l.quote_line_id
9516: AND lines.line_type_code='LINE'
9517: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9518: AND lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9519: )
9520: WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
9521: AND l.quote_line_id IN
9522: (SELECT line_id
9523: FROM qp_preq_lines_tmp lines
9524: WHERE lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
9525: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9526: AND lines.line_type_code='LINE');
9527:
9630: ldets_v.change_reason_text,
9631: ldets_v.OVERRIDE_FLAG,
9632: ldets_v.operand_value,
9633: ldets_v.adjustment_amount
9634: FROM QP_PREQ_LINES_TMP lines,
9635: QP_LDETS_V ldets_v
9636: WHERE lines.line_index = ldets_v.line_index
9637: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
9638: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
9703: ,ADJ.PRICE_ADJUSTMENT_ID
9704: ,RADJ.PRICE_ADJUSTMENT_ID
9705: FROM
9706: QP_PREQ_RLTD_LINES_TMP RLTD,
9707: QP_PREQ_LINES_TMP LINE,
9708: QP_PREQ_LINES_TMP RLTD_LINE,
9709: ASO_PRICE_ADJUSTMENTS ADJ,
9710: ASO_PRICE_ADJUSTMENTS RADJ
9711: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9704: ,RADJ.PRICE_ADJUSTMENT_ID
9705: FROM
9706: QP_PREQ_RLTD_LINES_TMP RLTD,
9707: QP_PREQ_LINES_TMP LINE,
9708: QP_PREQ_LINES_TMP RLTD_LINE,
9709: ASO_PRICE_ADJUSTMENTS ADJ,
9710: ASO_PRICE_ADJUSTMENTS RADJ
9711: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
9712: AND RLTD_LINE.line_index = RLTD.related_line_index
9766: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
9767: 'QP_ATTR_DEFNS_PRICING')
9768: FROM
9769: ASO_PRICE_ADJUSTMENTS ADJ,
9770: QP_PREQ_LINES_TMP LINES ,
9771: QP_PREQ_LDETS_TMP LDETS,
9772: QP_PREQ_LINE_ATTRS_TMP QPLAT
9773: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
9774: AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
9817: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
9818: 'QP_ATTR_DEFNS_PRICING')
9819: FROM
9820: ASO_PRICE_ADJUSTMENTS ADJ,
9821: QP_PREQ_LINES_TMP LINES ,
9822: QP_PREQ_LDETS_TMP LDETS,
9823: QP_PREQ_LINE_ATTRS_TMP QPLAT
9824: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
9825: AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
9948: ldets_v.change_reason_text,
9949: ldets_v.OVERRIDE_FLAG,
9950: ldets_v.operand_value,
9951: ldets_v.adjustment_amount
9952: FROM QP_PREQ_LINES_TMP lines,
9953: QP_LDETS_V ldets_v
9954: WHERE lines.line_index = ldets_v.line_index
9955: AND ldets_v.modifier_level_code = 'LINE'
9956: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
10023: ,ADJ.PRICE_ADJUSTMENT_ID
10024: ,RADJ.PRICE_ADJUSTMENT_ID
10025: FROM
10026: QP_PREQ_RLTD_LINES_TMP RLTD,
10027: QP_PREQ_LINES_TMP LINE,
10028: QP_PREQ_LINES_TMP RLTD_LINE,
10029: ASO_PRICE_ADJUSTMENTS ADJ,
10030: ASO_PRICE_ADJUSTMENTS RADJ
10031: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
10024: ,RADJ.PRICE_ADJUSTMENT_ID
10025: FROM
10026: QP_PREQ_RLTD_LINES_TMP RLTD,
10027: QP_PREQ_LINES_TMP LINE,
10028: QP_PREQ_LINES_TMP RLTD_LINE,
10029: ASO_PRICE_ADJUSTMENTS ADJ,
10030: ASO_PRICE_ADJUSTMENTS RADJ
10031: WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
10032: AND RLTD_LINE.line_index = RLTD.related_line_index
10084: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
10085: 'QP_ATTR_DEFNS_PRICING')
10086: FROM
10087: ASO_PRICE_ADJUSTMENTS ADJ,
10088: QP_PREQ_LINES_TMP LINES ,
10089: QP_PREQ_LDETS_TMP LDETS,
10090: QP_PREQ_LINE_ATTRS_TMP QPLAT
10091: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
10092: AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
10203: ldets_v.change_reason_text,
10204: ldets_v.OVERRIDE_FLAG,
10205: ldets_v.operand_value,
10206: ldets_v.adjustment_amount
10207: FROM QP_PREQ_LINES_TMP lines,
10208: QP_LDETS_V ldets_v
10209: WHERE lines.line_index = ldets_v.line_index
10210: AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
10211: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
10250: 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
10251: 'QP_ATTR_DEFNS_PRICING')
10252: FROM
10253: ASO_PRICE_ADJUSTMENTS ADJ,
10254: QP_PREQ_LINES_TMP LINES ,
10255: QP_PREQ_LDETS_TMP LDETS,
10256: QP_PREQ_LINE_ATTRS_TMP QPLAT
10257: WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
10258: AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
10369: ldets_v.change_reason_text,
10370: ldets_v.OVERRIDE_FLAG,
10371: ldets_v.operand_value,
10372: ldets_v.adjustment_amount
10373: FROM QP_PREQ_LINES_TMP lines,
10374: QP_LDETS_V ldets_v
10375: WHERE lines.line_index = ldets_v.line_index
10376: AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
10377: AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
10784: lines.ACTUAL_ORDER_QUANTITY ACTUAL_ORDER_QUANTITY,
10785: attrs.ATTRIBUTE ATTRIBUTE,
10786: attrs.CONTEXT CONTEXT,
10787: attrs.VALUE_FROM VALUE_FROM
10788: FROM QP_PREQ_LINES_TMP lines,
10789: QP_PREQ_LINE_ATTRS_TMP attrs
10790: --WHERE lines.PROCESSED_CODE = QP_PREQ_PUB.G_BY_ENGINE
10791: WHERE lines.process_status in (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_DELETED)
10792: AND lines.line_index = attrs.line_index
11126: ||C_PRG_RLTD_INFO_REC.line_index,1,'Y');
11127: END IF;
11128: SELECT LINE_ID
11129: INTO l_parent_line_id
11130: FROM QP_PREQ_LINES_TMP
11131: WHERE LINE_INDEX = C_PRG_RLTD_INFO_REC.line_index;
11132: IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
11133: aso_debug_pub.add('ASO_PRICING_CORE_PVT: l_parent_line_id:'||l_parent_line_id,1,'Y');
11134: END IF;