[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from pa_draft_invoice_details did
where project_id = p_proj_id
and draft_invoice_num = p_inv_num
and not exists
( select 'x'
from pa_draft_invoice_details did1
where did1.project_id = p_proj_id
and did1.detail_id_reversed = did.draft_invoice_detail_id) ;
l_user_id := pa_ic_inv_utils.g_last_update_login;
Invoice generation process and other processes that update invoice details.
*/
IF (pa_ic_inv_utils.Set_User_Lock (P_Project_Id) <> 0) THEN
IF g1_debug_mode = 'Y' THEN
pa_ic_inv_utils.log_message('cancel_invoice: ' || 'unable to acquire lock');
update the Invoice comment to indicate that Invoice is canceled
provided it satisfies the following criteria
+ Invoice has not been canceled earlier
+ Credit memo has not been generated for the Invoice
*/
-- This update statement also returns the agreement_id
--
Update pa_draft_invoices
Set canceled_flag = 'Y',
invoice_comment =
(select rtrim(upper(l.meaning)||' '||
rtrim(substrb(i.invoice_comment,1,232)))
from pa_lookups l,
pa_draft_invoices i
where i.project_id = p_project_id
and i.draft_invoice_num = p_draft_inv_num
and l.lookup_type = 'INVOICE_CREDIT_TYPE'
AND l.lookup_code = 'CANCEL'
) ,
last_update_date = SYSDATE,
last_update_login = l_user_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id
where project_id = P_PROJECT_ID
and draft_invoice_num = P_DRAFT_INV_NUM
and nvl(canceled_flag, 'N') <> 'Y'
and not exists
(
select null
from pa_draft_invoices di
where di.project_id = P_PROJECT_ID
and di.draft_invoice_num_credited = P_DRAFT_INV_NUM
)
returning agreement_id into l_agreement_id;
/* If no rows are updated then raise the exception
so that the ineligibility criteria is reported.
*/
if SQL%ROWCOUNT = 0 then
IF g1_debug_mode = 'Y' THEN
pa_ic_inv_utils.log_message('cancel_invoice: ' || 'raising error');
Issue: As part of the MOAC changes, the org_id insertion is missing
Fix : Org Id is inserted for the new invoices */
INSERT INTO PA_DRAFT_INVOICES
(
DRAFT_INVOICE_NUM,
PROJECT_ID,
AGREEMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSFER_STATUS_CODE,
PA_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BILL_THROUGH_DATE,
WRITE_OFF_FLAG,
UNEARNED_REVENUE_CR,
UNBILLED_RECEIVABLE_DR,
INVOICE_COMMENT,
DRAFT_INVOICE_NUM_CREDITED,
CANCEL_CREDIT_MEMO_FLAG,
APPROVED_BY_PERSON_ID,
APPROVED_DATE,
GENERATION_ERROR_FLAG,
TRANSFER_REJECTION_REASON,
RETENTION_PERCENTAGE,
INV_CURRENCY_CODE,
INV_RATE_TYPE,
INV_RATE_DATE,
INV_EXCHANGE_RATE,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
LANGUAGE,
CC_PROJECT_ID,
CC_INVOICE_GROUP_CODE ,
INVPROC_CURRENCY_CODE,
CUSTOMER_ID,
BILL_TO_CUSTOMER_ID,
SHIP_TO_CUSTOMER_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID /*Added for 2760630*/,
PROJFUNC_INVTRANS_RATE_TYPE, /* Added below columns for bug 4500281*/
PROJFUNC_INVTRANS_RATE_DATE,
PROJFUNC_INVTRANS_EX_RATE,
ORG_ID
)
SELECT l_new_draft_inv_num,
I2.PROJECT_ID,
I2.AGREEMENT_ID,
TRUNC(SYSDATE),
l_user_id,
TRUNC(SYSDATE),
l_user_id,
'P',
TRUNC(SYSDATE),
l_request_id,
l_program_application_id,
l_program_id,
TRUNC(SYSDATE),
I2.BILL_THROUGH_DATE,
NULL,
0,
0,
P.INVOICE_COMMENT,
I2.DRAFT_INVOICE_NUM,
'Y',
NULL,
TRUNC(SYSDATE),
'N',
NULL,
I2.RETENTION_PERCENTAGE,
I2.INV_CURRENCY_CODE,
I2.INV_RATE_TYPE,
I2.INV_RATE_DATE,
I2.INV_EXCHANGE_RATE,
I2.BILL_TO_ADDRESS_ID,
I2.SHIP_TO_ADDRESS_ID,
I2.LANGUAGE,
I2.CC_PROJECT_ID,
I2.CC_INVOICE_GROUP_CODE,
I2.INVPROC_CURRENCY_CODE,
I2.CUSTOMER_ID,
I2.BILL_TO_CUSTOMER_ID,
I2.SHIP_TO_CUSTOMER_ID,
I2.BILL_TO_CONTACT_ID,
I2.SHIP_TO_CONTACT_ID,
I2.PROJFUNC_INVTRANS_RATE_TYPE, /* Added below columns for bug 4500281*/
I2.PROJFUNC_INVTRANS_RATE_DATE,
I2.PROJFUNC_INVTRANS_EX_RATE,
I2.ORG_ID
FROM PA_DRAFT_INVOICES I2, PA_PROJECTS P
WHERE I2.DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
AND I2.PROJECT_ID = P_PROJECT_ID
AND P.PROJECT_ID = P_PROJECT_ID;
pa_ic_inv_utils.log_message('Inserting pa_draft_invoices rows = '||
SQL%ROWCOUNT);
The following insert statement will create all the crediting invoice
lines.
*/
INSERT INTO PA_DRAFT_INVOICE_ITEMS
(LINE_NUM,
DRAFT_INVOICE_NUM,
PROJECT_ID,
TASK_ID,
AMOUNT,
INV_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TEXT,
EVENT_TASK_ID,
EVENT_NUM,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
UNEARNED_REVENUE_CR,
UNBILLED_RECEIVABLE_DR,
DRAFT_INV_LINE_NUM_CREDITED,
INVOICE_LINE_TYPE,
TAXABLE_FLAG,
SHIP_TO_ADDRESS_ID,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
CC_PROJECT_ID,
CC_TAX_TASK_ID,
CC_REV_CODE_COMBINATION_ID,
TRANSLATED_TEXT,
invproc_currency_code,
projfunc_currency_code,
projfunc_bill_amount,
project_currency_code,
project_bill_amount,
funding_currency_code,
funding_bill_amount
)
SELECT I2.LINE_NUM,
l_new_draft_inv_num,
I2.PROJECT_ID,
I2.TASK_ID,
-1 * AMOUNT,
-1 * INV_AMOUNT,
TRUNC(SYSDATE),
l_user_id,
TRUNC(SYSDATE),
l_user_id,
I2.TEXT,
I2.EVENT_TASK_ID,
NULL,
l_request_id,
l_program_application_id,
l_program_id,
TRUNC(SYSDATE),
0,
0,
I2.LINE_NUM,
I2.INVOICE_LINE_TYPE,
I2.TAXABLE_FLAG,
I2.SHIP_TO_ADDRESS_ID,
I2.OUTPUT_TAX_CLASSIFICATION_CODE,
I2.OUTPUT_TAX_EXEMPT_FLAG,
I2.OUTPUT_TAX_EXEMPT_REASON_CODE,
I2.OUTPUT_TAX_EXEMPT_NUMBER,
I2.CC_PROJECT_ID,
I2.CC_TAX_TASK_ID,
I2.CC_REV_CODE_COMBINATION_ID,
I2.TRANSLATED_TEXT,
I2.invproc_currency_code,
I2.projfunc_currency_code,
-1 * I2.projfunc_bill_amount,
I2.project_currency_code,
-1 * I2.project_bill_amount,
I2.funding_currency_code,
-1 * I2.funding_bill_amount
FROM PA_DRAFT_INVOICE_ITEMS I2
WHERE I2.PROJECT_ID = P_PROJECT_ID
AND I2.DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM;
pa_ic_inv_utils.log_message('Inserting pa_draft_invoice_items rows = '||
SQL%ROWCOUNT);
but the reversing invoice details will be updated with the new Invoice
number and line number.
*/
/* Open the cursor to fetch the non reversed invoice details of
the original invoice .
For each row create reversing invoice details using the table
handlers.
*/
l_EI_upd_cnt := 0;
/* Store the values in local tables to be used later for EI update */
l_expenditure_item_id (l_EI_upd_cnt)
:= l_inv_detail_rec.expenditure_item_id;
/* Update the existing reversing invoice details ,
these rows will already have the columns orig_draft_invoice_num
and orig_draft_invoice_line_num populated */
UPDATE PA_DRAFT_INVOICE_DETAILS
SET DRAFT_INVOICE_NUM = l_new_draft_inv_num ,
DRAFT_INVOICE_LINE_NUM = ORIG_DRAFT_INVOICE_LINE_NUM,
INVOICED_FLAG = 'Y',
last_update_date = SYSDATE,
last_update_login = l_user_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id
WHERE ORIG_DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
AND INVOICED_FLAG = 'N'
AND PROJECT_ID = P_PROJECT_ID;
/* Update the project summary fundings to reflect the cancellation */
pa_ic_inv_utils.update_SPF(l_new_draft_inv_num,
l_agreement_id,
P_DRAFT_INV_NUM,
'CANCEL');
/* Update the EI's with the transfer price attributes.
Only those EI's corresponding to Invoice details
that have been reversed in the procedure will be Updated.
The others are not updated since the other processes
must have updated the appropriate TP attributes
*/
/* For bug 2968292 Assigned a value of NULL to denom_transfer_price,
acct_transfer_price and projacct_transfer_price */
/* For bug 3857986, added system_linkage_fuction in returning clause of this update statement */
FORALL I IN 1..l_EI_upd_cnt
UPDATE PA_EXPENDITURE_ITEMS
SET DENOM_TP_CURRENCY_CODE = l_DENOM_TP_CURRENCY_CODE(I),
DENOM_TRANSFER_PRICE = NULL ,
ACCT_TP_RATE_TYPE = l_ACCT_TP_RATE_TYPE(I),
ACCT_TP_RATE_DATE = l_ACCT_TP_RATE_DATE(I),
ACCT_TP_EXCHANGE_RATE = l_ACCT_TP_EXCHANGE_RATE(I),
ACCT_TRANSFER_PRICE = NULL,
PROJACCT_TRANSFER_PRICE = NULL,
CC_MARKUP_BASE_CODE = l_CC_MARKUP_BASE_CODE(I),
TP_BASE_AMOUNT = l_TP_BASE_AMOUNT(I),
TP_IND_COMPILED_SET_ID = l_TP_IND_COMPILED_SET_ID(I),
TP_BILL_RATE = l_TP_BILL_RATE(I),
TP_BILL_MARKUP_PERCENTAGE = l_TP_BILL_MARKUP_PERCENTAGE(I),
TP_SCHEDULE_LINE_PERCENTAGE = l_TP_SCHEDULE_LINE_PERCENTAGE(I),
TP_RULE_PERCENTAGE = l_TP_RULE_PERCENTAGE(I),
cc_ic_processed_code = decode(cc_cross_charge_code,'I','N','X'),
last_update_date = SYSDATE,
last_update_login = l_user_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id
WHERE EXPENDITURE_ITEM_ID = L_EXPENDITURE_ITEM_ID(I)
RETURNING expenditure_item_date , system_linkage_function
BULK COLLECT INTO l_ei_date, l_sys_linkage;