[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_Insert_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
P_Delete_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
P_Update_tab PA_INVOICE_DETAIL_PKG.inv_rec_tab;
,p_user_id => PA_IC_INV_UTILS.G_LAST_UPDATED_BY
,p_login_id => PA_IC_INV_UTILS.G_LAST_UPDATE_LOGIN
,p_prvdr_org_id => PA_MC_INVOICE_DETAIL_PKG.G_ORG_ID
,p_primary_sob_id => PA_MC_INVOICE_DETAIL_PKG.G_SOB);
SELECT pa_draft_invoice_details_s.nextval
INTO l_nextval
FROM SYS.DUAL;
PROCEDURE delete_all_cc_dist (P_Draft_Inv_Id IN Number,
P_index IN number)
IS
cursor get_cc_del_lines
is
select rowid,cc_dist_line_id
from pa_cc_dist_lines
where reference_1 = to_char(P_Draft_Inv_Id);
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_all_cc_dist ...');
END delete_all_cc_dist;
PROCEDURE delete_cc_dist (P_CC_REC IN get_cc_dist%rowtype,
P_index IN number)
IS
BEGIN
IF g1_debug_mode = 'Y' THEN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_cc_dist ...');
END delete_cc_dist;
delete_cc_dist (P_CC_REC,P_index);
PROCEDURE update_cc_dist(P_Cdl_rec IN prv_class_rec,
P_CC_rec IN get_cc_dist%rowtype,
P_Index IN NUMBER)
IS
l_pa_date date;
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_cc_dist ...');
END update_cc_dist;
PROCEDURE insert_row ( p_inv_rec IN OUT NOCOPY pa_draft_invoice_details%rowtype )
IS
BEGIN
IF g1_debug_mode = 'Y' THEN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.insert_row ...');
P_Insert_tab(PA_INVOICE_DETAIL_PKG.G_Ins_count) := p_inv_rec;
END insert_row;
PROCEDURE delete_row ( p_inv_rec IN pa_draft_invoice_details%rowtype )
IS
BEGIN
IF g1_debug_mode = 'Y' THEN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.delete_row ...');
P_Delete_tab(PA_INVOICE_DETAIL_PKG.G_Del_count) := p_inv_rec;
END delete_row;
PROCEDURE update_row ( p_inv_rec IN pa_draft_invoice_details%rowtype,
p_mrc_required IN varchar2 )
IS
BEGIN
IF g1_debug_mode = 'Y' THEN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_row ...');
P_Update_tab(PA_INVOICE_DETAIL_PKG.G_Upd_count) := p_inv_rec;
END update_row;
PROCEDURE update_ei
IS
BEGIN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_ei ...');
Update PA_EXPENDITURE_ITEMS
Set CC_IC_PROCESSED_CODE = 'X',
DENOM_TP_CURRENCY_CODE = NULL,
DENOM_TRANSFER_PRICE = NULL,
ACCT_TP_RATE_TYPE = NULL,
ACCT_TP_RATE_DATE = NULL,
ACCT_TP_EXCHANGE_RATE = NULL,
ACCT_TRANSFER_PRICE = NULL,
PROJACCT_TRANSFER_PRICE = NULL,
CC_MARKUP_BASE_CODE = NULL,
TP_BASE_AMOUNT = NULL,
TP_IND_COMPILED_SET_ID = NULL,
TP_BILL_RATE = NULL,
TP_BILL_MARKUP_PERCENTAGE = NULL,
TP_SCHEDULE_LINE_PERCENTAGE = NULL,
TP_RULE_PERCENTAGE = NULL,
TP_JOB_ID = NULL,
PROJECT_TRANSFER_PRICE = NULL,
PROJFUNC_TRANSFER_PRICE = NULL,
PROV_PROJ_BILL_JOB_ID = NULL
where EXPENDITURE_ITEM_ID = P_EI_table(i);
END update_ei;
update_row(p_inv_rec,'N');
insert_row(p_inv_rec); */
SELECT max(line_num) INTO t_line_num
FROM pa_draft_invoice_details_all
WHERE expenditure_item_id = p_inv_rec.expenditure_item_id
AND draft_invoice_num IS NULL
AND DRAFT_INVOICE_LINE_NUM IS NULL
AND NVL(invoiced_flag,'N') ='N';
insert_row(p_inv_rec);
update_row(p_inv_rec,'N');
/** Add the row to the insert buffer **/
I_Line_num := I_Line_num + 1;
insert_row(P_Inv_rec);
update_row(P_Inv_rec,'Y');
/** Add the row to the insert buffer **/
insert_row(l_inv_rec);
Select *
INTO X_IND_REC
FROM PA_DRAFT_INVOICE_DETAILS
WHERE EXPENDITURE_ITEM_ID = P_Expenditure_item_id
AND LINE_NUM = P_Line_num ;
PROCEDURE update_rejection_reason ( P_Error_code
IN PA_PLSQL_DATATYPES.Char30TabTyp,
P_Exp_Id IN PA_PLSQL_DATATYPES.IdTabTyp,
P_No_of_records IN number )
IS
BEGIN
IF g1_debug_mode = 'Y' THEN
PA_IC_INV_UTILS.log_message('Enter pa_invoice_detail_process.update_rejection_reason...');
Update PA_EXPENDITURE_ITEMS
Set CC_REJECTION_CODE = P_Error_code(I)
Where EXPENDITURE_ITEM_ID = P_Exp_Id(I);
END update_rejection_reason;
/* Apply Pending Delete/Update/Insert in PA_DRAFT_INVOICE Details */
PA_INVOICE_DETAIL_PKG.delete_rows(P_Delete_tab);
PA_INVOICE_DETAIL_PKG.update_rows(P_Update_tab,I_MRC_REQD_FLAG);
PA_INVOICE_DETAIL_PKG.insert_rows(P_Insert_tab);
update_ei;
update_rejection_reason( P_Error_code, P_Exp_Id, P_no_of_records );
PA_CC_BL_PROCESS.mass_delete;
PA_CC_BL_PROCESS.mass_update;
PA_CC_BL_PROCESS.mass_insert;
SELECT bill_job_group_id
INTO l_job_group_id
FROM pa_projects_all
WHERE project_id = P_project_id;
/** Select the existing invoice details for EI if exists **/
FOR I in 1..P_No_of_records
LOOP
-- Checck whether any invoice detail exists for that EI
-- If Yes, fetech the latest invoice details of the EI.
BEGIN
L_IND_REC := L_IND_REC_NULL; /*Bug 2296735 */
SELECT DET.*
INTO L_IND_REC
FROM PA_DRAFT_INVOICE_DETAILS DET
WHERE DET.EXPENDITURE_ITEM_ID
= decode(nvl(P_AdjEI_id(I),0),0,P_EI_id(I),P_AdjEI_id(I))
AND DET.LINE_NUM = ( SELECT MAX(I.LINE_NUM)
FROM PA_DRAFT_INVOICE_DETAILS I
WHERE I.EXPENDITURE_ITEM_ID =
DET.EXPENDITURE_ITEM_ID );
SELECT cc_Cross_charge_code
INTO l_cc_Cross_charge_code
FROM pa_expenditure_items
WHERE expenditure_item_id = P_EI_id(I);
SELECT decode(I_PRV_ENABLED(I),'B',(cdl.DENOM_BURDENED_COST+NVL(CDL.DENOM_BURDENED_CHANGE,0)),
'R',cdl.DENOM_RAW_COST),
decode(I_PRV_ENABLED(I),'B',(cdl.ACCT_BURDENED_COST+NVL(CDL.ACCT_BURDENED_CHANGE,0)),
'R',cdl.ACCT_RAW_COST),
cdl.DENOM_CURRENCY_CODE,
cdl.ACCT_CURRENCY_CODE,
cdl.ACCT_RATE_DATE,
cdl.ACCT_RATE_TYPE,
cdl.ACCT_EXCHANGE_RATE,
cdl.LINE_NUM,
cdl.IND_COMPILED_SET_ID
INTO l_denom_transfer_price,
l_amount,
l_denom_tp_currency_code,
l_acct_currency_code,
l_acct_tp_rate_date,
l_acct_tp_rate_type,
l_acct_tp_exchange_rate,
l_line_num,
l_ind_compiled_set_id
FROM pa_cost_distribution_lines_all cdl
WHERE cdl.EXPENDITURE_ITEM_ID = P_EI_id(I)
AND cdl.LINE_NUM_REVERSED IS NULL
AND cdl.LINE_NUM = ( SELECT MAX(cdl1.LINE_NUM)
FROM pa_cost_distribution_lines_all cdl1
WHERE cdl1.EXPENDITURE_ITEM_ID =
cdl.EXPENDITURE_ITEM_ID
AND cdl.LINE_NUM_REVERSED IS NULL
AND cdl1.line_type = 'R' );
select NVL(OVR_COST_IND_RATE_SCH_ID, COST_IND_RATE_SCH_ID)
into l_rate_schedule_id
from pa_tasks
where task_id in
( select task_id
from pa_expenditure_items_all
where expenditure_item_id = P_EI_id(I)
);
/** The existing record is to be deleted **/
delete_row(L_IND_REC);
/** Delete provider reclass entry **/
delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
update_row(L_IND_REC,'N');
update_row(L_IND_REC,'N');
update_cc_dist(L_CDL_REC,L_CC_REC,I);
update_row(L_IND_REC,'N');
delete_row(L_IND_REC);
delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
delete_row(L_IND_REC);
delete_all_cc_dist(L_IND_REC.DRAFT_INVOICE_DETAIL_ID,I);
PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing provider reclass only reversing/delete .2');
PA_IC_INV_UTILS.log_message('process_invoice_details: ' || 'Processing provider reclass only reversing/delete .3');
update_cc_dist(L_CDL_REC,L_CC_REC,I);
PA_INVOICE_DETAIL_PKG.insert_rows(P_Insert_tab);
PA_CC_BL_PROCESS.mass_insert;