[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(bill_through_date)
INTO last_bill_thru_date
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND retention_invoice_flag = 'Y';
SELECT MAX(di.bill_through_date)
INTO last_bill_thru_date
FROM pa_draft_invoices di
WHERE di.project_id = p_project_id
AND di.retention_invoice_flag = 'Y'
AND EXISTS(
SELECT null FROM pa_draft_invoice_items dii
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND dii.task_id = p_task_id);
SELECT MIN(di.bill_through_date)
INTO last_bill_thru_date
FROM pa_draft_invoices di
WHERE EXISTS (
SELECT null FROM pa_draft_invoice_items dii
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND di.project_id = p_project_id
AND dii.invoice_line_type = 'RETENTION');
SELECT MIN(di.bill_through_date)
INTO last_bill_thru_date
FROM pa_draft_invoices di
WHERE EXISTS (
SELECT null FROM pa_draft_invoice_items dii
WHERE dii.draft_invoice_num = di.draft_invoice_num
AND dii.project_id = di.project_id
AND dii.task_id = p_task_id
AND dii.invoice_line_type = 'RETENTION');
/* This cursor selects retention_level_code (source project) of customers existing in both the
source and destination project */
cursor pc_cur (l_fr_project_id number, l_to_project_id number) IS
select pc.customer_id, pc.retention_level_code
from pa_project_customers pc
where pc.project_id = l_fr_project_id
and pc.customer_id in (select customer_id from pa_project_customers
where project_id = l_to_project_id);
/* This cursor selects those customer records of destination project which do not have
the same customers in the source project
These records will not have their retention_level_code updated by the previous cursor */
cursor no_cust_cur (l_fr_project_id number, l_to_project_id number) IS
select pc.customer_id, pc.retention_level_code
from pa_project_customers pc
where pc.project_id = l_to_project_id
and pc.customer_id not in (select customer_id from pa_project_customers
where project_id = l_fr_project_id);
l_not_update NUMBER;
update pa_project_customers
set retention_level_code = (select retention_level_code
from pa_project_customers
where project_id = p_fr_project_id
and customer_id = p_fr_customer_id)
where project_id = p_to_project_id
and customer_id = p_to_customer_id;
/* If the setup already exists for the customer it has to be deleted
The validation (retained_amount / billed amount is zero is done at UI*/
delete_retn_rules_customer (
p_project_id => p_to_project_id ,
p_customer_id => p_to_customer_id ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
/* Insert into retention rules table */
insert_retention_rules (
p_fr_project_id => p_fr_project_id ,
p_fr_customer_id => p_fr_customer_id ,
p_to_project_id => p_to_project_id ,
p_to_customer_id => p_to_customer_id ,
p_fr_date => p_fr_date ,
p_to_date => p_to_date ,
p_delta => l_delta ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
/* If the setup already exists for the project it has to be deleted */
delete_retention_rules (
p_project_id => p_to_project_id ,
p_task_id => NULL ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
update pa_project_customers
set retention_level_code = pc_rec.retention_level_code
where project_id = p_to_project_id
and customer_id = pc_rec.customer_id;
insert_retention_rules (
p_fr_project_id => p_fr_project_id ,
p_fr_customer_id => pc_rec.customer_id ,
p_to_project_id => p_to_project_id ,
p_to_customer_id => pc_rec.customer_id ,
p_fr_date => p_fr_date ,
p_to_date => p_to_date ,
p_delta => l_delta ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
SELECT count(*) into l_not_update
from pa_project_customers pc
where pc.project_id = p_to_project_id
and pc.customer_id not in (select customer_id from pa_project_customers
where project_id = p_fr_project_id);
if l_not_update <> 0 then
--dbms_output.put_line ('same customer not in source getting primary cust');
select pc.retention_level_code
into l_retention_level_code
from pa_project_customers pc
where pc.project_id = p_fr_project_id
and pc.customer_id = l_primary_cust_id;
update pa_project_customers
set retention_level_code = l_retention_level_code
where project_id = p_to_project_id
and customer_id = pc_no_rec.customer_id;
insert_retention_rules (
p_fr_project_id => p_fr_project_id ,
p_fr_customer_id => l_primary_cust_id ,
p_to_project_id => p_to_project_id ,
p_to_customer_id => pc_no_rec.customer_id ,
p_fr_date => p_fr_date ,
p_to_date => p_to_date ,
p_delta => l_delta ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
| Procedure : delete_retn_rules_customer |
| Purpose : To delete from retention rules table for a project and customer |
| This will be called from OA |
| |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Destination project id |
| p_customer_id IN Destination customer id |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE delete_retn_rules_customer (
p_project_id IN NUMBER,
p_customer_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(30) := NULL;
DELETE FROM pa_proj_retn_rules
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
DELETE FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
p_procedure_name => 'delete_retn_rules_customer');
END delete_retn_rules_customer;
| Procedure : delete_retention_rules |
| Purpose : To delete from retention rules table for a project |
| This will be called from Forms |
| |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Destination project id |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE delete_retention_rules (
p_project_id IN NUMBER,
p_task_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(30) := NULL;
DELETE FROM pa_proj_retn_rules
WHERE project_id = p_project_id;
DELETE FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id;
DELETE FROM pa_proj_retn_rules
WHERE project_id = p_project_id
AND task_id = p_task_id;
DELETE FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND task_id = p_task_id;
p_procedure_name => 'delete_retention_rules');
END delete_retention_rules;
| Procedure : insert_retention_rules |
| Purpose : To insert into retention rules table |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_fr_project_id IN Source project id |
| p_fr_customer_id IN Source customer id |
| p_to_project_id IN Destination project id |
| p_to_customer_id IN Destination customer id |
| p_fr_date IN From effective date |
| p_to_date IN To effective date |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
PROCEDURE insert_retention_rules (
p_fr_project_id IN NUMBER,
p_fr_customer_id IN NUMBER,
p_to_project_id IN NUMBER,
p_to_customer_id IN NUMBER,
p_fr_date IN DATE,
p_to_date IN DATE,
p_delta IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
cursor retn_cur( l_fr_project_id number, l_fr_customer_id number) IS
select retention_level_code, task_id, expenditure_category,
expenditure_type, non_labor_resource, event_type,
effective_start_date, effective_end_date,
retention_percentage, retention_amount, threshold_amount,
projfunc_total_retained, project_total_retained,
total_retained, revenue_category_code
from pa_proj_retn_rules
where project_id = l_fr_project_id
and customer_id = l_fr_customer_id;
select billing_method_code, task_id, completed_percentage,
total_retention_amount, retn_billing_cycle_id, client_extension_flag,
retn_billing_percentage, retn_billing_amount
from pa_proj_retn_bill_rules
where project_id = l_fr_project_id
and customer_id = l_fr_customer_id;
INSERT INTO pa_proj_retn_rules
(RETENTION_RULE_ID,
RETENTION_LEVEL_CODE,
PROJECT_ID,
CUSTOMER_ID,
TASK_ID ,
EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE,
NON_LABOR_RESOURCE,
EVENT_TYPE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
RETENTION_PERCENTAGE,
RETENTION_AMOUNT,
THRESHOLD_AMOUNT,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
REVENUE_CATEGORY_CODE)
VALUES
(pa_proj_retn_rules_s.nextval,
retn_rec.retention_level_code,
p_to_project_id,
p_to_customer_id,
l_to_task_id,
retn_rec.expenditure_category,
retn_rec.expenditure_type,
retn_rec.non_labor_resource,
retn_rec.event_type,
decode(l_to_start_date, NULL,
retn_rec.effective_start_date + p_delta,
retn_rec.effective_start_date + (l_to_start_date -
l_fr_start_date)),
decode( retn_rec.effective_end_date, null, null,
decode(l_to_start_date, NULL,
retn_rec.effective_end_date + p_delta,
retn_rec.effective_end_date +
(l_to_start_date - l_fr_start_date))),
retn_rec.retention_percentage,
retn_rec.retention_amount,
retn_rec.threshold_amount,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
retn_rec.revenue_category_code);
INSERT INTO pa_proj_retn_rules
(RETENTION_RULE_ID,
RETENTION_LEVEL_CODE,
PROJECT_ID,
CUSTOMER_ID,
TASK_ID ,
EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE,
NON_LABOR_RESOURCE,
EVENT_TYPE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
RETENTION_PERCENTAGE,
RETENTION_AMOUNT,
THRESHOLD_AMOUNT,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
REVENUE_CATEGORY_CODE)
VALUES
(pa_proj_retn_rules_s.nextval,
retn_rec.retention_level_code,
p_to_project_id,
p_to_customer_id,
l_to_task_id,
retn_rec.expenditure_category,
retn_rec.expenditure_type,
retn_rec.non_labor_resource,
retn_rec.event_type,
retn_rec.effective_start_date + p_delta,
retn_rec.effective_end_date + p_delta,
retn_rec.retention_percentage,
retn_rec.retention_amount,
retn_rec.threshold_amount,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
retn_rec.revenue_category_code);
INSERT INTO pa_proj_retn_bill_rules
( RETN_BILLING_RULE_ID,
BILLING_METHOD_CODE,
PROJECT_ID,
CUSTOMER_ID,
TASK_ID ,
COMPLETED_PERCENTAGE,
TOTAL_RETENTION_AMOUNT,
RETN_BILLING_CYCLE_ID,
CLIENT_EXTENSION_FLAG,
RETN_BILLING_PERCENTAGE,
RETN_BILLING_AMOUNT,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY)
VALUES
(pa_proj_retn_bill_rules_s.nextval,
bill_rec.billing_method_code,
p_to_project_id,
p_to_customer_id,
l_to_task_id,
bill_rec.completed_percentage,
bill_rec.total_retention_amount,
bill_rec.retn_billing_cycle_id,
bill_rec.client_extension_flag,
bill_rec.retn_billing_percentage,
bill_rec.retn_billing_amount,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id );
p_procedure_name => 'insert_retention_rules');
END insert_retention_rules;
SELECT invproc_currency_type,
project_currency_code,
projfunc_currency_code
INTO x_invproc_currency_type,
x_project_currency_code,
x_projfunc_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT funding_currency_code
INTO x_invproc_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum = 1
GROUP BY funding_currency_code
HAVING sum(nvl(total_baselined_amount,0)) > 0;
SELECT new.task_id, new.start_date, old.start_date
INTO x_task_id, x_to_start_date, x_fr_start_date
FROM pa_tasks old, pa_tasks new
WHERE old.project_id = p_fr_project_id
AND old.task_id = p_fr_task_id
AND old.task_number = new.task_number
AND new.project_id = p_to_project_id;
SELECT p.segment1, p.name, p.invproc_currency_type,
p.project_currency_code, p.projfunc_currency_code, lk.meaning
INTO x_project_number, x_project_name, l_invproc_currency_type,
l_project_currency_code, x_projfunc_currency_code, x_invproc_currency_type
FROM pa_projects_all p , pa_lookups lk
WHERE project_id = p_project_id
and lk.lookup_type = 'INVPROCE_CURR_TYPE'
and lk.lookup_code = p.invproc_currency_type;
SELECT funding_currency_code
INTO x_invproc_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum = 1
GROUP BY funding_currency_code
HAVING sum(nvl(total_baselined_amount,0)) > 0;
CURSOR c2 is SELECT min(start_date) min_start
FROM pa_tasks
WHERE project_id = p_fr_project_id;
SELECT fr_proj.start_date, to_proj.start_date
INTO l_fr_start_date, l_to_start_date
FROM pa_projects_all fr_proj, pa_projects_all to_proj
WHERE fr_proj.project_id = p_fr_project_id
AND to_proj.project_id = p_to_project_id;
SELECT record_version_number
INTO x_version_num
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
SELECT record_version_number
INTO l_version_num
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
update pa_project_customers
set record_version_number = p_version_num + 1
where project_id = p_project_id
and customer_id = p_customer_id
and record_version_number = p_version_num;
If both are differnt then Other User is already updated the project Record so raising the error */
pa_retention_util.set_rec_version_num ( p_project_id,
p_customer_id,
p_version_num,
l_x_return_status,
l_x_msg_count,
l_x_msg_data
);
Delete the Old method from Table and Insert a Row with New Method
------------------------------------------------------------------ */
DELETE FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND nvl(task_id, -99) = nvl(p_task_id, -99)
AND customer_id = p_customer_id ;
Insert for the Following Billing Method.
Total Retention Amount
Retention Billing Cycle
Client Extension
'None' - No Insertion for or this method
------------------------------------------------------------------ */
IF ((p_billing_method_code = 'TOTAL_RETENTION_AMOUNT') OR (p_billing_method_code = 'RETENTION_BILLING_CYCLE')
OR (p_billing_method_code = 'CLIENT_EXTENSION')) THEN
INSERT INTO pa_proj_retn_bill_rules
( PROJECT_ID ,
CUSTOMER_ID ,
TASK_ID,
BILLING_METHOD_CODE,
COMPLETED_PERCENTAGE,
TOTAL_RETENTION_AMOUNT,
RETN_BILLING_CYCLE_ID,
CLIENT_EXTENSION_FLAG,
RETN_BILLING_PERCENTAGE,
RETN_BILLING_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RETN_BILLING_RULE_ID
)
VALUES(
p_project_id,
p_customer_id,
p_task_id,
p_billing_method_code,
p_completed_percentage,
p_total_retention_amount,
p_retn_billing_cycle_id,
p_client_extension_flag,
p_retn_billing_percentage,
p_retn_billing_amount,
sysdate,
-1,
sysdate,
-1,
pa_proj_retn_bill_rules_s.nextval
);
If both are differnt then Other User is already updated the project Record so raising the error
--------------------------------------------------------------------------------------------------- */
pa_retention_util.set_rec_version_num ( p_project_id,
p_customer_id,
p_version_num,
l_x_return_status,
l_x_msg_count,
l_x_msg_data
);
Delete the Old Method from Database
--------------------------------------------------------------- */
DELETE FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND nvl(task_id, -99) = nvl(p_task_id, -99)
AND customer_id = p_customer_id ;
INSERT INTO pa_proj_retn_bill_rules
( PROJECT_ID ,
CUSTOMER_ID ,
TASK_ID,
BILLING_METHOD_CODE,
COMPLETED_PERCENTAGE,
TOTAL_RETENTION_AMOUNT,
RETN_BILLING_CYCLE_ID,
CLIENT_EXTENSION_FLAG,
RETN_BILLING_PERCENTAGE,
RETN_BILLING_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
RETN_BILLING_RULE_ID
)
VALUES(
p_project_id,
p_customer_id,
p_task_id,
p_billing_method_code,
l_completed_percentage(i),
p_total_retention_amount,
p_retn_billing_cycle_id,
p_client_extension_flag,
l_retn_billing_percentage(i),
l_retn_billing_amount(i),
sysdate,
-1,
sysdate,
-1,
pa_proj_retn_bill_rules_s.nextval
);
SELECT top_task_id
INTO x_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = top_task_id
AND task_name = p_task_name;
SELECT top_task_id
INTO x_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = top_task_id
AND task_number = p_task_no;
SELECT top_task_id
INTO l_name_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = top_task_id
AND task_name = p_task_name;
SELECT top_task_id
INTO l_no_task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = top_task_id
AND task_number = p_task_no;
SELECT count(*)
INTO l_bill_rec_count
FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND task_id = x_task_id
AND customer_id = p_customer_id;
SELECT count(*)
INTO l_bill_rec_count
FROM pa_proj_retn_bill_rules
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
SELECT Effective_Start_Date, Effective_End_Date
FROM PA_PROJ_RETN_RULES
WHERE Project_ID = P_Project_ID
AND NVL(Task_ID, -1) = NVL(P_Task_ID, -1)
AND Customer_ID = P_Customer_ID
AND Retention_Level_Code = P_Retention_Level_Code
AND NVL(Expenditure_Category, 'X') = NVL(P_Expenditure_Category, 'X')
AND NVL(Expenditure_Type, 'X') = NVL(P_Expenditure_Type, 'X')
AND NVL(Non_Labor_Resource, 'X') = NVL(P_Non_Labor_Resource, 'X')
AND NVL(Revenue_Category_Code, 'X')= NVL(P_Revenue_Category_Code, 'X')
AND NVL(Event_Type, 'X') = NVL(P_Event_Type, 'X')
AND decode(P_RowID, NULL, 'X', RowIDToChar(RowID))
<> decode(P_RowID, NULL, 'Y', P_RowID );
SELECT
Expenditure_Category
INTO
P_Expenditure_Category
FROM
PA_EXPENDITURE_CATEGORIES
WHERE
upper(Expenditure_Category) = upper(P_Expenditure_Category);
SELECT
Expenditure_Type
INTO
P_Expenditure_Type
FROM
PA_EXPENDITURE_TYPES
WHERE
upper(Expenditure_Category) = upper(P_Expenditure_Category)
AND upper(Expenditure_Type) = upper(P_Expenditure_Type);
SELECT
Non_Labor_Resource
INTO
P_Non_Labor_Resource
FROM
PA_NON_LABOR_RESOURCES
WHERE
upper(Non_Labor_Resource) = upper(P_Non_Labor_Resource)
AND upper(Expenditure_Type) = upper(P_Expenditure_Type);
SELECT Lookup_Code, Meaning
INTO P_Revenue_Category_Code, P_Revenue_Category
FROM PA_LOOKUPS
WHERE Lookup_Type = 'REVENUE CATEGORY'
AND upper(Meaning) = upper(P_Revenue_Category);
SELECT Event_Type
INTO P_Event_Type
FROM PA_EVENT_TYPES
WHERE upper(Revenue_Category_Code) = upper(P_Revenue_Category_Code)
AND upper(Event_Type) = upper(P_Event_Type);
PROCEDURE Delete_Retentions (
P_Project_ID NUMBER,
P_Customer_ID NUMBER,
X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_Exist_Flag VARCHAR2(1) := '';
DELETE FROM PA_PROJ_RETN_RULES
WHERE Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID;
DELETE FROM PA_PROJ_RETN_BILL_RULES
WHERE Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID;
UPDATE PA_PROJECT_CUSTOMERS
SET Retention_Level_Code = ''
WHERE Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID;
END Delete_Retentions;
SELECT Task_ID
INTO X_Task_ID
FROM PA_TASKS
WHERE Project_ID = P_Project_ID
AND upper(Task_Number) = upper(P_Task_Number)
AND upper(Task_Name) = upper(P_Task_Name) ;
PROCEDURE Delete_Bill_Retentions (
P_Bill_Rule_ID NUMBER,
X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
BEGIN
X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
DELETE FROM
PA_PROJ_RETN_BILL_RULES
WHERE
RETN_BILLING_RULE_ID = P_Bill_Rule_ID ;
END Delete_Bill_Retentions ;
SELECT
1
INTO
l_Exist_Flag
FROM
PA_PROJ_RETN_BILL_RULES
WHERE
Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID
AND RowNum < 2;
SELECT
NVL(sum( decode(nvl(total_retained,0),0,1,0)),0),
NVL(sum( decode(nvl(total_retained,0),0,0,1)),0)
INTO
l_NonRetained_Count,
l_Retained_Count
FROM
PA_PROJ_RETN_RULES
WHERE
Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID;
SELECT 1
INTO l_Billing_Rules_Count
FROM
PA_PROJ_RETN_BILL_RULES
WHERE
Project_ID = P_Project_ID
AND Customer_ID = P_Customer_ID
AND RowNum < 2;
SELECT 1
INTO l_retn_invfmt_error
FROM dual
WHERE EXISTS(SELECT null
FROM pa_proj_retn_rules rtn
WHERE rtn.project_id = p_project_id);