[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT glsob.currency_code
,glsob.chart_of_accounts_id
FROM gl_sets_of_books glsob,
hr_organization_information hoi
WHERE glsob.set_of_books_id = hoi.org_information1
AND hoi.org_information_context ||'' = 'Accounting Information'
AND hoi.organization_id = p_organization_id;
SELECT cic.item_cost
FROM cst_item_costs_for_gl_view cic,
mtl_parameters mp
WHERE cic.inventory_item_id = p_item_id
AND cic.organization_id = mp.cost_organization_id
AND cic.inventory_asset_flag = 1
AND mp.organization_id= p_source_organization_id;
SELECT msi.list_price_per_unit *
round(gl_currency_api.get_closest_rate_sql
(p_set_of_books_id,
glsob.currency_code,
trunc(sysdate),
psp.DEFAULT_RATE_TYPE,
30),10)
FROM mtl_system_items msi,
gl_sets_of_books glsob,
hr_organization_information hoi,
po_system_parameters psp
WHERE msi.inventory_item_id = p_item_id
AND hoi.organization_id = p_source_organization_id
AND hoi.org_information_context = 'Accounting Information'
AND msi.organization_id = hoi.organization_id
AND glsob.set_of_books_id = hoi.org_information1;
SELECT round(gl_currency_api.get_closest_rate_sql
(p_set_of_books_id,
glsob.currency_code,
trunc(sysdate),
psp.DEFAULT_RATE_TYPE,
30),10)
FROM gl_sets_of_books glsob,
hr_organization_information hoi,
po_system_parameters psp
WHERE hoi.organization_id = p_source_organization_id
AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND glsob.set_of_books_id = hoi.org_information1;
SELECT asset_inventory
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = NVL(p_destination_subinventory,'X')
AND organization_id = p_destination_organization_id;
SELECT inventory_asset_flag
FROM mtl_system_items
WHERE organization_id = p_destination_organization_id
AND inventory_item_id = p_item_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_system_items
WHERE organization_id = p_destination_organization_id
and inventory_item_id = p_item_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT expense_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_destination_subinventory
AND organization_id = p_destination_organization_id;
SELECT material_account
INTO l_charge_account
FROM mtl_parameters
WHERE organization_id = p_destination_organization_id;
SELECT 'X'
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_ccid
AND gcc.enabled_flag = 'Y'
AND trunc(nvl(p_gl_date,SYSDATE))
BETWEEN trunc(nvl(start_date_active, nvl(p_gl_date,SYSDATE) ))
AND trunc(nvl (end_date_active, SYSDATE+1))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id= p_chart_of_accounts_id
AND gcc.summary_flag = 'N';
SELECT org_information2 ,
org_information3 ,
org_information4
FROM hr_organization_information hou
WHERE hou.organization_id = p_new_org_id
AND hou.org_information1 = 'FIELD_SERVICE'
AND hou.org_information_context = 'CS_USER_CONTEXT';
update po_requisition_lines
set quantity_cancelled = l_line_Tbl(I).quantity,
cancel_flag = 'Y',
cancel_reason = l_line_tbl(I).change_reason,
cancel_date = sysdate
where requisition_line_id = l_line_tbl(I).requisition_line_id;
SELECT org_id
INTO l_source_operating_unit
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_oe_header_id;
l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT org_id
INTO l_source_operating_unit
FROM oe_order_lines_all
WHERE line_id = l_line_tbl(i).order_line_id;
l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
update po_requisition_lines_all
set quantity_cancelled = lx_oe_line_Tbl(I).cancelled_quantity,
cancel_flag = 'Y',
cancel_reason = lx_oe_line_tbl(I).change_reason,
cancel_date = sysdate
where requisition_line_id = lx_oe_line_tbl(I).source_document_line_id;
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_line_id = lx_oe_line_Tbl(I).source_document_line_id;
l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
update mtl_supply
set change_flag = NULL
where supply_type_code = 'REQ'
and req_line_id = lx_oe_line_Tbl(I).source_document_line_id;
SELECT rowid FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id = l_header_rec.requisition_header_id;
SELECT to_char(current_max_unique_identifier + 1)
FROM po_unique_identifier_control
WHERE table_name = 'PO_REQUISITION_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
SELECT po_requisition_headers_s.nextval
FROM sys.dual;
SELECT po_requisition_lines_s.nextval
FROM sys.dual;
SELECT po_req_distributions_s.nextval
FROM sys.dual;
SELECT mp.ap_accrual_account,
mp.invoice_price_var_account
FROM mtl_parameters mp
WHERE mp.organization_id = p_destination_organization_id;
SELECT mic.category_id
FROM mtl_item_categories mic,
mtl_default_sets_view mdsv
WHERE mic.inventory_item_id = p_item_id
AND mic.organization_id = p_destination_org_id
AND mic.category_set_id = mdsv.category_set_id
AND mdsv.functional_area_id = 2;
SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = p_organization_id;
SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
FROM mtl_system_items msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_destination_organization_id
AND mp.organization_id = msi.organization_id;
SELECT nvl (fsp.req_encumbrance_flag,'N')
FROM financials_system_parameters fsp
WHERE fsp.set_of_books_id = p_set_of_books_id;
SELECT NVL(expense_account,-1)
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_destination_organization_id;
SELECT mum.unit_of_measure
FROM mtl_units_of_measure mum
WHERE mum.uom_code = p_uom_code;
SELECT psp.line_type_id,
plt.order_type_lookup_code,
plt.purchase_basis,
plt.matching_basis
FROM PO_SYSTEM_PARAMETERS_ALL psp,
PO_LINE_TYPES plt
WHERE psp.org_id = l_org_id
AND plt.line_type_id = psp.line_type_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT pol.customer_id, pol.site_use_id, cust_acct.cust_account_id
FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
HZ_CUST_ACCT_SITES_ALL cust_acct,
HZ_CUST_SITE_USES_ALL site_use
WHERE pol.location_id = l_header_rec.ship_to_location_id
AND site_use.site_use_id = pol.site_use_id
AND cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
AND pol.org_id = l_source_operating_unit;
SELECT cust_acct.cust_acct_site_id,
pol.customer_id,
(SELECT cust_acct2.party_site_id
FROM HZ_CUST_ACCT_SITES_ALL cust_acct2,
HZ_CUST_SITE_USES_ALL site_use2
WHERE cust_acct2.party_site_id = cust_acct.party_site_id
AND cust_acct2.org_id = l_source_operating_unit
AND cust_acct2.cust_account_id = cust_acct.cust_account_id
AND cust_acct2.cust_acct_site_id = site_use2.cust_acct_site_id
AND site_use2.site_use_code = 'SHIP_TO'
AND site_use2.status = 'A'
AND cust_acct2.status = 'A'
),
pol.org_id
FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL cust_acct
WHERE pol.location_id = l_header_rec.ship_to_location_id
AND pol.site_use_id = site_use.site_use_id
AND site_use.cust_acct_site_id = cust_acct.cust_acct_site_id;
SELECT cust_acct.cust_acct_site_id,
pol.customer_id,
(SELECT cust_acct2.party_site_id
FROM HZ_CUST_ACCT_SITES_ALL cust_acct2
WHERE cust_acct2.party_site_id = cust_acct.party_site_id
AND cust_acct2.org_id = l_source_operating_unit
AND cust_acct2.cust_account_id = cust_acct.cust_account_id
AND cust_acct2.status = 'A'
),
pol.org_id,
(SELECT cust_acct2.cust_acct_site_id
FROM HZ_CUST_ACCT_SITES_ALL cust_acct2
WHERE cust_acct2.party_site_id = cust_acct.party_site_id
AND cust_acct2.org_id = l_source_operating_unit
AND cust_acct2.cust_account_id = cust_acct.cust_account_id
)
FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL cust_acct
WHERE pol.location_id = l_header_rec.ship_to_location_id
AND pol.site_use_id = site_use.site_use_id
AND site_use.cust_acct_site_id = cust_acct.cust_acct_site_id;
select
cust_acct.cust_acct_site_id,
site_use.site_use_id
from
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL cust_acct
where
cust_acct.party_site_id = l_party_site_id
-- AND cust_acct.cust_account_id = l_customer_id -- bug # 12545721
AND cust_acct.org_id = l_source_operating_unit
AND cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
AND site_use.site_use_code = 'SHIP_TO'
AND site_use.status = 'A'
AND rownum = 1;
select site_use_id, cust_acct_site_id from HZ_CUST_SITE_USES_ALL where cust_acct_site_id in
(select hsa.cust_acct_site_id from HZ_CUST_ACCT_SITES_ALL hsa
where hsa.cust_account_id = l_cust_acct_id
and hsa.status = 'A'
and hsa.org_id = p_operating_unit)
and site_use_code = 'BILL_TO'
and primary_flag = 'Y'
and status = 'A';
SELECT site_use_id
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id = l_cust_site_id
AND status = 'A'
AND site_use_code NOT IN
(SELECT site_use_code
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id = x_cust_acct_site_id
AND status = 'A'
);
select cust_acct_site_id
from HZ_CUST_SITE_USES_ALL
where site_use_code = 'BILL_TO'
and site_use_id = v_ship_bill_site;
select newu.site_use_id
from HZ_CUST_SITE_USES_ALL orgu,
HZ_CUST_ACCT_SITES_ALL orgs,
HZ_CUST_SITE_USES_ALL newu,
HZ_CUST_ACCT_SITES_ALL news
where orgu.site_use_code = 'BILL_TO'
and orgu.site_use_id = v_ship_bill_site
and orgu.cust_acct_site_id = orgs.cust_acct_site_id
and news.party_site_id = orgs.party_site_id
and news.cust_acct_site_id = newu.cust_acct_site_id
and newu.site_use_code = 'BILL_TO'
and newu.location = orgu.location
and news.org_id = l_source_operating_unit;
select site_use_id
from hz_cust_site_uses_all
where cust_acct_site_id = v_bill_site_id
and site_use_id = v_ship_bill_site;
SELECT description
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_orgn_id;
SELECT ps.location_id site_loc_id
from csp_rs_cust_relations rcr,
hz_cust_acct_sites cas,
hz_cust_site_uses csu,
po_location_associations pla,
hz_party_sites ps
where rcr.customer_id = cas.cust_account_id
and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
and csu.site_use_code = 'SHIP_TO'
and csu.site_use_id = pla.site_use_id
and cas.party_site_id = ps.party_site_id
and rcr.resource_type = p_resource_type
and rcr.resource_id = p_resource_id
and pla.location_id = p_inv_loc_id;*/
SELECT crh.address_type,
crh.ship_to_location_id,
decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id),
decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
from jtf_task_assignments jta,
csp_requirement_headers crh
where jta.task_assignment_id(+) = crh.task_assignment_id
and crh.requirement_header_id = p_rqmt_header_id;
SELECT org_information2 ,
org_information3 ,
org_information4
FROM hr_organization_information hou
WHERE hou.organization_id = p_new_org_id
AND hou.org_information1 = 'FIELD_SERVICE'
AND hou.org_information_context = 'CS_USER_CONTEXT';
SELECT site_use.site_use_id, cia.ship_to_contact_id, cia.bill_to_contact_id
FROM HZ_CUST_ACCT_SITES_ALL cust_acct,
HZ_CUST_SITE_USES_ALL site_use,
hz_party_site_uses hpsu,
cs_incidents_all cia,
csp_requirement_headers_v req
WHERE req.requirement_header_id = p_rqmt_header_id
and cia.incident_id = req.incident_id
and cust_acct.cust_account_id = cia.bill_to_account_id
and hpsu.party_site_use_id = cia.bill_to_site_use_id
and cust_acct.party_site_id = hpsu.party_site_id
and cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
and site_use.site_use_code = 'BILL_TO'
and site_use.org_id = cia.org_id
and site_use.status = 'A';
select csu.site_use_id
from
csp_rs_cust_relations cr,
hz_cust_acct_sites cas,
hz_cust_site_uses csu
where
cr.resource_type = p_resource_type
and cr.resource_id = p_resource_id
and cr.customer_id = cas.cust_account_id
and cas.bill_to_flag = 'P'
and cas.cust_acct_site_id = csu.cust_acct_site_id
and csu.site_use_code = 'BILL_TO';
SELECT site_use.site_use_id
FROM HZ_CUST_ACCT_SITES_ALL cust_acct,
HZ_CUST_SITE_USES_ALL site_use,
hz_party_site_uses hpsu,
cs_incidents_all cia,
csp_requirement_headers_v req
WHERE req.requirement_header_id = v_rqmt_header_id
and req.address_type in ('C', 'T') -- bug # 14743823
and cia.incident_id = req.incident_id
and cust_acct.cust_account_id = cia.bill_to_account_id
and hpsu.party_site_use_id = cia.bill_to_site_use_id
and cust_acct.party_site_id = hpsu.party_site_id
and cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
and site_use.site_use_code = 'BILL_TO'
and site_use.org_id = v_src_org_id
and site_use.status = 'A';
SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_CUST_SITE_USES_ALL SITE_USE,
HZ_CUST_ACCT_SITES_all ADDR
WHERE
ACCT_ROLE.party_id = v_party_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ADDR.CUST_ACCT_SITE_ID = SITE_USE.CUST_ACCT_SITE_ID
AND SITE_USE.SITE_USE_ID = v_org_id
AND SITE_USE.STATUS = 'A'
AND ADDR.STATUS ='A'
AND ACCT_ROLE.STATUS = 'A'
AND ROWNUM = 1;
SELECT 1
FROM oe_transaction_types_all ot
WHERE ot.org_id = v_org_id
AND ot.transaction_type_id = v_order_type_id
AND sysdate BETWEEN NVL(ot.start_date_active, sysdate-1)
AND NVL(ot.end_date_active, sysdate+1);
select
nvl(sub.location_id, org.location_id)
into l_header_rec.ship_to_location_id
from
MTL_SECONDARY_INVENTORIES sub,
hr_all_organization_units org
where org.organization_id = l_header_rec.dest_organization_id
and org.organization_id = sub.organization_id(+)
and sub.secondary_inventory_name(+) = l_header_rec.dest_subinventory;
SELECT Sysdate INTO l_today FROM dual;
/*SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM dual;*/
SELECT operating_unit
INTO l_dest_operating_unit
FROM org_organization_Definitions
WHERE organization_id = l_header_rec.dest_organization_id;
SELECT cia.org_id
INTO l_sr_org_id
FROM cs_incidents_all cia,
csp_requirement_headers_v req
WHERE req.requirement_header_id = l_header_rec.requirement_header_id
AND cia.incident_id = req.incident_id;
select nvl(ship_to_contact_id, -9999)
into l_pr_ship_to_contact_id
from csp_requirement_headers
where requirement_header_id = l_header_rec.requirement_header_id;
SELECT party_id
into l_ship_to_contact_id
FROM HZ_CUST_ACCOUNT_ROLES
WHERE cust_account_role_id = l_pr_ship_to_contact_id;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier
= current_max_unique_identifier + 1
WHERE CURRENT of req_number_cur;
SELECT 1 INTO l_dummy
FROM DUAL
WHERE NOT EXISTS
( SELECT 1
FROM po_requisition_headers
WHERE Segment1 = l_header_rec.requisition_number)
AND NOT EXISTS
( SELECT 1
FROM po_history_requisitions phr
WHERE phr.segment1 = l_header_rec.requisition_number);
'Inserting data into PO_REQUISITION_HEADERS');
INSERT INTO PO_REQUISITION_HEADERS(
org_id,
requisition_header_id,
preparer_id,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
description,
authorization_status,
note_to_authorizer,
type_lookup_code,
transferred_to_oe_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
on_line_flag,
preliminary_research_flag,
research_complete_flag,
preparer_finished_flag,
preparer_finished_date,
agent_return_flag,
agent_return_note,
cancel_flag,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
interface_source_code,
interface_source_line_id,
closed_code
) VALUES (
l_org_id,
l_header_rec.requisition_header_id,
l_employee_id,
l_today,
nvl(l_user_id, 1),
l_header_Rec.requisition_number,
'N', -- summary_flag
'Y', -- Enabled_Flag
null,
null,
null,
null,
null, -- Start_Date_Active
null, -- End_Date_Active
nvl(l_login_id, -1), -- Last_Update_Login
l_today, -- Creation_Date
nvl(l_user_id, 1), -- Created_By
l_header_rec.description, -- Description
'APPROVED', -- Authorization_Status
null, -- note to Authorizor
'INTERNAL', -- Type_Lookup_Code; need to confirm this. po_lookup_codes has different values for document_type
'Inserting data into PO_ACTION_HISTORY');
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(l_header_rec.requisition_header_id,
'REQUISITION',
'INTERNAL',
0,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
'SUBMIT',
sysdate,
l_employee_id,
null,
null,
fnd_global.login_id,
0,
0,
0,
'',
null,
'' );
'Inserting data into PO_ACTION_HISTORY');
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(l_header_rec.requisition_header_id,
'REQUISITION',
'INTERNAL',
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
'APPROVE',
sysdate,
l_employee_id,
null,
null,
fnd_global.login_id,
0,
0,
0,
'',
null,
'' );
SELECT operating_unit
INTO l_source_operating_unit
FROM org_organization_Definitions
WHERE organization_id = l_line_tbl(1).source_organization_id;
SELECT ORDER_TYPE_ID
INTO l_header_rec.order_type_id
FROM PO_SYSTEM_PARAMETERS_ALL
WHERE ORG_ID = l_source_operating_unit;
SELECT hdr.price_list_id,
hdr.currency_code,
hdr.default_outbound_line_type_id,
line.price_list_id,
line.order_category_code,
nvl(line.scheduling_level_code, hdr.scheduling_level_code)
INTO l_price_list_id,
l_currency_code,
l_order_line_type_id,
l_line_price_list_id,
l_order_line_category_code,
l_scheduling_code
FROM oe_transaction_types_all hdr,
oe_transaction_types_all line
WHERE hdr.transaction_Type_id = l_header_rec.order_type_id
AND line.transaction_type_id = hdr.default_outbound_line_type_id
AND hdr.org_id = l_source_operating_unit
AND line.org_id = l_source_operating_unit;
SELECT glsob.CURRENCY_CODE
INTO l_currency_code
FROM GL_SETS_OF_BOOKS GLSOB,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE GLSOB.SET_OF_BOOKS_ID=FSP.SET_OF_BOOKS_ID
AND nvl(FSP.org_id,-1) = l_source_operating_unit;
arp_clas_pkg.insert_po_loc_associations(
p_inventory_location_id => l_header_rec.ship_to_location_id,
p_inventory_organization_id => l_source_operating_unit,
p_customer_id => l_customer_id,
p_address_id => x_cust_acct_site_id,
p_site_use_id => temp_ship_to_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select oe_order_headers_s.nextval
into l_header_rec.order_header_id
from dual;
ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
IF (l_header_rec.order_header_id is null) THEN
FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT concatenated_segments
INTO l_line_tbl(i).item_description -- taking dummy variable as we are going to throw an error only
FROM mtl_system_items_kfv
WHERE organization_id = l_line_tbl(i).source_organization_id
and inventory_item_id = l_line_tbl(i).inventory_item_id;
select organization_code
into l_line_tbl(i).ATTRIBUTE1 -- taking dummy variable as we are going to throw an error only
from mtl_parameters
where organization_id = l_header_rec.dest_organization_id;
'Inserting data into PO_REQUISITION_LINES...');
INSERT INTO PO_REQUISITION_LINES(
org_id,
requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
encumbered_flag,
rfq_required_flag,
need_by_date,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
cancel_flag,
order_type_lookup_code,
purchase_basis,
matching_basis,
transferred_to_oe_flag
) VALUES (
l_org_id,
l_line_tbl(i).requisition_line_id,
l_header_rec.requisition_header_id,
l_line_tbl(i).line_num,
l_line_type_rec.line_type_id, -- Line_Type_Id
nvl(l_Category_id, 1), -- Category_id
l_line_tbl(i).item_description, -- Item_Description
nvl(l_unit_meas_lookup_code, l_line_tbl(i).unit_of_measure), -- Unit_Meas_Lookup_Code
l_unit_price,
l_line_tbl(i).ordered_quantity,
l_header_rec.ship_to_location_id, -- Deliver_To_Location_Id
l_employee_id, -- To_Person_Id
l_today, -- Last_Update_Date
nvl(l_user_id, -1), -- Last_Updated_By
'INVENTORY', -- Source_Type_Code
nvl(l_login_id, -1),
l_today, -- Creation_Date
nvl(l_user_id, -1),
l_line_tbl(i).inventory_item_id,
l_line_tbl(i).revision,
'N', -- Encumbered_flag
'N', -- X_Rfq_Required_Flag
l_header_rec.need_by_date,
l_line_tbl(i).source_organization_id,
l_line_tbl(i).source_subinventory,
'INVENTORY', -- Destination_Type_Code
l_header_rec.dest_organization_id,
nvl(l_line_tbl(i).dest_subinventory, l_header_rec.dest_subinventory), /* Bug 7242187*/
'N',
l_line_type_rec.order_type_lookup_code,
l_line_type_rec.purchase_basis,
l_line_Type_rec.matching_basis, --Cancel_Flag
l_transferred_to_oe_flag
);
'Inserting data into po_req_distributions ...');
INSERT INTO po_req_distributions(
distribution_id
,last_update_date
,last_updated_by
,requisition_line_id
,set_of_books_id
,code_combination_id
,req_line_quantity
,last_update_login
,creation_date
,created_by
,encumbered_flag
,gl_encumbered_date
,gl_encumbered_period_name
,gl_cancelled_date
,failed_funds_lookup_code
,encumbered_amount
,budget_account_id
,accrual_account_id
,variance_account_id
,prevent_encumbrance_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,ussgl_transaction_code
,government_context
,project_id
,task_id
,expenditure_type
,project_accounting_context
,expenditure_organization_id
,gl_closed_date
,source_req_distribution_id
,distribution_num
,project_related_flag
,expenditure_item_date
,org_id
,allocation_type
,allocation_value
,award_id
,end_item_unit_number
,recoverable_tax
,nonrecoverable_tax
,recovery_rate
,tax_recovery_override_flag
,oke_contract_line_id
,oke_contract_deliverable_id
)
VALUES
(
l_dist_rec.distribution_id
,l_today --last_update_date
,l_user_id --last_updated_by
,l_dist_rec.requisition_line_id
,l_dist_rec.set_of_books_id
,l_dist_rec.code_combination_id
,l_dist_rec.req_line_quantity
,l_login_id --last_update_login
,l_today --creation_date
,l_user_id --created_by
,l_dist_rec.encumbered_flag
,l_dist_rec.gl_encumbered_date
,l_dist_rec.gl_encumbered_period_name
,l_dist_rec.gl_cancelled_date
,l_dist_rec.failed_funds_lookup_code
,l_dist_rec.encumbered_amount
,l_dist_rec.budget_account_id
,l_dist_rec.accrual_account_id
,l_dist_rec.variance_account_id
,l_dist_rec.prevent_encumbrance_flag
,l_dist_rec.attribute_category
,l_dist_rec.attribute1
,l_dist_rec.attribute2
,l_dist_rec.attribute3
,l_dist_rec.attribute4
,l_dist_rec.attribute5
,l_dist_rec.attribute6
,l_dist_rec.attribute7
,l_dist_rec.attribute8
,l_dist_rec.attribute9
,l_dist_rec.attribute10
,l_dist_rec.attribute11
,l_dist_rec.attribute12
,l_dist_rec.attribute13
,l_dist_rec.attribute14
,l_dist_rec.attribute15
,l_dist_rec.ussgl_transaction_code
,l_dist_rec.government_context
,l_dist_rec.project_id
,l_dist_rec.task_id
,l_dist_rec.expenditure_type
,l_dist_rec.project_accounting_context
,l_dist_rec.expenditure_organization_id
,l_dist_rec.gl_closed_date
,l_dist_rec.source_req_distribution_id
,l_dist_rec.distribution_num
,l_dist_rec.project_related_flag
,l_dist_rec.expenditure_item_date
,l_dist_rec.org_id
,l_dist_rec.allocation_type
,l_dist_rec.allocation_value
,l_dist_rec.award_id
,l_dist_rec.end_item_unit_number
,l_dist_rec.recoverable_tax
,l_dist_rec.nonrecoverable_tax
,l_dist_rec.recovery_rate
,l_dist_rec.tax_recovery_override_flag
,l_dist_rec.oke_contract_line_id
,l_dist_rec.oke_contract_deliverable_id
);
select oe_order_lines_s.nextval
into l_line_tbl(i).order_line_id
from dual;
ELSIF (l_header_rec.operation = CSP_PARTS_ORDER.G_OPR_UPDATE) THEN
IF (p_process_Type = 'REQUISITION') THEN
IF (l_line_Tbl(I).requisition_line_id IS NULL) THEN
FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
update po_requisition_lines
set quantity = l_line_tbl(I).ordered_quantity
where requisition_line_id = l_line_Tbl(I).requisition_line_id;
update po_req_distributions
set req_line_quantity = l_line_tbl(i).ordered_quantity
where requisition_line_id = l_line_tbl(i).requisition_line_id;
p_action => 'Update_Req_Line_Qty',
p_recreate_flag => NULL,
p_qty => l_line_tbl(i).ordered_quantity,
p_receipt_date => NULL) THEN
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_SUPPLY',
token2 => 'ERR_NUMBER',
value2 => '035',
token3 => 'SUBROUTINE',
value3 => 'PO_REQ_SUPPLY()');
l_oe_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
DELETE FROM mtl_supply ms1
WHERE ms1.supply_source_id IN
(
SELECT pl.requisition_line_id
FROM po_requisition_lines pl
WHERE pl.requisition_header_id = l_header_rec.requisition_header_id
AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
AND NVL(pl.cancel_flag, 'N') = 'N'
AND pl.line_location_id IS NULL
)
AND ms1.supply_type_code = 'REQ';
'Inserting data into mtl_supply...');
INSERT INTO mtl_supply
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
change_flag)
SELECT 'REQ',
prl.requisition_line_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
prl.requisition_header_id,
prl.requisition_line_id,
prl.item_id,
decode(prl.source_type_code,'INVENTORY', null,
prl.item_revision),
prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
nvl(prl.QUANTITY_DELIVERED, 0) ),
prl.unit_meas_lookup_code,
prl.need_by_date,
prl.need_by_date,
prl.destination_type_code,
prl.deliver_to_location_id,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
'Y'
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = l_header_rec.requisition_header_id
AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
-- : Filter out amount basis
AND prl.matching_basis <> 'AMOUNT'
AND prl.line_location_id is null
AND not exists
(SELECT 'supply exists'
FROM mtl_supply ms
WHERE ms.supply_type_code = 'REQ'
AND ms.supply_source_id = prl.requisition_line_id);
update mtl_supply
set expected_delivery_date = nvl(l_header_rec.need_by_date, sysdate),
need_by_date = nvl(l_header_rec.need_by_date, sysdate)
where req_header_id = l_header_rec.requisition_header_id;
csp_process_order_hook.update_oe_dff_info(
px_req_header_rec => px_header_rec
,px_req_line_table => px_line_table
,px_oe_header_rec => l_oe_header_rec
,px_oe_line_table => l_oe_line_tbl);
ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
-- update requisition line table with new quantity
-- quantity is the only change allowed
FOR i in 1..lx_oe_line_tbl.count LOOP
IF (lx_oe_line_tbl(I).ordered_quantity IS NOT NULL OR
lx_oe_line_tbl(I).ordered_quantity <> FND_API.G_MISS_NUM) THEN
update po_requisition_lines
set quantity = lx_oe_line_tbl(I).ordered_quantity
where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
update po_req_distributions
set req_line_quantity = lx_oe_line_tbl(I).ordered_quantity
where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
p_action => 'Update_Req_Line_Qty',
p_recreate_flag => NULL,
p_qty => lx_oe_line_tbl(I).ordered_quantity,
p_receipt_date => NULL) THEN
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_SUPPLY',
token2 => 'ERR_NUMBER',
value2 => '035',
token3 => 'SUBROUTINE',
value3 => 'PO_REQ_SUPPLY()');
update mtl_supply
set change_flag = NULL
where req_header_id = l_header_rec.requisition_header_id;
SELECT to_char(current_max_unique_identifier + 1)
FROM po_unique_identifier_control
WHERE table_name = 'PO_REQUISITION_HEADERS'
FOR UPDATE OF current_max_unique_identifier;
SELECT po_requisition_headers_s.nextval
FROM sys.dual;
SELECT po_requisition_lines_s.nextval
FROM sys.dual;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT Sysdate INTO l_today FROM dual;
SELECT operating_unit
INTO l_dest_operating_unit
FROM org_organization_Definitions
WHERE organization_id = l_header_rec.dest_organization_id;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier
= current_max_unique_identifier + 1
WHERE CURRENT of req_number_cur;
SELECT 1 INTO l_dummy
FROM DUAL
WHERE NOT EXISTS
( SELECT 1
FROM po_requisition_headers
WHERE Segment1 = l_header_rec.requisition_number)
AND NOT EXISTS
( SELECT 1
FROM po_history_requisitions phr
WHERE phr.segment1 = l_header_rec.requisition_number);
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_item_uoms_view
WHERE organization_id = l_header_rec.dest_organization_id
AND inventory_item_id = l_line_Tbl(i).inventory_item_id
AND uom_code = l_line_Tbl(i).unit_of_measure;
Select employee_id into l_planner_employee_id From MTL_PLANNERS
Where Organization_id = l_header_rec.dest_organization_id
and Planner_code = ( Select Planner_code from mtl_system_items_b
where inventory_item_id = l_line_tbl(i).inventory_item_id and organization_id =
l_header_rec.dest_organization_id )
and nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE;
SELECT ORG_INFORMATION3, ORG_INFORMATION4
INTO l_VENDOR_ID, l_VENDOR_SITE_ID
FROM HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = l_source_organization_id
and org_information_context = 'Customer/Supplier Association';
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
INTERFACE_SOURCE_CODE,
--INTERFACE_SOURCE_LINE_ID,
SOURCE_TYPE_CODE,
REQUISITION_TYPE,
DESTINATION_TYPE_CODE,
QUANTITY,
UOM_CODE,
UNIT_OF_MEASURE,
--UNIT_PRICE,
AUTHORIZATION_STATUS,
PREPARER_ID,
ITEM_ID,
CHARGE_ACCOUNT_ID,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_REQUESTOR_ID,
NEED_BY_DATE,
ORG_ID,
LINE_TYPE_ID,
REQ_NUMBER_SEGMENT1,
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,
REFERENCE_NUM,
JUSTIFICATION,
NOTE_TO_BUYER,
--TRANSACTION_REASON_CODE
NOTE1_ID,
NOTE1_TITLE,
SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_NAME,
SUGGESTED_VENDOR_SITE_ID,
source_organization_id,
AUTOSOURCE_FLAG
)
VALUES
(l_today, --creation_date
l_user_id, --created_by
l_today, -- last_update_date
l_user_id, -- last_update_login
l_login_id, --last_update_login
'CSP', -- interface_source_code
'VENDOR',
'PURCHASE',
'INVENTORY',
l_line_tbl(i).ordered_quantity,
l_line_tbl(i).unit_of_measure,
l_unit_of_measure,
'APPROVED',
l_employee_id,
l_line_tbl(i).inventory_item_id,
l_charge_Account_id,
l_header_Rec.dest_organization_id,
l_line_tbl(i).dest_subinventory,
l_header_rec.ship_to_location_id,
l_employee_id,
nvl(l_line_tbl(i).need_by_date, l_header_rec.need_by_date),
l_org_id,
l_line_Type_id,
l_header_rec.requisition_number,
l_header_rec.requisition_header_id,
l_line_tbl(i).requisition_line_id,
l_header_rec.requirement_header_id,
l_justification,
l_note_to_buyer,
--'Spares Parts Order'
l_note1_id,
l_note1_title,
l_SUGGESTED_VENDOR_ID,
l_SUGGESTED_VENDOR_NAME,
l_VENDOR_SITE_ID,
l_source_organization_id,
l_autosource_flag
);
select org_id into l_org_id from oe_order_headers_all where header_id = p_oe_header_id;
SELECT csp.arrival_date
FROM CSP_SHIPPING_DETAILS_V csp,
csp_requirement_headers ch,
csp_requirement_lines cl,
csp_req_line_details cld
WHERE csp.organization_id = l_ship_from_org_id
AND csp.shipping_method = p_ship_method
AND cld.source_id = p_oe_line_id
AND cld.requirement_line_id = cl.requirement_line_id
AND cl.requirement_header_id = ch.requirement_header_id
AND csp.to_location_id = ch.ship_to_location_id
AND csp.location_source = 'HR';
select org_id into l_org_id from oe_order_lines_all where line_id = p_oe_line_id;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
select ship_from_org_id into l_ship_from_org_id from oe_order_lines_all where line_id = p_oe_line_id;
,p_update_req_header IN VARCHAR2
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_module_name varchar2(100) := 'csp.plsql.csp_parts_order.upd_oe_ship_to_add';
SELECT count(1)
FROM csp_requirement_lines cl,
csp_req_line_details cld,
oe_order_lines_all oel
WHERE cl.requirement_header_id = p_req_header_id
AND cl.requirement_line_id = cld.requirement_line_id
AND cld.source_type = 'IO'
AND cld.source_id = oel.line_id
AND oel.booked_flag = 'Y';
SELECT oeh.order_number
FROM csp_requirement_lines cl,
csp_req_line_details cld,
oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE cl.requirement_header_id = p_req_header_id
AND cl.requirement_line_id = cld.requirement_line_id
AND cld.source_type = 'IO'
AND cld.source_id = oel.line_id
AND oel.booked_flag = 'Y'
AND oel.header_id = oeh.header_id
AND rownum = 1;
SELECT distinct oel.header_id
FROM csp_requirement_lines cl,
csp_req_line_details cld,
oe_order_lines_all oel
WHERE cl.requirement_header_id = p_req_header_id
AND cl.requirement_line_id = cld.requirement_line_id
AND cld.source_type = 'IO'
AND cld.source_id = oel.line_id
AND oel.booked_flag = 'N'
AND oel.cancelled_flag = 'N'
AND oel.open_flag = 'Y';
SELECT destination_organization_id,
destination_subinventory,
need_by_date
FROM csp_requirement_headers
WHERE requirement_header_id = p_req_header_id;
SELECT requirement_line_id,
req_line_detail_id
FROM csp_req_line_details
WHERE source_type= 'IO'
AND source_id = v_oe_line_id;
|| ', p_update_req_header = ' || p_update_req_header
|| ', p_new_add_type = ' || p_new_add_type);
'before calling update req line details...');
CSP_REQ_LINE_DETAILS_PVT.Update_req_line_details(
P_Api_Version_Number => 1.0,
P_Req_Line_Details_Tbl => l_req_line_dtl_rec,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
'after CSP_REQ_LINE_DETAILS_PVT.Update_req_line_details... x_return_status = ' || x_return_status);
'p_update_req_header = ' || p_update_req_header);
if nvl(p_update_req_header, 'N') = 'Y' then
-- update req header with new ship_to_location_id
l_req_hdr_pvt_rec.REQUIREMENT_HEADER_ID := p_req_header_id;
'before calling CSP_Requirement_headers_PVT.Update_requirement_headers...');
CSP_Requirement_headers_PVT.Update_requirement_headers(
P_Api_Version_Number => 1.0,
P_REQUIREMENT_HEADER_Rec => l_req_hdr_pvt_rec,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
'after calling CSP_Requirement_headers_PVT.Update_requirement_headers... x_return_status = ' || x_return_status);