[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CONT.Contract_Id,
CONT.Contract_Number,
STAT.Name Status,
CONT.Start_Date_Active START_DATE_ACTIVE,
CONT.End_Date_Active END_DATE_ACTIVE,
CONT.Duration DURATION,
CONT.Currency_Code CURRENCY_CODE,
CONT.Bill_On BILL_ON,
CONT.First_Bill_Date FIRST_BILL_DATE,
CONT.Next_Bill_Date NEXT_BILL_DATE,
CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
CONT.Renewal_Rule RENEWAL_RULE,
CONT.Termination_Rule TERMINATION_RULE,
CONT.Contract_Amount CONTRACT_AMOUNT,
SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
MTL.Concatenated_Segments SERVICE,
SERV.Start_Date_Active SERVICE_START_DATE,
SERV.End_Date_Active SERVICE_END_DATE,
COV2.Coverage_Id COVERAGE_ID,
COV2.Name COVERAGE
FROM CS_Contracts CONT,
Cs_Coverages COV,
Cs_Coverages COV2,
Cs_Coverage_Txn_Groups CTG,
CS_Contract_Statuses STAT,
Cs_Covered_Products COVPROD,
Cs_Contract_Cov_Levels CL,
Mtl_System_Items_Kfv MTL,
CS_Contract_Statuses STAT2,
Cs_Cp_Services SERV
WHERE COVPROD.Customer_Product_Id = P_Customer_Product_Id
AND COVPROD.Coverage_Level_Id = CL.Coverage_Level_Id
AND CL.Cp_Service_Id = SERV.Cp_Service_Id
AND SERV.Contract_Id = CONT.Contract_Id
AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
AND CTG.Business_Process_Id = P_Business_Process_Id
AND (( COV.Coverage_Id = COV2.Coverage_Id
AND COV.Coverage_Id = CTG.Coverage_Id
AND P_Exception_Coverage_Flag = 'N')
OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
AND COV.Exception_Coverage_Id = CTG.Coverage_Id
AND P_Exception_Coverage_Flag = 'Y'))
AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
AND STAT2.Eligible_For_Entitlements = 'Y'
AND trunc(to_date(P_Charge_Date_Time))
BETWEEN SERV.Start_Date_Active AND
SERV.End_Date_Active
AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
AND MTL.Organization_Id =
FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
AND CONT.Contract_Status_Id = STAT.Contract_Status_Id
AND STAT.Eligible_for_Entitlements = 'Y';
SELECT CONT.Contract_Id,
CONT.Contract_Number,
STAT.Name Status,
CONT.Start_Date_Active START_DATE_ACTIVE,
CONT.End_Date_Active END_DATE_ACTIVE,
CONT.Duration DURATION,
CONT.Currency_Code CURRENCY_CODE,
CONT.Bill_On BILL_ON,
CONT.First_Bill_Date FIRST_BILL_DATE,
CONT.Next_Bill_Date NEXT_BILL_DATE,
CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
CONT.Renewal_Rule RENEWAL_RULE,
CONT.Termination_Rule TERMINATION_RULE,
CONT.Contract_Amount CONTRACT_AMOUNT,
SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
MTL.Concatenated_Segments SERVICE,
SERV.Start_Date_Active SERVICE_START_DATE,
SERV.End_Date_Active SERVICE_END_DATE,
COV2.Coverage_Id COVERAGE_ID,
COV2.Name COVERAGE
FROM CS_Contracts CONT,
Cs_Coverages COV,
Cs_Coverages COV2,
Cs_Coverage_Txn_Groups CTG,
CS_Contract_Statuses STAT,
Cs_Contract_Cov_Levels CL,
Mtl_System_Items_Kfv MTL,
CS_Contract_Statuses STAT2,
Cs_Cp_Services SERV
WHERE CL.Coverage_Level_Value = P_Coverage_level_value
AND CL.Cp_Service_Id = SERV.Cp_Service_Id
AND SERV.Contract_Id = CONT.Contract_Id
AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
AND CTG.Business_Process_Id = P_Business_Process_ID
AND (( COV.Coverage_Id = COV2.Coverage_Id
AND COV.Coverage_Id = CTG.Coverage_Id
AND P_Exception_Coverage_Flag = 'N')
OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
AND P_Exception_Coverage_Flag = 'Y'
AND COV.Exception_Coverage_Id = CTG.Coverage_Id))
AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
AND STAT2.Eligible_For_Entitlements = 'Y'
AND trunc(p_charge_date_time)
BETWEEN SERV.Start_Date_Active AND
SERV.End_Date_Active
AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
AND MTL.Organization_Id =
FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
AND CONT.COntract_Status_Id = STAT.Contract_Status_Id
AND STAT.Eligible_for_Entitlements = 'Y';
SELECT CONT.Contract_Id,
CONT.Contract_Number,
STAT.Name Status,
CONT.Start_Date_Active START_DATE_ACTIVE,
CONT.End_Date_Active END_DATE_ACTIVE,
CONT.Duration DURATION,
CONT.Currency_Code CURRENCY_CODE,
CONT.Bill_On BILL_ON,
CONT.First_Bill_Date FIRST_BILL_DATE,
CONT.Next_Bill_Date NEXT_BILL_DATE,
CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
CONT.Renewal_Rule RENEWAL_RULE,
CONT.Termination_Rule TERMINATION_RULE,
CONT.Contract_Amount CONTRACT_AMOUNT,
SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
MTL.Concatenated_Segments SERVICE,
SERV.Start_Date_Active SERVICE_START_DATE,
SERV.End_Date_Active SERVICE_END_DATE,
COV2.Coverage_Id COVERAGE_ID,
COV2.Name COVERAGE
FROM CS_Contracts CONT,
CS_Contract_Statuses STAT,
Cs_Coverages COV,
Cs_Coverages COV2,
Cs_Coverage_Txn_Groups CTG,
Cs_Contract_Cov_Levels CL,
Mtl_System_Items_Kfv MTL,
CS_Contract_Statuses STAT2,
Cs_Cp_Services SERV
WHERE CL.Coverage_Level_Value IN (P_System_Id, P_Site_Use_Id)
AND CL.Cp_Service_Id = SERV.Cp_Service_Id
AND SERV.Contract_Id = CONT.Contract_Id
AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
AND CTG.Business_Process_Id = P_Business_Process_Id
AND (( COV.Coverage_Id = COV2.Coverage_Id
AND COV.Coverage_Id = CTG.Coverage_Id
AND P_Exception_Coverage_Flag = 'N')
OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
AND COV.Exception_Coverage_Id = CTG.Coverage_Id
AND P_Exception_Coverage_Flag = 'Y'))
AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
AND STAT2.Eligible_For_Entitlements = 'Y'
AND trunc(p_charge_date_time)
BETWEEN SERV.Start_Date_Active AND
SERV.End_Date_Active
AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
AND MTL.Organization_Id =
FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
AND CONT.Contract_Status_Id = STAT.Contract_Status_Id
AND STAT.Eligible_for_Entitlements = 'Y';