[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_revenue_and_billing(
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 DEFAULT 'N',
p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_project_id IN NUMBER ,
p_distribution_rule IN VARCHAR2 ,
p_billing_cycle_id IN NUMBER ,
p_first_bill_offset IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_next_billing_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
p_output_tax_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_billing_job_group_id IN NUMBER ,
p_invoice_comment IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_labor_id IN NUMBER ,
p_non_labor_id IN NUMBER ,
p_retention_inv_format_id IN VARCHAR2 ,
p_retention_percent IN NUMBER ,
p_retention_output_tax_code IN VARCHAR2 ,
p_record_version_number IN NUMBER DEFAULT 1,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('Update_reve_and_billing PVT: Checking Lock on record');
/* Lock pa_projects table and update */
IF p_validate_only <> FND_API.G_TRUE
THEN
BEGIN
SELECT 'x' INTO l_dummy_char
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy_char
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
UPDATE pa_projects
SET DISTRIBUTION_RULE = p_distribution_rule,
BILLING_CYCLE_ID = p_billing_cycle_id,
BILLING_OFFSET = l_first_bill_offset,
OUTPUT_TAX_CODE = l_output_tax_code,
BILL_JOB_GROUP_ID = p_billing_job_group_id ,
INVOICE_COMMENT = l_invoice_comment,
LABOR_INVOICE_FORMAT_ID = p_labor_id,
NON_LABOR_INVOICE_FORMAT_ID = p_non_labor_id,
RETENTION_INVOICE_FORMAT_ID = p_retention_inv_format_id,
RETENTION_PERCENTAGE = l_retention_percent,
RETENTION_TAX_CODE = l_retention_output_tax_code,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
/* If job group bill id is changed then update
the JOB_BILL_RATE_SCHEDULE_ID to null */
JOB_BILL_RATE_SCHEDULE_ID = DECODE( p_billing_job_group_id, BILL_JOB_GROUP_ID, JOB_BILL_RATE_SCHEDULE_ID ),
RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
p_procedure_name => 'update_rev_and_billing',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'update_rev_and_billing',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_revenue_and_billing;
SELECT pa_credit_receivers_s.NEXTVAL INTO l_credit_receiver_id
FROM dual;
INSERT INTO pa_credit_receivers(
credit_receiver_id,
PERSON_ID,
CREDIT_TYPE_CODE ,
PROJECT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY,
LAST_UPDATE_LOGIN ,
START_DATE_ACTIVE ,
CREDIT_PERCENTAGE ,
TASK_ID ,
END_DATE_ACTIVE ,
TRANSFER_TO_AR_FLAG ,
SALESREP_ID ,
BUDGET_TYPE_CODE ,
RECORD_VERSION_NUMBER
)
VALUES (
l_credit_receiver_id,
p_person_id,
p_credit_type,
p_project_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_effective_from_date,
p_credit_percentage,
l_task_id,
p_effective_to_date,
p_transfer_to_AR,
null,
null,
1
);
PROCEDURE update_credit_receivers(
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 DEFAULT 'N',
p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_credit_receiver_id IN NUMBER ,
p_project_id IN NUMBER ,
p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_credit_type IN VARCHAR2 ,
p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_credit_percentage IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_transfer_to_AR IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_effective_from_date IN DATE ,
p_effective_to_date IN DATE DEFAULT FND_API.G_MISS_DATE,
p_record_version_number IN NUMBER DEFAULT 1,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('Update_Credit_Receivers PVT: Checking Lock on record');
SELECT 'x' INTO l_dummy_char
FROM pa_credit_receivers
WHERE credit_receiver_id = p_credit_receiver_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy_char
FROM pa_credit_receivers
WHERE credit_receiver_id = p_credit_receiver_id
AND record_version_number = p_record_version_number;
UPDATE pa_credit_receivers
SET CREDIT_PERCENTAGE = l_credit_percentage,
start_date_active = p_effective_from_date,
TRANSFER_TO_AR_FLAG = p_TRANSFER_TO_AR,
end_date_active = l_effective_to_date,
credit_type_code = p_credit_type,
person_id = p_person_id,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
WHERE credit_receiver_id = p_credit_receiver_id
AND RECORD_VERSION_NUMBER = p_RECORD_VERSION_NUMBER;
p_procedure_name => 'UPDATE_CREDIT_RECEIVERS',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'UPDATE_CREDIT_RECEIVERS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_credit_receivers;
PROCEDURE delete_credit_receivers(
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 DEFAULT 'N',
p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_credit_receiver_id IN NUMBER ,
p_project_id IN NUMBER ,
p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_credit_type IN VARCHAR2 ,
p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_effective_from_date IN DATE ,
p_record_version_number IN NUMBER DEFAULT 1,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('Delete_Credit_Receivers PVT: Checking Lock on record');
SELECT 'x' INTO l_dummy_char
FROM pa_credit_receivers
WHERE credit_receiver_id = p_credit_receiver_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy_char
FROM pa_credit_receivers
WHERE credit_receiver_id = p_credit_receiver_id
AND record_version_number = p_record_version_number;
DELETE FROM pa_credit_receivers
WHERE credit_receiver_id = p_credit_receiver_id
AND RECORD_VERSION_NUMBER = p_RECORD_VERSION_NUMBER;
p_procedure_name => 'delete_CREDIT_RECEIVERS',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'delete_CREDIT_RECEIVERS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_credit_receivers;
SELECT org_id, distribution_rule, project_type
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT PA_BILLING_ASSIGNMENTS_S.NEXTVAL INTO l_billing_assignment_id
FROM dual;
INSERT INTO pa_billing_assignments_all(
BILLING_ASSIGNMENT_ID ,
BILLING_EXTENSION_ID ,
PROJECT_TYPE ,
PROJECT_ID ,
TOP_TASK_ID ,
AMOUNT ,
PERCENTAGE ,
ACTIVE_FLAG ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
DISTRIBUTION_RULE ,
ORG_ID ,
RECORD_VERSION_NUMBER )
VALUES ( l_billing_assignment_id,
p_billing_extension_id ,
l_rec_proj_all.project_type ,
p_project_id ,
l_task_id ,
l_amount ,
l_percent ,
p_active ,
sysdate ,
FND_GLOBAL.USER_ID ,
sysdate ,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.LOGIN_ID ,
null ,
l_rec_proj_all.distribution_rule ,
l_rec_proj_all.org_id ,
1
);
PROCEDURE update_billing_assignments(
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 DEFAULT 'N',
p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_project_id IN NUMBER ,
p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_billing_extension_id IN NUMBER ,
p_billing_assignment_id IN NUMBER ,
p_amount IN NUMBER ,
p_percent IN NUMBER ,
p_active IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_record_version_number IN NUMBER DEFAULT 1,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('Update Billing Assignments PVT: Checking Lock on record');
SELECT 'x' INTO l_dummy_char
FROM pa_billing_assignments_all
WHERE billing_assignment_id = p_billing_assignment_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy_char
FROM pa_billing_assignments_all
WHERE billing_assignment_id = p_billing_assignment_id
AND record_version_number = p_record_version_number;
UPDATE pa_billing_assignments_all
SET
billing_extension_id = p_billing_extension_id,
AMOUNT = l_amount ,
PERCENTAGE = l_percent ,
ACTIVE_FLAG = p_active ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
WHERE billing_assignment_id = p_billing_assignment_id
AND RECORD_VERSION_NUMBER = p_record_version_number;
p_procedure_name => 'UPDATE_BILLING_ASSIGNMENTS',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'UPDATE_BILLING_ASSIGNMENTS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_billing_assignments;
PROCEDURE delete_billing_assignments(
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
p_debug_mode IN VARCHAR2 DEFAULT 'N',
p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_project_id IN NUMBER ,
p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_billing_extension_id IN NUMBER ,
p_billing_assignment_id IN NUMBER ,
p_record_version_number IN NUMBER DEFAULT 1,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
pa_debug.debug('Delete Billing Assignments PVT: Checking Lock on record');
SELECT 'x' INTO l_dummy_char
FROM pa_billing_assignments_all
WHERE billing_extension_id = p_billing_extension_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy_char
FROM pa_billing_assignments_all
WHERE billing_extension_id = p_billing_extension_id
AND record_version_number = p_record_version_number;
DELETE FROM pa_billing_assignments_all
WHERE billing_extension_id = p_billing_extension_id
AND RECORD_VERSION_NUMBER = p_record_version_number;
p_procedure_name => 'DELETE_BILLING_ASSIGNMENTS',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'DELETE_BILLING_ASSIGNMENTS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_billing_assignments;