[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Select_CustomerPrice(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_cust_hdr_rec IN dpp_cust_hdr_rec_type
,p_customer_tbl IN OUT NOCOPY dpp_customer_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Select_CustomerPrice';
l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_CUSTOMERCLAIMS_PVT.SELECT_CUSTOMERPRICE';
SELECT oola.sold_to_org_id customer_id
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_accounts hca
WHERE ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.org_id = p_org_id
AND oola.inventory_item_id = p_inventory_item_id
AND (actual_shipment_date >= p_start_date AND actual_shipment_date < p_end_date)
--BETWEEN p_start_date AND p_end_date
AND hca.cust_account_id = oola.sold_to_org_id
AND hca.status = 'A'
GROUP BY oola.sold_to_org_id;
SELECT
rct.sold_to_customer_id cust_account_id,
unit_selling_price last_price,
rct.invoice_currency_code
FROM
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
ra_cust_trx_types_all rctt
WHERE
line_type = 'LINE' AND
inventory_item_id = p_inventory_item_id AND
uom_code = p_uom_code AND
rct.customer_trx_id = rctl.customer_trx_id AND
rct.org_id = p_org_id AND
rctt.cust_trx_type_id = rct.cust_trx_type_id AND
rct.org_id = rctt.org_id AND
rctt.name = 'Invoice' AND
rct.org_id = rctl.org_id AND
rct.sold_to_customer_id = p_customer_id AND
rct.complete_flag = 'Y' AND
rctl.customer_trx_line_id = (
SELECT
MAX(rctl1.customer_trx_line_id)
FROM
ra_customer_trx_lines_all rctl1,
ra_customer_trx_all rct1,
ra_cust_trx_types_all rctt1
WHERE
line_type = 'LINE' AND
inventory_item_id = p_inventory_item_id AND
uom_code = p_uom_code AND
rct1.customer_trx_id = rctl1.customer_trx_id AND
rct1.org_id = p_org_id AND
rctt1.cust_trx_type_id = rct1.cust_trx_type_id AND
rct1.org_id = rctt1.org_id AND
rctt1.name = 'Invoice' AND
rct1.org_id = rctl1.org_id AND
rct1.sold_to_customer_id = p_customer_id AND
rct1.complete_flag = 'Y');
SAVEPOINT Select_CustomerPrice_PVT;
l_customer_price_tbl.delete();
ROLLBACK TO Select_CustomerPrice_PVT;
ROLLBACK TO Select_CustomerPrice_PVT;
ROLLBACK TO Select_CustomerPrice_PVT;
fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
END Select_CustomerPrice;
SELECT SUPPLIER_NEW_PRICE, PRICE_CHANGE
INTO l_supp_new_price, l_price_change
FROM DPP_TRANSACTION_LINES_ALL
WHERE transaction_line_id = l_customer_tbl(i).transaction_line_id;
SELECT DPP_CUST_INV_LINE_ID_SEQ.nextval
INTO l_cust_inv_line_id
FROM DUAL;
l_cust_inv_tbl.delete();
INSERT INTO DPP_CUSTOMER_CLAIMS_ALL(TRANSACTION_HEADER_ID,
CUSTOMER_INV_LINE_ID,
LINE_NUMBER,
LAST_PRICE,
SUPPLIER_NEW_PRICE,
CUSTOMER_NEW_PRICE,
TRX_CURRENCY,
REPORTED_INVENTORY,
CALCULATED_INVENTORY,
UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
CUST_ACCOUNT_ID,
ORG_ID,
OBJECT_VERSION_NUMBER,
SUPPLIER_PRICE_DROP,
CUST_CLAIM_AMT,
SUPP_CLAIM_AMT,
CUSTOMER_CLAIM_CREATED,
SUPPLIER_CLAIM_CREATED)
VALUES(l_cust_hdr_rec.transaction_header_id,
l_cust_inv_line_id,
l_line_number,
l_last_price,
l_rnd_supp_new_price,
l_rnd_cust_new_price,
nvl(l_customer_tbl(i).customer_price_tbl(j).invoice_currency_code,l_cust_hdr_rec.currency_code),
l_reported_inventory,
NVL(l_cust_inv_tbl(1).onhand_quantity,0),
NVL(l_cust_inv_tbl(1).uom_code, l_customer_tbl(i).uom_code),
l_sysdate,
l_cust_hdr_rec.Last_Updated_By,
l_sysdate,
l_cust_hdr_rec.Last_Updated_By,
FND_GLOBAL.login_ID,
l_customer_tbl(i).inventory_item_id,
l_customer_tbl(i).customer_price_tbl(j).cust_account_id,
l_cust_hdr_rec.org_id,
1,
l_rnd_price_change,
l_cust_claim_amt,
l_supp_claim_amt,
'N',
'N');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Insertion Done in table DPP_CUSTOMER_CLAIMS_ALL');
l_claim_lines_tbl(i).log_mode := 'I'; -- Insert
l_claim_lines_tbl(i).created_by := l_cust_hdr_rec.Last_Updated_By;
l_claim_lines_tbl(i).last_update_date := l_sysdate;
l_claim_lines_tbl(i).last_updated_by := l_cust_hdr_rec.Last_Updated_By;
l_claim_lines_tbl(i).last_update_login := FND_GLOBAL.login_ID;
DPP_LOG_PVT.Insert_ClaimsLog(p_api_version => l_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_lines_tbl => l_claim_lines_tbl
);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Insertion Done in table DPP_CUSTOMER_CLAIMS_LOG');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Return Status from DPP_LOG_PVT.Insert_ClaimsLog: '|| l_return_status);
UPDATE DPP_EXECUTION_DETAILS
SET execution_end_date = sysdate
,execution_status = DECODE(l_return_status,'S','SUCCESS','WARNING')
,last_update_date = sysdate
,last_updated_by = l_cust_hdr_rec.Last_Updated_By
,last_update_login = l_cust_hdr_rec.Last_Updated_By
,provider_process_id = l_cust_hdr_rec.Provider_Process_Id
,provider_process_instance_id = l_cust_hdr_rec.Provider_Process_Instance_id
,output_xml = XMLType(l_cust_hdr_rec.Output_XML)
,object_version_number = nvl(object_version_number,0) + 1
WHERE execution_detail_id = l_cust_hdr_rec.Execution_Detail_ID;
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name|| ': '||SQL%ROWCOUNT ||' row(s) updated in DPP_EXECUTION_DETAILS.');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in inserting into DPP_CUSTOMER_CLAIMS_ALL: '||SQLERRM);