[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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';
SAVEPOINT insert_AR_Interface_PVT;
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;
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;
select unit_of_measure into l_uom_name
from mtl_units_of_measure
where uom_code = p_ra_line.uom_code;
SELECT
site.site_use_id
INTO
l_site_use_id
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE
site.site_use_code = 'BILL_TO'
and site.status='A'
and site.cust_acct_site_id = acct_site.cust_acct_site_id
and site.status = acct_site.status
and acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc.location_id
and acct_site.cust_account_id = cust_acct.cust_account_id
and acct_site.status = cust_acct.status
and cust_acct.cust_account_id = p_ra_line.customer_id
and site.cust_acct_site_id = p_ra_line.bill_to_address
and NVL(SITE.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
-99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
-99)
and NVL(ACCT_SITE.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
-99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
-99);
select mp.master_organization_id
into l_master_org_id
from mtl_parameters mp
where organization_id =p_ra_line.org_id;
select nvl(su.payment_term_id,
decode(spt.billing_cycle_id,
-- if cycle is NULL
NULL, nvl(sp.standard_terms,
decode(apt.billing_cycle_id,
-- if cycle is NULL
NULL, nvl(ap.standard_terms , -94) ,
-- if cycle is NOT NULL
-92)),
-- if cycle is NOT NULL
decode(ap.cons_bill_level,
-- if bill level = Account
'ACCOUNT', nvl(ap.standard_terms, -93),
-- if bill level = Site
'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
-- if bill level is not set
, -91)))
into l_term_id
from --ra_cust_trx_types tt,
hz_customer_profiles ap,
hz_cust_site_uses_all su,
ra_terms_b apt,
ra_terms_b spt,
( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
su2.site_use_id profile_bill_to_site_use_id
from hz_customer_profiles cp,
hz_cust_site_uses_all su1,
hz_cust_site_uses_all su2
where cp.site_use_id = su1.site_use_id
and cp.status ='A'
and su1.cust_acct_site_id =su2.cust_acct_site_id
and su2.site_use_code = 'BILL_TO'
) sp
where --p_trx_type_id = tt.cust_trx_type_id and
--p_org_id = tt.org_id and
l_site_use_id = su.site_use_id
and p_ra_line.customer_id = ap.cust_account_id
and ap.site_use_id is null
and p_ra_line.customer_id = sp.cust_account_id (+)
and su.site_use_id = sp.profile_bill_to_site_use_id (+)
and ap.standard_terms = apt.term_id (+)
and sysdate between nvl(apt.start_date_active, sysdate) and
nvl(apt.end_date_active, sysdate)
and sp.standard_terms = spt.term_id (+)
and sysdate between nvl(spt.start_date_active, sysdate) and
nvl(spt.end_date_active, sysdate);
select rt.name
into l_term_name
from hz_cust_accounts hca,
ra_terms rt
where nvl(hca.payment_term_id,4) = rt.term_id
and cust_account_id = p_ra_line.customer_id;
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;
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;
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;
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
/*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,
term_name,
--12737435
warehouse_id)
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,
p_ra_line.unit_selling_price, /*bug 8836415, validation 3 fails in AR if rounded value passed*/
--l_rounded_unit_price,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate ,
fnd_global.login_id,
TO_CHAR(-3),
l_term_name, -- Added for bug 6037087
--12737435
l_warehouse_id
);
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,
ORG_ID)
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,
l_ou_id);
ROLLBACK TO insert_AR_Interface_PVT;
ROLLBACK TO insert_AR_Interface_PVT;
ROLLBACK TO insert_AR_Interface_PVT;
END insert_AR_Interface;
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';
SAVEPOINT insert_WO_Table_PVT;
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;
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;
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;
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
);
ROLLBACK TO insert_WO_Table_PVT;
ROLLBACK TO insert_WO_Table_PVT;
ROLLBACK TO insert_WO_Table_PVT;
END insert_WOB_Table;
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';
SAVEPOINT insert_PAEvent_Table_PVT;
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);
SELECT pa_events_s.nextval into l_event_id
FROM dual;
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;
-- 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
);
--dbms_output.put_line('finished inserting');
ROLLBACK TO insert_PAEvent_Table_PVT;
ROLLBACK TO insert_PAEvent_Table_PVT;
ROLLBACK TO insert_PAEvent_Table_PVT;
END insert_PAEvent_Table;