eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_MC_UPG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 94


   SELECT org_id
   FROM   PA_IMPLEMENTATIONS_ALL
   WHERE  set_of_books_id = G_Pri_SOB_ID;

Line: 99


   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;

Line: 115


 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;

Line: 224


  (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;

Line: 263


/** 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;

Line: 280


   G_Err_Stack := G_Err_Stack || '->insert_temp_rates';

Line: 281


   G_Err_Stage := 'Entering insert_temp_rates';

Line: 294


END insert_temp_rates;

Line: 388


/** 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);

Line: 410


   G_Err_Stack := G_Err_Stack || '->Insert_History_Rec';

Line: 412


   G_Err_Stage:= 'Inside Insert_History_Rec ' ||x_Table_Name ;

Line: 420


END Insert_History_Rec;

Line: 593


PROCEDURE	Insert_Recs (		x_Table_Name	IN	VARCHAR2)
IS
v_Old_Stack VARCHAR2(650);

Line: 598


   G_Err_Stack := G_Err_Stack || '->Insert_Recs ';

Line: 600


   G_Err_Stage:= 'Inserting  Records: ' ||x_Table_Name;

Line: 602


END Insert_Recs;

Line: 606


PROCEDURE	Update_Recs (		x_Table_Name	IN	VARCHAR2)
IS
v_Old_Stack VARCHAR2(650);

Line: 611


   G_Err_Stack := G_Err_Stack || '->Update_Recs ';

Line: 613


   G_Err_Stage:= 'Starting Update_Recs: ' ||x_Table_Name;

Line: 615


END Update_Recs;

Line: 618


PROCEDURE	Insert_CDL
IS
v_Old_Stack VARCHAR2(650);

Line: 623


   G_Err_Stack := G_Err_Stack || '->Insert_CDL';

Line: 625


   G_Err_Stage:= 'Inside Insert_CDL ';

Line: 644


END Insert_CDL;

Line: 647


PROCEDURE	Insert_CRDL
IS
v_Old_Stack VARCHAR2(650);

Line: 653


   G_Err_Stack := G_Err_Stack || '->Insert_CRDL';

Line: 655


   G_Err_Stage:= 'Inside Insert_CRDL ';

Line: 663


END Insert_CRDL;

Line: 666


PROCEDURE	Insert_ERDL
IS
v_Old_Stack VARCHAR2(650);

Line: 671


   G_Err_Stack := G_Err_Stack || '->Insert_ERDL';

Line: 673


   G_Err_Stage:= 'Inside Insert_ERDL ';

Line: 680


END Insert_ERDL;

Line: 683


PROCEDURE	Insert_DR
IS
v_Old_Stack VARCHAR2(650);

Line: 689


   G_Err_Stack := G_Err_Stack || '->Insert_DR';

Line: 691


   G_Err_Stage:= 'Inside Insert_DR ';

Line: 698


END Insert_DR;

Line: 701


PROCEDURE	Insert_Event
IS
v_Old_Stack VARCHAR2(650);

Line: 706


   G_Err_Stack := G_Err_Stack || '->Insert_Event';

Line: 708


   G_Err_Stage:= 'Inside Insert_Event ';

Line: 716


END Insert_Event;

Line: 719


PROCEDURE	Insert_AL
IS
v_Old_Stack VARCHAR2(650);

Line: 724


   G_Err_Stack := G_Err_Stack || '->Insert_AL';

Line: 726


   G_Err_Stage:= 'Inside Insert_AL ';

Line: 733


END Insert_AL;

Line: 736


PROCEDURE	Insert_ALD
IS
v_Old_Stack VARCHAR2(650);

Line: 741


   G_Err_Stack := G_Err_Stack || '->Insert_ALD';

Line: 743


   G_Err_Stage:= 'Inside Insert_ALD';

Line: 751


END Insert_ALD;

Line: 754


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;

Line: 781


    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;

Line: 805


   G_Err_Stack := G_Err_Stack || '->Insert_DINV';

Line: 807


   G_Err_Stage:= 'Inside Insert_DINV ';

Line: 823


   	   Write_Log ('Exitting Insert_DINV');

Line: 826


END Insert_DINV;

Line: 829


PROCEDURE Insert_exp_items(	x_Project_ID	IN	Number,
				x_Rep_SOB_ID	IN	Number)
IS
l_raw_cost                Number := 0;

Line: 869


	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;

Line: 910


	        and  	Task_id IN (	Select task_id
				        From	PA_Tasks
				        Where	Project_ID = x_Project_ID );

Line: 917


   G_Err_Stack := G_Err_Stack || '->Insert_exp_items';

Line: 919


   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||']';

Line: 931


END Insert_exp_items;

Line: 934


PROCEDURE	Update_CDL
IS
v_Old_Stack VARCHAR2(650);

Line: 939


   G_Err_Stack := G_Err_Stack || '->Update_CDL';

Line: 941


   G_Err_Stage:= 'Inside Update_CDL ';

Line: 947


END Update_CDL;

Line: 950


PROCEDURE	Update_CRDL
IS
v_Old_Stack VARCHAR2(650);

Line: 955


   G_Err_Stack := G_Err_Stack || '->Update_CRDL';

Line: 957


   G_Err_Stage:= 'Inside Update_CRDL ';

Line: 959


END Update_CRDL;

Line: 961


PROCEDURE	Update_ERDL
IS
v_Old_Stack VARCHAR2(650);

Line: 966


   G_Err_Stack := G_Err_Stack || '->Update_ERDL';

Line: 968


   G_Err_Stage:= 'Inside Update_ERDL ';

Line: 970


END Update_ERDL;

Line: 973


PROCEDURE	Update_DR
IS
BEGIN
G_Err_Code := 0;

Line: 977


END Update_DR;

Line: 980


PROCEDURE	Update_Event
IS
BEGIN
G_Err_Code := 0;

Line: 984


END Update_Event;

Line: 987


PROCEDURE	Update_AL
IS
BEGIN
G_Err_Code := 0;

Line: 991


END Update_AL;

Line: 994


PROCEDURE	Update_ALD
IS
BEGIN

G_Err_Code := 0;

Line: 1000


END Update_ALD;

Line: 1003


PROCEDURE	Update_DINV
IS
BEGIN

G_Err_Code := 0;

Line: 1009


END Update_DINV;

Line: 1011


PROCEDURE update_exp_items (	x_Project_ID 	IN	Number,
				x_Rep_SOB_ID	IN	Number)
IS

prev_orig_ei Number := 0;

Line: 1030


   G_Err_Stack := G_Err_Stack || '->Update_exp_items';

Line: 1032


   G_Err_Stage:= 'Inside Update_exp_items ';

Line: 1033


END Update_exp_items ;

Line: 1161


PROCEDURE           Insert_CCDL
IS
	l_rep_rsob_id                 PA_PLSQL_DATATYPES.IDTabTyp;

Line: 1186


   G_Err_Stack := G_Err_Stack || '->Insert_CCDL';

Line: 1188


   G_Err_Stage:= 'Inside Insert_CCDL ';

Line: 1195


END Insert_CCDL;

Line: 1197


PROCEDURE	Update_CCDL
IS
BEGIN
G_Err_Code := 0;

Line: 1201


END Update_CCDL;

Line: 1209


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;

Line: 1234


PROCEDURE	Insert_DINVDTLS

IS

I Integer;

Line: 1247


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;

Line: 1273


   G_Err_Stack := G_Err_Stack || '->Insert_DINVDTLS';

Line: 1275


   G_Err_Stage:= 'Inside Insert_DINVDTLS';

Line: 1281


END Insert_DINVDTLS;

Line: 1285


PROCEDURE	Update_DINVDTLS

IS

BEGIN

G_Err_Code := 0;

Line: 1292


END Update_DINVDTLS;

Line: 1306


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;

Line: 1345


Procedure Insert_CC_CDL
IS

v_old_stack     Varchar2(2000);

Line: 1350


/** Create the array element to store the CDL records to be inserted **/

l_expenditure_item_id   PA_PLSQL_DATATYPES.IdTabTyp;

Line: 1370


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;

Line: 1410


   G_Err_Stack := G_Err_Stack || '->Insert_CC_CDL';

Line: 1412


   G_Err_Stage:= 'Inside Insert_CC_CDL';

Line: 1419


END Insert_CC_CDL;

Line: 1421


PROCEDURE	Update_CC_CDL
IS
v_Old_Stack VARCHAR2(650);

Line: 1426


   G_Err_Stack := G_Err_Stack || '->Update_CC_CDL';

Line: 1428


   G_Err_Stage:= 'Inside Update_CC_CDL ';

Line: 1433


END Update_CC_CDL;

Line: 1435


PROCEDURE insert_CC_exp_items(	x_Project_ID	IN	Number,
				x_Rep_SOB_ID	IN	Number)
IS
l_raw_cost                      PA_PLSQL_DATATYPES.NumTabtyp;

Line: 1466


	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);

Line: 1487


   G_Err_Stack := G_Err_Stack || '->Insert_cc_exp_items';

Line: 1489


   G_Err_Stage:= 'Inside Insert_CC_exp_items ';

Line: 1499


	Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');

Line: 1503


      G_Err_Stage  := 'Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']';

Line: 1504


        Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');

Line: 1507


END Insert_CC_exp_items;

Line: 1509


PROCEDURE update_cc_exp_items (	x_Project_ID 	IN	Number,
				x_Rep_SOB_ID	IN	Number)
IS

prev_orig_ei Number := 0;

Line: 1524


   G_Err_Stack := G_Err_Stack || '->Update_cc_exp_items';

Line: 1526


   G_Err_Stage:= 'Inside Update_cc_exp_items ';

Line: 1527


END Update_cc_exp_items ;

Line: 1529


PROCEDURE           Insert_CC_CCDL
IS
	l_rep_rsob_id                 PA_PLSQL_DATATYPES.IDTabTyp;

Line: 1556


   G_Err_Stack := G_Err_Stack || '->Insert_CC_CCDL';

Line: 1558


   G_Err_Stage:= 'Inside Insert_CC_CCDL ';

Line: 1566


END Insert_CC_CCDL;