[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This Private Procedure Update_Approve_Invoices Updates |
| PA_DRAFT_INVOICES_ALL table with invoice approval columns |
+----------------------------------------------------------------------------*/
Procedure Update_Approve_Invoices ( P_Project_ID in number,
P_Draft_Invoice_Num in number,
P_User_ID in number,
P_Employee_ID in number) is
BEGIN
UPDATE PA_Draft_Invoices_ALL
SET Approved_Date = sysdate,
Approved_by_person_id = P_Employee_ID,
Last_Update_Date = sysdate,
Last_Updated_By = P_User_ID
WHERE Project_ID = P_Project_ID
AND Draft_Invoice_Num = P_Draft_Invoice_Num;
END Update_Approve_Invoices;
| This Private Procedure Update_Unapprove_Invoices Updates |
| PA_DRAFT_INVOICES_ALL table with invoice approval columns as NULL |
+----------------------------------------------------------------------------*/
Procedure Update_Unapprove_Invoices ( P_Invoice_Set_ID in number,
P_User_ID in number) is
BEGIN
UPDATE PA_Draft_Invoices_ALL
SET Approved_Date = NULL,
Approved_by_person_id = NULL,
Released_Date = NULL,
Released_by_person_id = NULL,
RA_Invoice_Number = NULL,
Invoice_Date = NULL,
Last_Update_Date = sysdate,
Last_Updated_By = P_User_ID
WHERE Invoice_Set_ID = P_Invoice_Set_ID;
END Update_Unapprove_Invoices;
| This Private Procedure Update_Release_Invoices Updates |
| PA_DRAFT_INVOICES_ALL table with invoice Release attributes |
+----------------------------------------------------------------------------*/
Procedure Update_Release_Invoices ( P_Project_ID in number,
P_Draft_Invoice_Num in number,
P_RA_Invoice_Date in date,
P_RA_Invoice_Num in varchar2,
P_User_ID in number,
P_Employee_ID in number,
P_Credit_Memo_Reason_Code in varchar2) is
BEGIN
UPDATE PA_Draft_Invoices_ALL
SET Released_Date = sysdate,
Released_by_person_id = P_Employee_ID,
RA_Invoice_Number = P_RA_Invoice_Num,
Invoice_Date = P_RA_Invoice_Date,
Last_Update_Date = sysdate,
Last_Updated_By = P_User_ID,
Credit_Memo_Reason_Code = P_Credit_Memo_Reason_Code /* Bug #2728431*/
WHERE Project_ID = P_Project_ID
AND Draft_Invoice_Num = P_Draft_Invoice_Num;
END Update_Release_Invoices;
| This Private Procedure Update_Unrelease_Invoices Updates |
| PA_DRAFT_INVOICES_ALL table with invoice Release attributes as NULL |
+----------------------------------------------------------------------------*/
Procedure Update_Unrelease_Invoices ( P_Invoice_Set_ID in number,
P_User_ID in number) is
BEGIN
UPDATE PA_Draft_Invoices_ALL
SET Released_Date = NULL,
Released_by_person_id = NULL,
RA_Invoice_Number = NULL,
Invoice_Date = NULL,
Last_Update_Date = sysdate,
Last_Updated_By = P_User_ID,
Credit_memo_reason_code = NULL /* Bug #2728431*/
WHERE Invoice_Set_ID = P_Invoice_Set_ID;
END Update_Unrelease_Invoices;
| This Private Procedure Insert_Distrbution_Warning Inserts draft Invoice |
| distribution warning. |
+----------------------------------------------------------------------------*/
Procedure Insert_Distrbution_Warning ( P_Project_ID in number,
P_Draft_Invoice_Num in number,
P_User_ID in number,
P_Request_ID in number,
P_Invoice_Set_ID in number,
P_Error_Message_Code in varchar2) is
l_error_message pa_lookups.meaning%TYPE;
SELECT Meaning
INTO l_error_message
FROM PA_Lookups
WHERE Lookup_Type = 'BILLING EXTENSION MESSAGES'
AND Lookup_Code = P_Error_Message_Code;
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
)
VALUES
(
P_Project_ID, P_Draft_Invoice_Num, sysdate, P_User_ID,
sysdate, P_User_ID, P_Request_ID, l_error_message
);
INSERT INTO PA_DISTRIBUTION_WARNINGS
(
PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
)
SELECT Project_ID, Draft_Invoice_Num, sysdate, P_User_ID,
sysdate, P_User_ID, P_Request_ID, l_error_message
FROM PA_Draft_Invoices_ALL
WHERE Invoice_Set_ID = P_Invoice_Set_ID;
END Insert_Distrbution_Warning;
SELECT Credit_Memo_Reason_Flag
FROM pa_implementations;
SELECT 'Y'
FROM dual
WHERE EXISTS(SELECT draft_invoice_num
FROM pa_draft_invoices i
WHERE i.project_id = P_Project_ID
AND i.draft_invoice_num = P_Draft_Invoice_Num
AND i.draft_invoice_num_credited IS NOT NULL);
SELECT 'Y' FROM dual
WHERE EXISTS( select lookup_code
from fnd_lookup_values_vl
where lookup_type='CREDIT_MEMO_REASON'
and lookup_code = P_Credit_Memo_Reason_Code
and enabled_flag='Y'
and P_RA_invoice_date between start_date_active and nvl(end_date_active,P_RA_invoice_date));
SELECT i.customer_id, i.generation_error_flag,
i.approved_date , i.project_status_code
FROM pa_draft_invoices_v i
WHERE i.project_id = P_Project_ID
AND i.draft_invoice_num = P_Draft_Invoice_Num;
SELECT i.customer_id, i.generation_error_flag,
i.approved_date, prj.project_status_code project_status_code
FROM pa_draft_invoices i,
pa_projects_all prj
WHERE prj.project_id = P_Project_ID
AND i.project_id = prj.project_id
AND i.draft_invoice_num = P_Draft_Invoice_Num;
SELECT 1
FROM RA_CUSTOMERS
WHERE customer_id = l_customer_id
AND NVL(status, 'A') <> 'A'
AND customer_prospect_code = 'CUSTOMER';
SELECT 1
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = l_customer_id
AND NVL(status, 'A') <> 'A';
SELECT user_defined_invoice_num_code, manual_invoice_num_type
FROM pa_implementations;
SELECT CC_MANUAL_INVOICE_NUM_CODE, CC_MANUAL_INVOICE_NUM_TYPE
FROM pa_implementations;
SELECT PROJCUST.customer_id,PROJCUST.bill_to_customer_id,PROJCUST.bill_to_address_id,
PROJCUST.ship_to_address_id,
decode(nvl(PROJTYPE.cc_prvdr_flag,'N'),
'Y', 'INTERNAL-INVOICE',
decode(nvl(PROJCUST.bill_another_project_flag,'N'),
'Y', 'INTERNAL-INVOICE',
'EXTERNAL-INVOICE'))
INTO l_customer_id,l_bill_ship_to_customer_id, l_bill_to, l_ship_to, l_invoice_category
FROM pa_draft_invoices INV, pa_agreements_all AGREE, /* fix bug 2082864 for MCB2 */
pa_project_customers PROJCUST,
pa_projects PROJ,
pa_project_types PROJTYPE
WHERE INV.project_id = P_Project_id
AND INV.draft_invoice_num = P_Draft_Invoice_Num
AND AGREE.agreement_id = INV.agreement_id
AND PROJCUST.customer_id = AGREE.customer_id
AND PROJCUST.project_id = P_Project_id
AND PROJ.project_id = INV.project_id
AND PROJ.project_type = PROJTYPE.project_type;
SELECT null INTO l_dummy
FROM sys.dual
WHERE EXISTS ( SELECT project_id
FROM pa_project_contacts
WHERE project_id = P_project_id
AND customer_id = l_customer_id
AND bill_ship_customer_id=l_bill_ship_to_customer_id -- Added for customer account relation
-- enhancement
AND project_contact_type_code = 'BILLING');
SELECT nvl(draft_invoice_num_credited,0)
INTO l_draft_inv_num_cr
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND draft_invoice_num = P_Draft_Invoice_Num;
SELECT null INTO l_dummy
FROM dual
WHERE EXISTS ( SELECT project_id
FROM pa_draft_invoices di, hz_cust_account_roles rc
WHERE di.project_id = p_project_id
AND di.draft_invoice_num = P_Draft_Invoice_Num
AND di.bill_to_contact_id = rc.cust_account_role_id
AND nvl(rc.status,'N') = 'A'
);
select to_number(P_RA_Invoice_Num)
into l_dummy
from dual;
SELECT COUNT(*)
INTO l_dummy
FROM pa_draft_invoices_all cm, pa_draft_invoices_all i
WHERE cm.project_id = P_Project_ID
AND cm.draft_invoice_num = P_Draft_Invoice_Num
AND cm.project_id = i.project_id
AND i.draft_invoice_num = cm.draft_invoice_num_credited
AND i.invoice_date > P_RA_Invoice_Date;
SELECT count(*)
INTO l_dummy
FROM pa_draft_invoices_all
WHERE project_id = P_Project_ID
AND draft_invoice_num < P_Draft_Invoice_Num
AND released_date is null
AND nvl(generation_error_flag, 'N') = 'N';
SELECT 1
into l_dummy
FROM PA_DRAFT_INVOICES_ALL
WHERE PROJECT_ID =P_Project_ID
AND DRAFT_INVOICE_NUM = P_Draft_Invoice_Num
AND RELEASED_DATE IS NOT NULL;
SELECT count(*)
INTO l_dummy
FROM pa_draft_invoices i,
pa_projects p,
pa_project_types pt,
pa_agreements_all AGREE, /* fix bug 2082864 for MCB2 */
pa_project_customers PROJCUST
WHERE i.ra_invoice_number = l_RA_Invoice_Num
AND NOT EXISTS ( SELECT 'x'
FROM pa_draft_invoices x
WHERE x.project_id = P_Project_ID
AND x.draft_invoice_num = P_Draft_Invoice_Num
AND x.project_id = i.project_id
AND x.draft_invoice_num = i.draft_invoice_num)
AND i.project_id = p.project_id
AND pt.project_type = p.project_type
AND AGREE.agreement_id = i.agreement_id
AND PROJCUST.customer_id = AGREE.customer_id
AND projcust.project_id = p.project_id /* added for bug#2634995 */
AND decode(nvl(pt.cc_prvdr_flag,'N'),
'Y', 'INTERNAL-INVOICE',
decode(nvl(PROJCUST.bill_another_project_flag,'N'),
'Y', 'INTERNAL-INVOICE',
'EXTERNAL-INVOICE'))
= l_invoice_category;
SELECT count(*)
INTO l_dummy
FROM pa_draft_invoices_all
WHERE project_id = P_Project_ID
AND draft_invoice_num < P_Draft_Invoice_Num
AND released_date is null
AND nvl(generation_error_flag, 'N') = 'Y';
SELECT count(*)
INTO l_dummy
FROM PA_Draft_Invoices_ALL i
WHERE i.invoice_set_id = P_Invoice_Set_ID
AND i.customer_bill_split not in (0, 100)
AND ( ( (i.approved_date is not null)
AND EXISTS ( SELECT 'APPROVED ERROR'
FROM pa_draft_invoices ia
WHERE ia.project_id = i.project_id
AND ia.invoice_set_id = i.invoice_set_id
AND ia.approved_date is null))
OR ( (i.released_date is not null)
AND EXISTS ( SELECT 'RELEASED ERROR'
FROM pa_draft_invoices ir
WHERE ir.project_id = i.project_id
AND ir.invoice_set_id = i.invoice_set_id
AND ir.released_date is null)));
Procedure Post_Update_Release ( P_Project_ID in number,
P_Draft_Invoice_Num in number,
P_User_ID in number,
P_Employee_ID in number) is
/* Commented for bug 3088395 */ /* The following SQL statement was uncommented for bug 3872496 */
CURSOR EI_BILL_HOLD_CUR IS
SELECT ei.expenditure_item_id
FROM pa_expenditure_items_all ei, pa_tasks t
WHERE ei.bill_hold_flag = 'O'
AND ei.task_id = t.task_id
AND t.project_id = P_Project_ID;
SELECT ei.expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE ei.bill_hold_flag = 'O' /*Bill Hold Once
AND ei.project_id = P_Project_ID;
UPDATE pa_draft_revenues dr
SET dr.last_update_date = sysdate,
dr.last_updated_by = P_User_ID,
dr.released_date = sysdate,
dr.last_update_login = P_Employee_ID
WHERE dr.project_id = P_Project_ID
AND dr.generation_error_flag = 'N'
AND dr.released_date IS NULL
AND dr.draft_revenue_num <=
(SELECT max(rdl.draft_revenue_num)
FROM pa_cust_event_rev_dist_lines rdl
WHERE rdl.project_id = P_Project_ID
AND rdl.draft_invoice_num = P_Draft_Invoice_Num);
UPDATE pa_draft_revenues dr
SET dr.last_update_date = sysdate,
dr.last_updated_by = P_User_ID,
dr.released_date = sysdate,
dr.last_update_login = P_Employee_ID
WHERE dr.project_id = P_Project_ID
AND dr.generation_error_flag = 'N'
AND dr.released_date IS NULL
AND dr.draft_revenue_num <=
(SELECT max(rdl.draft_revenue_num)
FROM pa_cust_event_rev_dist_lines rdl
WHERE rdl.project_id = P_Project_ID
and exists /* check if the event is an automatic event */
(select 1
from pa_events e, pa_event_types et
where e.project_id = rdl.project_id
and e.event_num = rdl.event_num
and nvl(e.task_id,-99) = nvl(rdl.task_id,-99)
and e.event_type = et.event_type
and et.event_type_classification = 'AUTOMATIC')
AND exists /* check if the invoice released is related to this automatic event */
(select 1
from pa_draft_invoice_items dii
where dii.project_id = rdl.project_id
and dii.draft_invoice_num = P_Draft_Invoice_Num
and dii.event_num is not null
and dii.event_num = rdl.event_num
and nvl(dii.event_task_id, -99) = nvl(rdl.task_id, -99)));
UPDATE pa_draft_revenues dr
SET dr.last_update_date = sysdate,
dr.last_updated_by = P_User_ID,
dr.released_date = sysdate,
dr.last_update_login = P_Employee_ID
WHERE dr.project_id = P_Project_ID
AND dr.generation_error_flag = 'N'
AND dr.released_date IS NULL
AND dr.draft_revenue_num <=
/* Commented code fix for bug 2968645
(SELECT max(rdl.draft_revenue_num)
FROM pa_cust_rev_dist_lines rdl
WHERE rdl.project_id = P_Project_ID
AND rdl.draft_invoice_num = P_Draft_Invoice_Num); */
(SELECT max(rdl1.draft_revenue_num)
FROM pa_cust_rev_dist_lines rdl1
WHERE rdl1. expenditure_item_id in
( SELECT expenditure_item_id
FROM pa_cust_rev_dist_lines rdl2
WHERE rdl2.project_id = P_Project_ID
AND rdl2.draft_invoice_num = P_Draft_Invoice_Num));
UPDATE pa_events
SET bill_hold_flag = 'N',
last_update_date = sysdate,
last_updated_by = P_User_ID,
last_update_login = P_Employee_ID
WHERE project_id = P_Project_ID
AND bill_hold_flag || '' = 'O';
END Post_Update_Release;
/* Cursor for Select All Unapproved invoices created in This Run */
CURSOR UNAPP_INV_CUR is
SELECT i.project_id,
nvl(i.invoice_set_id, 0),
i.draft_invoice_num,
decode(P_Calling_Place, 'INV_CR_MEMO',
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO'), P_Calling_Place) invoice_class,
sum(ii.amount),
p.project_currency_code,
i.inv_currency_code,
sum(ii.inv_amount)
FROM pa_projects p,
pa_draft_invoices i,
pa_draft_invoice_items ii
WHERE p.project_id = i.project_id
AND i.project_id = ii.project_id
AND i.draft_invoice_num = ii.draft_invoice_num
AND i.request_id = P_Request_ID
AND i.approved_date is null
AND nvl(i.generation_error_flag, 'N') = 'N'
AND (i.project_id+0 = P_Project_ID or P_Project_ID is NULL)
GROUP BY i.project_id,
nvl(i.invoice_set_id, 0),
i.draft_invoice_num,
decode(P_Calling_Place, 'INV_CR_MEMO',
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO'), P_Calling_Place),
p.project_currency_code,
i.inv_currency_code
ORDER BY i.project_id, i.draft_invoice_num; /*Added order by clause for bug 6009706 */
/* Cursor for Select All Unreleased invoices created in This Run */
CURSOR UNREL_INV_CUR is
SELECT i.project_id,
nvl(i.invoice_set_id, 0),
i.draft_invoice_num,
decode(P_Calling_Place, 'INV_CR_MEMO',
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO'), P_Calling_Place) invoice_class,
sum(ii.amount),
p.project_currency_code,
i.inv_currency_code,
sum(ii.inv_amount)
FROM pa_projects p,
pa_draft_invoices i,
pa_draft_invoice_items ii
WHERE p.project_id = i.project_id
AND i.project_id = ii.project_id
AND i.draft_invoice_num = ii.draft_invoice_num
AND i.request_id = P_Request_ID
AND i.approved_date is not null
AND i.released_date is null /* For bug 2863710 */
AND nvl(i.generation_error_flag, 'N') = 'N'
AND (i.project_id+0 = P_Project_ID or P_Project_ID is NULL)
GROUP BY i.project_id,
nvl(i.invoice_set_id, 0),
i.draft_invoice_num,
decode(P_Calling_Place, 'INV_CR_MEMO',
decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
'CREDIT_MEMO'), P_Calling_Place),
p.project_currency_code,
i.inv_currency_code
ORDER BY i.project_id, i.draft_invoice_num; /*Added order by clause for bug 6009706 */
/* Cursor for Select All invoices Released in This Run */
CURSOR RELEASED_INV_CUR is
SELECT i.project_id,
i.draft_invoice_num
FROM pa_draft_invoices i
WHERE i.request_id = P_Request_ID
AND i.released_date is not null;
SELECT nvl(Employee_ID,0)
INTO l_employee_id
FROM FND_USER
WHERE User_ID = P_User_ID;
| Select The Invoices that were created in this run |
| For a single run of generate Draft invoice program this API |
| will be called once for Invoice/Credit Memo, Write Off or |
| Cancellation of an invoice. |
+------------------------------------------------------------------*/
/*------------------------------------------------------------------+
| Select UnApproved Invoices that were Created in this run |
+------------------------------------------------------------------*/
-- Initialize Local Variables
l_prv_project_id := 0;
SELECT Project_ID
INTO l_dummy
FROM PA_Projects_ALL
WHERE Project_ID = l_project_id
FOR UPDATE OF LAST_UPDATE_DATE;
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
'PA_CLIENT_EXTN_APP_ERROR');
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
'PA_CLIENT_EXTN_ORACLE_ERROR');
Update_Approve_Invoices( l_Project_ID, l_draft_invoice_num,
P_User_ID, l_employee_id);
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
l_err_msg_code);
Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
l_prv_invoice_set_id, l_err_msg_code);
Update_Unapprove_Invoices ( l_prv_invoice_set_id, P_User_ID);
Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
l_invoice_set_id, l_err_msg_code);
Update_Unapprove_Invoices ( l_invoice_set_id, P_User_ID);
| Select UnReleased Invoices that were Created in this run |
+------------------------------------------------------------------*/
-- Initialize Local Variables
l_prv_project_id := 0;
SELECT Project_ID
INTO l_dummy
FROM PA_Projects_ALL
WHERE Project_ID = l_project_id
FOR UPDATE OF LAST_UPDATE_DATE;
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
'PA_CLIENT_EXTN_APP_ERROR');
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
'PA_CLIENT_EXTN_ORACLE_ERROR');
Update_Release_Invoices( l_Project_ID, l_draft_invoice_num,
l_ra_invoice_date, l_ra_invoice_num,
P_User_ID, l_employee_id,l_Credit_memo_reason_code);
Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
P_User_ID, P_Request_ID, NULL,
l_err_msg_code);
Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
l_prv_invoice_set_id, l_err_msg_code);
Update_Unrelease_Invoices ( l_prv_invoice_set_id, P_User_ID);
Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
l_invoice_set_id, l_err_msg_code);
Update_Unrelease_Invoices ( l_invoice_set_id, P_User_ID);
/* Do the Post Release update for all the released Records */
FOR CUR_REC in RELEASED_INV_CUR
LOOP
PA_Invoice_Actions.Post_Update_Release(CUR_REC.Project_ID,
CUR_REC.Draft_Invoice_Num,
P_User_ID,
l_employee_id);
select record_version_number, last_credit_request_id
into l_record_version_number, l_last_credit_request_id
from pa_draft_invoices
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num;
l_msg_data := 'PA_REC_ALREADY_UPDATED';
/* This means that another session has updated the record_version_number and fired
the concurrent request for invoice processing since the start of the current session.
Hence the current session needs to rollback and restart in order to include the changes
from the other session.
*/
rollback;
| p_net_inv_amount IN Net invoice amount of the selected lines. |
| p_credit_amount IN Total entered credit amount. |
| p_balance due IN Due amount in AR. |
| x_tot_credited_amt OUT Total credited amount applied on invoice. |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+---------------------------------------------------------------------------------------------*/
Procedure validate_invoice_amount (
p_project_id IN NUMBER,
p_credit_action IN VARCHAR2,
p_credit_action_type IN VARCHAR2,
p_draft_invoice_num IN NUMBER,
p_invoice_amount IN NUMBER,
p_net_inv_amount IN NUMBER,
p_credit_amount IN NUMBER,
p_balance_due IN NUMBER,
x_tot_credited_amt OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(30) := NULL;
SELECT NVL(typ.allow_overapplication_flag,'N')
FROM pa_implementations imp,
ra_batch_sources bs,
ra_cust_trx_types typ
WHERE decode(l_intercompany_flag,'N',imp.invoice_batch_source_id,
imp.cc_ic_ar_batch_source_id) = bs.batch_source_id
and bs.default_inv_trx_type = typ.cust_trx_type_id;
SELECT NVL(ptype.cc_prvdr_flag, 'N')
FROM pa_projects proj,
pa_project_types ptype
WHERE proj.PROJECT_ID = p_project_id
AND proj.PROJECT_TYPE = ptype.PROJECT_TYPE;
SELECT DECODE(CENTRALIZED_INVOICING_FLAG, 'N', 'Y', 'N')
FROM PA_IMPLEMENTATIONS;
SELECT IMP.business_group_id,
IMP.proj_org_structure_version_id,
BASELANG.language_code,
INV.invoice_date,
PROJ.Carrying_Out_Organization_ID
FROM pa_implementations IMP,
fnd_languages BASELANG,
pa_draft_invoices_all INV,
pa_projects PROJ
WHERE INV.project_id = p_project_id
AND INV.draft_invoice_num = p_draft_invoice_num
AND BASELANG.installed_flag = 'B'
AND PROJ.project_id = p_project_id;
SELECT NVL(typ.allow_overapplication_flag,'N')
FROM ra_cust_trx_types typ
WHERE typ.cust_trx_type_id = TO_NUMBER(x_trx_type);
/* Second Check - Check if net invoice amount of the selected lines is negative.
Write-Off/Concession cannot be performed when net invoice amount of the
selected lines is negative. */
IF (p_credit_action_type in ('GROUP', 'LINE'))THEN
IF sign(p_net_inv_amount) <> 1 THEN
l_return_status := FND_API.G_RET_STS_ERROR;
/* Get the total credited amount for the selected invoice in Oracle Projects. */
SELECT ABS(nvl(SUM(nvl(ii.inv_amount,0)),0))
INTO l_tot_credited_amt
FROM pa_draft_invoices i,
pa_draft_invoice_items ii
WHERE i.project_id = p_project_id
AND ii.project_id = i.project_id
AND ii.draft_invoice_num = i.draft_invoice_num
AND i.draft_invoice_num_credited = p_draft_invoice_num
AND i.canceled_flag IS NULL
AND i.generation_error_flag = 'N';
/* Update credit_process_flag, credit_amount to NULL */
update pa_draft_invoice_items
set credit_process_flag = NULL,
credit_amount = NULL
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num;
| Procedure : update_credit_qual_lines |
| Purpose : To mark the draft invoice lines which have been selected for |
| crediting |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
| p_credit_action_type IN Indicates if credit action type is |
| SUMMARY/GROUP/LINES |
| p_draft_invoice_num IN Draft invoice for which credit action is to be done |
| p_draft_invoice_line_num IN Draft invoice line which has to be credited |
| p_line_credit_amount IN Total credit amount on the invoice |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+---------------------------------------------------------------------------------------------*/
Procedure update_credit_qual_lines (
p_project_id IN NUMBER,
p_credit_action IN VARCHAR2,
p_credit_action_type IN VARCHAR2,
p_draft_invoice_num IN NUMBER,
p_draft_invoice_line_num IN NUMBER,
p_line_credit_amount IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(30) := NULL;
b) GROUP level (user selects specific lines and gives credit amount which has to pro-rated only on those lines)
The total credit amount will be pro-rated across the specified lines by subsequent API's. The line selected for
crediting by the user will be specified by p_draft_invoice_line_num. Since the credit amount will be given as one
whole amount, p_line_credit_amount will be NULL. Credit_process_flag will be set to 'Y' for the line specified
by p_draft_invoice_line_num. While calculating the rounding difference the sum of line amounts
SELECTED BY USER (INFERRED BY CREDIT_PROCESS_FLAG) should match the credit amount entered by the user.
In this case p_line_credit_amount will be null
c) LINES level (user selects the line and specifies the credit amount on the line)
No pro-ration of credit amount is to be performed as it is specified one on one on the line. No rounding also
needs to be checked. Both p_line_credit_amount/p_draft_invoice_line_num will be specified
Both (b) and (c) will be called in a loop for as many lines selected by the user in Invoice Review Form
*/
if p_credit_action_type = 'SUMMARY' then
/* Update all lines of draft_invoice_items for credit_process_flag = 'Y' */
update pa_draft_invoice_items
set credit_process_flag = 'Y'
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
/* Added for Payroll Billing ER #11896864 - Start */
and NVL(credit_process_flag, 'N') <> 'X';
/* Update specific line of draft_invoice_items for credit_process_flag = 'Y' */
update pa_draft_invoice_items
set credit_process_flag = 'Y'
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and line_num = p_draft_invoice_line_num
/* Added for Payroll Billing ER #11896864 - Start */
and NVL(credit_process_flag, 'N') <> 'X';
/* Update specific line of draft_invoice_items for credit_process_flag = 'Y'and credit_amount = p_line_credit_amount */
update pa_draft_invoice_items
set credit_process_flag = 'Y',
credit_amount = p_line_credit_amount
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and line_num = p_draft_invoice_line_num
/* Added for Payroll Billing ER #11896864 - Start */
and NVL(credit_process_flag, 'N') <> 'X';
END update_credit_qual_lines;
total selected line invoice amount for credit action WRITE-OFF. For Concession, we allow
over credit application. */
IF p_credit_action_type = 'GROUP' THEN
/* First check - Total credit amount should not exceed the net invoice amount of the selected
lines. This is true for Write-Off only. */
IF p_credit_action = 'CONCESSION' THEN
NULL;
| Purpose : To pro-rate the total credit amount across the lines selected for |
| crediting on the specified invoice |
| Parameters : |
| ================================================================================== |
| Name Mode Description |
| ================================================================================== |
| p_project_id IN Project ID |
| p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
| p_credit_action_type IN Indicates if credit action type is |
| SUMMARY/GROUP/LINES |
| p_draft_invoice_num IN Draft invoice for which credit action is to be done |
| p_total_credit_amount IN Total credit amount on the invoice |
| x_return_status OUT Return status of this procedure |
| x_msg_count OUT Error message count |
| x_msg_data OUT Error message |
| ================================================================================== |
+----------------------------------------------------------------------------------------*/
Procedure distribute_credit_amount (
p_project_id IN NUMBER,
p_credit_action IN VARCHAR2,
p_credit_action_type IN VARCHAR2,
p_draft_invoice_num IN NUMBER,
p_total_credit_amount IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_inv_amount NUMBER;
In case of GROUP, the total credit amount has to be pro-rated across the user selected lines indicated by
credit_process_flag = 'Y'
In case of LINES the credit amount is already populated on the lines that are to be credited
In case of GROUP/LINES, after the credit amount is populated in the standard lines, corresponding retention lines
are also to be credited . A separate API distribute_credit_amount_retn will be called to do the same */
if p_credit_action_type = 'GROUP' then
/* Get the sum of invoice amount of the user selected lines (credit_process_flag = 'Y'). Apply the ratio
of p_total_credit_amount over sum_invoice_amount on the line invoice amount. Inv_currency_code is required
to define the precision of the computed credit amount */
select sum(dii.inv_amount), max(di.inv_currency_code)
into l_inv_amount, l_inv_currency_code
from pa_draft_invoice_items dii, pa_draft_invoices di
where di.project_id = p_project_id
and di.draft_invoice_num = p_draft_invoice_num
and dii.project_id = di.project_id
and dii.draft_invoice_num = di.draft_invoice_num
and nvl(dii.credit_process_flag,'N') = 'Y' ;
update pa_draft_invoice_items dii
set credit_amount =
pa_currency.round_trans_currency_amt(
(inv_amount * (p_total_credit_amount/l_inv_amount)),
rtrim(l_inv_currency_code))
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and nvl(dii.credit_process_flag,'N') = 'Y' ;
/* insert into bss_conc
(serial_no, proc_ind)
values(2, 'DISTRIBUTE_RETN_CREDIT');
SELECT nvl(retention_percentage,0), inv_currency_code
INTO l_retention_percentage, l_inv_currency_code
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND EXISTS (select null from pa_draft_invoice_items
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND invoice_line_type = 'RETENTION');
select sum(nvl(credit_amount,0))
into l_tot_credit_amount
from pa_draft_invoice_items
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num;
update pa_draft_invoice_items
set credit_amount =
pa_currency.round_trans_currency_amt(l_retn_credit_amount, rtrim(l_inv_currency_code))
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and invoice_line_type = 'RETENTION';
/* select the standard lines which are to be credited within a loop
For every line of the draft invoice item, we require the retained amount
(will be in invoice processing currency) , retention rule id and retention_line_num (indicates which line
in the draft_invoice_items corresponds to retention of this standard line.
Once we get these, store these values into local variables and set line_processed_flag to TRUE
Call compute_retn_credit_amount API with the fetched values. The logic to get these values will be:
If the line represents event (event_num will be not null)
the retained amount (will be in invoice processing currency) ,
retention rule id, retention_line_num will be stored on the line itself. Set line_processed flag to TRUE
If line_processed is FALSE, then check in ERDL( Could be WRITE-ON events) with project_id, draft_invoice_num,
and line_num. One line of draft invoice item may have multiple lines in ERDL. Since we need to get the
retention info for every retention_rule_id, we group by retention_rule_id and get sum of the amount.
Set line_processed_flag to TRUE
If line_processed is FALSE, then check in RDL( Could be EI's) with project_id, draft_invoice_num,
and line_num. One line of draft invoice item may have multiple lines in RDL. Since we need to get the
retention info for every retention_rule_id, we group by retention_rule_id and get sum of the amount.
Set line_processed_flag to TRUE
*/
for inv_lines in (
select nvl(dii.line_num,0) line_num,
nvl(dii.event_num,0) event_num,
nvl(dii.retn_draft_invoice_line_num,0) retention_line_num,
nvl(dii.retention_rule_id,-1) retention_rule_id,
nvl(dii.retained_amount,0) retained_amount,
dii.amount amount,
dii.credit_amount credit_amount
from pa_draft_invoice_items dii
where project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND invoice_line_type <> 'RETENTION'
AND nvl(dii.credit_amount, 0) <> 0
order by dii.line_num) LOOP
l_amount := inv_lines.amount;
/* insert into bss_conc values (3, p_project_id, p_draft_invoice_num,
'DII', l_retention_rule_id, l_retention_line_num,
l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num, 'RETN');
select nvl(erdl.retn_draft_invoice_line_num, -1) retention_line_num,
nvl(erdl.retention_rule_id,-1) retention_rule_id,
nvl(sum(nvl(erdl.retained_amount,0)),0) retained_amount
from pa_cust_event_rdl_all erdl
where project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND draft_invoice_item_line_num = inv_lines.line_num
group by nvl(erdl.retn_draft_invoice_line_num,-1) , nvl(erdl.retention_rule_id,-1) ) LOOP
l_line_processed := 'TRUE';
/* insert into bss_conc values (4,p_project_id, p_draft_invoice_num,
'ERDL', l_retention_rule_id, l_retention_line_num,
l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num, 'RETN');
select nvl(rdl.retn_draft_invoice_line_num, -1) retention_line_num,
nvl(rdl.retention_rule_id,-1) retention_rule_id,
sum(nvl(rdl.retained_amount,0)) retained_amount
from pa_cust_rev_dist_lines_all rdl
where project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num
AND draft_invoice_item_line_num = inv_lines.line_num
group by nvl(rdl.retn_draft_invoice_line_num,-1) , nvl(rdl.retention_rule_id,-1) ) LOOP
l_line_processed := 'TRUE';
/* insert into bss_conc values (5, p_project_id, p_draft_invoice_num,
'RDL', l_retention_rule_id, l_retention_line_num,
l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num,'RETN');
update pa_draft_invoice_items
set credit_amount =
pa_currency.round_trans_currency_amt(credit_amount, rtrim(l_inv_currency_code)) * -1
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and invoice_line_type = 'RETENTION';
select nvl(threshold_amount,0), nvl(retention_percentage,0), nvl(retention_amount,0)
into l_threshold_amount, l_retention_percentage, l_retention_amount
from pa_proj_retn_rules
where retention_rule_id = p_retention_rule_id;
update pa_draft_invoice_items
set credit_amount = nvl(credit_amount ,0) + l_retn_credit_amount
where project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num
and line_num = p_retention_line_num;