[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM ASO_QUOTE_STATUSES_B
WHERE Quote_Status_Id = l_status
AND Status_Code = 'ORDER SUBMITTED';
SELECT 'Y'
FROM ASO_QUOTE_LINES_ALL
WHERE Quote_Header_Id = l_qte_hdr;
SELECT Count (Resource_Id)
FROM ASO_QUOTE_ACCESSES A
WHERE A.Quote_Number = l_qte_num
AND A.Role_Id IS NOT NULL
AND EXISTS
( SELECT B.Resource_Id
/* FROM JTF_RS_SRP_VL B */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V B --New Code Yogeshwar (MOAC)
WHERE B.Resource_Id = A.Resource_Id
AND NVL(B.status,'A') = 'A'
AND nvl(trunc(B.start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(B.end_date_active), trunc(sysdate)) >= trunc(sysdate));
aso_debug_pub.add('Allocate_Sales_Credits: p_qte_header_rec.last_update_date: '||p_qte_header_rec.last_update_date,1,'N');
IF (l_qte_header_rec.last_update_date IS NULL OR
l_qte_header_rec.last_update_date = FND_API.G_MISS_DATE) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
IF (p_qte_header_rec.last_update_date IS NOT NULL AND
p_qte_header_rec.last_update_date <> FND_API.G_MISS_DATE) AND
(l_qte_header_rec.last_update_date <> p_qte_header_rec.last_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
P_Operation => 'UPDATE',
x_Qte_Header_Rec => lx_qte_header_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
aso_debug_pub.add('Allocate_Sales_Credits: Before Update Qte Hdr ',1,'N');
UPDATE ASO_QUOTE_HEADERS_ALL
SET Credit_Update_date = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
object_version_number = object_version_number+1
WHERE Quote_Header_Id = l_Qte_Header_Rec.Quote_Header_Id
RETURNING quote_header_id, last_update_date, credit_update_date, object_version_number
INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
x_qte_header_rec.credit_update_date, x_qte_header_rec.object_version_number;
aso_debug_pub.add('Allocate_Sales_Credits: After Update Qte Hdr ',1,'N');
aso_debug_pub.add('ASO_SALES_CREDIT_PVT.Allocate_Sales_Credits : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_qte_header_rec.Quote_Number, 1, 'Y');
ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_qte_header_rec.Quote_Number);
l_cred_upd_lines_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_SALES_CREDIT_UPDATE');
SELECT CN_SCA_BATCH_S.NextVal
FROM DUAL;
SELECT Account_Number
FROM HZ_CUST_ACCOUNTS
WHERE Cust_Account_Id = l_acct_id;
SELECT Party_Name
FROM HZ_PARTIES
WHERE Party_Id = l_party_id;
SELECT UPPER(B.city) City, UPPER(B.county) County, UPPER(B.state)State, UPPER(B.province) Province, B.postal_code, B.country
FROM HZ_PARTY_SITES A, HZ_LOCATIONS B
WHERE A.Location_Id = B.Location_Id
AND A.party_site_id = l_party_site;
SELECT Phone_Area_Code
FROM HZ_CONTACT_POINTS
WHERE Owner_Table_Id = l_party_id
AND Owner_Table_Name = 'HZ_PARTIES'
AND Contact_Point_Type = 'PHONE'
AND Status = 'A'
AND Primary_Flag = 'Y';
SELECT Sales_Credit_Type_Id
FROM OE_SALES_CREDIT_TYPES
WHERE Quota_Flag = 'Y';
SELECT Sales_Credit_Type_Id
FROM OE_SALES_CREDIT_TYPES
WHERE Quota_Flag = 'N';
SELECT SUM (NVL(A.Allocation_Percentage,0))
FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
WHERE A.SCA_Batch_Id = l_batch
AND A.Revenue_Type = 'REVENUE'
AND B.SCA_Batch_Id = l_batch
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND B.Source_Line_Id IS NULL;
SELECT SUM (NVL(A.Allocation_Percentage,0))
FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
WHERE A.SCA_Batch_Id = l_batch
AND A.Revenue_Type = 'REVENUE'
AND B.SCA_Batch_Id = l_batch
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND B.Source_Line_Id = l_line_id;
SELECT SUM (NVL(Allocation_Percentage,0))
FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
WHERE A.SCA_Batch_Id = l_batch
AND B.SCA_Batch_Id = l_batch
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND B.Source_Line_Id IS NULL;
SELECT SUM (NVL(Allocation_Percentage,0))
FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
WHERE A.SCA_Batch_Id = l_batch
AND B.SCA_Batch_Id = l_batch
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND B.Source_Line_Id IS NOT NULL;
SELECT Resource_Id
FROM ASO_SALES_CREDITS
WHERE Resource_Id = l_res
AND Quote_Header_Id = l_qte_Hdr
AND Sales_Credit_Type_Id = l_quota
AND Quote_Line_Id IS NULL;
SELECT Resource_Id
FROM ASO_SALES_CREDITS
WHERE Resource_Id = l_res
AND Quote_Header_Id = l_qte_Hdr
AND Sales_Credit_Type_Id = l_quota
AND Quote_Line_Id = l_qte_line;
SELECT Quote_Line_Id
FROM ASO_QUOTE_LINES_ALL
WHERE Quote_Header_Id = l_qte_hdr;
SELECT Credit_Rule_Id, Process_Status
FROM CN_SCA_HEADERS_INTERFACE_GTT;
DELETE FROM CN_SCA_LINES_INTERFACE_GTT;
DELETE FROM CN_SCA_HEADERS_INTERFACE_GTT;
DELETE FROM CN_SCA_LINES_OUTPUT_GTT;
aso_debug_pub.add('Get_Credits: Before insert header info ',1,'N');
INSERT INTO CN_SCA_HEADERS_INTERFACE_GTT (
SCA_HEADERS_INTERFACE_ID,
SCA_BATCH_ID,
TRANSACTION_SOURCE,
SOURCE_TYPE,
SOURCE_ID,
SOURCE_LINE_ID,
PROCESSED_DATE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24 )
VALUES ( CN_SCA_HEADERS_INTERFACE_GTT_S.NextVal,
l_batch_id,
'QOT',
NULL,
p_qte_header_rec.Quote_Header_Id,
NULL,
SYSDATE,
(NVL(p_qte_header_rec.Total_Quote_Price,0) - (NVL(p_qte_header_rec.Total_Tax,0) + NVL(p_qte_header_rec.Total_Shipping_Charge,0))),
p_qte_header_rec.Total_List_Price,
p_qte_header_rec.Total_Quote_Price,
p_qte_header_rec.Total_Adjusted_Amount,
p_qte_header_rec.Total_Adjusted_Percent,
C_Acct_Rec.Account_Number,
C_Party_Rec.Party_Name,
C_Cust_Cont_Rec.Phone_Area_Code,
C_Party_Site_Rec.City,
C_Party_Site_Rec.Country,
C_Party_Site_Rec.State,
C_Party_Site_Rec.Province,
C_Party_Site_Rec.County,
C_Party_Site_Rec.Postal_Code,
p_qte_header_rec.Marketing_Source_Code_Id,
p_qte_header_rec.Sales_Channel_Code );
aso_debug_pub.add('Get_Credits: Before insert line info ',1,'N');
INSERT INTO CN_SCA_HEADERS_INTERFACE_GTT (
SCA_HEADERS_INTERFACE_ID,
SCA_BATCH_ID,
TRANSACTION_SOURCE,
SOURCE_TYPE,
SOURCE_ID,
SOURCE_LINE_ID,
PROCESSED_DATE,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24 )
SELECT CN_SCA_HEADERS_INTERFACE_GTT_S.NextVal,
l_batch_id,
'QOT',
NULL,
p_qte_header_rec.Quote_Header_Id,
A.Quote_Line_Id,
SYSDATE,
(A.Line_Quote_Price * A.Quantity),
A.Line_List_Price,
A.Line_Quote_Price,
A.Line_Adjusted_Amount,
A.Line_Adjusted_Percent,
A.Quantity,
A.UOM_Code,
A.Inventory_Item_Id,
C_Acct_Rec.Account_Number,
C_Party_Rec.Party_Name,
C_Cust_Cont_Rec.Phone_Area_Code,
C_Party_Site_Rec.City,
C_Party_Site_Rec.Country,
C_Party_Site_Rec.State,
C_Party_Site_Rec.Province,
C_Party_Site_Rec.County,
C_Party_Site_Rec.Postal_Code,
p_qte_header_rec.Marketing_Source_Code_Id,
p_qte_header_rec.Sales_Channel_Code
FROM ASO_QUOTE_LINES_ALL A
WHERE Quote_Header_Id = p_qte_header_rec.Quote_Header_Id;
aso_debug_pub.add('Get_Credits: Before insert line interface tbl ',1,'N');
INSERT INTO CN_SCA_LINES_INTERFACE_GTT (
SCA_LINES_INTERFACE_ID,
SCA_HEADERS_INTERFACE_ID,
SCA_BATCH_ID,
RESOURCE_ID,
ROLE_ID,
SOURCE_TRX_ID )
SELECT CN_SCA_LINES_INTERFACE_GTT_S.NextVal,
B.SCA_Headers_Interface_Id,
l_batch_id,
A.Resource_Id,
A.Role_Id,
p_qte_header_rec.quote_header_id
FROM ASO_QUOTE_ACCESSES A, CN_SCA_HEADERS_INTERFACE_GTT B
WHERE A.Quote_Number = p_qte_header_rec.Quote_Number
AND A.Role_Id IS NOT NULL
AND EXISTS
( SELECT C.Resource_Id
/* FROM JTF_RS_SRP_VL C */ --Commented Code Yogeshwar (MOAC)
FROM JTF_RS_SALESREPS_MO_V C --New Code yogeshwar (MOAC)
WHERE C.Resource_Id = A.Resource_Id
AND NVL(status,'A') = 'A'
AND nvl(trunc(C.start_date_active), trunc(sysdate)) <= trunc(sysdate)
AND nvl(trunc(C.end_date_active), trunc(sysdate)) >= trunc(sysdate));
select count(*) into l_dumb from CN_SCA_HEADERS_INTERFACE_GTT;
select count(*) into l_dumb from CN_SCA_LINES_INTERFACE_GTT;
select count(*) into l_dumb from CN_SCA_LINES_OUTPUT_GTT;
ASO_SALES_CREDITS_PKG.Delete_Header_Row ( P_Quote_Header_Id => p_Qte_Header_Rec.Quote_Header_Id );
aso_debug_pub.add('Get_Credits: After Delete_Row ',1,'N');
INSERT INTO ASO_SALES_CREDITS (
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SALES_CREDIT_ID,
QUOTE_HEADER_ID,
QUOTE_LINE_ID,
PERCENT,
RESOURCE_ID,
RESOURCE_GROUP_ID,
SALES_CREDIT_TYPE_ID,
SYSTEM_ASSIGNED_FLAG,
CREDIT_RULE_ID )
SELECT SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
ASO_SALES_CREDITS_S.nextval,
B.Source_Id,
B.Source_Line_Id,
A.Allocation_Percentage,
A.Resource_Id,
C.Resource_Grp_Id,
Decode(A.Revenue_Type, 'REVENUE', l_quota_id,l_non_quota_id),
'Y',
B.Credit_Rule_Id
FROM CN_SCA_LINES_OUTPUT_GTT A,
CN_SCA_HEADERS_INTERFACE_GTT B,
ASO_QUOTE_ACCESSES C
WHERE A.SCA_Batch_Id = l_batch_id
AND B.SCA_Batch_Id = l_batch_id
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND A.Resource_Id = C.Resource_Id
AND C.Quote_Number = p_qte_header_rec.Quote_Number
AND B.Source_Line_Id IS NULL;
aso_debug_pub.add('Get_Credits: After Insert_Row to ASO_SALES_CREDITS ',1,'N');
UPDATE ASO_SALES_CREDITS
SET Percent = Percent + l_credit_diff
WHERE Resource_Id = l_PSRep
AND Sales_Credit_Type_Id = l_quota_id
AND Quote_Header_Id = p_Qte_Header_Rec.Quote_Header_Id;
aso_debug_pub.add('Get_Credits: After Update to ASO_SALES_CREDITS PSRep ',1,'N');
INSERT INTO ASO_SALES_CREDITS (
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SALES_CREDIT_ID,
QUOTE_HEADER_ID,
QUOTE_LINE_ID,
PERCENT,
RESOURCE_ID,
RESOURCE_GROUP_ID,
SALES_CREDIT_TYPE_ID,
SYSTEM_ASSIGNED_FLAG,
CREDIT_RULE_ID )
SELECT SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
ASO_SALES_CREDITS_S.nextval,
p_Qte_Header_Rec.Quote_Header_Id,
NULL,
l_credit_diff,
A.Resource_Id,
A.Resource_Grp_Id,
l_quota_id,
'Y',
NULL
FROM ASO_QUOTE_ACCESSES A
WHERE A.Resource_Id = p_Qte_Header_Rec.Resource_Id
AND A.Quote_Number = p_qte_header_rec.Quote_Number;
aso_debug_pub.add('Get_Credits: After Insert to ASO_SALES_CREDITS PSRep ',1,'N');
ASO_SALES_CREDITS_PKG.Delete_Row ( P_Quote_Line_Id => C_Get_Line_Rec.Quote_Line_Id );
aso_debug_pub.add('Get_Credits: After Delete_Row ',1,'N');
INSERT INTO ASO_SALES_CREDITS (
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SALES_CREDIT_ID,
QUOTE_HEADER_ID,
QUOTE_LINE_ID,
PERCENT,
RESOURCE_ID,
RESOURCE_GROUP_ID,
SALES_CREDIT_TYPE_ID,
SYSTEM_ASSIGNED_FLAG,
CREDIT_RULE_ID )
SELECT SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
ASO_SALES_CREDITS_S.nextval,
B.Source_Id,
B.Source_Line_Id,
A.Allocation_Percentage,
A.Resource_Id,
C.Resource_Grp_Id,
Decode(A.Revenue_Type, 'REVENUE', l_quota_id,l_non_quota_id),
'Y',
B.Credit_Rule_Id
FROM CN_SCA_LINES_OUTPUT_GTT A,
CN_SCA_HEADERS_INTERFACE_GTT B,
ASO_QUOTE_ACCESSES C
WHERE A.SCA_Batch_Id = l_batch_id
AND B.SCA_Batch_Id = l_batch_id
AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
AND A.Resource_Id = C.Resource_Id
AND C.Quote_Number = p_qte_header_rec.Quote_Number
AND B.Source_Line_Id = C_Get_Line_Rec.Quote_Line_Id;
aso_debug_pub.add('Get_Credits: After Insert_Row to ASO_SALES_CREDITS ',1,'N');
UPDATE ASO_SALES_CREDITS
SET Percent = Percent + l_credit_diff
WHERE Resource_Id = l_PSRep
AND Sales_Credit_Type_Id = l_quota_id
AND Quote_Header_Id = p_Qte_Header_Rec.Quote_Header_Id
AND Quote_Line_Id = C_Get_Line_Rec.Quote_Line_Id;
INSERT INTO ASO_SALES_CREDITS (
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SALES_CREDIT_ID,
QUOTE_HEADER_ID,
QUOTE_LINE_ID,
PERCENT,
RESOURCE_ID,
RESOURCE_GROUP_ID,
SALES_CREDIT_TYPE_ID,
SYSTEM_ASSIGNED_FLAG,
CREDIT_RULE_ID )
SELECT SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
ASO_SALES_CREDITS_S.nextval,
p_Qte_Header_Rec.Quote_Header_Id,
C_Get_Line_Rec.Quote_Line_Id,
l_cred_line_diff,
A.Resource_Id,
A.Resource_Grp_Id,
l_quota_id,
'Y',
NULL
FROM ASO_QUOTE_ACCESSES A
WHERE A.Resource_Id = p_Qte_Header_Rec.Resource_Id
AND A.Quote_Number = p_qte_header_rec.Quote_Number;