The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM PA_IMPLEMENTATIONS_ALL
WHERE set_of_books_id = G_Pri_SOB_ID;
SELECT Proj.Project_ID
FROM PA_PROJECT_STATUSES ST,PA_PROJECTS PROJ
WHERE PROJ.segment1 between G_From_Prj_Num and G_To_Prj_Num
AND (G_First_MRC_Period_Flag = 'N'
OR
(G_First_MRC_Period_Flag = 'Y'
AND
(x_include_closed_prj = 'Y'
OR
(x_include_closed_prj = 'N'
AND ST.project_system_status_code <> 'CLOSED'))))
AND ST.Project_Status_Code = Proj.Project_status_code
AND ST.status_type = 'PROJECT'
Order BY Proj.Project_ID;
SELECT EI.expenditure_item_id
FROM PA_EXPENDITURE_ITEMS_ALL EI,
PA_IMPLEMENTATIONS_ALL IMP1,
PA_IMPLEMENTATIONS_ALL IMP2
WHERE EI.ORG_ID = G_Org_ID
AND EI.ORG_ID <> EI.RECVR_ORG_ID
AND IMP1.ORG_ID = EI.ORG_ID
AND IMP1.set_of_books_id = G_Pri_SOB_ID
AND IMP2.ORG_ID = EI.RECVR_ORG_ID
AND IMP1.SET_OF_BOOKS_ID <> IMP2.SET_OF_BOOKS_ID;
(SELECT distinct denom_currency_code
from pa_expenditure_items_all pei
where not exists (select null
from PA_MC_UPGRADE_RATES
where From_currency = pei.denom_currency_code
and To_Currency = G_Rep_Curr_Code
and Exchange_date = G_Fixed_Date
and Rate_Type = G_Fixed_Type)
and denom_currency_code <> G_Rep_Curr_Code
UNION
SELECT distinct bill_trans_currency_code
from pa_events pevt
where not exists (select null
from PA_MC_UPGRADE_RATES
where From_currency = pevt.bill_trans_currency_code
and To_Currency = G_Rep_Curr_Code
and Exchange_date = G_Fixed_Date
and Rate_Type = G_Fixed_Type)
and bill_trans_currency_code <> G_Rep_Curr_Code)
order by 1;
/** insert_temp_rates : Procedure to Insert a transaction currency along with its
Fixed rate on the Initial MRC Date, into the cache table.
Package modified for Different Conversion Options msundare on 27-06-00
**/
PROCEDURE insert_temp_rates ( x_currency_code IN VARCHAR2 )
IS
v_denominator_rate NUMBER;
G_Err_Stack := G_Err_Stack || '->insert_temp_rates';
G_Err_Stage := 'Entering insert_temp_rates';
END insert_temp_rates;
/** Insert_History_Rec : Procedure to insert a history rec.
x_table_name : Table to insert rec for.
x_Project_ID : Project to insert recoed for.
x_Status : 'CONVERSION' or 'ROUNDING' status
x_Status_Value : 'C' Converted, 'S' In Process or NULL.
G_Err_Code = 0 - Success, -1 - Error ( Abort ).
G_Err_Stage contains the error msg.
Ora Errors will be raised as exceptions.
**/
PROCEDURE Insert_History_Rec ( x_Table_Name IN VARCHAR2,
x_Project_ID IN NUMBER,
x_Status IN VARCHAR2,
x_Status_Value IN VARCHAR2)
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_History_Rec';
G_Err_Stage:= 'Inside Insert_History_Rec ' ||x_Table_Name ;
END Insert_History_Rec;
PROCEDURE Insert_Recs ( x_Table_Name IN VARCHAR2)
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_Recs ';
G_Err_Stage:= 'Inserting Records: ' ||x_Table_Name;
END Insert_Recs;
PROCEDURE Update_Recs ( x_Table_Name IN VARCHAR2)
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Update_Recs ';
G_Err_Stage:= 'Starting Update_Recs: ' ||x_Table_Name;
END Update_Recs;
PROCEDURE Insert_CDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_CDL';
G_Err_Stage:= 'Inside Insert_CDL ';
END Insert_CDL;
PROCEDURE Insert_CRDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_CRDL';
G_Err_Stage:= 'Inside Insert_CRDL ';
END Insert_CRDL;
PROCEDURE Insert_ERDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_ERDL';
G_Err_Stage:= 'Inside Insert_ERDL ';
END Insert_ERDL;
PROCEDURE Insert_DR
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_DR';
G_Err_Stage:= 'Inside Insert_DR ';
END Insert_DR;
PROCEDURE Insert_Event
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_Event';
G_Err_Stage:= 'Inside Insert_Event ';
END Insert_Event;
PROCEDURE Insert_AL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_AL';
G_Err_Stage:= 'Inside Insert_AL ';
END Insert_AL;
PROCEDURE Insert_ALD
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Insert_ALD';
G_Err_Stage:= 'Inside Insert_ALD';
END Insert_ALD;
Procedure Insert_DINV (x_Project_ID IN NUMBER,
x_Rep_SOB_ID IN NUMBER)
IS
/* added di.canceled_flag orig_canceled_flag, di.invoice_date invoice_date
for bug fix 1924362 */
CURSOR c_dinv ( rprojectid IN Number ) IS
SELECT di.draft_invoice_num draft_invoice_num,
di.draft_invoice_num_credited draft_invoice_num_credited,
NVL(di.unbilled_receivable_dr,0) unbilled_receivable_dr,
NVL(di.unearned_revenue_cr,0) unearned_revenue_cr,
di.write_off_flag write_off_flag ,
di.customer_bill_split customer_bill_split,
NVL(di.retention_percentage,0) retention_percentage,
NVL(di.retention_invoice_flag,'N') retention_invoice_flag, /* added bug2966251 */
dic.canceled_flag canceled_flag,
di.canceled_flag orig_canceled_flag,
di.invoice_date invoice_date
FROM PA_Draft_Invoices dic,
PA_Draft_Invoices di
WHERE di.project_id = rprojectid
AND dic.project_id(+) = di.project_id
AND dic.draft_invoice_num(+) = di.draft_Invoice_num_credited
ORDER BY 1;
SELECT decode(invoice_line_type,'RETENTION',2,1) l_type, line_num,
event_task_id, event_num, invoice_line_type,amount /* added for bug 1946624 */
, bill_trans_currency_code, bill_trans_bill_amount /* MCB2 */
, projfunc_bill_amount, retention_rule_id,invproc_currency_code/* added bug 2966251 */
FROM pa_draft_invoice_items
WHERE project_id = rprojectid
AND draft_invoice_num = rdinvnum
ORDER BY 1,2;
G_Err_Stack := G_Err_Stack || '->Insert_DINV';
G_Err_Stage:= 'Inside Insert_DINV ';
Write_Log ('Exitting Insert_DINV');
END Insert_DINV;
PROCEDURE Insert_exp_items( x_Project_ID IN Number,
x_Rep_SOB_ID IN Number)
IS
l_raw_cost Number := 0;
Select expenditure_item_id, Denom_raw_cost,
quantity,
acct_currency_code,
project_currency_code,
expenditure_item_date,
Denom_burdened_cost, raw_revenue, accrued_revenue,
adjusted_revenue, forecast_revenue,
bill_amount, net_zero_adjustment_flag,
bill_trans_bill_amount,
bill_trans_raw_revenue,
projfunc_inv_rate_date,
projfunc_inv_rate_type,
projfunc_inv_exchange_rate,
projfunc_rev_rate_type,
projfunc_rev_exchange_rate,
projfunc_rev_rate_date,
bill_trans_forecast_revenue,
projfunc_fcst_rate_date,
projfunc_fcst_rate_type,
projfunc_fcst_exchange_rate,
bill_trans_currency_code,
transferred_from_exp_item_id,
denom_transfer_price,
cc_cross_charge_code,
project_exchange_rate,
recvr_org_id,
acct_raw_cost,
acct_burdened_cost,
acct_rate_type,
acct_rate_date,
acct_exchange_rate,
acct_transfer_price,
acct_tp_rate_type,
acct_tp_rate_date,
acct_tp_exchange_rate,
denom_currency_code -- added for MRC enhancement
,org_id
From PA_Expenditure_Items_ALL
Where expenditure_item_id < NVL(G_MIN_exp_item_id,G_MAX_exp_item_id)
And Project_Id = x_Project_ID;
and Task_id IN ( Select task_id
From PA_Tasks
Where Project_ID = x_Project_ID );
G_Err_Stack := G_Err_Stack || '->Insert_exp_items';
G_Err_Stage:= 'Inside Insert_exp_items: Max EI['||G_MAX_exp_item_id||']Min EI['||G_MIN_exp_item_id||
']ProjectId['||x_Project_ID||']';
END Insert_exp_items;
PROCEDURE Update_CDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Update_CDL';
G_Err_Stage:= 'Inside Update_CDL ';
END Update_CDL;
PROCEDURE Update_CRDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Update_CRDL';
G_Err_Stage:= 'Inside Update_CRDL ';
END Update_CRDL;
PROCEDURE Update_ERDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Update_ERDL';
G_Err_Stage:= 'Inside Update_ERDL ';
END Update_ERDL;
PROCEDURE Update_DR
IS
BEGIN
G_Err_Code := 0;
END Update_DR;
PROCEDURE Update_Event
IS
BEGIN
G_Err_Code := 0;
END Update_Event;
PROCEDURE Update_AL
IS
BEGIN
G_Err_Code := 0;
END Update_AL;
PROCEDURE Update_ALD
IS
BEGIN
G_Err_Code := 0;
END Update_ALD;
PROCEDURE Update_DINV
IS
BEGIN
G_Err_Code := 0;
END Update_DINV;
PROCEDURE update_exp_items ( x_Project_ID IN Number,
x_Rep_SOB_ID IN Number)
IS
prev_orig_ei Number := 0;
G_Err_Stack := G_Err_Stack || '->Update_exp_items';
G_Err_Stage:= 'Inside Update_exp_items ';
END Update_exp_items ;
PROCEDURE Insert_CCDL
IS
l_rep_rsob_id PA_PLSQL_DATATYPES.IDTabTyp;
G_Err_Stack := G_Err_Stack || '->Insert_CCDL';
G_Err_Stage:= 'Inside Insert_CCDL ';
END Insert_CCDL;
PROCEDURE Update_CCDL
IS
BEGIN
G_Err_Code := 0;
END Update_CCDL;
SELECT TYP.CC_PRVDR_FLAG
FROM PA_PROJECTS PROJ,
PA_PROJECT_TYPES TYP
WHERE TYP.PROJECT_TYPE = PROJ.PROJECT_TYPE
AND PROJ.PROJECT_ID = p_PROJECT_ID;
PROCEDURE Insert_DINVDTLS
IS
I Integer;
SELECT DINVDTLS.DRAFT_INVOICE_DETAIL_ID INVOICE_DETAIL_ID,
DINVDTLS.EXPENDITURE_ITEM_ID EI_ID,
DINVDTLS.LINE_NUM LINE_NUM,
DINVDTLS.PROJECT_ID PROJECT_ID,
DINVDTLS.DENOM_CURRENCY_CODE DENOM_CURRENCY_CODE,
DINVDTLS.DENOM_BILL_AMOUNT DENOM_BILL_AMOUNT,
DINVDTLS.INVOICED_FLAG INVOICED_FLAG,
DINVDTLS.ACCT_CURRENCY_CODE ACCT_CURRENCY_CODE,
DINVDTLS.BILL_AMOUNT BILL_AMOUNT,
DINVDTLS.ACCT_RATE_TYPE ACCT_RATE_TYPE,
DINVDTLS.ACCT_RATE_DATE ACCT_RATE_DATE,
DINVDTLS.ACCT_EXCHANGE_RATE ACCT_EXCHANGE_RATE,
DINVDTLS.CC_PROJECT_ID CC_PROJECT_ID,
DINVDTLS.CC_TAX_TASK_ID CC_TAX_TASK_ID,
EI.Expenditure_item_date EI_DATE
FROM PA_DRAFT_INVOICE_DETAILS_ALL DINVDTLS,
PA_EXPENDITURE_ITEMS_ALL EI
WHERE DINVDTLS.project_id = G_Project_ID
AND DINVDTLS.draft_invoice_detail_id <
NVL(G_MIN_DRAFT_INV_DTL_ID,G_MAX_DRAFT_INV_DTL_ID)
AND EI.expenditure_item_id = DINVDTLS.EXPENDITURE_ITEM_ID;
G_Err_Stack := G_Err_Stack || '->Insert_DINVDTLS';
G_Err_Stage:= 'Inside Insert_DINVDTLS';
END Insert_DINVDTLS;
PROCEDURE Update_DINVDTLS
IS
BEGIN
G_Err_Code := 0;
END Update_DINVDTLS;
SELECT IMP1.set_of_books_id,
IMP2.set_of_books_id
FROM PA_IMPLEMENTATIONS_ALL IMP1,
PA_IMPLEMENTATIONS_ALL IMP2
WHERE IMP1.org_id = p_prvdr_org_id
AND IMP2.org_id = p_recvr_org_id;
Procedure Insert_CC_CDL
IS
v_old_stack Varchar2(2000);
/** Create the array element to store the CDL records to be inserted **/
l_expenditure_item_id PA_PLSQL_DATATYPES.IdTabTyp;
SELECT CDL.expenditure_item_id EI_ID,
CDL.line_num line_num,
CDL.line_type line_type,
decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))-G_MRC_LED),
-1,
CDL.transfer_status_code,'P') transfer_status_code,
Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
EI.expenditure_item_date,
CDL.Denom_Raw_Cost, CDL.Acct_Raw_Cost,'N') Amount,
CDL.quantity quantity,
decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))
-G_MRC_LED),-1,CDL.transferred_date,null) transferred_date,
CDL.transfer_rejection_reason rejection_reason,
decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))
-G_MRC_LED),-1,
NULL,'CONVERTED') Batch_name,
/*burdening enhancements*/
--Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
-- EI.expenditure_item_date,
-- CDL.Denom_burdened_cost,CDL.Acct_Burdened_Cost,'N')
Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
EI.expenditure_item_date,
CDL.Denom_burdened_cost+NVL(CDL.Denom_burdened_change,0)
,CDL.Acct_Burdened_Cost+NVL(CDL.Acct_Burdened_Change,0),'N')
burdened_cost,
Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
EI.expenditure_item_date,1,1,'Y') exchange_rate,
decode (G_Use_Curr_rate,'N',G_Fixed_Date,
'Y', decode(sign(EI.expenditure_item_date - G_MRC_LED),-1 ,
G_Fixed_Date, EI.expenditure_item_date)
) conversion_date
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
PA_EXPENDITURE_ITEMS_ALL EI
WHERE CDL.expenditure_item_id = G_EI_Array(k)
AND CDL.line_type <> 'I' -- burdening enhancements
AND EI.expenditure_item_id = CDL.expenditure_item_id;
G_Err_Stack := G_Err_Stack || '->Insert_CC_CDL';
G_Err_Stage:= 'Inside Insert_CC_CDL';
END Insert_CC_CDL;
PROCEDURE Update_CC_CDL
IS
v_Old_Stack VARCHAR2(650);
G_Err_Stack := G_Err_Stack || '->Update_CC_CDL';
G_Err_Stage:= 'Inside Update_CC_CDL ';
END Update_CC_CDL;
PROCEDURE insert_CC_exp_items( x_Project_ID IN Number,
x_Rep_SOB_ID IN Number)
IS
l_raw_cost PA_PLSQL_DATATYPES.NumTabtyp;
Select expenditure_item_id,
Denom_raw_cost,
quantity,
Denom_burdened_cost,
raw_revenue,
accrued_revenue,
adjusted_revenue,
forecast_revenue,
bill_amount,
net_zero_adjustment_flag,
transferred_from_exp_item_id,
denom_transfer_price,
cc_cross_charge_code,
project_exchange_rate,
recvr_org_id
From PA_Expenditure_Items_ALL
Where expenditure_item_id = G_EI_Array(k);
G_Err_Stack := G_Err_Stack || '->Insert_cc_exp_items';
G_Err_Stage:= 'Inside Insert_CC_exp_items ';
Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');
G_Err_Stage := 'Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']';
Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');
END Insert_CC_exp_items;
PROCEDURE update_cc_exp_items ( x_Project_ID IN Number,
x_Rep_SOB_ID IN Number)
IS
prev_orig_ei Number := 0;
G_Err_Stack := G_Err_Stack || '->Update_cc_exp_items';
G_Err_Stage:= 'Inside Update_cc_exp_items ';
END Update_cc_exp_items ;
PROCEDURE Insert_CC_CCDL
IS
l_rep_rsob_id PA_PLSQL_DATATYPES.IDTabTyp;
G_Err_Stack := G_Err_Stack || '->Insert_CC_CCDL';
G_Err_Stage:= 'Inside Insert_CC_CCDL ';
END Insert_CC_CCDL;