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.EAM_WORKORDERBILLING_PVT SQL Statements

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

Line: 45


PROCEDURE insert_AR_Interface
( 	p_api_version           	IN	NUMBER				,
  	p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE	,
	p_commit	    		IN  	VARCHAR2 := FND_API.G_FALSE	,
	p_validation_level		IN  	NUMBER	:= FND_API.G_VALID_LEVEL_FULL	,
	x_return_status		OUT NOCOPY	VARCHAR2		  	,
	x_msg_count			OUT NOCOPY	NUMBER				,
	x_msg_data			OUT NOCOPY	VARCHAR2			,
	p_ra_line			IN	WO_Billing_RA_Rec_Type
)

IS

l_api_name			CONSTANT VARCHAR2(30)	:= 'insert_AR_Interface';

Line: 79


    SAVEPOINT	insert_AR_Interface_PVT;

Line: 113


     COMMENTED THE FOLLOWING CODE AND ADDED ANOTHER COLUMN 'PRIMARY_SALESREP_NUMBER' IN THE INSERT STATEMENT TO ENTER ALWAYS -3 INTO AR INTERFACE TABLES

    EFFECT : EAM WILL NOT CHECK IF THE 'REQUIRE SALESREP 'OPTION IS SET TO YES/NO
  */


-- validation 2: customer chosen should exist in the table 'hz_cust_accounts'.
	select count(*) into l_count
	from hz_cust_accounts
	where cust_account_id=p_ra_line.customer_id;

Line: 144


	SELECT org_information1, to_number(ORG_INFORMATION3)
	  INTO l_set_of_books, l_ou_id
	  FROM hr_organization_information
	 WHERE org_information_context = 'Accounting Information'
	   AND organization_id = p_ra_line.org_id;

Line: 165


	select unit_of_measure into l_uom_name
	from mtl_units_of_measure
	where uom_code = p_ra_line.uom_code;

Line: 178


select nvl(payment_term_id, 4)
into l_term_id
from hz_cust_accounts
where cust_account_id = p_ra_line.customer_id;

Line: 191


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_ra_line.billed_amount, c1.precision),
                        round(p_ra_line.billed_amount/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_amount
      from
        fnd_currencies c1
      where
        c1.currency_code = p_ra_line.currency_code;

Line: 202


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_ra_line.unit_selling_price, c1.precision),
                        round(p_ra_line.unit_selling_price/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_unit_price
      from
        fnd_currencies c1
      where
        c1.currency_code = p_ra_line.currency_code;

Line: 213


      select decode(c1.minimum_accountable_unit,
                        NULL, round(nvl(p_ra_line.conversion_rate, 1), c1.precision),
                        round(nvl(p_ra_line.conversion_rate,1)/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_conv_rate
      from
        fnd_currencies c1
      where
        c1.currency_code = p_ra_line.currency_code;

Line: 231


		SELECT NAME
        INTO l_batch_source_name
        FROM RA_BATCH_SOURCES_all
        WHERE BATCH_SOURCE_ID=25
		and org_id = l_ou_id;       --For Work Order Billing

Line: 241


/*CBOPPANA 3050249 ALWAYS INSERT VALUE -3 FOR COLUMN 'PRIMARY_SALESREP_NUMBER' */

	insert into ra_interface_lines_all
(interface_line_context,
interface_line_Attribute1,
interface_line_Attribute2,
interface_line_Attribute3,
interface_line_Attribute4,
interface_line_Attribute5,
interface_line_Attribute6,
interface_line_Attribute7,
interface_line_Attribute8,
batch_source_name,
set_of_books_id,
line_type,
description,
currency_code,
amount,
cust_trx_type_name,
cust_trx_type_id,
term_id,
orig_system_bill_customer_id,
orig_system_bill_address_id,
conversion_type,
conversion_date,
conversion_rate,
inventory_item_id,
uom_code,
uom_name,
tax_exempt_flag,
org_id,
quantity,
unit_selling_price,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
primary_salesrep_number)
values
('Work Order Billing',
 p_ra_line.wip_entity_id,
 p_ra_line.wip_entity_name,
 p_ra_line.invoice_num,
 p_ra_line.line_num,
 p_ra_line.work_request,
 null,
 p_ra_line.project_id,
 p_ra_line.task_id,
l_batch_source_name, --bug 3680865
l_set_of_books,
'LINE',
l_desc_of_invoice,
p_ra_line.currency_code,
--p_ra_line.billed_amount,
l_rounded_amount,
'INVOICE',
1,
l_term_id,
p_ra_line.customer_id,
p_ra_line.bill_to_address,
nvl(p_ra_line.conversion_type, 'User'),
p_ra_line.conversion_date,
--nvl(p_ra_line.conversion_rate, 1),
l_rounded_conv_rate,
p_ra_line.billed_inventory_item_id,
p_ra_line.uom_code,
l_uom_name,
'S',
l_ou_id,
p_ra_line.quantity,
l_rounded_unit_price,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate    ,
fnd_global.login_id,
TO_CHAR(-3)
);

Line: 324


insert into RA_INTERFACE_SALESCREDITS_ALL
(interface_line_context,
interface_line_Attribute1,
interface_line_Attribute2,
interface_line_Attribute3,
interface_line_Attribute4,
interface_line_Attribute5,
interface_line_Attribute6,
interface_line_Attribute7,
interface_line_Attribute8,
salesrep_number,
sales_credit_type_name,
sales_credit_percent_split)
values(
'Work Order Billing' ,
 p_ra_line.wip_entity_id,
 p_ra_line.wip_entity_name,
 p_ra_line.invoice_num,
 p_ra_line.line_num,
 p_ra_line.work_request,
 null,
 p_ra_line.project_id,
 p_ra_line.task_id,
'-3',
'Quota Sales Credit',
100);

Line: 367


		ROLLBACK TO insert_AR_Interface_PVT;

Line: 374


		ROLLBACK TO insert_AR_Interface_PVT;

Line: 381


		ROLLBACK TO insert_AR_Interface_PVT;

Line: 395


END insert_AR_Interface;

Line: 399


PROCEDURE insert_WOB_Table
(       p_api_version                   IN      NUMBER                          ,
        p_init_msg_list         IN      VARCHAR2 := FND_API.G_FALSE     ,
        p_commit                        IN      VARCHAR2 := FND_API.G_FALSE     ,
        p_validation_level              IN      NUMBER  :=
                                                FND_API.G_VALID_LEVEL_FULL      ,
        x_return_status         OUT NOCOPY     VARCHAR2                        ,
        x_msg_count                     OUT NOCOPY    NUMBER                          ,
        x_msg_data                      OUT NOCOPY    VARCHAR2                        ,
        p_wob_rec                       IN      WO_Billing_Rec_Type
) IS
l_api_name			CONSTANT VARCHAR2(30)	:= 'insert_WO_Table';

Line: 418


    SAVEPOINT	insert_WO_Table_PVT;

Line: 453


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_wob_rec.billed_amount, c1.precision),
                        round(p_wob_rec.billed_amount/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_amount
      from
        fnd_currencies c1
      where
        c1.currency_code = p_wob_rec.currency_code;

Line: 464


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_wob_rec.cost_or_listprice, c1.precision),
                        round(p_wob_rec.cost_or_listprice/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_unit_price
      from
        fnd_currencies c1
      where
        c1.currency_code = p_wob_rec.currency_code;

Line: 475


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_wob_rec.conversion_rate, c1.precision),
                        round(p_wob_rec.conversion_rate/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
        into l_rounded_conv_rate
      from
        fnd_currencies c1
      where
        c1.currency_code = p_wob_rec.currency_code;

Line: 486


	insert into eam_work_order_bills
(
	 ORGANIZATION_ID               ,
 CUSTOMER_ID                     ,
 BILL_TO_ADDRESS_ID            ,
 WIP_ENTITY_ID                ,
 OPERATION_SEQ_NUM            ,
 INVENTORY_ITEM_ID      ,
 RESOURCE_ID                   ,
 BILLED_INVENTORY_ITEM_ID     ,
 BILLED_UOM_CODE             ,
 BILLED_QUANTITY            ,
 PRICE_LIST_HEADER_ID      ,
 COST_TYPE_ID             ,
 COST_OR_LISTPRICE       ,
 COSTPLUS_PERCENTAGE    ,
 BILLED_AMOUNT         ,
 INVOICE_TRX_NUMBER     ,
 INVOICE_LINE_NUMBER   ,
 CURRENCY_CODE        ,
 CONVERSION_RATE     ,
 CONVERSION_TYPE_CODE      ,
 CONVERSION_RATE_DATE     ,
 PROJECT_ID             ,
 TASK_ID                 ,
 WORK_REQUEST_ID       ,
 PA_EVENT_ID          ,
 BILLING_BASIS,
 BILLING_METHOD,
 LAST_UPDATE_DATE            ,
 LAST_UPDATED_BY            ,
 CREATION_DATE             ,
 CREATED_BY               ,
 LAST_UPDATE_LOGIN
)
values
(
 p_wob_rec.organization_id,
 p_wob_rec.customer_id,
 p_wob_rec.bill_to_address_id,
 p_wob_rec.wip_entity_id,
 p_wob_rec.operation_seq_num,
 p_wob_rec.inventory_item_id,
 p_wob_rec.resource_id,
 p_wob_rec.billed_inventory_item_id,
 p_wob_rec.billed_uom_code,
 p_wob_rec.billed_quantity,
 p_wob_rec.price_list_header_id,
 p_wob_rec.cost_type_id,
-- p_wob_rec.cost_or_listprice,
 l_rounded_unit_price,
 p_wob_rec.costplus_percentage,
-- p_wob_rec.billed_amount,
 l_rounded_amount,
 p_wob_rec.invoice_trx_number,
 p_wob_rec.invoice_line_number,
 p_wob_rec.currency_code,
-- p_wob_rec.conversion_rate,
 l_rounded_conv_rate,
 p_wob_rec.conversion_type_code,
 p_wob_rec.conversion_rate_date,
 p_wob_rec.project_id,
 p_wob_rec.task_id,
 p_wob_rec.work_request_id,
 p_wob_rec.pa_event_id,
 p_wob_rec.billing_basis,
 p_wob_rec.billing_method,
 sysdate,
 fnd_global.user_id,
 sysdate    ,
 fnd_global.user_id,
 fnd_global.login_id
);

Line: 574


		ROLLBACK TO insert_WO_Table_PVT;

Line: 581


		ROLLBACK TO insert_WO_Table_PVT;

Line: 588


		ROLLBACK TO insert_WO_Table_PVT;

Line: 602


END insert_WOB_Table;

Line: 606


PROCEDURE insert_PAEvent_Table
(       p_api_version                   IN      NUMBER
,
        p_init_msg_list         IN      VARCHAR2 := FND_API.G_FALSE     ,
        p_commit                        IN      VARCHAR2 := FND_API.G_FALSE
,
        p_validation_level              IN      NUMBER  :=
                                                FND_API.G_VALID_LEVEL_FULL
,
        x_return_status         OUT NOCOPY    VARCHAR2                        ,
        x_msg_count                     OUT NOCOPY     NUMBER
,
        x_msg_data                      OUT NOCOPY    VARCHAR2
,
        p_pa_rec                       IN      WO_Billing_PA_Event_Rec_Type
)
IS

l_api_name			CONSTANT VARCHAR2(30)	:= 'insert_PAEvent_Table';

Line: 656


    SAVEPOINT	insert_PAEvent_Table_PVT;

Line: 676


	SELECT NVL(MAX(Event_Num) , 0) + 1 into l_event_num
  	FROM pa_events
  	WHERE Project_ID = p_pa_rec.project_id
  	AND (( Task_ID IS NULL and p_pa_rec.task_id is null) OR Task_ID = p_pa_rec.task_id);

Line: 683


	SELECT pa_events_s.nextval into l_event_id
    	FROM   dual;

Line: 724


      select decode(c1.minimum_accountable_unit,
                        NULL, round(p_pa_rec.bill_trans_bill_amount, c1.precision),
                        round(p_pa_rec.bill_trans_bill_amount/c1.minimum_accountable_unit)
                        * c1.minimum_accountable_unit )
	into l_rounded_amount
      from
        fnd_currencies c1
      where
        c1.currency_code = p_pa_rec.billing_currency_code;

Line: 736


	-- insert into pa_events
	insert into pa_events
	(
	task_id,
	event_num,
	event_type,
	description,
	bill_amount,
	revenue_amount,
	revenue_distributed_flag,
	bill_hold_flag,
	project_id,
	organization_id,
	calling_place,
	calling_process,
	event_id,
	reference1,
	reference2,
	reference3,
	reference4,
	billed_flag,
	bill_trans_currency_code,
	bill_trans_bill_amount,
	bill_trans_rev_amount,
	project_currency_code,
	projfunc_currency_code,
	funding_rate_type,
	funding_rate_date,
	funding_exchange_rate,
	revproc_currency_code,
	invproc_currency_code,
	completion_date,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login)
	values
	(
	p_pa_rec.task_id,
	p_pa_rec.event_num,
	'Manual',
	'Event for Work Order #' || p_pa_rec.wip_entity_id || ', ' || p_pa_rec.wip_entity_name,
	0,
	0,
	'N',
	'N',
	p_pa_rec.project_id,
	p_pa_rec.organization_id,
	'EAM',
	'Work Order Billing',
	p_pa_rec.event_id,
	p_pa_rec.wip_entity_id,
	p_pa_rec.wip_entity_name,
	p_pa_rec.work_request_id,
	p_pa_rec.service_request_id,
	'N',
	p_pa_rec.billing_currency_code,
	--p_pa_rec.bill_trans_bill_amount,
	--p_pa_rec.bill_trans_rev_amount,
	l_rounded_amount,
	l_rounded_amount,
        l_project_currency_code,
        l_projfunc_currency_code,
        l_funding_rate_type,
        l_funding_rate_date,
        l_funding_exchange_rate,
        l_revproc_currency_code,
        l_invproc_currency_code,
	sysdate,
 	sysdate    ,
 	fnd_global.user_id,
 	sysdate,
 	fnd_global.user_id,
 	fnd_global.login_id
	);

Line: 813


	--dbms_output.put_line('finished inserting');

Line: 828


		ROLLBACK TO insert_PAEvent_Table_PVT;

Line: 835


		ROLLBACK TO insert_PAEvent_Table_PVT;

Line: 842


		ROLLBACK TO insert_PAEvent_Table_PVT;

Line: 856


END insert_PAEvent_Table;