[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT primary_uom_code
INTO g_primary_uom
FROM mtl_system_items_b
WHERE inventory_item_id = g_inventory_item_id
AND organization_id = g_from_organization_id;
SELECT currency_code
INTO g_from_org_currency
FROM hr_organization_information org, gl_ledgers gll
WHERE org.organization_id = g_from_organization_id
AND gll.ledger_id = org.org_information1
AND org.org_information_context = 'Accounting Information';
SELECT mp_from.process_enabled_flag, mp_to.process_enabled_flag
INTO l_process_enabled_flag_from, l_process_enabled_flag_to
FROM mtl_parameters mp_from, mtl_parameters mp_to
WHERE mp_from.organization_id = g_from_organization_id
AND mp_to.organization_id = g_to_organization_id;
SELECT 1
INTO l_exists
FROM mtl_intercompany_parameters
WHERE sell_organization_id = g_to_ou
AND ship_organization_id = g_from_ou
AND flow_type = 1
;
SELECT NVL(mip.pricelist_id,-1)
INTO l_pricelist_Id
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = g_from_organization_id
AND mip.to_organization_id = g_to_organization_id;
SELECT spll.operand, substr(spl.currency_code, 1, 15)
INTO l_transfer_price, l_pricelist_currency
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE spl.list_header_id = l_pricelist_id
AND spll.list_header_id = spl.list_header_id
AND spll.list_line_id = qpa.list_line_id
AND qpa.product_attribute_context = 'ITEM'
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.product_attr_value = to_Char(g_inventory_item_id)
AND qpa.product_uom_code = g_transaction_uom
AND sysdate BETWEEN NVL(spll.start_date_active, (sysdate-1)) AND
NVL(spll.end_date_active+0.99999, (sysdate+1))
AND rownum = 1
;
SELECT spll.operand, substr(spl.currency_code, 1, 15), msi.primary_uom_code
INTO l_transfer_price, l_pricelist_currency, l_primary_uom
FROM qp_list_headers_b spl, qp_list_lines spll,
qp_pricing_attributes qpa, mtl_system_items_b msi
WHERE msi.organization_id = g_from_organization_id
AND msi.inventory_item_id = g_inventory_item_id
AND spl.list_header_id = l_pricelist_id
AND spll.list_header_id = spl.list_header_id
AND qpa.list_header_id = spl.list_header_id
AND spll.list_line_id = qpa.list_line_id
AND qpa.product_attribute_context = 'ITEM'
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
AND qpa.product_attr_value = to_char(msi.inventory_item_id)
AND qpa.product_uom_code = msi.primary_uom_code
AND sysdate BETWEEN NVL(spll.start_date_active, (sysdate-1)) AND
NVL(spll.end_date_active + 0.99999, (sysdate+1))
AND rownum = 1
;
SELECT concatenated_segments
INTO l_item_description
FROM mtl_system_items_kfv
WHERE organization_id = g_from_organization_id
AND inventory_item_id = g_inventory_item_id;
SELECT name
INTO l_pricelist_name
FROM QP_LIST_HEADERS
WHERE list_header_id = l_pricelist_id;
Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp:: Selecting Line Identifier...');
l_control_rec.temp_table_insert_flag := 'N';
print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: Calling QP:Bulk insert routine...' );
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_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,
x_status_code => l_return_status,
x_status_text => l_return_status_text );
print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: after QP:Bulk insert routine...' );
print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: QP_PREQ_GRP.INSERT_LINES2 error ' );
fnd_message.set_token('ENTITY1', 'QP_PREQ_GRP.INSERT_LINES2');
SELECT count(*)
INTO i
FROM qp_preq_lines_tmp;
print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: No. of records inserted in QP_PREQ_LINES_TMP=' || to_char(i));
SELECT lines.ADJUSTED_UNIT_PRICE
, lines.UNIT_PRICE
, lines.ADJUSTED_UNIT_PRICE
, lines.UNIT_PRICE
, lines.priced_quantity
, lines.priced_uom_code
, lines.price_list_header_id
, nvl(lines.percent_price, NULL)
, nvl(lines.parent_price, NULL)
, decode(lines.parent_price, NULL, 0, 0, 0, lines.adjusted_unit_price/lines.parent_price)
, lines.currency_code
, lines.pricing_status_code
, lines.pricing_status_text
INTO G_UNIT_SELLING_PRICE_TBL(i)
, G_UNIT_LIST_PRICE_TBL(i)
, G_UNIT_SELL_PRICE_PER_PQTY_TBL(i)
, G_UNIT_LIST_PRICE_PER_PQTY_TBL(i)
, G_PRICING_QUANTITY_TBL(i)
, G_PRICED_UOM_CODE_TBL(i)
, G_PRICE_LIST_ID_TBL(i)
, G_UNIT_LIST_PERCENT_TBL(i)
, G_UNIT_PERCENT_BASE_PRICE_TBL(i)
, G_UNIT_SELLING_PERCENT_TBL(i)
, G_CURRENCY_CODE_TBL(i)
, G_PRICING_STATUS_CODE_TBL(i)
, G_PRICING_STATUS_TEXT_TBL(i)
FROM qp_preq_lines_tmp lines
WHERE lines.line_id=G_LINE_ID_TBL(i);
IF G_PRICING_STATUS_CODE_TBL(i) = QP_PREQ_GRP.G_STATUS_UPDATED THEN
IF (l_debug = 1) THEN
print_debug('GMF_get_transfer_price_PUB.Populate_Results: Unit_Price=' || G_UNIT_SELLING_PRICE_TBL(i));