[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LINE_INDEX_tbl.delete;
G_LINE_TYPE_CODE_TBL.delete;
G_PRICING_EFFECTIVE_DATE_TBL.delete;
G_ACTIVE_DATE_FIRST_TBL.delete;
G_ACTIVE_DATE_FIRST_TYPE_TBL.delete;
G_ACTIVE_DATE_SECOND_TBL.delete;
G_ACTIVE_DATE_SECOND_TYPE_TBL.delete;
G_LINE_QUANTITY_TBL.delete;
G_LINE_UOM_CODE_TBL.delete;
G_REQUEST_TYPE_CODE_TBL.delete;
G_PRICED_QUANTITY_TBL.delete;
G_UOM_QUANTITY_TBL.delete;
G_PRICED_UOM_CODE_TBL.delete;
G_CURRENCY_CODE_TBL.delete;
G_UNIT_PRICE_TBL.delete;
G_PERCENT_PRICE_TBL.delete;
G_ADJUSTED_UNIT_PRICE_TBL.delete;
G_UPD_ADJUSTED_UNIT_PRICE_TBL.delete;
G_PROCESSED_FLAG_TBL.delete;
G_PRICE_FLAG_TBL.delete;
G_LINE_ID_TBL.delete;
G_PROCESSING_ORDER_TBL.delete;
G_ROUNDING_FACTOR_TBL.delete;
G_ROUNDING_FLAG_TBL.delete;
G_QUALIFIERS_EXIST_FLAG_TBL.delete;
G_PRICING_ATTRS_EXIST_FLAG_TBL.delete;
G_PRICE_LIST_ID_TBL.delete;
G_PL_VALIDATED_FLAG_TBL.delete;
G_PRICE_REQUEST_CODE_TBL.delete;
G_USAGE_PRICING_TYPE_TBL.delete;
G_LINE_CATEGORY_TBL.delete;
G_PRICING_STATUS_CODE_tbl.delete;
G_PRICING_STATUS_TEXT_tbl.delete;
G_CHRG_PERIODICITY_CODE_TBL.delete;
G_CONTRACT_START_DATE_TBL.delete;
G_CONTRACT_END_DATE_TBL.delete;
G_ATTR_LINE_INDEX_tbl.delete;
G_ATTR_LINE_DETAIL_INDEX_tbl.delete;
G_ATTR_VALIDATED_FLAG_tbl.delete;
G_ATTR_PRICING_CONTEXT_tbl.delete;
G_ATTR_PRICING_ATTRIBUTE_tbl.delete;
G_ATTR_ATTRIBUTE_LEVEL_tbl.delete;
G_ATTR_ATTRIBUTE_TYPE_tbl.delete;
G_ATTR_APPLIED_FLAG_tbl.delete;
G_ATTR_PRICING_STATUS_CODE_tbl.delete;
G_ATTR_PRICING_ATTR_FLAG_tbl.delete;
G_ATTR_LIST_HEADER_ID_tbl.delete;
G_ATTR_LIST_LINE_ID_tbl.delete;
G_ATTR_VALUE_FROM_tbl.delete;
G_ATTR_SETUP_VALUE_FROM_tbl.delete;
G_ATTR_VALUE_TO_tbl.delete;
G_ATTR_SETUP_VALUE_TO_tbl.delete;
G_ATTR_GROUPING_NUMBER_tbl.delete;
G_ATTR_NO_QUAL_IN_GRP_tbl.delete;
G_ATTR_COMP_OPERATOR_TYPE_tbl.delete;
G_ATTR_PRICING_STATUS_TEXT_tbl.delete;
G_ATTR_QUAL_PRECEDENCE_tbl.delete;
G_ATTR_DATATYPE_tbl.delete;
G_ATTR_QUALIFIER_TYPE_tbl.delete;
G_ATTR_PRODUCT_UOM_CODE_TBL.delete;
G_ATTR_EXCLUDER_FLAG_TBL.delete;
G_ATTR_PRICING_PHASE_ID_TBL.delete;
G_ATTR_INCOM_GRP_CODE_TBL.delete;
G_ATTR_LDET_TYPE_CODE_TBL.delete;
G_ATTR_MODIFIER_LEVEL_CODE_TBL.delete;
G_ATTR_PRIMARY_UOM_FLAG_TBL.delete;
G_LDET_LINE_DTL_INDEX_TBL.delete;
G_LDET_PRICE_ADJ_ID_TBL.delete;
G_LDET_LINE_DTL_TYPE_TBL.delete;
G_LDET_PRICE_BREAK_TYPE_TBL.delete;
G_LDET_LIST_PRICE_TBL.delete;
G_LDET_LINE_INDEX_TBL.delete;
G_LDET_LIST_HEADER_ID_TBL.delete;
G_LDET_LIST_LINE_ID_TBL.delete;
G_LDET_LIST_LINE_TYPE_TBL.delete;
G_LDET_LIST_TYPE_CODE_TBL.delete;
G_LDET_CREATED_FROM_SQL_TBL.delete;
G_LDET_PRICING_GRP_SEQ_TBL.delete;
G_LDET_PRICING_PHASE_ID_TBL.delete;
G_LDET_OPERAND_CALC_CODE_TBL.delete;
G_LDET_OPERAND_VALUE_TBL.delete;
G_LDET_SUBSTN_TYPE_TBL.delete;
G_LDET_SUBSTN_VALUE_FROM_TBL.delete;
G_LDET_SUBSTN_VALUE_TO_TBL.delete;
G_LDET_ASK_FOR_FLAG_TBL.delete;
G_LDET_PRICE_FORMULA_ID_TBL.delete;
G_LDET_PRICING_STATUS_CODE_TBL.delete;
G_LDET_PRICING_STATUS_TXT_TBL.delete;
G_LDET_PRODUCT_PRECEDENCE_TBL.delete;
G_LDET_INCOMPAT_GRP_CODE_TBL.delete;
G_LDET_PROCESSED_FLAG_TBL.delete;
G_LDET_APPLIED_FLAG_TBL.delete;
G_LDET_AUTOMATIC_FLAG_TBL.delete;
G_LDET_OVERRIDE_FLAG_TBL.delete;
G_LDET_PRIMARY_UOM_FLAG_TBL.delete;
G_LDET_PRINT_ON_INV_FLAG_TBL.delete;
G_LDET_MODIFIER_LEVEL_TBL.delete;
G_LDET_BENEFIT_QTY_TBL.delete;
G_LDET_BENEFIT_UOM_CODE_TBL.delete;
G_LDET_LIST_LINE_NO_TBL.delete;
G_LDET_ACCRUAL_FLAG_TBL.delete;
G_LDET_ACCR_CONV_RATE_TBL.delete;
G_LDET_ESTIM_ACCR_RATE_TBL.delete;
G_LDET_RECURRING_FLAG_TBL.delete;
G_LDET_SELECTED_VOL_ATTR_TBL.delete;
G_LDET_ROUNDING_FACTOR_TBL.delete;
G_LDET_HDR_LIMIT_EXISTS_TBL.delete;
G_LDET_LINE_LIMIT_EXISTS_TBL.delete;
G_LDET_CHARGE_TYPE_TBL.delete;
G_LDET_CHARGE_SUBTYPE_TBL.delete;
G_LDET_CURRENCY_DTL_ID_TBL.delete;
G_LDET_CURRENCY_HDR_ID_TBL.delete;
G_LDET_SELLING_ROUND_TBL.delete;
G_LDET_ORDER_CURRENCY_TBL.delete;
G_LDET_PRICING_EFF_DATE_TBL.delete;
G_LDET_BASE_CURRENCY_TBL.delete;
G_LDET_LINE_QUANTITY_TBL.delete;
G_LDET_UPDATED_FLAG_TBL.delete;
G_LDET_CALC_CODE_TBL.delete;
G_LDET_CHG_REASON_CODE_TBL.delete;
G_LDET_CHG_REASON_TEXT_TBL.delete;
G_RLTD_LINE_INDEX_TBL.delete;
G_RLTD_LINE_DTL_INDEX_TBL.delete;
G_RLTD_RELATION_TYPE_CODE_TBL.delete;
G_RLTD_RELATED_LINE_IND_TBL.delete;
G_RLTD_RLTD_LINE_DTL_IND_TBL.delete;
G_RLTD_LST_LN_ID_DEF_TBL.delete;
G_RLTD_RLTD_LST_LN_ID_DEF_TBL.delete;
l_header_rec.LAST_UPDATE_DATE := p_qte_header_rec.LAST_UPDATE_DATE;
l_header_rec.LAST_UPDATED_BY := p_qte_header_rec.LAST_UPDATED_BY;
l_header_rec.LAST_UPDATE_LOGIN := p_qte_header_rec.LAST_UPDATE_LOGIN;
l_header_rec.PROGRAM_UPDATE_DATE := p_qte_header_rec.PROGRAM_UPDATE_DATE;
SELECT qte.inventory_item_id
FROM aso_quote_line_details line_dtl
,aso_quote_lines_all qte
WHERE qte.quote_line_id = line_dtl.quote_line_id
AND line_dtl.config_header_id = p_config_hdr_id
AND line_dtl.config_revision_num = p_rev_num
AND line_dtl.ref_type_code = 'CONFIG'
AND line_dtl.ref_line_id is null;
l_line_rec.LAST_UPDATE_DATE := p_qte_line_rec.LAST_UPDATE_DATE;
l_line_rec.LAST_UPDATED_BY := p_qte_line_rec.LAST_UPDATED_BY;
l_line_rec.LAST_UPDATE_LOGIN := p_qte_line_rec.LAST_UPDATE_LOGIN;
l_line_rec.PROGRAM_UPDATE_DATE := p_qte_line_rec.PROGRAM_UPDATE_DATE;
SELECT payment_id
FROM ASO_PAYMENTS
WHERE quote_header_id = p_quote_header_id;
SELECT shipment_id
FROM ASO_SHIPMENTS
WHERE quote_header_id = p_quote_header_id;
SELECT shipment_id
FROM ASO_SHIPMENTS
WHERE quote_header_id = p_quote_header_id;
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE,
b.substitution_attribute, lines.line_id,
lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
a.modifier_level_code,a.process_code
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B
WHERE lines.line_id = p_quote_header_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (p_quote_header_id IS NOT NULL
AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_ORDER_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B,
RA_TERMS_B ratv
WHERE lines.line_id = p_quote_header_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (p_quote_header_id IS NOT NULL
AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE1
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_ORDER_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1)
AND ratv.term_id = B.SUBSTITUTION_VALUE
AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ratv.start_date_active),TRUNC(sysdate))
AND NVL(TRUNC(ratv.end_date_active ), TRUNC(sysdate)));
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B
WHERE lines.line_id = p_quote_header_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (p_quote_header_id IS NOT NULL
AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE11
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_ORDER_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B,
fnd_lookup_values olk
WHERE lines.line_id = p_quote_header_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (p_quote_header_id IS NOT NULL
AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE10
AND A.CREATED_FROM_LIST_LINE_TYPE = G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_ORDER_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
AND olk.lookup_type = G_FREIGHT_TERM_LK_TYPE
AND olk.enabled_flag = G_YES_FLAG
AND olk.lookup_code = B.SUBSTITUTION_VALUE
AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(olk.start_date_active),TRUNC( sysdate))
AND NVL(TRUNC(olk.end_date_active), TRUNC(sysdate)))
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1)
and olk.LANGUAGE = USERENV('LANG')
and olk.VIEW_APPLICATION_ID = 660
and olk.SECURITY_GROUP_ID =0;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Header Level Payment TSN exists hence update with from value...',1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = payment_term_id_from
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Payment Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Payment Record Exists...Before Payment Insert Row',1,'Y');
ASO_PAYMENTS_PKG.Insert_Row(
px_PAYMENT_ID => lx_PAYMENT_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REQUEST_ID => l_payment_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_payment_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_payment_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_payment_rec.PROGRAM_UPDATE_DATE,
p_QUOTE_HEADER_ID => p_quote_header_id,
p_QUOTE_LINE_ID => NULL,
p_PAYMENT_TYPE_CODE => l_payment_rec.PAYMENT_TYPE_CODE,
p_PAYMENT_REF_NUMBER => l_payment_rec.PAYMENT_REF_NUMBER,
p_PAYMENT_OPTION => l_payment_rec.PAYMENT_OPTION,
p_PAYMENT_TERM_ID => l_payment_rec.PAYMENT_TERM_ID,
p_CREDIT_CARD_CODE => l_payment_rec.CREDIT_CARD_CODE,
p_CREDIT_CARD_HOLDER_NAME => l_payment_rec.CREDIT_CARD_HOLDER_NAME,
p_CREDIT_CARD_EXPIRATION_DATE => l_payment_rec.CREDIT_CARD_EXPIRATION_DATE,
p_CREDIT_CARD_APPROVAL_CODE => l_payment_rec.CREDIT_CARD_APPROVAL_CODE,
p_CREDIT_CARD_APPROVAL_DATE => l_payment_rec.CREDIT_CARD_APPROVAL_DATE,
p_PAYMENT_AMOUNT => l_payment_rec.PAYMENT_AMOUNT,
p_ATTRIBUTE_CATEGORY => l_payment_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_payment_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_payment_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_payment_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_payment_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_payment_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_payment_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_payment_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_payment_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_payment_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_payment_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_payment_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_payment_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_payment_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_payment_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_payment_rec.ATTRIBUTE15,
p_ATTRIBUTE16 => l_payment_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => l_payment_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => l_payment_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => l_payment_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => l_payment_rec.ATTRIBUTE20,
p_QUOTE_SHIPMENT_ID => l_payment_rec.QUOTE_SHIPMENT_ID,
p_CUST_PO_NUMBER => l_payment_rec.CUST_PO_NUMBER,
p_PAYMENT_TERM_ID_FROM => l_payment_rec.PAYMENT_TERM_ID_FROM,
p_OBJECT_VERSION_NUMBER => l_payment_rec.OBJECT_VERSION_NUMBER,
p_CUST_PO_LINE_NUMBER => l_payment_rec.CUST_PO_LINE_NUMBER, -- Line Payments Change
p_TRXN_EXTENSION_ID => l_payment_rec.TRXN_EXTENSION_ID
);
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After Payment Insert Row...Payment_ID :'||NVL(lx_payment_id,0),1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = l_req_payment_term_id
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Payment Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Header Level Ship Method Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = ship_method_code_from
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Shipment Record exists...Before Shipment Insert Rows ',1,'Y');
ASO_SHIPMENTS_PKG.Insert_Row(
px_SHIPMENT_ID => lx_shipment_id,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REQUEST_ID => l_shipment_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_shipment_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_shipment_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_shipment_rec.PROGRAM_UPDATE_DATE,
p_QUOTE_HEADER_ID => p_quote_header_id,
p_QUOTE_LINE_ID => NULL,
p_PROMISE_DATE => l_shipment_rec.PROMISE_DATE,
p_REQUEST_DATE => l_shipment_rec.REQUEST_DATE,
p_SCHEDULE_SHIP_DATE => l_shipment_rec.SCHEDULE_SHIP_DATE,
p_SHIP_TO_PARTY_SITE_ID => l_shipment_rec.SHIP_TO_PARTY_SITE_ID,
p_SHIP_TO_PARTY_ID => l_shipment_rec.SHIP_TO_PARTY_ID,
p_SHIP_TO_CUST_ACCOUNT_ID => l_shipment_rec.SHIP_TO_CUST_ACCOUNT_ID,
p_SHIP_PARTIAL_FLAG => l_shipment_rec.SHIP_PARTIAL_FLAG,
p_SHIP_SET_ID => l_shipment_rec.SHIP_SET_ID,
p_SHIP_METHOD_CODE => l_shipment_rec.SHIP_METHOD_CODE,
p_FREIGHT_TERMS_CODE => l_shipment_rec.FREIGHT_TERMS_CODE,
p_FREIGHT_CARRIER_CODE => l_shipment_rec.FREIGHT_CARRIER_CODE,
p_FOB_CODE => l_shipment_rec.FOB_CODE,
p_SHIPPING_INSTRUCTIONS => l_shipment_rec.SHIPPING_INSTRUCTIONS,
p_PACKING_INSTRUCTIONS => l_shipment_rec.PACKING_INSTRUCTIONS,
p_QUANTITY => l_shipment_rec.QUANTITY,
p_RESERVED_QUANTITY => l_shipment_rec.RESERVED_QUANTITY,
p_RESERVATION_ID => l_shipment_rec.RESERVATION_ID,
p_ORDER_LINE_ID => l_shipment_rec.ORDER_LINE_ID,
p_ATTRIBUTE_CATEGORY => l_shipment_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_shipment_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_shipment_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_shipment_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_shipment_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_shipment_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_shipment_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_shipment_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_shipment_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_shipment_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_shipment_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_shipment_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_shipment_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_shipment_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_shipment_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_shipment_rec.ATTRIBUTE15,
p_ATTRIBUTE16 => l_shipment_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => l_shipment_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => l_shipment_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => l_shipment_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => l_shipment_rec.ATTRIBUTE20,
p_SHIPMENT_PRIORITY_CODE => l_shipment_rec.SHIPMENT_PRIORITY_CODE,
p_SHIP_QUOTE_PRICE => l_shipment_rec.SHIP_QUOTE_PRICE,
p_SHIP_FROM_ORG_ID => l_shipment_rec.SHIP_FROM_ORG_ID,
p_SHIP_TO_CUST_PARTY_ID => l_shipment_rec.SHIP_TO_CUST_PARTY_ID,
p_SHIP_METHOD_CODE_FROM => l_shipment_rec.SHIP_METHOD_CODE_FROM,
p_FREIGHT_TERMS_CODE_FROM => l_shipment_rec.FREIGHT_TERMS_CODE_FROM,
p_OBJECT_VERSION_NUMBER => l_shipment_rec.OBJECT_VERSION_NUMBER,
p_request_date_type => l_shipment_rec.request_date_type,
p_demand_class_code => l_shipment_rec.demand_class_code
);
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After Shipment Insert Row...Shipment_ID '||NVL(lx_shipment_id,0),1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = l_req_ship_method_code
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL
AND ('S' = (ASO_VALIDATE_PVT.Validate_ShipMethods('T', l_req_ship_method_code,
aso.ship_from_org_id, p_quote_header_id, NULL)));
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Header Level Freight Terms Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = freight_terms_code_from
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Shipment(Freight) Record exists...Before Shipment(Freight) Insert Rows ',1,'Y');
ASO_SHIPMENTS_PKG.Insert_Row(
px_SHIPMENT_ID => lx_freight_shipment_id,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REQUEST_ID => l_freight_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_freight_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_freight_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_freight_rec.PROGRAM_UPDATE_DATE,
p_QUOTE_HEADER_ID => p_quote_header_id,
p_QUOTE_LINE_ID => NULL,
p_PROMISE_DATE => l_freight_rec.PROMISE_DATE,
p_REQUEST_DATE => l_freight_rec.REQUEST_DATE,
p_SCHEDULE_SHIP_DATE => l_freight_rec.SCHEDULE_SHIP_DATE,
p_SHIP_TO_PARTY_SITE_ID => l_freight_rec.SHIP_TO_PARTY_SITE_ID,
p_SHIP_TO_PARTY_ID => l_freight_rec.SHIP_TO_PARTY_ID,
p_SHIP_TO_CUST_ACCOUNT_ID => l_freight_rec.SHIP_TO_CUST_ACCOUNT_ID,
p_SHIP_PARTIAL_FLAG => l_freight_rec.SHIP_PARTIAL_FLAG,
p_SHIP_SET_ID => l_freight_rec.SHIP_SET_ID,
p_SHIP_METHOD_CODE => l_freight_rec.SHIP_METHOD_CODE,
p_FREIGHT_TERMS_CODE => l_freight_rec.FREIGHT_TERMS_CODE,
p_FREIGHT_CARRIER_CODE => l_freight_rec.FREIGHT_CARRIER_CODE,
p_FOB_CODE => l_freight_rec.FOB_CODE,
p_SHIPPING_INSTRUCTIONS => l_freight_rec.SHIPPING_INSTRUCTIONS,
p_PACKING_INSTRUCTIONS => l_freight_rec.PACKING_INSTRUCTIONS,
p_QUANTITY => l_freight_rec.QUANTITY,
p_RESERVED_QUANTITY => l_freight_rec.RESERVED_QUANTITY,
p_RESERVATION_ID => l_freight_rec.RESERVATION_ID,
p_ORDER_LINE_ID => l_freight_rec.ORDER_LINE_ID,
p_ATTRIBUTE_CATEGORY => l_freight_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_freight_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_freight_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_freight_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_freight_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_freight_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_freight_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_freight_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_freight_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_freight_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_freight_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_freight_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_freight_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_freight_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_freight_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_freight_rec.ATTRIBUTE15,
p_ATTRIBUTE16 => l_freight_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => l_freight_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => l_freight_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => l_freight_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => l_freight_rec.ATTRIBUTE20,
p_SHIPMENT_PRIORITY_CODE => l_freight_rec.SHIPMENT_PRIORITY_CODE,
p_SHIP_QUOTE_PRICE => l_freight_rec.SHIP_QUOTE_PRICE,
p_SHIP_FROM_ORG_ID => l_freight_rec.SHIP_FROM_ORG_ID,
p_SHIP_TO_CUST_PARTY_ID => l_freight_rec.SHIP_TO_CUST_PARTY_ID,
p_SHIP_METHOD_CODE_FROM => l_freight_rec.SHIP_METHOD_CODE_FROM,
p_FREIGHT_TERMS_CODE_FROM => l_freight_rec.FREIGHT_TERMS_CODE_FROM,
p_OBJECT_VERSION_NUMBER => l_freight_rec.OBJECT_VERSION_NUMBER,
p_request_date_type => l_freight_rec.request_date_type,
p_demand_class_code => l_freight_rec.demand_class_code
);
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After Shipment(Freight) Insert Row...Freight_Shipment_ID '||NVL(lx_freight_shipment_id,0),1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = l_req_freight_terms_code
WHERE quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND quote_line_id IS NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
PROCEDURE PROCESS_LN_TSN(p_quote_header_id NUMBER, p_insert_type VARCHAR2) IS
CURSOR c_qte_lines IS
SELECT quote_line_id
FROM ASO_QUOTE_LINES_ALL
WHERE quote_header_id = p_quote_header_id;
SELECT quote_line_id
FROM ASO_QUOTE_LINES_ALL
WHERE quote_header_id = p_quote_header_id
AND nvl(pricing_line_type_indicator,'X') = G_FREE_LINE_FLAG;
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE,
b.substitution_attribute, lines.line_id,
lines.line_index, A.CREATED_FROM_LIST_LINE_TYPE,a.applied_flag,
a.modifier_level_code,a.process_code
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B
WHERE lines.line_id = p_quote_line_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (lines.line_id IS NOT NULL
AND lines.line_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_LINE_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B
WHERE lines.line_id = p_quote_line_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (lines.line_id IS NOT NULL
AND lines.line_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE11
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_LINE_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1);
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B olk) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B,
fnd_lookup_values olk
WHERE lines.line_id = p_quote_line_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (lines.line_id IS NOT NULL
AND lines.line_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE10
AND A.CREATED_FROM_LIST_LINE_TYPE = G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_LINE_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
AND olk.lookup_type = G_FREIGHT_TERM_LK_TYPE
AND olk.enabled_flag = G_YES_FLAG
AND olk.lookup_code = B.SUBSTITUTION_VALUE
AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(olk.start_date_active),TRUNC( sysdate))
AND NVL(TRUNC(olk.end_date_active), TRUNC(sysdate)))
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1)
and olk.LANGUAGE = USERENV('LANG')
and olk.VIEW_APPLICATION_ID = 660
and olk.SECURITY_GROUP_ID =0;
SELECT payment_id
FROM ASO_PAYMENTS
WHERE quote_header_id = p_quote_header_id
and quote_line_id = p_quote_line_id;
SELECT /*+ LEADING (lines) INDEX (lines, QP_PREQ_LINES_TMP_N1) USE_NL (lines A B ) */
B.SUBSTITUTION_VALUE
FROM QP_PREQ_LINES_TMP_T lines,
QP_PREQ_LDETS_TMP_T A,
QP_LIST_LINES B,
RA_TERMS_B ratv
WHERE lines.line_id = p_quote_line_id
AND lines.REQUEST_ID = nvl(sys_context('QP_CONTEXT','REQUEST_ID'),1)
AND (lines.line_id IS NOT NULL
AND lines.line_id <> FND_API.G_MISS_NUM)
AND lines.line_index = a.line_index
AND b.substitution_attribute = G_QUAL_ATTRIBUTE1
AND A.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_TERMS_SUBSTITUTION
AND a.applied_flag = G_YES_FLAG
AND a.modifier_level_code = G_LINE_LEVEL
AND a.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
and a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = 'N'
and a.REQUEST_ID = nvl(SYS_CONTEXT('QP_CONTEXT','REQUEST_ID'),1)
AND ratv.term_id = B.SUBSTITUTION_VALUE
AND (TRUNC(sysdate) BETWEEN NVL(TRUNC(ratv.start_date_active),TRUNC(sysdate))
AND NVL(TRUNC(ratv.end_date_active ), TRUNC(sysdate)));
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Start of line tsn p_insert_type: '||p_insert_type,1,'Y');
If p_insert_type = 'HDR' Then
FOR l_qte_ln_rec IN c_qte_lines LOOP
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT: l_qte_ln_rec.quote_line_id: '||NVL(l_qte_ln_rec.quote_line_id,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Payment TSN exists hence update with from value...',1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = payment_term_id_from
WHERE quote_header_id = p_quote_header_id
AND quote_line_id = l_qte_ln_rec.quote_line_id;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Payment Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = l_substitution_value_to
WHERE quote_header_id = p_quote_header_id
and quote_line_id = l_qte_ln_rec.quote_line_id;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Payment Record Exists...Before Payment Insert Row',1,'Y');
ASO_PAYMENTS_PKG.Insert_Row(
px_PAYMENT_ID => lx_PAYMENT_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REQUEST_ID => l_payment_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_payment_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_payment_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_payment_rec.PROGRAM_UPDATE_DATE,
p_QUOTE_HEADER_ID => p_quote_header_id,
p_QUOTE_LINE_ID => l_qte_ln_rec.quote_line_id,
p_PAYMENT_TYPE_CODE => l_payment_rec.PAYMENT_TYPE_CODE,
p_PAYMENT_REF_NUMBER => l_payment_rec.PAYMENT_REF_NUMBER,
p_PAYMENT_OPTION => l_payment_rec.PAYMENT_OPTION,
p_PAYMENT_TERM_ID => l_substitution_value_to,
p_CREDIT_CARD_CODE => l_payment_rec.CREDIT_CARD_CODE,
p_CREDIT_CARD_HOLDER_NAME => l_payment_rec.CREDIT_CARD_HOLDER_NAME,
p_CREDIT_CARD_EXPIRATION_DATE => l_payment_rec.CREDIT_CARD_EXPIRATION_DATE,
p_CREDIT_CARD_APPROVAL_CODE => l_payment_rec.CREDIT_CARD_APPROVAL_CODE,
p_CREDIT_CARD_APPROVAL_DATE => l_payment_rec.CREDIT_CARD_APPROVAL_DATE,
p_PAYMENT_AMOUNT => l_payment_rec.PAYMENT_AMOUNT,
p_ATTRIBUTE_CATEGORY => l_payment_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_payment_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_payment_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_payment_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_payment_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_payment_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_payment_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_payment_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_payment_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_payment_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_payment_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_payment_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_payment_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_payment_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_payment_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_payment_rec.ATTRIBUTE15,
p_ATTRIBUTE16 => l_payment_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => l_payment_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => l_payment_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => l_payment_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => l_payment_rec.ATTRIBUTE20,
p_QUOTE_SHIPMENT_ID => l_payment_rec.QUOTE_SHIPMENT_ID,
p_CUST_PO_NUMBER => l_payment_rec.CUST_PO_NUMBER,
p_PAYMENT_TERM_ID_FROM => l_payment_rec.PAYMENT_TERM_ID_FROM,
p_OBJECT_VERSION_NUMBER => l_payment_rec.OBJECT_VERSION_NUMBER,
p_CUST_PO_LINE_NUMBER => l_payment_rec.CUST_PO_LINE_NUMBER, -- Line Payments Change
p_TRXN_EXTENSION_ID => l_payment_rec.TRXN_EXTENSION_ID
);
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After Payment Insert Row...Payment_ID :'||NVL(lx_payment_id,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Ship Method Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = ship_method_code_from
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = l_req_ship_method_code
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL
AND ('S' = (ASO_VALIDATE_PVT.Validate_ShipMethods('T', l_req_ship_method_code,
aso.ship_from_org_id, p_quote_header_id, l_qte_ln_rec.quote_line_id)));
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Freight Terms Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = freight_terms_code_from
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = l_req_freight_terms_code
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Payment TSN exists hence update with from value...',1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = payment_term_id_from
WHERE quote_header_id = p_quote_header_id
AND quote_line_id = l_qte_ln_rec.quote_line_id;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Payment Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_PAYMENTS
SET payment_term_id = l_substitution_value_to
WHERE quote_header_id = p_quote_header_id
and quote_line_id = l_qte_ln_rec.quote_line_id;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Payment Record Exists...Before Payment Insert Row',1,'Y');
ASO_PAYMENTS_PKG.Insert_Row(
px_PAYMENT_ID => lx_PAYMENT_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_REQUEST_ID => l_payment_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_payment_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_payment_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_payment_rec.PROGRAM_UPDATE_DATE,
p_QUOTE_HEADER_ID => p_quote_header_id,
p_QUOTE_LINE_ID => l_qte_ln_rec.quote_line_id,
p_PAYMENT_TYPE_CODE => l_payment_rec.PAYMENT_TYPE_CODE,
p_PAYMENT_REF_NUMBER => l_payment_rec.PAYMENT_REF_NUMBER,
p_PAYMENT_OPTION => l_payment_rec.PAYMENT_OPTION,
p_PAYMENT_TERM_ID => l_substitution_value_to,
p_CREDIT_CARD_CODE => l_payment_rec.CREDIT_CARD_CODE,
p_CREDIT_CARD_HOLDER_NAME => l_payment_rec.CREDIT_CARD_HOLDER_NAME,
p_CREDIT_CARD_EXPIRATION_DATE => l_payment_rec.CREDIT_CARD_EXPIRATION_DATE,
p_CREDIT_CARD_APPROVAL_CODE => l_payment_rec.CREDIT_CARD_APPROVAL_CODE,
p_CREDIT_CARD_APPROVAL_DATE => l_payment_rec.CREDIT_CARD_APPROVAL_DATE,
p_PAYMENT_AMOUNT => l_payment_rec.PAYMENT_AMOUNT,
p_ATTRIBUTE_CATEGORY => l_payment_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_payment_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_payment_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_payment_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_payment_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_payment_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_payment_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_payment_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_payment_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_payment_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_payment_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_payment_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_payment_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_payment_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_payment_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_payment_rec.ATTRIBUTE15,
p_ATTRIBUTE16 => l_payment_rec.ATTRIBUTE16,
p_ATTRIBUTE17 => l_payment_rec.ATTRIBUTE17,
p_ATTRIBUTE18 => l_payment_rec.ATTRIBUTE18,
p_ATTRIBUTE19 => l_payment_rec.ATTRIBUTE19,
p_ATTRIBUTE20 => l_payment_rec.ATTRIBUTE20,
p_QUOTE_SHIPMENT_ID => l_payment_rec.QUOTE_SHIPMENT_ID,
p_CUST_PO_NUMBER => l_payment_rec.CUST_PO_NUMBER,
p_PAYMENT_TERM_ID_FROM => l_payment_rec.PAYMENT_TERM_ID_FROM,
p_OBJECT_VERSION_NUMBER => l_payment_rec.OBJECT_VERSION_NUMBER,
p_CUST_PO_LINE_NUMBER => l_payment_rec.CUST_PO_LINE_NUMBER, -- Line Payments Change
p_TRXN_EXTENSION_ID => l_payment_rec.TRXN_EXTENSION_ID
);
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After Payment Insert Row...Payment_ID :'||NVL(lx_payment_id,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Ship Method Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = ship_method_code_from
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.ship_method_code = l_req_ship_method_code
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL
AND ('S' = (ASO_VALIDATE_PVT.Validate_ShipMethods('T', l_req_ship_method_code,
aso.ship_from_org_id, p_quote_header_id, l_qte_ln_rec.quote_line_id)));
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Ship Method Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No Line Level Freight Terms Code TSN exists hence update with From value...',1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = freight_terms_code_from
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_SHIPMENTS aso
SET aso.freight_terms_code = l_req_freight_terms_code
WHERE aso.quote_header_id = p_quote_header_id
AND (p_quote_header_id IS NOT NULL AND p_quote_header_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id = l_qte_ln_rec.quote_line_id
AND (l_qte_ln_rec.quote_line_id IS NOT NULL AND l_qte_ln_rec.quote_line_id <> FND_API.G_MISS_NUM)
AND aso.quote_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: No of Freight Terms Code Rows Updated : '||sql%ROWCOUNT,1,'Y');
End If;--If p_insert_type = 'HDR'
/*New Append_ask_for to use direct insert*/
PROCEDURE Append_Asked_For(
p_pricing_event varchar2
,p_price_line_index NUMBER
,p_header_id number := null
,p_Line_id number := null
,px_index_counter IN OUT NOCOPY /* file.sql.39 change */ number)
IS
cursor asked_for_cur is
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
from aso_price_attributes a
where a.QUOTE_HEADER_ID = p_header_id
and p_header_id is not null
and a.quote_line_id is null
/*
* New Code - Union is changed to union all
*/
UNION ALL
select flex_title, pricing_context, pricing_attribute1,
pricing_attribute2 , pricing_attribute3 , pricing_attribute4 , pricing_attribute5 ,
pricing_attribute6 , pricing_attribute7 , pricing_attribute8 , pricing_attribute9 ,
pricing_attribute10 , pricing_attribute11 , pricing_attribute12 , pricing_attribute13 ,
pricing_attribute14 , pricing_attribute15 , pricing_attribute16 , pricing_attribute17 ,
pricing_attribute18 , pricing_attribute19 , pricing_attribute20 , pricing_attribute21 ,
pricing_attribute22 , pricing_attribute23 , pricing_attribute24 , pricing_attribute25 ,
pricing_attribute26 , pricing_attribute27 , pricing_attribute28 , pricing_attribute29 ,
pricing_attribute30 , pricing_attribute31 , pricing_attribute32 , pricing_attribute33 ,
pricing_attribute34 , pricing_attribute35 , pricing_attribute36 , pricing_attribute37 ,
pricing_attribute38 , pricing_attribute39 , pricing_attribute40 , pricing_attribute41 ,
pricing_attribute42 , pricing_attribute43 , pricing_attribute44 , pricing_attribute45 ,
pricing_attribute46 , pricing_attribute47 , pricing_attribute48 , pricing_attribute49 ,
pricing_attribute50 , pricing_attribute51 , pricing_attribute52 , pricing_attribute53 ,
pricing_attribute54 , pricing_attribute55 , pricing_attribute56 , pricing_attribute57 ,
pricing_attribute58 , pricing_attribute59 , pricing_attribute60 , pricing_attribute61 ,
pricing_attribute62 , pricing_attribute63 , pricing_attribute64 , pricing_attribute65 ,
pricing_attribute66 , pricing_attribute67 , pricing_attribute68 , pricing_attribute69 ,
pricing_attribute70 , pricing_attribute71 , pricing_attribute72 , pricing_attribute73 ,
pricing_attribute74 , pricing_attribute75 , pricing_attribute76 , pricing_attribute77 ,
pricing_attribute78 , pricing_attribute79 , pricing_attribute80 , pricing_attribute81 ,
pricing_attribute82 , pricing_attribute83 , pricing_attribute84 , pricing_attribute85 ,
pricing_attribute86 , pricing_attribute87 , pricing_attribute88 , pricing_attribute89 ,
pricing_attribute90 , pricing_attribute91 , pricing_attribute92 , pricing_attribute93 ,
pricing_attribute94 , pricing_attribute95 , pricing_attribute96 , pricing_attribute97 ,
pricing_attribute98 , pricing_attribute99 , pricing_attribute100
,Override_Flag
FROM ASO_PRICE_ATTRIBUTES a
WHERE a.quote_header_id = p_header_id
AND a.QUOTE_line_id = p_line_id
AND p_line_id IS NOT NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT: In Direct Insert Append_Asked_for',1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Direct Insert Append_Ask_for - Global_Header_Rec.quote_status_id:'
||ASO_PRICING_INT.G_HEADER_REC.quote_status_id,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:End of Direct Insert Append_asked_for',1,'Y');
/*New copy_Header_to_request to use direct insert*/
PROCEDURE Copy_Header_To_Request(
p_Request_Type VARCHAR2,
p_price_line_index NUMBER,
px_index_counter NUMBER)
IS
BEGIN
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT: Start of the direct insert Copy_Header_To_Request',1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: End of the direct insert Copy_Header_To_Request',1,'Y');
/*New copy_Line_to_request to use direct insert*/
PROCEDURE Copy_Line_To_Request(
p_Request_Type VARCHAR2,
p_price_line_index NUMBER,
px_index_counter NUMBER)
is
l_uom_rate NUMBER;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Start of Direct Insert of Copy_Line_To_Request',1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:End of Direct Insert of Copy_Line_To_Request',1,'Y');
UPDATE ASO_PRICE_ADJUSTMENTS apa
SET OPERAND_PER_PQTY = (SELECT decode(arithmetic_operator,'%',operand,
'LUMPSUM',operand,
'AMT',(operand*l.quantity)/l.PRICING_QUANTITY,
'NEWPRICE',(operand*l.quantity)/l.PRICING_QUANTITY)
FROM ASO_QUOTE_LINES_ALL l
WHERE l.quote_header_id = apa.quote_header_id
AND l.quote_line_id = apa.quote_line_id
)
WHERE apa.quote_header_id = p_quote_header_id
AND apa.quote_line_id is not null
AND (apa.applied_flag = 'Y' or apa.updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Line level: '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_PRICE_ADJUSTMENTS
SET OPERAND_PER_PQTY = operand
WHERE quote_header_id = p_quote_header_id
AND quote_line_id is null
AND (applied_flag = 'Y' OR updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Header Level: '||sql%ROWCOUNT,1,'Y');
SELECT
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
'NULL', --line_detail_type_code
PRICE_BREAK_TYPE_CODE,
NULL,
decode(quote_line_id,NULL,1,quote_line_id),
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
NULL,--List type code that we do not store currently
NULL,--Created from SQL
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
ARITHMETIC_OPERATOR,
nvl(OPERAND_PER_PQTY,OPERAND),
NULL,--substitution_attribute
MODIFIED_FROM,
MODIFIED_TO,
NULL,--ask_for_flag that we do not store currently
NULL,--formula_id
'X',--pricing_status_code
NULL,--pricing_status_text
NULL,--product_precedence
NULL,--incompatibility_group
'N',--processed_flag
APPLIED_FLAG,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
NULL,--primary_uom_flag
ON_INVOICE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
NULL,--estim_accrual_rate
'N',--recurring_flag
NULL,--selected_vol_attr
NULL,--rounding_factor
NULL,--hdr_limit_exist
NULL,--Line_limit_exist
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
NULL,--currency_detail_id
NULL,--currency_hdr_id
NULL,--selling_round
NULL,--order_currency
NULL,--pricing_effect_date
NULL,--base_currency
RANGE_BREAK_QUANTITY,
UPDATED_FLAG,
MODIFIER_MECHANISM_TYPE_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT
BULK COLLECT INTO
l_adj_id_tbl,
G_LDET_LINE_DTL_INDEX_TBL,
G_LDET_PRICE_ADJ_ID_TBL,
G_LDET_LINE_DTL_TYPE_TBL,
G_LDET_PRICE_BREAK_TYPE_TBL,
G_LDET_LIST_PRICE_TBL,
G_LDET_LINE_INDEX_TBL,
G_LDET_LIST_HEADER_ID_TBL,
G_LDET_LIST_LINE_ID_TBL,
G_LDET_LIST_LINE_TYPE_TBL,
G_LDET_LIST_TYPE_CODE_TBL,
G_LDET_CREATED_FROM_SQL_TBL,
G_LDET_PRICING_GRP_SEQ_TBL,
G_LDET_PRICING_PHASE_ID_TBL,
G_LDET_OPERAND_CALC_CODE_TBL,
G_LDET_OPERAND_VALUE_TBL,
G_LDET_SUBSTN_TYPE_TBL,
G_LDET_SUBSTN_VALUE_FROM_TBL,
G_LDET_SUBSTN_VALUE_TO_TBL,
G_LDET_ASK_FOR_FLAG_TBL,
G_LDET_PRICE_FORMULA_ID_TBL,
G_LDET_PRICING_STATUS_CODE_TBL,
G_LDET_PRICING_STATUS_TXT_TBL,
G_LDET_PRODUCT_PRECEDENCE_TBL,
G_LDET_INCOMPAT_GRP_CODE_TBL,
G_LDET_PROCESSED_FLAG_TBL,
G_LDET_APPLIED_FLAG_TBL,
G_LDET_AUTOMATIC_FLAG_TBL,
G_LDET_OVERRIDE_FLAG_TBL,
G_LDET_PRIMARY_UOM_FLAG_TBL,
G_LDET_PRINT_ON_INV_FLAG_TBL,
G_LDET_MODIFIER_LEVEL_TBL,
G_LDET_BENEFIT_QTY_TBL,
G_LDET_BENEFIT_UOM_CODE_TBL,
G_LDET_LIST_LINE_NO_TBL,
G_LDET_ACCRUAL_FLAG_TBL,
G_LDET_ACCR_CONV_RATE_TBL,
G_LDET_ESTIM_ACCR_RATE_TBL,
G_LDET_RECURRING_FLAG_TBL,
G_LDET_SELECTED_VOL_ATTR_TBL,
G_LDET_ROUNDING_FACTOR_TBL,
G_LDET_HDR_LIMIT_EXISTS_TBL,
G_LDET_LINE_LIMIT_EXISTS_TBL,
G_LDET_CHARGE_TYPE_TBL,
G_LDET_CHARGE_SUBTYPE_TBL,
G_LDET_CURRENCY_DTL_ID_TBL,
G_LDET_CURRENCY_HDR_ID_TBL,
G_LDET_SELLING_ROUND_TBL,
G_LDET_ORDER_CURRENCY_TBL,
G_LDET_PRICING_EFF_DATE_TBL,
G_LDET_BASE_CURRENCY_TBL,
G_LDET_LINE_QUANTITY_TBL,
G_LDET_UPDATED_FLAG_TBL,
G_LDET_CALC_CODE_TBL,
G_LDET_CHG_REASON_CODE_TBL,
G_LDET_CHG_REASON_TEXT_TBL
FROM ASO_PRICE_ADJUSTMENTS adj
WHERE adj.QUOTE_HEADER_ID = p_quote_header_id
AND (NVL(adj.updated_flag,'N') = 'Y' OR nvl(adj.automatic_flag,'N') = 'Y');
UPDATE ASO_PRICE_ADJUSTMENTS
SET OPERAND_PER_PQTY = operand
WHERE quote_header_id = p_quote_header_id
AND quote_line_id is null
AND (applied_flag = 'Y' OR updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Header Level: '||sql%ROWCOUNT,1,'Y');
SELECT
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
'NULL', --line_detail_type_code
PRICE_BREAK_TYPE_CODE,
NULL,
1,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
NULL,--List type code that we do not store currently
NULL,--Created from SQL
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
ARITHMETIC_OPERATOR,
nvl(OPERAND_PER_PQTY,OPERAND),
NULL,--substitution_attribute
MODIFIED_FROM,
MODIFIED_TO,
NULL,--ask_for_flag that we do not store currently
NULL,--formula_id
'X',--pricing_status_code
NULL,--pricing_status_text
NULL,--product_precedence
NULL,--incompatibility_group
'N',--processed_flag
APPLIED_FLAG,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
NULL,--primary_uom_flag
ON_INVOICE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
NULL,--estim_accrual_rate
'N',--recurring_flag
NULL,--selected_vol_attr
NULL,--rounding_factor
NULL,--hdr_limit_exist
NULL,--Line_limit_exist
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
NULL,--currency_detail_id
NULL,--currency_hdr_id
NULL,--selling_round
NULL,--order_currency
NULL,--pricing_effect_date
NULL,--base_currency
RANGE_BREAK_QUANTITY,
UPDATED_FLAG,
MODIFIER_MECHANISM_TYPE_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT
BULK COLLECT INTO
G_LDET_LINE_DTL_INDEX_TBL,
G_LDET_PRICE_ADJ_ID_TBL,
G_LDET_LINE_DTL_TYPE_TBL,
G_LDET_PRICE_BREAK_TYPE_TBL,
G_LDET_LIST_PRICE_TBL,
G_LDET_LINE_INDEX_TBL,
G_LDET_LIST_HEADER_ID_TBL,
G_LDET_LIST_LINE_ID_TBL,
G_LDET_LIST_LINE_TYPE_TBL,
G_LDET_LIST_TYPE_CODE_TBL,
G_LDET_CREATED_FROM_SQL_TBL,
G_LDET_PRICING_GRP_SEQ_TBL,
G_LDET_PRICING_PHASE_ID_TBL,
G_LDET_OPERAND_CALC_CODE_TBL,
G_LDET_OPERAND_VALUE_TBL,
G_LDET_SUBSTN_TYPE_TBL,
G_LDET_SUBSTN_VALUE_FROM_TBL,
G_LDET_SUBSTN_VALUE_TO_TBL,
G_LDET_ASK_FOR_FLAG_TBL,
G_LDET_PRICE_FORMULA_ID_TBL,
G_LDET_PRICING_STATUS_CODE_TBL,
G_LDET_PRICING_STATUS_TXT_TBL,
G_LDET_PRODUCT_PRECEDENCE_TBL,
G_LDET_INCOMPAT_GRP_CODE_TBL,
G_LDET_PROCESSED_FLAG_TBL,
G_LDET_APPLIED_FLAG_TBL,
G_LDET_AUTOMATIC_FLAG_TBL,
G_LDET_OVERRIDE_FLAG_TBL,
G_LDET_PRIMARY_UOM_FLAG_TBL,
G_LDET_PRINT_ON_INV_FLAG_TBL,
G_LDET_MODIFIER_LEVEL_TBL,
G_LDET_BENEFIT_QTY_TBL,
G_LDET_BENEFIT_UOM_CODE_TBL,
G_LDET_LIST_LINE_NO_TBL,
G_LDET_ACCRUAL_FLAG_TBL,
G_LDET_ACCR_CONV_RATE_TBL,
G_LDET_ESTIM_ACCR_RATE_TBL,
G_LDET_RECURRING_FLAG_TBL,
G_LDET_SELECTED_VOL_ATTR_TBL,
G_LDET_ROUNDING_FACTOR_TBL,
G_LDET_HDR_LIMIT_EXISTS_TBL,
G_LDET_LINE_LIMIT_EXISTS_TBL,
G_LDET_CHARGE_TYPE_TBL,
G_LDET_CHARGE_SUBTYPE_TBL,
G_LDET_CURRENCY_DTL_ID_TBL,
G_LDET_CURRENCY_HDR_ID_TBL,
G_LDET_SELLING_ROUND_TBL,
G_LDET_ORDER_CURRENCY_TBL,
G_LDET_PRICING_EFF_DATE_TBL,
G_LDET_BASE_CURRENCY_TBL,
G_LDET_LINE_QUANTITY_TBL,
G_LDET_UPDATED_FLAG_TBL,
G_LDET_CALC_CODE_TBL,
G_LDET_CHG_REASON_CODE_TBL,
G_LDET_CHG_REASON_TEXT_TBL
FROM ASO_PRICE_ADJUSTMENTS adj
WHERE adj.QUOTE_HEADER_ID = p_quote_header_id
AND adj.QUOTE_LINE_ID IS NULL
AND NVL(ASO_PRICING_INT.G_HEADER_REC.RECALCULATE_FLAG,'N') = 'N'
AND NVL(updated_flag,'N') = 'Y';
UPDATE ASO_PRICE_ADJUSTMENTS apa
SET OPERAND_PER_PQTY = (SELECT decode(arithmetic_operator,'%',operand,
'LUMPSUM',operand,
'AMT',(operand*l.quantity)/l.PRICING_QUANTITY,
'NEWPRICE',(operand*l.quantity)/l.PRICING_QUANTITY)
FROM ASO_QUOTE_LINES_ALL l
WHERE l.quote_header_id = apa.quote_header_id
AND l.quote_line_id = apa.quote_line_id
)
WHERE apa.quote_header_id = p_quote_header_id
AND apa.quote_line_id is not null
AND (apa.applied_flag = 'Y' or apa.updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Line level: '||sql%ROWCOUNT,1,'Y');
SELECT
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
'NULL', --line_detail_type_code
PRICE_BREAK_TYPE_CODE,
NULL,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
NULL,--List type code that we do not store currently
NULL,--Created from SQL
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
ARITHMETIC_OPERATOR,
nvl(OPERAND_PER_PQTY,OPERAND),
NULL,--substitution_attribute
MODIFIED_FROM,
MODIFIED_TO,
NULL,--ask_for_flag that we do not store currently
NULL,--formula_id
'X',--pricing_status_code
NULL,--pricing_status_text
NULL,--product_precedence
NULL,--incompatibility_group
'N',--processed_flag
APPLIED_FLAG,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
NULL,--primary_uom_flag
ON_INVOICE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
NULL,--estim_accrual_rate
'N',--recurring_flag
NULL,--selected_vol_attr
NULL,--rounding_factor
NULL,--hdr_limit_exist
NULL,--Line_limit_exist
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
NULL,--currency_detail_id
NULL,--currency_hdr_id
NULL,--selling_round
NULL,--order_currency
NULL,--pricing_effect_date
NULL,--base_currency
RANGE_BREAK_QUANTITY,
UPDATED_FLAG,
MODIFIER_MECHANISM_TYPE_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT
BULK COLLECT INTO
l_adj_id_tbl,
G_LDET_LINE_DTL_INDEX_TBL,
G_LDET_PRICE_ADJ_ID_TBL,
G_LDET_LINE_DTL_TYPE_TBL,
G_LDET_PRICE_BREAK_TYPE_TBL,
G_LDET_LIST_PRICE_TBL,
G_LDET_LINE_INDEX_TBL,
G_LDET_LIST_HEADER_ID_TBL,
G_LDET_LIST_LINE_ID_TBL,
G_LDET_LIST_LINE_TYPE_TBL,
G_LDET_LIST_TYPE_CODE_TBL,
G_LDET_CREATED_FROM_SQL_TBL,
G_LDET_PRICING_GRP_SEQ_TBL,
G_LDET_PRICING_PHASE_ID_TBL,
G_LDET_OPERAND_CALC_CODE_TBL,
G_LDET_OPERAND_VALUE_TBL,
G_LDET_SUBSTN_TYPE_TBL,
G_LDET_SUBSTN_VALUE_FROM_TBL,
G_LDET_SUBSTN_VALUE_TO_TBL,
G_LDET_ASK_FOR_FLAG_TBL,
G_LDET_PRICE_FORMULA_ID_TBL,
G_LDET_PRICING_STATUS_CODE_TBL,
G_LDET_PRICING_STATUS_TXT_TBL,
G_LDET_PRODUCT_PRECEDENCE_TBL,
G_LDET_INCOMPAT_GRP_CODE_TBL,
G_LDET_PROCESSED_FLAG_TBL,
G_LDET_APPLIED_FLAG_TBL,
G_LDET_AUTOMATIC_FLAG_TBL,
G_LDET_OVERRIDE_FLAG_TBL,
G_LDET_PRIMARY_UOM_FLAG_TBL,
G_LDET_PRINT_ON_INV_FLAG_TBL,
G_LDET_MODIFIER_LEVEL_TBL,
G_LDET_BENEFIT_QTY_TBL,
G_LDET_BENEFIT_UOM_CODE_TBL,
G_LDET_LIST_LINE_NO_TBL,
G_LDET_ACCRUAL_FLAG_TBL,
G_LDET_ACCR_CONV_RATE_TBL,
G_LDET_ESTIM_ACCR_RATE_TBL,
G_LDET_RECURRING_FLAG_TBL,
G_LDET_SELECTED_VOL_ATTR_TBL,
G_LDET_ROUNDING_FACTOR_TBL,
G_LDET_HDR_LIMIT_EXISTS_TBL,
G_LDET_LINE_LIMIT_EXISTS_TBL,
G_LDET_CHARGE_TYPE_TBL,
G_LDET_CHARGE_SUBTYPE_TBL,
G_LDET_CURRENCY_DTL_ID_TBL,
G_LDET_CURRENCY_HDR_ID_TBL,
G_LDET_SELLING_ROUND_TBL,
G_LDET_ORDER_CURRENCY_TBL,
G_LDET_PRICING_EFF_DATE_TBL,
G_LDET_BASE_CURRENCY_TBL,
G_LDET_LINE_QUANTITY_TBL,
G_LDET_UPDATED_FLAG_TBL,
G_LDET_CALC_CODE_TBL,
G_LDET_CHG_REASON_CODE_TBL,
G_LDET_CHG_REASON_TEXT_TBL
FROM ASO_PRICE_ADJUSTMENTS adj,
TABLE (CAST(P_Qte_Line_id_tbl AS JTF_NUMBER_TABLE)) Lines
WHERE adj.QUOTE_HEADER_ID = p_quote_header_id
AND adj.QUOTE_LINE_ID = Lines.column_value
AND adj.QUOTE_LINE_ID IS NOT NULL
AND adj.modifier_line_type_code = 'DIS';
/*AND EXISTS ( SELECT null
FROM ASO_PRICE_ADJUSTMENTS adj2,
ASO_PRICE_ADJ_RELATIONSHIPS rlt
WHERE rlt.rltd_price_adj_id = adj.price_adjustment_id
AND adj2.modifier_line_type_code = 'PRG'
AND adj2.quote_header_id = adj.quote_header_id
AND adj2.price_adjustment_id = rlt.price_adjustment_id));*/
UPDATE ASO_PRICE_ADJUSTMENTS apa
SET OPERAND_PER_PQTY = (SELECT decode(arithmetic_operator,'%',operand,
'LUMPSUM',operand,
'AMT',operand,
'NEWPRICE',operand)
FROM ASO_QUOTE_LINES_ALL l
WHERE l.quote_header_id = apa.quote_header_id
AND l.quote_line_id = apa.quote_line_id
)
WHERE apa.quote_header_id = p_quote_header_id
AND apa.quote_line_id is not null
AND (apa.applied_flag = 'Y' or apa.updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Line level: '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_PRICE_ADJUSTMENTS
SET OPERAND_PER_PQTY = operand
WHERE quote_header_id = p_quote_header_id
AND quote_line_id is null
AND (applied_flag = 'Y' OR updated_flag = 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Operand per pqty updated rows - Header Level: '||sql%ROWCOUNT,1,'Y');
SELECT
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
'NULL', --line_detail_type_code
PRICE_BREAK_TYPE_CODE,
NULL,
1,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
NULL,--List type code that we do not store currently
NULL,--Created from SQL
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
ARITHMETIC_OPERATOR,
nvl(OPERAND_PER_PQTY,OPERAND),
NULL,--substitution_attribute
MODIFIED_FROM,
MODIFIED_TO,
NULL,--ask_for_flag that we do not store currently
NULL,--formula_id
'X',--pricing_status_code
NULL,--pricing_status_text
NULL,--product_precedence
NULL,--incompatibility_group
'N',--processed_flag
APPLIED_FLAG,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
NULL,--primary_uom_flag
ON_INVOICE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
NULL,--estim_accrual_rate
'N',--recurring_flag
NULL,--selected_vol_attr
NULL,--rounding_factor
NULL,--hdr_limit_exist
NULL,--Line_limit_exist
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
NULL,--currency_detail_id
NULL,--currency_hdr_id
NULL,--selling_round
NULL,--order_currency
NULL,--pricing_effect_date
NULL,--base_currency
RANGE_BREAK_QUANTITY,
UPDATED_FLAG,
MODIFIER_MECHANISM_TYPE_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT
BULK COLLECT INTO
l_adj_id_tbl,
G_LDET_LINE_DTL_INDEX_TBL,
G_LDET_PRICE_ADJ_ID_TBL,
G_LDET_LINE_DTL_TYPE_TBL,
G_LDET_PRICE_BREAK_TYPE_TBL,
G_LDET_LIST_PRICE_TBL,
G_LDET_LINE_INDEX_TBL,
G_LDET_LIST_HEADER_ID_TBL,
G_LDET_LIST_LINE_ID_TBL,
G_LDET_LIST_LINE_TYPE_TBL,
G_LDET_LIST_TYPE_CODE_TBL,
G_LDET_CREATED_FROM_SQL_TBL,
G_LDET_PRICING_GRP_SEQ_TBL,
G_LDET_PRICING_PHASE_ID_TBL,
G_LDET_OPERAND_CALC_CODE_TBL,
G_LDET_OPERAND_VALUE_TBL,
G_LDET_SUBSTN_TYPE_TBL,
G_LDET_SUBSTN_VALUE_FROM_TBL,
G_LDET_SUBSTN_VALUE_TO_TBL,
G_LDET_ASK_FOR_FLAG_TBL,
G_LDET_PRICE_FORMULA_ID_TBL,
G_LDET_PRICING_STATUS_CODE_TBL,
G_LDET_PRICING_STATUS_TXT_TBL,
G_LDET_PRODUCT_PRECEDENCE_TBL,
G_LDET_INCOMPAT_GRP_CODE_TBL,
G_LDET_PROCESSED_FLAG_TBL,
G_LDET_APPLIED_FLAG_TBL,
G_LDET_AUTOMATIC_FLAG_TBL,
G_LDET_OVERRIDE_FLAG_TBL,
G_LDET_PRIMARY_UOM_FLAG_TBL,
G_LDET_PRINT_ON_INV_FLAG_TBL,
G_LDET_MODIFIER_LEVEL_TBL,
G_LDET_BENEFIT_QTY_TBL,
G_LDET_BENEFIT_UOM_CODE_TBL,
G_LDET_LIST_LINE_NO_TBL,
G_LDET_ACCRUAL_FLAG_TBL,
G_LDET_ACCR_CONV_RATE_TBL,
G_LDET_ESTIM_ACCR_RATE_TBL,
G_LDET_RECURRING_FLAG_TBL,
G_LDET_SELECTED_VOL_ATTR_TBL,
G_LDET_ROUNDING_FACTOR_TBL,
G_LDET_HDR_LIMIT_EXISTS_TBL,
G_LDET_LINE_LIMIT_EXISTS_TBL,
G_LDET_CHARGE_TYPE_TBL,
G_LDET_CHARGE_SUBTYPE_TBL,
G_LDET_CURRENCY_DTL_ID_TBL,
G_LDET_CURRENCY_HDR_ID_TBL,
G_LDET_SELLING_ROUND_TBL,
G_LDET_ORDER_CURRENCY_TBL,
G_LDET_PRICING_EFF_DATE_TBL,
G_LDET_BASE_CURRENCY_TBL,
G_LDET_LINE_QUANTITY_TBL,
G_LDET_UPDATED_FLAG_TBL,
G_LDET_CALC_CODE_TBL,
G_LDET_CHG_REASON_CODE_TBL,
G_LDET_CHG_REASON_TEXT_TBL
FROM ASO_PRICE_ADJUSTMENTS adj
WHERE adj.QUOTE_HEADER_ID = p_quote_header_id
AND NVL(adj.updated_flag,'N') = 'Y'
AND adj.QUOTE_LINE_ID IS NULL
AND NVL(ASO_PRICING_INT.G_HEADER_REC.RECALCULATE_FLAG,'N') = 'N'
UNION ALL
SELECT
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
PRICE_ADJUSTMENT_ID,
'NULL', --line_detail_type_code
PRICE_BREAK_TYPE_CODE,
NULL,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
NULL,--List type code that we do not store currently
NULL,--Created from SQL
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
ARITHMETIC_OPERATOR,
nvl(OPERAND_PER_PQTY,OPERAND),
NULL,--substitution_attribute
MODIFIED_FROM,
MODIFIED_TO,
NULL,--ask_for_flag that we do not store currently
NULL,--formula_id
'X',--pricing_status_code
NULL,--pricing_status_text
NULL,--product_precedence
NULL,--incompatibility_group
'N',--processed_flag
APPLIED_FLAG,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
NULL,--primary_uom_flag
ON_INVOICE_FLAG,
MODIFIER_LEVEL_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
LIST_LINE_NO,
ACCRUAL_FLAG,
ACCRUAL_CONVERSION_RATE,
NULL,--estim_accrual_rate
'N',--recurring_flag
NULL,--selected_vol_attr
NULL,--rounding_factor
NULL,--hdr_limit_exist
NULL,--Line_limit_exist
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
NULL,--currency_detail_id
NULL,--currency_hdr_id
NULL,--selling_round
NULL,--order_currency
NULL,--pricing_effect_date
NULL,--base_currency
RANGE_BREAK_QUANTITY,
UPDATED_FLAG,
MODIFIER_MECHANISM_TYPE_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT
FROM ASO_PRICE_ADJUSTMENTS adj,
TABLE (CAST(P_Qte_Line_id_tbl AS JTF_NUMBER_TABLE)) Lines
WHERE adj.QUOTE_HEADER_ID = p_quote_header_id
AND adj.QUOTE_LINE_ID = Lines.column_value
AND adj.QUOTE_LINE_ID IS NOT NULL
AND (( NVL(adj.updated_flag,'N') = 'Y'
AND EXISTS ( SELECT null from ASO_QUOTE_LINES_ALL lines2
WHERE lines2.quote_line_id = adj.quote_line_id
AND nvl(lines2.RECALCULATE_FLAG,'N') = 'N' )
AND NVL(ASO_PRICING_INT.G_HEADER_REC.RECALCULATE_FLAG,'N') = 'N')
OR ( adj.modifier_line_type_code = 'PRG'
OR
( adj.modifier_line_type_code = 'DIS' and exists ( select null from aso_price_adjustments adj2,
aso_price_adj_relationships rlt
where rlt.rltd_price_adj_id = adj.price_adjustment_id
and adj2.modifier_line_type_code = 'PRG'
and adj2.quote_header_id = adj.quote_header_id
and adj2.price_adjustment_id = rlt.price_adjustment_id))));
SELECT
adj_rel.quote_line_id,
adj_rel.price_adjustment_id,
decode(dbadj.modifier_line_type_code,QP_PREQ_GRP.G_PRICE_BREAK_TYPE,QP_PREQ_GRP.G_PBH_LINE,QP_PREQ_GRP.G_GENERATED_LINE),
adj_rel.rltd_price_adj_id,
dbadjrel.quote_line_id,
dbadj.modifier_line_id,
dbadjrel.modifier_line_id
BULK COLLECT INTO
G_RLTD_LINE_INDEX_TBL,
G_RLTD_LINE_DTL_INDEX_TBL,
G_RLTD_RELATION_TYPE_CODE_TBL,
G_RLTD_RLTD_LINE_DTL_IND_TBL,
G_RLTD_RELATED_LINE_IND_TBL,
G_RLTD_LST_LN_ID_DEF_TBL,
G_RLTD_RLTD_LST_LN_ID_DEF_TBL
FROM ASO_PRICE_ADJ_RELATIONSHIPS adj_rel,
ASO_PRICE_ADJUSTMENTS dbadj,
ASO_PRICE_ADJUSTMENTS dbadjrel,
TABLE (CAST(p_qte_adj_id_tbl AS JTF_NUMBER_TABLE)) adj
WHERE dbadj.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND dbadjrel.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND adj_rel.price_adjustment_id = adj.column_value
AND dbadj.price_adjustment_id = adj_rel.price_adjustment_id
AND dbadjrel.price_adjustment_id = adj_rel.rltd_price_adj_id
AND dbadj.modifier_line_type_code IN (QP_PREQ_GRP.G_PRICE_BREAK_TYPE,QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
UNION ALL
SELECT
ldets.service_ref_line_id quote_line_id,
0,
QP_PREQ_GRP.G_SERVICE_LINE,
0,
ldets.quote_line_id related_quote_line_id,
to_number(NULL),
to_number(NULL)
FROM ASO_QUOTE_LINE_DETAILS ldets,
TABLE (CAST(P_service_Qte_Line_id_tbl AS JTF_NUMBER_TABLE)) serviceLines,
ASO_QUOTE_LINES_ALL lines
WHERE ldets.quote_line_id = serviceLines.column_value
AND lines.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND lines.quote_line_id = ldets.quote_line_id
AND ldets.service_ref_line_id IS NOT NULL;
SELECT
ldets.service_ref_line_id quote_line_id,
0,
QP_PREQ_GRP.G_SERVICE_LINE,
0,
ldets.quote_line_id related_quote_line_id,
to_number(NULL),
to_number(NULL)
BULK COLLECT INTO
G_RLTD_LINE_INDEX_TBL,
G_RLTD_LINE_DTL_INDEX_TBL,
G_RLTD_RELATION_TYPE_CODE_TBL,
G_RLTD_RLTD_LINE_DTL_IND_TBL,
G_RLTD_RELATED_LINE_IND_TBL,
G_RLTD_LST_LN_ID_DEF_TBL,
G_RLTD_RLTD_LST_LN_ID_DEF_TBL
FROM ASO_QUOTE_LINE_DETAILS ldets,
TABLE (CAST(P_service_Qte_Line_id_tbl AS JTF_NUMBER_TABLE)) serviceLines,
ASO_QUOTE_LINES_ALL lines
WHERE ldets.quote_line_id = serviceLines.column_value
AND lines.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND lines.quote_line_id = ldets.quote_line_id
AND ldets.service_ref_line_id IS NOT NULL;
SELECT
adj_rel.quote_line_id,
adj_rel.price_adjustment_id,
decode(dbadj.modifier_line_type_code,QP_PREQ_GRP.G_PRICE_BREAK_TYPE,QP_PREQ_GRP.G_PBH_LINE,QP_PREQ_GRP.G_GENERATED_LINE),
adj_rel.rltd_price_adj_id,
dbadjrel.quote_line_id,
dbadj.modifier_line_id,
dbadjrel.modifier_line_id
BULK COLLECT INTO
G_RLTD_LINE_INDEX_TBL,
G_RLTD_LINE_DTL_INDEX_TBL,
G_RLTD_RELATION_TYPE_CODE_TBL,
G_RLTD_RLTD_LINE_DTL_IND_TBL,
G_RLTD_RELATED_LINE_IND_TBL,
G_RLTD_LST_LN_ID_DEF_TBL,
G_RLTD_RLTD_LST_LN_ID_DEF_TBL
FROM ASO_PRICE_ADJ_RELATIONSHIPS adj_rel,
ASO_PRICE_ADJUSTMENTS dbadj,
ASO_PRICE_ADJUSTMENTS dbadjrel,
TABLE (CAST(p_qte_adj_id_tbl AS JTF_NUMBER_TABLE)) adj
WHERE dbadj.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND dbadjrel.quote_header_id = ASO_PRICING_INT.G_HEADER_REC.quote_header_id
AND adj_rel.price_adjustment_id = adj.column_value
AND dbadj.price_adjustment_id = adj_rel.price_adjustment_id
AND dbadjrel.price_adjustment_id = adj_rel.rltd_price_adj_id
AND dbadj.modifier_line_type_code IN (QP_PREQ_GRP.G_PRICE_BREAK_TYPE,QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_INDEX_TBL.count:'
||nvl(G_LINE_INDEX_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_TYPE_CODE_TBL.count:'
||nvl(G_LINE_TYPE_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICING_EFFECTIVE_DATE_TBL.count:'
||nvl(G_PRICING_EFFECTIVE_DATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ACTIVE_DATE_FIRST_TBL.count:'
||nvl(G_ACTIVE_DATE_FIRST_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ACTIVE_DATE_FIRST_TYPE_TBL.count:'
||nvl(G_ACTIVE_DATE_FIRST_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ACTIVE_DATE_SECOND_TBL.count:'
||nvl(G_ACTIVE_DATE_SECOND_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ACTIVE_DATE_SECOND_TYPE_TBL.count:'
||nvl(G_ACTIVE_DATE_SECOND_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_QUANTITY_TBL.count:'
||nvl(G_LINE_QUANTITY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_UOM_CODE_TBL.count:'
||nvl(G_LINE_UOM_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_REQUEST_TYPE_CODE_TBL.count:'
||nvl(G_REQUEST_TYPE_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICED_QUANTITY_TBL.count:'
||nvl(G_PRICED_QUANTITY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICED_UOM_CODE_TBL.count:'
||nvl(G_PRICED_UOM_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_CURRENCY_CODE_TBL.count:'
||nvl(G_CURRENCY_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_UNIT_PRICE_TBL.count:'
||nvl(G_UNIT_PRICE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PERCENT_PRICE_TBL.count:'
||nvl(G_PERCENT_PRICE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_UOM_QUANTITY_TBL.count:'
||nvl(G_UOM_QUANTITY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ADJUSTED_UNIT_PRICE_TBL.count:'
||nvl(G_ADJUSTED_UNIT_PRICE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_UPD_ADJUSTED_UNIT_PRICE_TBL.count:'
||nvl(G_UPD_ADJUSTED_UNIT_PRICE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PROCESSED_FLAG_TBL.count:'
||nvl(G_PROCESSED_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICE_FLAG_TBL.count:'
||nvl(G_PRICE_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_ID_TBL.count:'
||nvl(G_LINE_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PROCESSING_ORDER_TBL.count:'
||nvl(G_PROCESSING_ORDER_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICING_STATUS_CODE_tbl.count:'
||nvl(G_PRICING_STATUS_CODE_tbl.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICING_STATUS_TEXT_tbl.count:'
||nvl(G_PRICING_STATUS_TEXT_tbl.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ROUNDING_FLAG_TBL.count:'
||nvl(G_ROUNDING_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_ROUNDING_FACTOR_TBL.count:'
||nvl(G_ROUNDING_FACTOR_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_QUALIFIERS_EXIST_FLAG_TBL.count:'
||nvl(G_QUALIFIERS_EXIST_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICING_ATTRS_EXIST_FLAG_TBL.count:'
||nvl(G_PRICING_ATTRS_EXIST_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICE_LIST_ID_TBL.count:'
||nvl(G_PRICE_LIST_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PL_VALIDATED_FLAG_TBL.count:'
||nvl(G_PL_VALIDATED_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_PRICE_REQUEST_CODE_TBL.count:'
||nvl(G_PRICE_REQUEST_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_USAGE_PRICING_TYPE_tbl.count:'
||nvl(G_USAGE_PRICING_TYPE_tbl.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LINE_CATEGORY_tbl.count:'
||nvl(G_LINE_CATEGORY_tbl.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_CHRG_PERIODICITY_CODE_TBL.count:'
||nvl(G_CHRG_PERIODICITY_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_CONTRACT_START_DATE_TBL.count:'
||nvl(G_CONTRACT_START_DATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_CONTRACT_END_DATE_TBL.count:'
||nvl(G_CONTRACT_END_DATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LINE_DTL_INDEX_TBL.count:'
||nvl(G_LDET_LINE_DTL_INDEX_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICE_ADJ_ID_TBL.count:'
||nvl(G_LDET_PRICE_ADJ_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LINE_DTL_TYPE_TBL.count:'
||nvl(G_LDET_LINE_DTL_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICE_BREAK_TYPE_TBL.count:'
||nvl(G_LDET_PRICE_BREAK_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_PRICE_TBL.count:'
||nvl(G_LDET_LIST_PRICE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LINE_INDEX_TBL.count:'
||nvl(G_LDET_LINE_INDEX_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_HEADER_ID_TBL.count:'
||nvl(G_LDET_LIST_HEADER_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_LINE_ID_TBL.count:'
||nvl(G_LDET_LIST_LINE_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_LINE_TYPE_TBL.count:'
||nvl(G_LDET_LIST_LINE_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_TYPE_CODE_TBL.count:'
||nvl(G_LDET_LIST_TYPE_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CREATED_FROM_SQL_TBL.count:'
||nvl(G_LDET_CREATED_FROM_SQL_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICING_GRP_SEQ_TBL.count:'
||nvl(G_LDET_PRICING_GRP_SEQ_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICING_PHASE_ID_TBL.count:'
||nvl(G_LDET_PRICING_PHASE_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_OPERAND_CALC_CODE_TBL.count:'
||nvl(G_LDET_OPERAND_CALC_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_OPERAND_VALUE_TBL.count:'
||nvl(G_LDET_OPERAND_VALUE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_SUBSTN_TYPE_TBL.count:'
||nvl(G_LDET_SUBSTN_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_SUBSTN_VALUE_FROM_TBL.count:'
||nvl(G_LDET_SUBSTN_VALUE_FROM_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_SUBSTN_VALUE_TO_TBL.count:'
||nvl(G_LDET_SUBSTN_VALUE_TO_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ASK_FOR_FLAG_TBL.count:'
||nvl(G_LDET_ASK_FOR_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICE_FORMULA_ID_TBL.count:'
||nvl(G_LDET_PRICE_FORMULA_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICING_STATUS_CODE_TBL.count:'
||nvl(G_LDET_PRICING_STATUS_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICING_STATUS_TXT_TBL.count:'
||nvl(G_LDET_PRICING_STATUS_TXT_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRODUCT_PRECEDENCE_TBL.count:'
||nvl(G_LDET_PRODUCT_PRECEDENCE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_INCOMPAT_GRP_CODE_TBL.count:'
||nvl(G_LDET_INCOMPAT_GRP_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PROCESSED_FLAG_TBL.count:'
||nvl(G_LDET_PROCESSED_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_APPLIED_FLAG_TBL.count:'
||nvl(G_LDET_APPLIED_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_AUTOMATIC_FLAG_TBL.count:'
||nvl(G_LDET_AUTOMATIC_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_OVERRIDE_FLAG_TBL.count:'
||nvl(G_LDET_OVERRIDE_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRIMARY_UOM_FLAG_TBL.count:'
||nvl(G_LDET_PRIMARY_UOM_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRINT_ON_INV_FLAG_TBL.count:'
||nvl(G_LDET_PRINT_ON_INV_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_MODIFIER_LEVEL_TBL.count:'
||nvl(G_LDET_MODIFIER_LEVEL_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_BENEFIT_QTY_TBL.count:'
||nvl(G_LDET_BENEFIT_QTY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_BENEFIT_UOM_CODE_TBL.count:'
||nvl(G_LDET_BENEFIT_UOM_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LIST_LINE_NO_TBL.count:'
||nvl(G_LDET_LIST_LINE_NO_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ACCRUAL_FLAG_TBL.count:'
||nvl(G_LDET_ACCRUAL_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ACCR_CONV_RATE_TBL.count:'
||nvl(G_LDET_ACCR_CONV_RATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ESTIM_ACCR_RATE_TBL.count:'
||nvl(G_LDET_ESTIM_ACCR_RATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_RECURRING_FLAG_TBL.count:'
||nvl(G_LDET_RECURRING_FLAG_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_SELECTED_VOL_ATTR_TBL.count:'
||nvl(G_LDET_SELECTED_VOL_ATTR_TBL.count,0),1,'Y');
If nvl(G_LDET_SELECTED_VOL_ATTR_TBL.count,0) > 0 then
For i in 1..G_LDET_SELECTED_VOL_ATTR_TBL.count loop
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('G_LDET_SELECTED_VOL_ATTR_TBL('||i||'):'||G_LDET_SELECTED_VOL_ATTR_TBL(i),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ROUNDING_FACTOR_TBL.count:'
||nvl(G_LDET_ROUNDING_FACTOR_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_HDR_LIMIT_EXISTS_TBL.count:'
||nvl(G_LDET_HDR_LIMIT_EXISTS_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LINE_LIMIT_EXISTS_TBL.count:'
||nvl(G_LDET_LINE_LIMIT_EXISTS_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CHARGE_TYPE_TBL.count:'
||nvl(G_LDET_CHARGE_TYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CHARGE_SUBTYPE_TBL.count:'
||nvl(G_LDET_CHARGE_SUBTYPE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CURRENCY_DTL_ID_TBL.count:'
||nvl(G_LDET_CURRENCY_DTL_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CURRENCY_HDR_ID_TBL.count:'
||nvl(G_LDET_CURRENCY_HDR_ID_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_SELLING_ROUND_TBL.count:'
||nvl(G_LDET_SELLING_ROUND_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_ORDER_CURRENCY_TBL.count:'
||nvl(G_LDET_ORDER_CURRENCY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_PRICING_EFF_DATE_TBL.count:'
||nvl(G_LDET_PRICING_EFF_DATE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_BASE_CURRENCY_TBL.count:'
||nvl(G_LDET_BASE_CURRENCY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_LINE_QUANTITY_TBL.count:'
||nvl(G_LDET_LINE_QUANTITY_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_UPDATED_FLAG_TBL.count:'
||nvl(G_LDET_UPDATED_FLAG_TBL.count,0),1,'Y');
If nvl(G_LDET_UPDATED_FLAG_TBL.count,0) > 0 then
For i in 1..G_LDET_UPDATED_FLAG_TBL.count loop
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('G_LDET_UPDATED_FLAG_TBL('||i||'):'||G_LDET_UPDATED_FLAG_TBL(i),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CALC_CODE_TBL.count:'
||nvl(G_LDET_CALC_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CHG_REASON_CODE_TBL.count:'
||nvl(G_LDET_CHG_REASON_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_LDET_CHG_REASON_TEXT_TBL.count:'
||nvl(G_LDET_CHG_REASON_TEXT_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_LINE_INDEX_TBL.count:'
||nvl(G_RLTD_LINE_INDEX_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_LINE_DTL_INDEX_TBL.count:'
||nvl(G_RLTD_LINE_DTL_INDEX_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_RELATION_TYPE_CODE_TBL.count:'
||nvl(G_RLTD_RELATION_TYPE_CODE_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_RELATED_LINE_IND_TBL.count:'
||nvl(G_RLTD_RELATED_LINE_IND_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_RLTD_LINE_DTL_IND_TBL.count:'
||nvl(G_RLTD_RLTD_LINE_DTL_IND_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_LST_LN_ID_DEF_TBL.count:'
||nvl(G_RLTD_LST_LN_ID_DEF_TBL.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:b4 insert temp table:G_RLTD_RLTD_LST_LN_ID_DEF_TBL.count:'
||nvl(G_RLTD_RLTD_LST_LN_ID_DEF_TBL.count,0),1,'Y');
QP_PREQ_GRP.INSERT_LINES2
(p_LINE_INDEX =>G_LINE_INDEX_TBL,
p_LINE_TYPE_CODE =>G_LINE_TYPE_CODE_TBL,
p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
p_LINE_QUANTITY =>G_LINE_QUANTITY_TBL,
p_LINE_UOM_CODE =>G_LINE_UOM_CODE_TBL,
p_REQUEST_TYPE_CODE =>G_REQUEST_TYPE_CODE_TBL,
p_PRICED_QUANTITY =>G_PRICED_QUANTITY_TBL,
p_PRICED_UOM_CODE =>G_PRICED_UOM_CODE_TBL,
p_CURRENCY_CODE =>G_CURRENCY_CODE_TBL,
p_UNIT_PRICE =>G_UNIT_PRICE_TBL,
p_LINE_UNIT_PRICE =>G_UNIT_PRICE_TBL,
p_PERCENT_PRICE =>G_PERCENT_PRICE_TBL,
p_UOM_QUANTITY =>G_UOM_QUANTITY_TBL,
p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
p_LINE_ID =>G_LINE_ID_TBL,
p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
p_QUALIFIERS_EXIST_FLAG =>G_QUALIFIERS_EXIST_FLAG_TBL,
p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
p_PRICE_LIST_ID =>G_PRICE_LIST_ID_TBL,
p_VALIDATED_FLAG =>G_PL_VALIDATED_FLAG_TBL,
p_PRICE_REQUEST_CODE =>G_PRICE_REQUEST_CODE_TBL,
p_USAGE_PRICING_TYPE =>G_USAGE_PRICING_TYPE_tbl,
p_line_category =>G_LINE_CATEGORY_tbl,
p_charge_periodicity_code =>G_CHRG_PERIODICITY_CODE_TBL,
/* Changes Made for OKS uptake bug 4900084 */
p_CONTRACT_START_DATE =>G_CONTRACT_START_DATE_TBL,
p_CONTRACT_END_DATE =>G_CONTRACT_END_DATE_TBL,
x_status_code =>l_return_status,
x_status_text =>l_return_status_text);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Error in insert_lines2',1,'Y');
QP_PREQ_GRP.INSERT_LINE_ATTRS2
( p_LINE_INDEX_tbl => G_ATTR_LINE_INDEX_tbl,
p_LINE_DETAIL_INDEX_tbl => G_ATTR_LINE_DETAIL_INDEX_tbl,
p_ATTRIBUTE_LEVEL_tbl => G_ATTR_ATTRIBUTE_LEVEL_tbl,
p_ATTRIBUTE_TYPE_tbl => G_ATTR_ATTRIBUTE_TYPE_tbl,
p_LIST_HEADER_ID_tbl => G_ATTR_LIST_HEADER_ID_tbl,
p_LIST_LINE_ID_tbl => G_ATTR_LIST_LINE_ID_tbl,
p_CONTEXT_tbl => G_ATTR_PRICING_CONTEXT_tbl,
p_ATTRIBUTE_tbl => G_ATTR_PRICING_ATTRIBUTE_tbl,
p_VALUE_FROM_tbl => G_ATTR_VALUE_FROM_tbl,
p_SETUP_VALUE_FROM_tbl => G_ATTR_SETUP_VALUE_FROM_tbl,
p_VALUE_TO_tbl => G_ATTR_VALUE_TO_tbl,
p_SETUP_VALUE_TO_tbl => G_ATTR_SETUP_VALUE_TO_tbl,
p_GROUPING_NUMBER_tbl => G_ATTR_GROUPING_NUMBER_tbl,
p_NO_QUALIFIERS_IN_GRP_tbl => G_ATTR_NO_QUAL_IN_GRP_tbl,
p_COMPARISON_OPERATOR_TYPE_tbl => G_ATTR_COMP_OPERATOR_TYPE_tbl,
p_VALIDATED_FLAG_tbl => G_ATTR_VALIDATED_FLAG_tbl,
p_APPLIED_FLAG_tbl => G_ATTR_APPLIED_FLAG_tbl,
p_PRICING_STATUS_CODE_tbl => G_ATTR_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT_tbl => G_ATTR_PRICING_STATUS_TEXT_tbl,
p_QUALIFIER_PRECEDENCE_tbl => G_ATTR_QUAL_PRECEDENCE_tbl,
p_DATATYPE_tbl => G_ATTR_DATATYPE_tbl,
p_PRICING_ATTR_FLAG_tbl => G_ATTR_PRICING_ATTR_FLAG_tbl,
p_QUALIFIER_TYPE_tbl => G_ATTR_QUALIFIER_TYPE_tbl,
p_PRODUCT_UOM_CODE_TBL => G_ATTR_PRODUCT_UOM_CODE_TBL,
p_EXCLUDER_FLAG_TBL => G_ATTR_EXCLUDER_FLAG_TBL,
p_PRICING_PHASE_ID_TBL => G_ATTR_PRICING_PHASE_ID_TBL,
p_INCOMPATABILITY_GRP_CODE_TBL => G_ATTR_INCOM_GRP_CODE_TBL,
p_LINE_DETAIL_TYPE_CODE_TBL => G_ATTR_LDET_TYPE_CODE_TBL,
p_MODIFIER_LEVEL_CODE_TBL => G_ATTR_MODIFIER_LEVEL_CODE_TBL,
p_PRIMARY_UOM_FLAG_TBL => G_ATTR_PRIMARY_UOM_FLAG_TBL,
x_status_code => l_return_status,
x_status_text => l_return_status_text );
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Error inserting into line attrs'||sqlerrm,1,'Y');
QP_PREQ_GRP.INSERT_LDETS2
(p_LINE_DETAIL_INDEX => G_LDET_LINE_DTL_INDEX_TBL,
p_LINE_DETAIL_TYPE_CODE => G_LDET_LINE_DTL_TYPE_TBL,
p_PRICE_BREAK_TYPE_CODE => G_LDET_PRICE_BREAK_TYPE_TBL,
p_LIST_PRICE => G_LDET_LIST_PRICE_TBL,
p_LINE_INDEX => G_LDET_LINE_INDEX_TBL,
p_CREATED_FROM_LIST_HEADER_ID => G_LDET_LIST_HEADER_ID_TBL,
p_CREATED_FROM_LIST_LINE_ID => G_LDET_LIST_LINE_ID_TBL,
p_CREATED_FROM_LIST_LINE_TYPE => G_LDET_LIST_LINE_TYPE_TBL,
p_CREATED_FROM_LIST_TYPE_CODE => G_LDET_LIST_TYPE_CODE_TBL,
p_CREATED_FROM_SQL => G_LDET_CREATED_FROM_SQL_TBL,
p_PRICING_GROUP_SEQUENCE => G_LDET_PRICING_GRP_SEQ_TBL,
p_PRICING_PHASE_ID => G_LDET_PRICING_PHASE_ID_TBL,
p_OPERAND_CALCULATION_CODE => G_LDET_OPERAND_CALC_CODE_TBL,
p_OPERAND_VALUE => G_LDET_OPERAND_VALUE_TBL,
p_SUBSTITUTION_TYPE_CODE => G_LDET_SUBSTN_TYPE_TBL,
p_SUBSTITUTION_VALUE_FROM => G_LDET_SUBSTN_VALUE_FROM_TBL,
p_SUBSTITUTION_VALUE_TO => G_LDET_SUBSTN_VALUE_TO_TBL,
p_ASK_FOR_FLAG => G_LDET_ASK_FOR_FLAG_TBL,
p_PRICE_FORMULA_ID => G_LDET_PRICE_FORMULA_ID_TBL,
p_PRICING_STATUS_CODE => G_LDET_PRICING_STATUS_CODE_TBL,
p_PRICING_STATUS_TEXT => G_LDET_PRICING_STATUS_TXT_TBL,
p_PRODUCT_PRECEDENCE => G_LDET_PRODUCT_PRECEDENCE_TBL,
p_INCOMPATABLILITY_GRP_CODE => G_LDET_INCOMPAT_GRP_CODE_TBL,
p_PROCESSED_FLAG => G_LDET_PROCESSED_FLAG_TBL,
p_APPLIED_FLAG => G_LDET_APPLIED_FLAG_TBL,
p_AUTOMATIC_FLAG => G_LDET_AUTOMATIC_FLAG_TBL,
p_OVERRIDE_FLAG => G_LDET_OVERRIDE_FLAG_TBL,
p_PRIMARY_UOM_FLAG => G_LDET_PRIMARY_UOM_FLAG_TBL,
p_PRINT_ON_INVOICE_FLAG => G_LDET_PRINT_ON_INV_FLAG_TBL,
p_MODIFIER_LEVEL_CODE => G_LDET_MODIFIER_LEVEL_TBL,
p_BENEFIT_QTY => G_LDET_BENEFIT_QTY_TBL,
p_BENEFIT_UOM_CODE => G_LDET_BENEFIT_UOM_CODE_TBL,
p_LIST_LINE_NO => G_LDET_LIST_LINE_NO_TBL,
p_ACCRUAL_FLAG => G_LDET_ACCRUAL_FLAG_TBL,
p_ACCRUAL_CONVERSION_RATE => G_LDET_ACCR_CONV_RATE_TBL,
p_ESTIM_ACCRUAL_RATE => G_LDET_ESTIM_ACCR_RATE_TBL,
p_RECURRING_FLAG => G_LDET_RECURRING_FLAG_TBL,
p_SELECTED_VOLUME_ATTR => G_LDET_SELECTED_VOL_ATTR_TBL,
p_ROUNDING_FACTOR => G_LDET_ROUNDING_FACTOR_TBL,
p_HEADER_LIMIT_EXISTS => G_LDET_HDR_LIMIT_EXISTS_TBL,
p_LINE_LIMIT_EXISTS => G_LDET_LINE_LIMIT_EXISTS_TBL,
p_CHARGE_TYPE_CODE => G_LDET_CHARGE_TYPE_TBL,
p_CHARGE_SUBTYPE_CODE => G_LDET_CHARGE_SUBTYPE_TBL,
p_CURRENCY_DETAIL_ID => G_LDET_CURRENCY_DTL_ID_TBL,
p_CURRENCY_HEADER_ID => G_LDET_CURRENCY_HDR_ID_TBL,
p_SELLING_ROUNDING_FACTOR => G_LDET_SELLING_ROUND_TBL,
p_ORDER_CURRENCY => G_LDET_ORDER_CURRENCY_TBL,
p_PRICING_EFFECTIVE_DATE => G_LDET_PRICING_EFF_DATE_TBL,
p_BASE_CURRENCY_CODE => G_LDET_BASE_CURRENCY_TBL,
p_LINE_QUANTITY => G_LDET_LINE_QUANTITY_TBL,
p_UPDATED_FLAG => G_LDET_UPDATED_FLAG_TBL,
p_CALCULATION_CODE => G_LDET_CALC_CODE_TBL,
p_CHANGE_REASON_CODE => G_LDET_CHG_REASON_CODE_TBL,
p_CHANGE_REASON_TEXT => G_LDET_CHG_REASON_TEXT_TBL,
p_PRICE_ADJUSTMENT_ID => G_LDET_PRICE_ADJ_ID_TBL,
x_status_code => l_return_status,
x_status_text => l_return_status_text);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Error inserting into adj ldets'||sqlerrm,1,'Y');
QP_PREQ_GRP.INSERT_RLTD_LINES2 (
p_LINE_INDEX => G_RLTD_LINE_INDEX_TBL,
p_LINE_DETAIL_INDEX => G_RLTD_LINE_DTL_INDEX_TBL,
p_RELATIONSHIP_TYPE_CODE => G_RLTD_RELATION_TYPE_CODE_TBL,
p_RELATED_LINE_INDEX => G_RLTD_RELATED_LINE_IND_TBL,
p_RELATED_LINE_DETAIL_INDEX => G_RLTD_RLTD_LINE_DTL_IND_TBL,
p_LIST_LINE_ID => G_RLTD_LST_LN_ID_DEF_TBL,
p_RELATED_LIST_LINE_ID => G_RLTD_RLTD_LST_LN_ID_DEF_TBL,
x_status_code => l_return_status,
x_status_text => l_return_status_text);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Error inserting into adj relationship'||sqlerrm,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: After direct insert into temp table: bulk insert',1,'Y');
PROCEDURE Delete_Promotion (
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
CURSOR C_get_adj_id_HdrH(l_quote_header_id NUMBER,l_price_attribute1 VARCHAR2) IS
SELECT price_adjustment_id
FROM aso_price_adjustments
WHERE quote_header_id = l_quote_header_id
AND quote_line_id is null
AND modifier_header_id = to_number(l_price_attribute1);
SELECT price_adjustment_id
FROM aso_price_adjustments
WHERE Quote_header_id = l_quote_header_id
AND quote_line_id is null
AND modifier_line_id = to_number(l_price_attribute2);
SELECT price_adjustment_id
FROM aso_price_adjustments
WHERE Quote_header_id = l_quote_header_id
AND Quote_line_id = l_quote_line_id
AND modifier_header_id = to_number(l_price_attribute1);
SELECT price_adjustment_id
FROM aso_price_adjustments
WHERE Quote_header_id = l_quote_header_id
AND Quote_line_id = l_quote_line_id
AND modifier_line_id = to_number(l_price_attribute2);
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Promotion';
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Inside Delete_Promotion', 1, 'Y');
SAVEPOINT Delete_Promotion_PVT;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:In Delete Promotion - p_price_attr_tbl(i).operation_code:'
||p_price_attr_tbl(i).operation_code,1,'Y');
If p_price_attr_tbl(i).operation_code = 'DELETE' Then
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Parameters passed to Delete_Promotion: loop '||i,1,'Y');
ASO_PRICE_ATTRIBUTES_PKG.Delete_Row
(p_PRICE_ATTRIBUTE_ID => p_price_attr_tbl(i).price_attribute_id);
/*Delete all the header level adjustments*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting header level adjustments',1,'Y');
/*Delete all the adjustment record with the modifier_header_id*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting header level adjustments with the modifier_header_id',1,'Y');
ASO_PRICE_ADJUSTMENTS_PKG.Delete_Row(p_PRICE_ADJ_ID => l_price_adjustment_id);
/*Delete all the adjustment record with the modifier_line_id*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting header level adjustments with the modifier_line_id',1,'Y');
ASO_PRICE_ADJUSTMENTS_PKG.Delete_Row(p_PRICE_ADJ_ID => l_price_adjustment_id);
Else/*Delete all the line level adjustments*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting line level adjustments',1,'Y');
/*Delete all the adjustment record with the modifier_header_id*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting line level adjustments with the modifier_header_id',1,'Y');
ASO_PRICE_ADJUSTMENTS_PKG.Delete_Row(p_PRICE_ADJ_ID => l_price_adjustment_id);
/*Delete all the adjustment record with the modifier_line_id*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Deleting line level adjustments with the modifier_line_id',1,'Y');
ASO_PRICE_ADJUSTMENTS_PKG.Delete_Row(p_PRICE_ADJ_ID => l_price_adjustment_id);
/*Delete the Price Attribute Record*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:After Deleting Adj Deleting the attribute rec',1,'Y');
ASO_PRICE_ATTRIBUTES_PKG.Delete_Row
(p_PRICE_ATTRIBUTE_ID => p_price_attr_tbl(i).price_attribute_id);
End If;-- p_price_attr_tbl(i).operation_code = 'DELETE'
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Delete_Promotion Ends', 1, 'Y');
END Delete_Promotion;
P_Insert_Type IN VARCHAR2 := 'HDR',
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Copy_Price_To_Quote';
l_last_update_date Date := SYSDATE;
SELECT line_id,
pricing_status_code,
pricing_status_text
FROM qp_preq_lines_tmp lines
WHERE lines.line_type_code='LINE'
AND lines.pricing_status_code in(
QP_PREQ_GRP.g_status_invalid_price_list,
QP_PREQ_GRP.g_sts_lhs_not_found,
QP_PREQ_GRP.g_status_formula_error,
QP_PREQ_GRP.g_status_other_errors,
fnd_api.g_ret_sts_unexp_error,
fnd_api.g_ret_sts_error,
QP_PREQ_GRP.g_status_calc_error,
QP_PREQ_GRP.g_status_uom_failure,
QP_PREQ_GRP.g_status_invalid_uom,
QP_PREQ_GRP.g_status_dup_price_list,
QP_PREQ_GRP.g_status_invalid_uom_conv,
QP_PREQ_GRP.g_status_invalid_incomp,
QP_PREQ_GRP.g_status_best_price_eval_error,
QP_PREQ_PUB.g_back_calculation_sts);
SELECT pricing_status_code,
pricing_status_text
FROM qp_preq_lines_tmp lines
WHERE lines.line_type_code='LINE'
AND lines.pricing_status_code = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
SELECT
rltd.REQUEST_TYPE_CODE,
rltd.LINE_INDEX,
rltd.LINE_DETAIL_INDEX,
rltd.RELATIONSHIP_TYPE_CODE,
rltd.RELATED_LINE_INDEX,
rltd.RELATED_LINE_DETAIL_INDEX,
rltd.PRICING_STATUS_CODE,
rltd.PRICING_STATUS_TEXT,
rltd.LIST_LINE_ID,
rltd.RELATED_LIST_LINE_ID,
rltd.RELATED_LIST_LINE_TYPE,
rltd.OPERAND_CALCULATION_CODE,
rltd.OPERAND,
rltd.PRICING_GROUP_SEQUENCE,
rltd.RELATIONSHIP_TYPE_DETAIL,
rltd.SETUP_VALUE_FROM,
rltd.SETUP_VALUE_TO,
rltd.QUALIFIER_VALUE,
rltd.ADJUSTMENT_AMOUNT,
rltd.SATISFIED_RANGE_VALUE,
rltd.REQUEST_ID,
lines.line_id,
lines.process_status
FROM QP_PREQ_RLTD_LINES_TMP rltd,
QP_PREQ_LINES_TMP lines
WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND RLTD.Relationship_Type_Code in (QP_PREQ_GRP.G_PBH_LINE ,QP_PREQ_GRP.G_GENERATED_LINE )
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND lines.line_type_code = 'LINE'
AND lines.line_index = rltd.line_index;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:p_insert_type:'||p_insert_type,1,'Y');
SELECT count(rowid)
INTO l_db_line_counter
FROM ASO_QUOTE_LINES_ALL
WHERE quote_header_id = p_qte_header_rec.quote_header_id;
UPDATE ASO_QUOTE_LINES_all l
SET (line_quote_price
,line_list_price
,line_adjusted_amount
,line_adjusted_percent
,quantity
,uom_code
,priced_price_list_id
,pricing_quantity
,pricing_quantity_uom
,last_update_date
,last_updated_by
,last_update_login
)
=
(SELECT to_number(substr(decode(l.selling_price_change,'Y',l.line_quote_price,NVL(Lines.order_uom_selling_price,lines.line_unit_price)),1,40)) -- bug 14680110
--decode(l.selling_price_change,'Y',l.line_quote_price,NVL(Lines.order_uom_selling_price,lines.line_unit_price))
,to_number(substr(lines.line_unit_price,1,40)) -- bug 14680110
,to_number(substr((NVL(lines.order_uom_selling_price, lines.line_unit_price)-lines.line_unit_price),1,40)) -- bug 14680110
,to_number(substr(decode(lines.line_unit_price,0,lines.line_unit_price,
((NVL(lines.order_uom_selling_price, lines.line_unit_price)-lines.line_unit_price)/lines.line_unit_price)*100),1,40)) -- bug 14680110
,lines.line_quantity
,lines.line_uom_code
,lines.price_list_header_id
,lines.priced_quantity
,lines.priced_uom_code
,sysdate
,G_USER_ID
,G_LOGIN_ID
FROM qp_preq_lines_tmp lines
WHERE lines.line_id=l.quote_line_id
AND lines.line_type_code='LINE'
)
WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
AND l.quote_line_id IN
(SELECT line_id
FROM qp_preq_lines_tmp lines
WHERE lines.pricing_status_code
IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.line_type_code='LINE');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:With Price Event Lines Updated '||sql%ROWCOUNT,1,'Y');
UPDATE ASO_QUOTE_LINES_all l
SET (line_quote_price
,line_list_price
,line_adjusted_amount
,line_adjusted_percent
,quantity
,uom_code
,priced_price_list_id
,pricing_quantity
,pricing_quantity_uom
,last_update_date
,last_updated_by
,last_update_login
,recalculate_flag
,selling_price_change
)
=
(SELECT to_number(substr(NVL(Lines.order_uom_selling_price,lines.line_unit_price),1,40)) -- bug 14680110
,to_number(substr(lines.line_unit_price,1,40)) -- bug 14680110
,to_number(substr((NVL(lines.order_uom_selling_price, lines.line_unit_price)-lines.line_unit_price),1,40)) -- bug 14680110
,to_number(substr(decode(lines.line_unit_price,0,lines.line_unit_price,
((NVL(lines.order_uom_selling_price, lines.line_unit_price)-lines.line_unit_price)/lines.line_unit_price)*100),1,40)) -- bug 14680110
,lines.line_quantity
,lines.line_uom_code
,lines.price_list_header_id
,lines.priced_quantity
,lines.priced_uom_code
,sysdate
,G_USER_ID
,G_LOGIN_ID
,'N'--recalculate_flag - reset back to the default value
,'N'--selling_price_change - reset back to the default value
FROM qp_preq_lines_tmp lines
WHERE lines.line_id=l.quote_line_id
AND lines.line_type_code='LINE'
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
)
WHERE l.quote_header_id=p_qte_header_rec.quote_header_id
AND l.quote_line_id IN
(SELECT line_id
FROM qp_preq_lines_tmp lines
WHERE lines.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND lines.line_type_code='LINE');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Lines Updated '||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:p_insert_type: '||p_insert_type,1,'Y');
If p_insert_type = 'HDR' then
INSERT INTO ASO_PRICE_ADJUSTMENTS
(price_adjustment_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_header_id,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
MODIFIED_FROM,
MODIFIED_TO,
OPERAND,
ARITHMETIC_OPERATOR,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
UPDATED_FLAG,
APPLIED_FLAG,
ON_INVOICE_FLAG,
CHARGE_TYPE_CODE,
PRICING_PHASE_ID,
PRICING_GROUP_SEQUENCE,
PRICE_BREAK_TYPE_CODE,
ADJUSTED_AMOUNT,
MODIFIER_LEVEL_CODE,
ACCRUAL_FLAG,
LIST_LINE_NO,
ACCRUAL_CONVERSION_RATE,
EXPIRATION_DATE,
CHARGE_SUBTYPE_CODE,
INCLUDE_ON_RETURNS_FLAG,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
PRORATION_TYPE_CODE,
REBATE_TRANSACTION_TYPE_CODE,
range_break_quantity,
MODIFIER_MECHANISM_TYPE_CODE,
SUBSTITUTION_ATTRIBUTE,
change_reason_code,
change_reason_text,
update_allowed,
operand_per_pqty,
adjusted_amount_per_pqty
)(
SELECT
nvl(ldets_v.PRICE_ADJUSTMENT_ID,ASO_PRICE_ADJUSTMENTS_S.nextval),
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,p_qte_header_rec.quote_header_id,
decode(ldets_v.modifier_level_code,'ORDER',NULL,lines.line_id),
ldets_v.list_header_id,
ldets_v.list_line_id,
ldets_v.list_line_type_code,
NULL,
NULL,
ldets_v.ORDER_QTY_OPERAND,
ldets_v.Operand_Calculation_Code,
ldets_v.Automatic_flag,
ldets_v.Override_flag,
ldets_v.UPDATED_FLAG,
ldets_v.Applied_Flag,
ldets_v.Print_On_Invoice_Flag,
ldets_v.CHARGE_TYPE_CODE,
ldets_v.Pricing_phase_id,
ldets_v.PRICING_GROUP_SEQUENCE,
ldets_v.PRICE_BREAK_TYPE_CODE,
ldets_v.ORDER_QTY_ADJ_AMT,
ldets_v.MODIFIER_LEVEL_CODE,
ldets_v.ACCRUAL_FLAG,
ldets_v.LIST_LINE_NO,
ldets_v.ACCRUAL_CONVERSION_RATE,
ldets_v.EXPIRATION_DATE,
ldets_v.CHARGE_SUBTYPE_CODE,
ldets_v.INCLUDE_ON_RETURNS_FLAG,
ldets_v.BENEFIT_QTY,
ldets_v.BENEFIT_UOM_CODE,
ldets_v.PRORATION_TYPE_CODE,
ldets_v.REBATE_TRANSACTION_TYPE_CODE,
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Line_quantity),
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Calculation_code),
ldets_v.SUBSTITUTION_ATTRIBUTE,
ldets_v.change_reason_code,
ldets_v.change_reason_text,
ldets_v.OVERRIDE_FLAG,
ldets_v.operand_value,
ldets_v.adjustment_amount
FROM QP_PREQ_LINES_TMP lines,
QP_LDETS_V ldets_v
WHERE lines.line_index = ldets_v.line_index
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
AND nvl(ldets_v.created_from_list_type_code,'X') not in ('PRL','AGR'));
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment '||sql%ROWCOUNT,1,'Y');
* Insert to Price Adj Relationships
*
*/
INSERT INTO ASO_PRICE_ADJ_RELATIONSHIPS
(adj_relationship_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_line_id,
price_adjustment_id,
rltd_price_adj_id
)
(SELECT ASO_PRICE_RELATIONSHIPS_S.nextval
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,adj.QUOTE_LINE_ID
,ADJ.PRICE_ADJUSTMENT_ID
,RADJ.PRICE_ADJUSTMENT_ID
FROM
QP_PREQ_RLTD_LINES_TMP RLTD,
QP_PREQ_LINES_TMP LINE,
QP_PREQ_LINES_TMP RLTD_LINE,
ASO_PRICE_ADJUSTMENTS ADJ,
ASO_PRICE_ADJUSTMENTS RADJ
WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND RLTD_LINE.line_index = RLTD.related_line_index
AND RLTD.Relationship_Type_Code in
(QP_PREQ_GRP.G_PBH_LINE ,QP_PREQ_GRP.G_GENERATED_LINE )
AND line.line_index = rltd.line_index
AND adj.quote_header_id = p_qte_header_rec.quote_header_id
AND adj.quote_line_id = line.line_id
AND line.line_type_code = 'LINE'
AND line.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND rltd_line.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND adj.modifier_line_id = rltd.list_line_id
AND radj.quote_header_id = p_qte_header_rec.quote_header_id
AND radj.quote_line_id = rltd_line.line_id
AND radj.modifier_line_id = rltd.related_list_line_id);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment Relationships '||sql%ROWCOUNT,1,'Y');
* Insert into Price Adj Attributes - Line Attributes
*/
-- Added hint to fix perf bug 5614878.
INSERT INTO ASO_PRICE_ADJ_ATTRIBS
( price_adj_attrib_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
price_adjustment_id,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
flex_title)
(SELECT /*+ ORDERED USE_NL(LINES LDETS QPLAT ADJ) INDEX(LINES) INDEX(LDETS) INDEX(QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */
ASO_PRICE_ADJ_ATTRIBS_S.nextval,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,nvl(QPLAT.SETUP_VALUE_FROM,QPLAT.VALUE_FROM)
,QPLAT.SETUP_VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
FROM
ASO_PRICE_ADJUSTMENTS ADJ,
QP_PREQ_LINES_TMP LINES ,
QP_PREQ_LDETS_TMP LDETS,
QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND LDETS.LINE_INDEX = LINES.LINE_INDEX
AND LDETS.PRICING_PHASE_ID = ADJ.PRICING_PHASE_ID
AND LDETS.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.CREATED_FROM_LIST_LINE_ID = ADJ.MODIFIER_LINE_ID
AND LDETS.CREATED_FROM_LIST_HEADER_ID = ADJ.MODIFIER_HEADER_ID
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment Attributes - Line Level Attributes'||sql%ROWCOUNT,1,'Y');
INSERT INTO ASO_PRICE_ADJ_ATTRIBS
( price_adj_attrib_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
price_adjustment_id,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
flex_title)
(SELECT /*+ ORDERED USE_NL(LINES LDETS QPLAT ADJ) INDEX(LINES) INDEX(LDETS) INDEX(QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */ ASO_PRICE_ADJ_ATTRIBS_S.nextval,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,nvl(QPLAT.SETUP_VALUE_FROM,QPLAT.VALUE_FROM)
,QPLAT.SETUP_VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
FROM
ASO_PRICE_ADJUSTMENTS ADJ,
QP_PREQ_LINES_TMP LINES ,
QP_PREQ_LDETS_TMP LDETS,
QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
AND LDETS.LINE_INDEX = LINES.LINE_INDEX
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND LDETS.PRICING_PHASE_ID = ADJ.PRICING_PHASE_ID
AND LDETS.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.CREATED_FROM_LIST_LINE_ID = ADJ.MODIFIER_LINE_ID
AND LDETS.CREATED_FROM_LIST_HEADER_ID = ADJ.MODIFIER_HEADER_ID
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment Attributes - Header Level Attributes'||sql%ROWCOUNT,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: p_insert_type :'||p_insert_type, 1, 'Y');
PROCESS_LN_TSN(p_qte_header_rec.quote_header_id, p_insert_type);
Elsif p_insert_type = 'NO_HDR' then
/*p_insert_type <> 'HDR'*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Only insert line level adjustments and relationships and attribs',1,'Y');
INSERT INTO ASO_PRICE_ADJUSTMENTS
(price_adjustment_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_header_id,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
MODIFIED_FROM,
MODIFIED_TO,
OPERAND,
ARITHMETIC_OPERATOR,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
UPDATED_FLAG,
APPLIED_FLAG,
ON_INVOICE_FLAG,
CHARGE_TYPE_CODE,
PRICING_PHASE_ID,
PRICING_GROUP_SEQUENCE,
PRICE_BREAK_TYPE_CODE,
ADJUSTED_AMOUNT,
MODIFIER_LEVEL_CODE,
ACCRUAL_FLAG,
LIST_LINE_NO,
ACCRUAL_CONVERSION_RATE,
EXPIRATION_DATE,
CHARGE_SUBTYPE_CODE,
INCLUDE_ON_RETURNS_FLAG,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
PRORATION_TYPE_CODE,
REBATE_TRANSACTION_TYPE_CODE,
range_break_quantity,
MODIFIER_MECHANISM_TYPE_CODE,
SUBSTITUTION_ATTRIBUTE,
change_reason_code,
change_reason_text,
update_allowed,
operand_per_pqty,
adjusted_amount_per_pqty
)(
SELECT
nvl(ldets_v.PRICE_ADJUSTMENT_ID,ASO_PRICE_ADJUSTMENTS_S.nextval),
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,p_qte_header_rec.quote_header_id,
lines.line_id,
ldets_v.list_header_id,
ldets_v.list_line_id,
ldets_v.list_line_type_code,
NULL,
NULL,
ldets_v.ORDER_QTY_OPERAND,
ldets_v.Operand_Calculation_Code,
ldets_v.Automatic_flag,
ldets_v.Override_flag,
ldets_v.UPDATED_FLAG,
ldets_v.Applied_Flag,
ldets_v.Print_On_Invoice_Flag,
ldets_v.CHARGE_TYPE_CODE,
ldets_v.Pricing_phase_id,
ldets_v.PRICING_GROUP_SEQUENCE,
ldets_v.PRICE_BREAK_TYPE_CODE,
ldets_v.ORDER_QTY_ADJ_AMT,
ldets_v.MODIFIER_LEVEL_CODE,
ldets_v.ACCRUAL_FLAG,
ldets_v.LIST_LINE_NO,
ldets_v.ACCRUAL_CONVERSION_RATE,
ldets_v.EXPIRATION_DATE,
ldets_v.CHARGE_SUBTYPE_CODE,
ldets_v.INCLUDE_ON_RETURNS_FLAG,
ldets_v.BENEFIT_QTY,
ldets_v.BENEFIT_UOM_CODE,
ldets_v.PRORATION_TYPE_CODE,
ldets_v.REBATE_TRANSACTION_TYPE_CODE,
ldets_v.Line_quantity,
ldets_v.Calculation_code,
ldets_v.SUBSTITUTION_ATTRIBUTE,
ldets_v.change_reason_code,
ldets_v.change_reason_text,
ldets_v.OVERRIDE_FLAG,
ldets_v.operand_value,
ldets_v.adjustment_amount
FROM QP_PREQ_LINES_TMP lines,
QP_LDETS_V ldets_v
WHERE lines.line_index = ldets_v.line_index
AND ldets_v.modifier_level_code = 'LINE'
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND nvl(ldets_v.Calculation_code,'X') <> QP_PREQ_PUB.G_FREEGOOD
AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
AND nvl(ldets_v.created_from_list_type_code,'X') not in ('PRL','AGR'));
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment '||sql%ROWCOUNT,1,'Y');
* Insert to Price Adj Relationships
*
*/
INSERT INTO ASO_PRICE_ADJ_RELATIONSHIPS
(adj_relationship_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_line_id,
price_adjustment_id,
rltd_price_adj_id
)
(SELECT ASO_PRICE_RELATIONSHIPS_S.nextval
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,adj.QUOTE_LINE_ID
,ADJ.PRICE_ADJUSTMENT_ID
,RADJ.PRICE_ADJUSTMENT_ID
FROM
QP_PREQ_RLTD_LINES_TMP RLTD,
QP_PREQ_LINES_TMP LINE,
QP_PREQ_LINES_TMP RLTD_LINE,
ASO_PRICE_ADJUSTMENTS ADJ,
ASO_PRICE_ADJUSTMENTS RADJ
WHERE RLTD.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND RLTD_LINE.line_index = RLTD.related_line_index
AND RLTD.Relationship_Type_Code in
(QP_PREQ_GRP.G_PBH_LINE ,QP_PREQ_GRP.G_GENERATED_LINE )
AND line.line_index = rltd.line_index
AND adj.quote_header_id = p_qte_header_rec.quote_header_id
AND adj.quote_line_id = line.line_id
AND line.line_type_code = 'LINE'
AND line.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND adj.modifier_line_id = rltd.list_line_id
AND radj.quote_header_id = p_qte_header_rec.quote_header_id
AND radj.quote_line_id = rltd_line.line_id
AND radj.modifier_line_id = rltd.related_list_line_id);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment Relationships '||sql%ROWCOUNT,1,'Y');
* Insert into Price Adj Attributes - Line Attributes
*/
-- Added hint to fix perf bug 5614878.
INSERT INTO ASO_PRICE_ADJ_ATTRIBS
( price_adj_attrib_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
price_adjustment_id,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
flex_title)
(SELECT /*+ ORDERED USE_NL(LINES LDETS QPLAT ADJ) INDEX(LINES) INDEX(LDETS) INDEX(QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */ ASO_PRICE_ADJ_ATTRIBS_S.nextval,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,nvl(QPLAT.SETUP_VALUE_FROM,QPLAT.VALUE_FROM)
,QPLAT.SETUP_VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
FROM
ASO_PRICE_ADJUSTMENTS ADJ,
QP_PREQ_LINES_TMP LINES ,
QP_PREQ_LDETS_TMP LDETS,
QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND LINES.LINE_ID = ADJ.QUOTE_LINE_ID
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND LDETS.LINE_INDEX = LINES.LINE_INDEX
AND LDETS.PRICING_PHASE_ID = ADJ.PRICING_PHASE_ID
AND LDETS.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.CREATED_FROM_LIST_LINE_ID = ADJ.MODIFIER_LINE_ID
AND LDETS.CREATED_FROM_LIST_HEADER_ID = ADJ.MODIFIER_HEADER_ID
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment Attributes - Line Level Attributes'||sql%ROWCOUNT,1,'Y');
PROCESS_LN_TSN(p_qte_header_rec.quote_header_id, p_insert_type);
/* p_insert_type = 'HDR_ONLY' */
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:p_insert_type: '||p_insert_type,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Inserting only header level adjustments',1,'Y');
INSERT INTO ASO_PRICE_ADJUSTMENTS
(price_adjustment_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_header_id,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
MODIFIED_FROM,
MODIFIED_TO,
OPERAND,
ARITHMETIC_OPERATOR,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
UPDATED_FLAG,
APPLIED_FLAG,
ON_INVOICE_FLAG,
CHARGE_TYPE_CODE,
PRICING_PHASE_ID,
PRICING_GROUP_SEQUENCE,
PRICE_BREAK_TYPE_CODE,
ADJUSTED_AMOUNT,
MODIFIER_LEVEL_CODE,
ACCRUAL_FLAG,
LIST_LINE_NO,
ACCRUAL_CONVERSION_RATE,
EXPIRATION_DATE,
CHARGE_SUBTYPE_CODE,
INCLUDE_ON_RETURNS_FLAG,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
PRORATION_TYPE_CODE,
REBATE_TRANSACTION_TYPE_CODE,
range_break_quantity,
MODIFIER_MECHANISM_TYPE_CODE,
SUBSTITUTION_ATTRIBUTE,
change_reason_code,
change_reason_text,
update_allowed,
operand_per_pqty,
adjusted_amount_per_pqty
)(
SELECT
nvl(ldets_v.PRICE_ADJUSTMENT_ID,ASO_PRICE_ADJUSTMENTS_S.nextval),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_qte_header_rec.quote_header_id,
decode(ldets_v.modifier_level_code,'ORDER',NULL,lines.line_id),
ldets_v.list_header_id,
ldets_v.list_line_id,
ldets_v.list_line_type_code,
NULL,
NULL,
ldets_v.ORDER_QTY_OPERAND,
ldets_v.Operand_Calculation_Code,
ldets_v.Automatic_flag,
ldets_v.Override_flag,
ldets_v.UPDATED_FLAG,
ldets_v.Applied_Flag,
ldets_v.Print_On_Invoice_Flag,
ldets_v.CHARGE_TYPE_CODE,
ldets_v.Pricing_phase_id,
ldets_v.PRICING_GROUP_SEQUENCE,
ldets_v.PRICE_BREAK_TYPE_CODE,
ldets_v.ORDER_QTY_ADJ_AMT,
ldets_v.MODIFIER_LEVEL_CODE,
ldets_v.ACCRUAL_FLAG,
ldets_v.LIST_LINE_NO,
ldets_v.ACCRUAL_CONVERSION_RATE,
ldets_v.EXPIRATION_DATE,
ldets_v.CHARGE_SUBTYPE_CODE,
ldets_v.INCLUDE_ON_RETURNS_FLAG,
ldets_v.BENEFIT_QTY,
ldets_v.BENEFIT_UOM_CODE,
ldets_v.PRORATION_TYPE_CODE,
ldets_v.REBATE_TRANSACTION_TYPE_CODE,
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Line_quantity),
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Calculation_code),
ldets_v.SUBSTITUTION_ATTRIBUTE,
ldets_v.change_reason_code,
ldets_v.change_reason_text,
ldets_v.OVERRIDE_FLAG,
ldets_v.operand_value,
ldets_v.adjustment_amount
FROM QP_PREQ_LINES_TMP lines,
QP_LDETS_V ldets_v
WHERE lines.line_index = ldets_v.line_index
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
AND nvl(ldets_v.created_from_list_type_code,'X') not in ('PRL','AGR')
AND ldets_v.modifier_level_code = 'ORDER');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert into Adjustment '||sql%ROWCOUNT,1,'Y');
INSERT INTO ASO_PRICE_ADJ_ATTRIBS
( price_adj_attrib_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
price_adjustment_id,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
flex_title)
(SELECT /*+ ORDERED USE_NL(LINES LDETS QPLAT ADJ) INDEX(LINES) INDEX(LDETS) INDEX(QPLAT QP_PREQ_LINE_ATTRS_TMP_N3) */ ASO_PRICE_ADJ_ATTRIBS_S.nextval,
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,ADJ.PRICE_ADJUSTMENT_ID
,QPLAT.CONTEXT
,QPLAT.ATTRIBUTE
,nvl(QPLAT.SETUP_VALUE_FROM,QPLAT.VALUE_FROM)
,QPLAT.SETUP_VALUE_TO
,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
,decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
FROM
ASO_PRICE_ADJUSTMENTS ADJ,
QP_PREQ_LINES_TMP LINES ,
QP_PREQ_LDETS_TMP LDETS,
QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE ADJ.QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND LINES.LINE_ID = ADJ.QUOTE_HEADER_ID
AND LDETS.LINE_INDEX = LINES.LINE_INDEX
AND lines.process_status IN (QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED)
AND LDETS.PRICING_PHASE_ID = ADJ.PRICING_PHASE_ID
AND LDETS.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.CREATED_FROM_LIST_LINE_ID = ADJ.MODIFIER_LINE_ID
AND LDETS.CREATED_FROM_LIST_HEADER_ID = ADJ.MODIFIER_HEADER_ID
AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
AND QPLAT.PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_NEW);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Insert Adjustment Attributes - Header Level Attributes'||sql%ROWCOUNT,1,'Y');
End If;--If p_insert_type = 'HDR'
INSERT INTO ASO_PRICE_ADJUSTMENTS
(price_adjustment_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
quote_header_id,
quote_line_id,
MODIFIER_HEADER_ID,
MODIFIER_LINE_ID,
MODIFIER_LINE_TYPE_CODE,
MODIFIED_FROM,
MODIFIED_TO,
OPERAND,
ARITHMETIC_OPERATOR,
AUTOMATIC_FLAG,
UPDATE_ALLOWABLE_FLAG,
UPDATED_FLAG,
APPLIED_FLAG,
ON_INVOICE_FLAG,
CHARGE_TYPE_CODE,
PRICING_PHASE_ID,
PRICING_GROUP_SEQUENCE,
PRICE_BREAK_TYPE_CODE,
ADJUSTED_AMOUNT,
MODIFIER_LEVEL_CODE,
ACCRUAL_FLAG,
LIST_LINE_NO,
ACCRUAL_CONVERSION_RATE,
EXPIRATION_DATE,
CHARGE_SUBTYPE_CODE,
INCLUDE_ON_RETURNS_FLAG,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
PRORATION_TYPE_CODE,
REBATE_TRANSACTION_TYPE_CODE,
range_break_quantity,
MODIFIER_MECHANISM_TYPE_CODE,
SUBSTITUTION_ATTRIBUTE,
change_reason_code,
change_reason_text,
update_allowed,
operand_per_pqty,
adjusted_amount_per_pqty
)(
SELECT
nvl(ldets_v.PRICE_ADJUSTMENT_ID,ASO_PRICE_ADJUSTMENTS_S.nextval),
sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,p_qte_header_rec.quote_header_id,
decode(ldets_v.modifier_level_code,'ORDER',NULL,lines.line_id),
ldets_v.list_header_id,
ldets_v.list_line_id,
ldets_v.list_line_type_code,
NULL,
NULL,
ldets_v.ORDER_QTY_OPERAND,
ldets_v.Operand_Calculation_Code,
ldets_v.Automatic_flag,
ldets_v.Override_flag,
ldets_v.UPDATED_FLAG,
ldets_v.Applied_Flag,
ldets_v.Print_On_Invoice_Flag,
ldets_v.CHARGE_TYPE_CODE,
ldets_v.Pricing_phase_id,
ldets_v.PRICING_GROUP_SEQUENCE,
ldets_v.PRICE_BREAK_TYPE_CODE,
ldets_v.ORDER_QTY_ADJ_AMT,
ldets_v.MODIFIER_LEVEL_CODE,
ldets_v.ACCRUAL_FLAG,
ldets_v.LIST_LINE_NO,
ldets_v.ACCRUAL_CONVERSION_RATE,
ldets_v.EXPIRATION_DATE,
ldets_v.CHARGE_SUBTYPE_CODE,
ldets_v.INCLUDE_ON_RETURNS_FLAG,
ldets_v.BENEFIT_QTY,
ldets_v.BENEFIT_UOM_CODE,
ldets_v.PRORATION_TYPE_CODE,
ldets_v.REBATE_TRANSACTION_TYPE_CODE,
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Line_quantity),
decode(ldets_v.modifier_level_code,'ORDER',NULL,ldets_v.Calculation_code),
ldets_v.SUBSTITUTION_ATTRIBUTE,
ldets_v.change_reason_code,
ldets_v.change_reason_text,
ldets_v.OVERRIDE_FLAG,
ldets_v.operand_value,
ldets_v.adjustment_amount
FROM QP_PREQ_LINES_TMP lines,
QP_LDETS_V ldets_v
WHERE lines.line_index = ldets_v.line_index
AND ldets_v.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets_v.process_code = QP_PREQ_GRP.G_STATUS_NEW
AND nvl(ldets_v.created_from_list_type_code,'X') not in ('PRL','AGR')
AND nvl(ldets_v.list_line_type_code,'X') <> 'FREIGHT_CHARGE');
aso_debug_pub.add('ASO_PRICING_CORE_PVT: p_insert_type :'||p_insert_type, 1, 'Y');
If p_insert_type = 'HDR' then
PROCESS_HDR_TSN(p_qte_header_rec.quote_header_id);
l_global_tbl.delete;
l_global_tbl.delete;
SELECT sum(NVL(LINE_LIST_PRICE*quantity, 0)) total_list_price,
sum(NVL(line_adjusted_amount*quantity,NVL(LINE_ADJUSTED_PERCENT*LINE_LIST_PRICE*quantity,0))) ln_total_discount
FROM ASO_QUOTE_LINES_ALL
WHERE QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id;
l_last_update_date Date := SYSDATE;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Before Update ASO_SHIPMENTS:l_hdr_shipping_charge: '
||l_hdr_shipping_charge,1,'Y');
UPDATE ASO_SHIPMENTS
SET SHIP_QUOTE_PRICE = l_hdr_shipping_charge,
last_update_date = l_last_update_date,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND QUOTE_LINE_ID is NULL;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Before Update ASO_SHIPMENTS:quote_line_id: '||ls_qte_line_tbl(i).quote_line_id
||' line charge for the quote line:'||l_line_shipping_charge,1,'Y');
UPDATE ASO_SHIPMENTS
SET SHIP_QUOTE_PRICE = l_line_shipping_charge,
QUANTITY = ls_qte_line_tbl(i).quantity,
last_update_date = l_last_update_date,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
AND QUOTE_LINE_ID = ls_qte_line_tbl(i).quote_line_id;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Update Shipment info and before calling get_line_freight_charges Ends', 1, 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Cursor c_qte_sum opens and update of total for ASO_QUOTE_HEADERS_ALL Starts',1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Before Update ASO_QUOTE_HEADERS_ALL l_total_list_price:'||l_total_list_price, 1, 'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Before Update ASO_QUOTE_HEADERS_ALL l_ln_total_discount:'||l_ln_total_discount,1,'Y');
UPDATE ASO_QUOTE_HEADERS_ALL
SET total_list_price = l_total_list_price,
TOTAL_ADJUSTED_AMOUNT = l_ln_total_discount,
total_adjusted_percent = decode(total_list_price, 0, NULL,
(l_ln_total_discount/total_list_price)*100),
total_quote_price = l_total_list_price+l_ln_total_discount+
NVL(total_tax, 0)+
NVL(total_shipping_charge, 0),
last_update_date = l_last_update_date,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE quote_header_id = p_qte_header_rec.quote_header_id;
l_last_update_date Date := SYSDATE;
SELECT
lines.REQUEST_TYPE_CODE REQUEST_TYPE_CODE,
lines.LINE_ID LINE_ID,
lines.LINE_INDEX LINE_INDEX,
lines.LINE_TYPE_CODE LINE_TYPE_CODE,
lines.PRICING_EFFECTIVE_DATE PRICING_EFFECTIVE_DATE,
lines.LINE_QUANTITY LINE_QUANTITY,
lines.LINE_UOM_CODE LINE_UOM_CODE,
lines.PRICED_QUANTITY PRICED_QUANTITY,
lines.PRICED_UOM_CODE PRICED_UOM_CODE,
lines.UOM_QUANTITY UOM_QUANTITY,
lines.CURRENCY_CODE CURRENCY_CODE,
lines.UNIT_PRICE UNIT_PRICE,
lines.PERCENT_PRICE PERCENT_PRICE,
lines.ADJUSTED_UNIT_PRICE ADJUSTED_UNIT_PRICE,
lines.PARENT_PRICE PARENT_PRICE,
lines.PARENT_QUANTITY PARENT_QUANTITY,
lines.PARENT_UOM_CODE PARENT_UOM_CODE,
lines.PROCESSING_ORDER PROCESSING_ORDER,
lines.PROCESSED_FLAG PROCESSED_FLAG,
lines.PROCESSED_CODE PROCESSED_CODE,
lines.PRICE_FLAG PRICE_FLAG,
lines.PRICING_STATUS_CODE PRICING_STATUS_CODE,
lines.PRICING_STATUS_TEXT PRICING_STATUS_TEXT,
lines.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST,
lines.ACTIVE_DATE_FIRST_TYPE ACTIVE_DATE_FIRST_TYPE,
lines.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND,
lines.ACTIVE_DATE_SECOND_TYPE ACTIVE_DATE_SECOND_TYPE,
lines.GROUP_QUANTITY GROUP_QUANTITY,
lines.GROUP_AMOUNT GROUP_AMOUNT,
lines.LINE_AMOUNT LINE_AMOUNT,
lines.ROUNDING_FLAG ROUNDING_FLAG,
lines.ROUNDING_FACTOR ROUNDING_FACTOR,
lines.UPDATED_ADJUSTED_UNIT_PRICE UPDATED_ADJUSTED_UNIT_PRICE,
lines.PRICE_REQUEST_CODE PRICE_REQUEST_CODE,
lines.HOLD_CODE HOLD_CODE,
lines.HOLD_TEXT HOLD_TEXT,
lines.PRICE_LIST_HEADER_ID PRICE_LIST_HEADER_ID,
lines.VALIDATED_FLAG VALIDATED_FLAG,
lines.QUALIFIERS_EXIST_FLAG QUALIFIERS_EXIST_FLAG,
lines.PRICING_ATTRS_EXIST_FLAG PRICING_ATTRS_EXIST_FLAG,
lines.PRIMARY_QUALIFIERS_MATCH_FLAG PRIMARY_QUALIFIERS_MATCH_FLAG,
lines.USAGE_PRICING_TYPE USAGE_PRICING_TYPE,
lines.LINE_CATEGORY LINE_CATEGORY,
lines.CONTRACT_START_DATE CONTRACT_START_DATE,
lines.CONTRACT_END_DATE CONTRACT_END_DATE,
lines.LINE_UNIT_PRICE LINE_UNIT_PRICE,
lines.REQUEST_ID REQUEST_ID,
lines.PROCESS_STATUS PROCESS_STATUS,
lines.EXTENDED_PRICE EXTENDED_PRICE,
lines.ORDER_UOM_SELLING_PRICE ORDER_UOM_SELLING_PRICE,
lines.CATCHWEIGHT_QTY CATCHWEIGHT_QTY,
lines.ACTUAL_ORDER_QUANTITY ACTUAL_ORDER_QUANTITY,
attrs.ATTRIBUTE ATTRIBUTE,
attrs.CONTEXT CONTEXT,
attrs.VALUE_FROM VALUE_FROM
FROM QP_PREQ_LINES_TMP lines,
QP_PREQ_LINE_ATTRS_TMP attrs
--WHERE lines.PROCESSED_CODE = QP_PREQ_PUB.G_BY_ENGINE
WHERE lines.process_status in (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_DELETED)
AND lines.line_index = attrs.line_index
AND attrs.CONTEXT = 'ITEM'
AND attrs.ATTRIBUTE = 'PRICING_ATTRIBUTE1';
SELECT
LINE_INDEX,
LINE_DETAIL_INDEX,
RELATIONSHIP_TYPE_CODE,
RELATED_LINE_INDEX,
RELATED_LINE_DETAIL_INDEX,
LIST_LINE_ID,
RELATED_LIST_LINE_ID
FROM QP_PREQ_RLTD_LINES_TMP
WHERE RELATED_LINE_INDEX = l_rltd_line_index
AND RELATIONSHIP_TYPE_CODE = QP_PREQ_GRP.G_GENERATED_LINE;
SELECT
LINE_DETAIL_INDEX,
LINE_DETAIL_TYPE_CODE,
LINE_INDEX,
LIST_HEADER_ID,
LIST_LINE_ID,
LIST_LINE_TYPE_CODE,
PRICE_BREAK_TYPE_CODE,
LINE_QUANTITY,
ADJUSTMENT_AMOUNT,
AUTOMATIC_FLAG,
PRICING_PHASE_ID,
OPERAND_CALCULATION_CODE,
OPERAND_VALUE,
PRICING_GROUP_SEQUENCE,
CREATED_FROM_LIST_TYPE_CODE,
APPLIED_FLAG,
PRICING_STATUS_CODE,
PRICING_STATUS_TEXT,
LIMIT_CODE,
LIMIT_TEXT,
LIST_LINE_NO,
GROUP_QUANTITY,
UPDATED_FLAG,
PROCESS_CODE,
SUBSTITUTION_VALUE_TO,
SUBSTITUTION_ATTRIBUTE,
ACCRUAL_FLAG,
MODIFIER_LEVEL_CODE,
ESTIM_GL_VALUE,
ACCRUAL_CONVERSION_RATE,
OVERRIDE_FLAG,
PRINT_ON_INVOICE_FLAG,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
RELATED_ITEM_ID,
RELATIONSHIP_TYPE_ID,
ESTIM_ACCRUAL_RATE,
EXPIRATION_DATE,
BENEFIT_PRICE_LIST_LINE_ID,
RECURRING_FLAG,
BENEFIT_LIMIT,
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
PRORATION_TYPE_CODE,
INCLUDE_ON_RETURNS_FLAG,
REBATE_TRANSACTION_TYPE_CODE,
NUMBER_EXPIRATION_PERIODS,
EXPIRATION_PERIOD_UOM,
COMMENTS,
CALCULATION_CODE,
CHANGE_REASON_CODE,
CHANGE_REASON_TEXT,
PRICE_ADJUSTMENT_ID,
NET_AMOUNT_FLAG,
ORDER_QTY_OPERAND,
ORDER_QTY_ADJ_AMT
FROM QP_LDETS_V
WHERE line_index = l_line_index
AND line_detail_index = l_line_detail_index;
SELECT
QPLAT.CONTEXT CONTEXT,
QPLAT.ATTRIBUTE ATTRIBUTE,
nvl(QPLAT.SETUP_VALUE_FROM,QPLAT.VALUE_FROM) VALUE_FROM,
QPLAT.SETUP_VALUE_TO VALUE_TO,
QPLAT.COMPARISON_OPERATOR_TYPE_CODE COMPARISON_OPERATOR,
decode(QPLAT.ATTRIBUTE_TYPE,
'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING') FLEX_TITLE
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE QPLAT.LINE_INDEX = l_line_index
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index;
SELECT PRICE_ADJUSTMENT_ID
FROM ASO_PRICE_ADJUSTMENTS
WHERE quote_line_id = l_qte_line_id
AND modifier_line_id = l_modifier_line_id;
/*Prepraring for Update_Quote Call*/
FOR C_PRG_LINES_TMP_REC in C_PRG_LINES_TMP Loop
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:C_PRG_LINES_TMP_REC.PROCESS_STATUS: '
||C_PRG_LINES_TMP_REC.PROCESS_STATUS,1,'Y');
If C_PRG_LINES_TMP_REC.PROCESS_STATUS = QP_PREQ_GRP.G_STATUS_DELETED Then
l_qte_line_tbl(l_line_counter).OPERATION_CODE := 'DELETE';
If l_qte_line_tbl(l_line_counter).operation_code <> 'DELETE' then
/*Create Adjustment table*/
--l_Price_Adjustment_Tbl
For C_PRG_DISCOUNT_DTL_REC in C_PRG_DISCOUNT_DTL(C_PRG_LINES_TMP_REC.line_index,
C_PRG_RLTD_INFO_REC.related_line_detail_index) loop
IF l_adj_search_tbl.exists(C_PRG_RLTD_INFO_REC.related_line_detail_index) then
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT: C_PRG_RLTD_INFO_REC.related_line_detail_index:'
||C_PRG_RLTD_INFO_REC.related_line_detail_index||' already inserted',1,'Y');
l_Price_Adjustment_Tbl(l_adj_counter).update_allowable_flag := C_PRG_DISCOUNT_DTL_REC.Override_flag;
l_Price_Adjustment_Tbl(l_adj_counter).updated_flag := C_PRG_DISCOUNT_DTL_REC.updated_flag;
l_Price_Adjustment_Tbl(l_adj_counter).update_allowed := C_PRG_DISCOUNT_DTL_REC.override_flag;
aso_debug_pub.add('ASO_PRICING_CORE_PVT:l_Price_Adjustment_Tbl(l_adj_counter).update_allowable_flag: '
||l_Price_Adjustment_Tbl(l_adj_counter).update_allowable_flag,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:l_Price_Adjustment_Tbl(l_adj_counter).updated_flag: '
||l_Price_Adjustment_Tbl(l_adj_counter).updated_flag,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:l_Price_Adjustment_Tbl(l_adj_counter).update_allowed: '
||l_Price_Adjustment_Tbl(l_adj_counter).update_allowed,1,'Y');
SELECT LINE_ID
INTO l_parent_line_id
FROM QP_PREQ_LINES_TMP
WHERE LINE_INDEX = C_PRG_RLTD_INFO_REC.line_index;
END If;--l_qte_line_tbl(l_line_counter).operation_code <> 'DELETE'
/*Call update quote if l_qte_line_tbl.count is >0*/
IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
aso_debug_pub.add('ASO_PRICING_CORE_PVT:l_qte_line_tbl.count:'||nvl(l_qte_line_tbl.count,0),1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:Calling Update Quote for PRG lines',1,'Y');
SELECT last_update_date into l_last_update_date
FROM ASO_QUOTE_HEADERS_ALL
WHERE quote_header_id = p_qte_header_rec.quote_header_id;
l_qte_header_rec.last_update_date := l_last_update_date;
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
ASO_QUOTE_PUB.Update_Quote(
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
P_Control_Rec => l_pub_control_rec,
p_qte_header_rec => l_qte_header_rec,
p_qte_line_tbl => l_qte_line_tbl,
p_price_adjustment_Tbl => l_price_adjustment_Tbl,
P_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl,
p_price_adj_rltship_Tbl => l_price_adj_rltship_Tbl,
X_Qte_Header_Rec => lx_out_qte_header_rec,
X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
X_hd_Price_Attributes_Tbl => lx_hd_Price_Attributes_Tbl,
X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
X_hd_Quote_Party_Tbl => lx_hd_Quote_Party_Tbl,
X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
X_line_rltship_tbl => lx_line_rltship_tbl,
X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
X_ln_Price_Attributes_Tbl=> lx_ln_Price_Attributes_Tbl,
X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
X_Return_Status => x_Return_Status,
X_Msg_Count => x_Msg_Count,
X_Msg_Data => x_Msg_Data);
aso_debug_pub.add('ASO_PRICING_CORE_PVT:After Update Quote X_Return_Status:'||X_Return_Status,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:After Update Quote X_Msg_Count:'||X_Msg_Count,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:After Update Quote:lx_Qte_Line_Tbl(i).quote_line_id:'
||lx_Qte_Line_Tbl(i).quote_line_id,1,'Y');
aso_debug_pub.add('ASO_PRICING_CORE_PVT:After Update Quote:Count of x_Qte_Line_Tbl.count:'
||x_Qte_Line_Tbl.count,1,'Y');
/*In the second call we are not going to delete any adjustments and relationships and attributes*/
--Need to modify the global index table of pls integer types
l_global_pls_tbl := ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL;
l_req_control_rec.TEMP_TABLE_INSERT_FLAG := 'N'; ---- Modified
p_insert_type => 'NO_HDR',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);