[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| sec_qty,sec_uom and grade inserted into po_reqs_iface_all
| call INV api to check for porcess org
| HARD Dependecnies:
| CTOUTILB.check_cto_can_create_supply
| INV api INV_GMI_RSV_BRANCH.Process_Branch
|
|Aug 9th,2005 Kiran Konada
| 4545070
| Replaced call to OE_ORDER_WF_UTIL.update_flow_status_code with
| call to CTO_WORKFLOW_API_PK.display_wf_status
|
|
|Aug 29th,2005 Kiran Konada
| bugfix 4545559
| changed the insert from po_requisitions_interface to
| po_requisitions_interface_all table
|
|
|Sep 22nd,2005 Kiran Konada
| Created new local procedure Get_opm_charge_account
| calling SLA OPM api to get charge and accrual account id
| Dependency: aru#4610085(pack spec and stubbed out pkg body)
| GMF_transaction_accounts_PUB.get_accounts
| Calling MRP_SOURCING_API_PK.mrp_sourcing to get sourcing
| vendor and vendor site id. This API has been there from 11.5.10
| Talked to Usha, we dont require a dependent aru for this.
|
|
=============================================================================*/
-- CTO_AUTO_PROCURE_PK
-- following parameters are created for
g_pkg_name CONSTANT VARCHAR2(30) := 'CTO_AUTO_PROCURE_PK';
procedure insert_blanket_line(
p_doc_line_id IN Number,
p_item_id IN Number,
p_item_rev IN Varchar2,
p_price IN Number,
p_int_header_id IN Number,
p_segment1 IN mtl_system_items.segment1%type,
p_start_date IN date,
p_end_date IN date,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY varchar2);
procedure insert_blanket_header(
p_doc_header_id IN Number,
p_batch_id IN OUT NOCOPY Number,
x_int_header_id Out NOCOPY Number,
x_org_id OUT NOCOPY po_headers_all.org_id%type,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY varchar2);
PROCEDURE update_bcolt_line_status(p_line_id NUMBER,
p_status NUMBER,
x_return_status out NOCOPY VARCHAR2 );
select nvl(postprocessing_lead_time,0) into l_offset_days
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_org_id;
select CAL.CALENDAR_DATE into x_need_by_date
from bom_calendar_dates cal, mtl_parameters mp
where mp.organization_id = p_org_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num = (select cal2.prior_seq_num - nvl(l_offset_days, 0)
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id = mp.calendar_exception_set_id
and cal2.calendar_date= trunc(p_schedule_ship_date)
);
i) Identify the lines to be processed and insert into
a global temp table with a PENDING status.
ii) Fetch it from this temp table in batch of 1000.
iii) Lock the line being processed.
At this time, get the line details once again to get the new
picture. An order-line could have changed from the time it was
populated in the temp table.
iv) Process the record.
v) COMMIT. Since we are processing this for a batch of 1000
from an array, commiting after each record should not cause
snapshot errors.
vi) Update the status to COMPLETE or ERROR once processing
is done. If ERROR, rollback the changes for that record and
continue with the next.
vii) Before fetching the next batch, close and reopen the cursor
from the temp table (only PENDING records will be fetched).
This is done to avoid "fetch across commits" (snapshot) problems.
Note:
-----
The whole idea of temp table was thought of because, we don't want
new eligible records to be picked up everytime we reopened the cursor.
Hence, we needed to have a mechanism to mark the records from the
first fetch.
Since the temp table is a global temp table, it will always get
refreshed once the session is over.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
cursor eligible_lines_cur is
select line_id
from bom_cto_order_lines_temp
where status = 1
order by org_id;
SELECT oeh.order_number
,oel.line_id
,oel.line_type_id
,oel.org_id
,oel.inventory_item_id
,oel.item_revision
,oel.ordered_quantity
,oel.cancelled_quantity
,oel.order_quantity_uom
,oel.unit_selling_price
,oel.created_by
,oel.ship_from_org_id
,oel.ship_to_org_id
,oel.schedule_ship_date
,oel.request_date
,oel.ordered_quantity2 --opm
,oel.ordered_quantity_uom2 --opm
,oel.preferred_grade --opm
from oe_order_lines_all oel,
oe_order_headers_all oeh
where oel.header_id = oeh.header_id
and oel.line_id = p_cursor_line_id
FOR UPDATE OF oel.line_id;
update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Insert successful.',1);
select sum(nvl(reservation_quantity, 0))
into l_inv_quantity
from mtl_reservations
where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and demand_source_line_id = so_line.line_id;
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order updated to REQ_REQUESTED.',1);
update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
update bom_cto_order_lines_temp
set status = 2 -- set status to completed
where line_id = line_id_tab(tab_index)
and status = 1;
line_id_tab.delete;
oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
SELECT location_id
FROM hr_locations_all loc
WHERE inventory_organization_id = l_dest_org_id
AND ship_to_site_flag = 'Y'
AND EXISTS ( SELECT 1 FROM po_location_associations_all pla
WHERE loc.location_id = pla.location_id
);
select org.location_id into x_location_id
from hr_all_organization_units org,
hr_locations_all loc
where org.organization_id = p_org_id
and org.location_id = loc.location_id
and exists(select 1 from po_location_associations_all pla
where pla.location_id = loc.location_id); */
select org.location_id into x_location_id
from hr_all_organization_units org,
hr_locations_all loc
where org.organization_id = p_org_id
and org.location_id = loc.location_id;
SELECT material_account
FROM mtl_parameters
WHERE organization_id = i_org_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = i_created_by;
select count(*) into l_chk_col
from all_tab_columns
where owner = l_customer_schema --bugfix 3871646 --'ONT'
and table_name = 'OE_ORDER_LINES_ALL'
and column_name = 'USER_ITEM_DESCRIPTION';
sql_stmt := 'SELECT substrb(oel.user_item_description,1,240)'
||' FROM oe_order_lines_all oel , mtl_system_items msi'
||' WHERE oel.ship_from_org_id = msi.organization_id'
||' AND oel.inventory_item_id = msi.inventory_item_id'
||' AND oel.line_id = :p_interface_source_line_id'
||' AND msi.organization_id = :p_destination_org_id'
||' AND msi.allow_item_desc_update_flag = :pflag ';
Select decode(project_id,-1,NULL,project_id),
decode(task_id,-1,NULL,task_id)
into l_project_id,
l_task_id
from oe_order_lines_all
where line_id = p_interface_source_line_id;
SELECT 'Y'
into l_pegging_flag
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_destination_org_id
AND end_assembly_pegging_flag IN ('I','X');
Select operating_unit
into l_operating_unit
from inv_organization_info_v
where organization_id = p_destination_org_id;
select ato_line_id into l_ato_line_id
from oe_order_lines_all
where line_id = p_interface_source_line_id;
select max(revision) into l_item_revision
from mtl_item_revisions mir,
mtl_system_items msi
where msi.organization_id = p_destination_org_id
and msi.inventory_item_id = p_item_id
and mir.organization_id = msi.organization_id
and mir.inventory_item_id = msi.inventory_item_id
and mir.effectivity_date = (select max(mir1.effectivity_date)
from mtl_item_revisions mir1
where mir1.organization_id = msi.organization_id
and mir1.inventory_item_id = msi.inventory_item_id
and mir1.effectivity_date <= sysdate )
and msi.revision_qty_control_code = 2 --revision controlled items only
and msi.base_item_id is null -- not preconfig or config
and msi.bom_item_type = 4; --standard item
-- Select material account from mtl_parameters instead of receiving
-- account from rcv_paramters
-- p_receiving_account_id := ch_act.receiving_account_id;
-- rkaza. 05/10/2005. Inserting new parameters...
-- requisition_type, sourcing_org, auto_source_flag
--OPM project, inserting parameters secondary qty,
--secondary uom and grade
--bugfix 4545559 changed the insert from
--po_requisitions_interface to _all table
l_stmt_num := 210;
INSERT INTO po_requisitions_interface_all (
interface_source_code,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
need_by_date,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
destination_type_code,
quantity,
uom_code,
authorization_status,
preparer_id,
item_id,
item_revision,
batch_id,
charge_account_id,
interface_source_line_id,
source_type_code,
source_organization_id,
unit_price,
note_to_buyer,
note_to_receiver,
org_id,
item_Description, -- 2503104 : Insert user_item_description
project_id,
task_id,
project_accounting_context,
requisition_type,
autosource_flag,
secondary_quantity, --opm case and also for buy in discrete orgs
secondary_uom_code, --opm and also for buy in discrete orgs
preferred_grade, --opm and also for buy in discrete orgs
accrual_account_id --opm
)
VALUES (
l_intf_source_code,
p_destination_org_id, -- ship_from_org_id
l_location_id,
p_preparer_id, --p_deliver_to_requestor_id/employee_id
p_need_by_date,
l_user_id,
l_system_date,
l_login_id,
l_system_date,
p_created_by,
l_dest_type_code,
p_order_quantity,
p_order_uom,
l_authorization_status,
p_preparer_id,
p_item_id,
l_item_revision,
p_batch_id,
p_receiving_account_id,
decode(p_interface_source_code,'CTO',p_interface_source_line_id,null), -- bugfix 3129117
l_source_type_code,
l_sourcing_org,
p_unit_price,
'Supply for the Sales Order :'||p_order_number||', '||v_note_to_buyer,
v_note_to_receiver,
l_operating_unit,
l_user_item_desc, -- 2503104 : user_item_description
decode(l_pegging_flag,'Y',l_project_id,null), -- bug 3129117
decode(l_pegging_flag,'Y',l_task_id,null), -- bug 3129117
decode(l_project_id,-1,null,null,null,'Y'),
l_req_type,
l_auto_source_flag,
p_req_interface_input_data.secondary_qty,
p_req_interface_input_data.secondary_uom,
p_req_interface_input_data.grade,
l_accrual_account_id
);
'insert into the req interface table failed interface_source_line_id'||
to_char(p_interface_source_line_id),1);
oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
to_char(l_stmt_num)||'::'||sqlerrm,1);
SELECT nvl(SUM(reservation_quantity), 0)
INTO v_rsv_quantity
FROM mtl_reservations
WHERE demand_source_line_id = p_line_id;
SELECT Nvl(Sum(quantity),0) qty
FROM po_requisitions_interface_all
WHERE interface_source_line_id = p_line_id
and item_id = p_inv_item_id
AND process_flag is null;
SELECT nvl(SUM(reservation_quantity), 0) qty
FROM mtl_reservations
WHERE demand_source_line_id = p_interface_source_line_id;
select line_id,
ordered_quantity,
inventory_item_id
from oe_order_lines_all
where line_id = p_line_id;
select nvl(sum(reservation_quantity), 0)
into l_inv_qty
from mtl_reservations
where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe )
and demand_source_line_id = so_line.line_id
and supply_source_type_id = inv_reservation_global.g_source_type_inv;
select nvl(sum(reservation_quantity), 0)
into l_po_qty
from mtl_reservations
where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe )
and demand_source_line_id = so_line.line_id
and supply_source_type_id = inv_reservation_global.g_source_type_po;
select nvl(sum(reservation_quantity), 0)
into l_req_qty
from mtl_reservations
where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe )
and demand_source_line_id = so_line.line_id
and supply_source_type_id = inv_reservation_global.g_source_type_req;
select 'EXTERNAL_REQ_REQUESTED'
into p_flow_status
from po_requisitions_interface_all
where interface_source_line_id = so_line.line_id
and item_id = so_line.inventory_item_id
and process_flag is null
and rownum =1;
This will rollup the list price for the Buy configurations, from the components selected in
the order. The list price will be taken from Po Validation org. If the component/Model is not
Defined in the Po validation org, the price will be taken as 0. The rolled up price of the configuration
will be update in Mtl_system_items in po validation org. Apart from Rolling up list price, This procedure
will also Rollup the blanket price from the model blanket and create a new blanket and ASL entries for
configuration items.
Parameter explanations
P_top_model_line_id -- Top ATO model's line id
P_overwrite_list_price -- It can have 'Y'/'N' value. The default value is 'N'.
If this parameter is passed as 'N' the list price of the
configuration will not be overwritten in Po validation org.
Only if the list_price_per_unit is null in po validation org
the rolled up price will be updated.
If this parameter is passed as 'Y', this API will update the
mtl_system_items anyway
P_Called_in_batch -- When the purchase price rollup is done for more than one order
this parameter should be set to 'Y'. If this is done online for a
Single order then it should be 'N'. The default value for this is 'N'.
If this paramter is 'N', the PDOI concurrent program will be
Launched by this API. If it is passed as 'Y' this API will not
Launch the PDOI concurrent program. The calling module will lauch in that
case. IN both cased the records are inserted to PDOI interface tables by
This api only.
p_batch_number -- The default value is null for this. If p_called_in_batch parameter is 'Y'
then the calling application should pass this value. This batch number is
used to populate in PDOI interface tables. If the case on online this API
will generate the batch id thru sequence.
X_oper_unit_list -- This is a out parameter. This is a table of records. This contains all the
Operating units processed by this API. In the case of on line call this output
parameter will not be used by the calling application. The batch calling program
will get this out parameter and uses this to launch the PDOI interface concurrent
Program. The batch calling program will loop thru this table and launch the
concurrent program that many times. While lauching the concurrent program it will
also set the org context to the operating unit specified in this table
*************************************************************************************************************************/
Procedure Create_Purchasing_Doc(
p_config_item_id IN Number,
p_overwrite_list_price IN Varchar2 default 'N',
p_called_in_batch IN Varchar2 default 'N',
p_batch_number IN OUT NOCOPY Number,
p_mode IN Varchar2 Default 'ORDER',
p_ato_line_id IN Number default null,
x_oper_unit_list IN OUT NOCOPY cto_auto_procure_pk.oper_unit_tbl,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY Varchar) is
lStmtNumber Number;
select component_item_id,
line_id
from bom_explosion_temp
where group_id = x_group_id
and configurator_flag = 'Y'
order by plan_level desc; /* Check With Sajani */
select line_id
into l_line_id
from bom_explosion_temp
where group_id = x_group_id
and assembly_item_id = p_config_item_id
and component_item_id = (select base_item_id
from mtl_system_items
where inventory_item_id = p_config_item_id
and rownum =1);
Select exp.component_quantity comp_qty,
exp.primary_uom_code uom_code,
exp.component_item_id comp_item_id,
msi.primary_uom_code prim_uom_code,
nvl(msi.list_price_per_unit,0) list_price_per_unit
from bom_explosion_temp exp,
mtl_system_items msi
where exp.group_id = p_group_id
and exp.assembly_item_id = p_config_item_id
and exp.component_item_id = msi.inventory_item_id
and msi.organization_id = p_org_id;
Select exp.component_item_id comp_item_id,
exp.component_quantity comp_qty
from bom_explosion_temp exp
where exp.group_id = p_group_id
and exp.assembly_item_id = p_config_item_id
and exp.configurator_flag = 'Y'
and not exists (select 'X'
from mtl_system_items msi
where msi.inventory_item_id = exp.component_item_id
and msi.organization_id = p_org_id);
also insert records into PDOI tables to create necessary purchasing documents
for configurations item.
***********************************************************************************************************************/
Procedure Rollup_purchase_price (
p_config_item_id in Number,
p_batch_id in out NOCOPY Number,
p_group_id in Number,
p_mode IN Varchar2 Default 'ORDER',
p_line_id in number,
x_oper_unit_list in out NOCOPY cto_auto_procure_pk.oper_unit_tbl,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2) is
lStmtNumber Number;
select base_item_id,
config_orgs
into l_model_item_id,
l_config_creation
from mtl_system_items
where inventory_item_id = p_config_item_id
and rownum =1;
select 'Y',organization_id
into l_config_exists,l_po_valid_org
from mtl_system_items
where inventory_item_id = p_config_item_id
and organization_id = (select fsp.inventory_organization_id
from financials_system_params_all fsp,
po_headers_all poh
where poh.po_header_id = l_doc_header_id
and fsp.org_id = poh.org_id);
insert_blanket_header(
p_doc_header_id => l_doc_header_id,
p_batch_id => p_batch_id,
x_int_header_id => x_int_header_id,
x_org_id => x_org_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select segment1
into x_segment1
from Mtl_system_items
where inventory_item_id = p_config_item_id
and rownum = 1;
insert_blanket_line(
p_doc_line_id => l_doc_line_id,
p_item_id => p_config_item_id,
p_item_rev => null,
p_price => x_rolled_price,
p_int_header_id => x_int_header_id,
p_segment1 => x_segment1,
p_start_date => x_start_date,
p_end_date => x_end_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Select 'X'
into line_exists
From po_lines_all pol,
Po_headers_all poh
Where
poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND nvl(poh.closed_code,'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
AND nvl(pol.closed_code, 'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.frozen_flag,'N') = 'N'
AND trunc(nvl(pol.expiration_date, sysdate + 1)) > trunc(sysdate)
AND nvl(pol.cancel_flag,'N') = 'N'
AND poh.po_header_id = p_doc_header_id
AND pol.po_header_id = poh.po_header_id
AND pol.item_id = p_config_item_id;
select exp.component_item_id comp_item_id,
exp.component_quantity comp_qty,
exp.primary_uom_code uom_code,
exp.configurator_flag config_flag
from bom_explosion_temp exp
where group_id = p_group_id
and assembly_item_id = p_config_item_id;
select base_item_id
into l_base_model_id
from mtl_system_items
where inventory_item_id = p_config_item_id
and rownum = 1;
Select pol.unit_price,
muom.uom_code
into l_unit_price,
l_po_uom
From po_lines_all pol,
Po_headers_all poh,
mtl_units_of_measure muom
Where
poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND nvl(poh.closed_code,'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
AND nvl(pol.closed_code, 'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.frozen_flag,'N') = 'N'
AND trunc(nvl(pol.expiration_date, sysdate + 1)) > trunc(sysdate)
AND nvl(pol.cancel_flag,'N') = 'N'
AND poh.po_header_id = p_doc_header_id
AND pol.po_header_id = poh.po_header_id
AND pol.item_id = buy_comps.comp_item_id
AND ( (p_doc_line_id is null)
or (buy_comps.comp_item_id <> l_base_model_id)
or (pol.po_line_id = p_doc_line_id)
)
AND muom.unit_of_measure = unit_meas_lookup_code
AND rownum = 1; -- Added by renga Kannan on 04/15/02
select nvl(list_price_per_unit,0),
primary_uom_code
into l_unit_price,
l_po_uom
from mtl_system_items
where inventory_item_id = buy_comps.comp_item_id
and organization_id = p_po_valid_org;
procedure insert_blanket_header(
p_doc_header_id IN Number,
p_batch_id IN OUT NOCOPY Number,
x_int_header_id Out NOCOPY Number,
x_org_id OUT NOCOPY po_headers_all.org_id%type,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY varchar2) is
begin
g_pg_level := g_pg_level + 3;
select po_headers_interface_s.nextval
into x_int_header_id
from dual;
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Interface header id ='||to_char(x_int_header_id),1);
Insert into Po_headers_interface(
interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
document_num,
po_header_id,
currency_code,
rate_type,
rate_date,
rate,
agent_id,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
note_to_vendor,
note_to_receiver,
acceptance_required_flag,
min_release_amount,
frozen_flag,
closed_code,
reply_date,
ussgl_transaction_code,
load_sourcing_rules_flag,
global_agreement_flag ) /* BUG#2726167 populate global_agreement_flag */
select
x_int_header_id,
p_batch_id,
'PENDING',
'UPDATE',
poh.org_id,
poh.type_lookup_code,
poh.segment1,
poh.po_header_id,
poh.currency_code,
poh.rate_type,
poh.rate_date,
poh.rate,
poh.agent_id,
poh.vendor_id,
poh.vendor_site_id,
poh.vendor_contact_id,
poh.ship_to_location_id,
poh.bill_to_location_id,
poh.terms_id,
poh.note_to_vendor,
poh.note_to_receiver,
poh.acceptance_required_flag,
poh.min_release_amount,
poh.frozen_flag,
poh.closed_code,
poh.reply_date,
poh.ussgl_transaction_code,
'Y',
global_agreement_flag /* BUG#2726167 populate global_agreement_flag */
From Po_headers_all poh
where poh.po_header_id = p_doc_header_id;
select org_id
into x_org_id
from po_headers_all
where po_header_id = p_doc_header_id;
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
End insert_blanket_header;
procedure insert_blanket_line(
p_doc_line_id IN Number,
p_item_id IN Number,
p_item_rev IN Varchar2,
p_price IN Number,
p_int_header_id IN Number,
p_segment1 IN Mtl_system_items.segment1%type,
p_start_date IN Date,
p_end_date IN Date,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY varchar2) is
l_interface_line_id Number;
select segment1
into l_segment1
from mtl_system_items
where inventory_item_id = p_item_id
and rownum=1;
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
Insert into po_lines_interface(
interface_line_id,
interface_header_id,
Line_num,
line_type_id,
item_id,
item, ---- As per beth I am adding this
item_revision,
category_id,
unit_of_measure,
quantity,
-- commited_acount,
min_order_quantity,
max_order_quantity,
unit_price,
negotiated_by_preparer_flag,
un_number_id,
hazard_class_id,
note_to_vendor,
taxable_flag,
tax_name,
--type_1099,
-- terms_id,
price_type,
min_release_amount,
price_break_lookup_code,
ussgl_transaction_code,
closed_date,
tax_code_id,
effective_date,
expiration_date)
select
po_lines_interface_s.nextval,
p_int_header_id,
null,
pol.line_type_id,
p_item_id,
p_segment1,
null,
pol.category_id,
pol.unit_meas_lookup_code,
pol.quantity,
-- pol.commited_amount,
pol.min_order_quantity,
pol.max_order_quantity,
p_price,
decode(pol.negotiated_by_preparer_flag,'X',null,pol.negotiated_by_preparer_flag),
pol.un_number_id,
pol.hazard_class_id,
pol.note_to_vendor,
pol.taxable_flag,
pol.tax_name,
--pol.type_1099,
-- pol.terms_id,
pol.price_type_lookup_code,
pol.min_release_amount,
pol.price_break_lookup_code,
pol.ussgl_transaction_code,
pol.closed_date,
pol.tax_code_id,
decode(poh.start_date,null,p_start_date,poh.start_date),
decode(poh.end_date,null,p_end_date,poh.end_date)
from po_lines_all pol,
po_headers_all poh
where pol.po_line_id =p_doc_line_id
and poh.po_header_id = pol.po_header_id;
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in lines = '||to_char(sql%rowcount),1);
-- Insert the rows from po_line_locations_all
-- Modified by Renga Kannan on 04/16/2002
-- Added ship_to_location_id in the interface table.
Insert into Po_lines_interface(
interface_line_id,
interface_header_id,
line_num,
shipment_num,
shipment_type,
line_type_id,
source_shipment_id,
item_id,
item,
item_revision,
category_id,
unit_of_measure,
quantity,
terms_id,
days_early_receipt_allowed,
days_late_receipt_allowed,
ship_to_organization_id,
ship_to_location_id,
price_discount,
unit_price,
effective_date,
expiration_date,
shipment_attribute_category,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
shipment_attribute7,
shipment_attribute8,
shipment_attribute9,
shipment_attribute10,
shipment_attribute11,
shipment_attribute12,
shipment_attribute13,
shipment_attribute14,
shipment_attribute15,
last_update_date)
--price_override) -- Check with Beth
select
po_lines_interface_s.nextval,
p_int_header_id,
null,
poll.shipment_num,
poll.shipment_type,
pol.line_type_id,
poll.source_shipment_id,
p_item_id,
p_segment1,
null,
pol.category_id,
poll.unit_meas_lookup_code,
poll.quantity,
null,
poll.days_early_receipt_allowed,
poll.days_late_receipt_allowed,
poll.ship_to_organization_id,
poll.ship_to_location_id,
poll.price_discount,
p_price*(1-poll.price_discount/100),
poll.start_date,
poll.end_date,
poll.attribute_category,
poll.attribute1,
poll.attribute2,
poll.attribute3,
poll.attribute4,
poll.attribute5,
poll.attribute6,
poll.attribute7,
poll.attribute8,
poll.attribute9,
poll.attribute10,
poll.attribute11,
poll.attribute12,
poll.attribute13,
poll.attribute14,
poll.attribute15,
sysdate
-- p_price*poll.price_discount/100
-- price_discount
from po_line_locations_all poll,
po_lines_all pol
where pol.po_line_id = p_doc_line_id
and pol.po_line_id = poll.po_line_id;
oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in locations = '||to_char(sql%rowcount),1);
End insert_blanket_line;
select vendor_id,
vendor_site_id
from mrp_sources_v
where assignment_set_id = p_assg_set_id
and inventory_item_id = p_config_item_id
and assignment_type in (3,6)
and source_type = 3
and vendor_site_id is not null;
select distinct operating_unit organization_id
from inv_organization_info_v
where organization_id in (select organization_id
from bom_cto_src_orgs
where line_id = p_line_id
and organization_type = 3);
Select option_specific_sourced
into l_option_specific
from mtl_system_items
where inventory_item_id = p_config_item_id
and rownum<2;
select config_creation
into l_config_creation
from bom_cto_order_lines
where line_id = p_line_id;
Select Org_id
into l_oper_unit
from po_vendor_sites_all
where vendor_site_id = p_vendor_details(i).vendor_site_id;
x_vendor_details.delete(i);
SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3) */
sourcing_rule_id
INTO l_sourcing_rule_id
FROM mrp_sr_assignments
WHERE inventory_item_id = p_item_id
AND assignment_set_id = p_assgn_set_id
AND sourcing_rule_type = 1
AND assignment_type = 3;
select msro.effective_date,
msro.disable_date
into x_start_date,
x_end_date
from mrp_sourcing_rules msr,
mrp_sr_receipt_org msro
where msr.sourcing_rule_id = msro.sourcing_rule_id
and msr.sourcing_rule_id = l_sourcing_rule_id
and trunc(sysdate) between trunc(nvl(msro.effective_date,sysdate)) and trunc(nvl(msro.disable_date,sysdate+1));
sql_stmt := 'select distinct msi.inventory_item_id '||
'from mtl_system_items msi '||
'where msi.base_item_id is not null '||
'and msi.bom_item_type = 4 '||
'and msi.replenish_to_order_flag = ''Y'' '||
'and msi.pick_components_flag = ''N'' ';
SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
from oe_order_lines_all oel,
oe_order_headers_all oeh,
mtl_system_items msi
where oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.header_id = oeh.header_id
and oel.source_type_code = 'INTERNAL' ---- For drop ship bug# 2234858
and msi.bom_item_type = 4
and oel.open_flag = 'Y'
and nvl(oel.cancelled_flag, 'N') = 'N'
and oel.schedule_status_code = 'SCHEDULED'
and oel.ordered_quantity > 0 -- bugfix 3043284: OQ > 0 is the correct condn instead of OQ-CQ
and msi.base_item_id is not null -- 4172156. Added to process only configured ATO items.
--
-- Given a Order Line ID
--
and (p_sales_order_line_id is NULL
or
oel.ato_line_id = p_sales_order_line_id
)--- 4172156. Added condition to pick up ATO item line also.
--
-- Given an Order Number
--
and ((p_sales_order is null)
or
(p_sales_order is not null
and oeh.order_number = p_sales_order))
--
-- Given an Organization
--
and ( p_organization_id is null
or oel.ship_from_org_id = p_organization_id
)
--
-- Given config
--
and (p_config_id is null or
msi.inventory_item_id = p_config_id)
--
-- Given base model
--
and (p_base_model_id is null or
msi.base_item_id = p_base_model_id)
--
-- Given created days ago
--
and (p_created_days_ago is null or
msi.creation_date > trunc(sysdate) - p_created_days_ago)
--
-- Given Offset days
--
and ((p_offset_days is null)
/* Bug 5520934 begin: We need to honour bom calendar in offset days calculation */
-- or (oel.schedule_ship_date <= trunc( sysdate + p_offset_days)))
or (sysdate >= (select cal.calendar_date
from bom_calendar_dates cal,
mtl_parameters mp
where mp.organization_id = oel.ship_from_org_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num = (select cal2.prior_seq_num - nvl(p_offset_days, 0)
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id = mp.calendar_exception_set_id
and cal2.calendar_date = trunc(oel.schedule_ship_date)))))
-- end bugfix 5520934
--
-- Given load type
--
and (p_load_type is null or
(p_load_type = 1
and msi.base_item_id is not null
and msi.auto_created_config_flag = 'Y') or
(p_load_type = 2
and msi.base_item_id is not null
and msi.auto_created_config_flag <> 'Y') or
(p_load_type = 3
and msi.base_item_id is not null)
)
--
-- for all the records with the status of REQ-CREATED
--
and (oel.item_type_code = 'CONFIG'
or(oel.line_id=oel.ato_line_id
and oel.item_type_code in ('STANDARD','OPTION')
)
)-- 4172156. Added Condition to pickup ATO item Line also
and msi.replenish_to_order_flag = 'Y'
and oel.ato_line_id is not null -- bugfix 3164399: although item_type_code will restrict
and msi.pick_components_flag = 'N'; -- the criteria, added the ato_line_id for consistency
select distinct config_item_id
from bom_cto_order_lines_upg
where ato_line_id = line_id -- get only the parent configs
and status = 'MRP_SRC';
SELECT substrb(kfv.concatenated_segments,1,35)
INTO l_pass_config_description
FROM mtl_system_items_kfv kfv
WHERE kfv.inventory_item_id = passedItems(j).config_item_id
AND rownum = 1;
SELECT substrb(kfv.concatenated_segments,1,35)
INTO l_err_config_description
FROM mtl_system_items_kfv kfv
WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
AND rownum = 1;
CTO_AUTO_PROCURE_PK.g_oper_unit_list.delete(i);
select distinct nvl(fsp.inventory_organization_id,0)
bulk collect into l_orgs_list
from inv_organization_info_v org,
financials_system_params_all fsp,
mtl_system_items msi
where org.organization_id in (select organization_id
from mtl_system_items_b
where inventory_item_id = p_config_item_id)
and fsp.org_id = org.operating_unit
and msi.inventory_item_id = p_config_item_id
and msi.organization_id = fsp.inventory_organization_id;
select base_item_id
into l_model_item_id
from mtl_system_items
where inventory_item_id = p_config_item_id
and rownum = 1;
Select list_price_per_unit
into l_list_price
from mtl_system_items
where inventory_item_id = p_config_item_id
and organization_id = l_orgs_list(i);
Update Mtl_system_items
set list_price_per_unit = x_rolled_price
where inventory_item_id = p_config_item_id
and organization_id = l_orgs_list(i)
and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of rows updated = '||sql%rowcount,1);
select config_orgs
into l_config_creation
from mtl_system_items
where inventory_item_id = l_model_item_id
and rownum=1;
select 'x'
into l_buy_found
from bom_cto_src_orgs
where line_id = p_line_id
and organization_type = 3
and rownum=1;
sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, status, inventory_item_id)'||
'select oel.line_id, oel.org_id, 1, 0 '||
'from oe_order_lines_all oel,'||
' mtl_system_items msi,'||
' wf_item_activity_statuses was,'||
' wf_process_activities WPA ';
' (select oeh.header_id '||
' from oe_order_headers_all oeh, '||
' oe_transaction_types_tl oet, '||
' mtl_sales_orders mso '||
' where oeh.order_number = to_char( :p_sales_order) '||
' and oeh.order_type_id = oet.transaction_type_id '||
' and mso.segment1 = to_char(oeh.order_number) '||
' and mso.segment2 = oet.name '||
' and oet.language = (select language_code '||
' from fnd_languages '||
' where installed_flag = ''B'') ' ||
' ) ' ;
sql_stmt := sql_stmt ||' and oel.line_id in (select oelc.line_id '||
'from oe_order_lines_all oelc '||
'where oelc.ato_line_id = :p_sales_order_line_id '||
'and (oelc.item_type_code = ''CONFIG'' '||
' or (oelc.item_type_code in (''STANDARD'',''OPTION'') '||
' and ato_line_id = line_id)) '||
') ';
sql_stmt := sql_stmt ||' and sysdate >= (select cal.calendar_date
from bom_calendar_dates cal,
mtl_parameters mp
where mp.organization_id = oel.ship_from_org_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num = (select cal2.prior_seq_num - nvl(:p_offset_days, 0)
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id = mp.calendar_exception_set_id
and cal2.calendar_date = trunc(oel.schedule_ship_date))) ';
we have selected. The drive_mark variable tells us which parameters
we are using, so we are sure to send the right ones to SQL.
*/
if (drive_mark = 0) then
-- No (optional) parameter is passed
EXECUTE IMMEDIATE sql_stmt;
PROCEDURE update_bcolt_line_status(p_line_id NUMBER,
p_status NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS ;
update bom_cto_order_lines_temp
set status = p_status
where line_id = p_line_id;
oe_debug_pub.add('update_bcolt_line_status: ' || 'others excpn::'||sqlerrm,1);
END update_bcolt_line_status;
SELECT end_customer_id,
end_customer_site_use_id
INTO l_cust_id,
l_cust_site_id
FROM oe_order_lines_all
WHERE line_id = p_interface_source_line_id;
SELECT mic.category_id
INTO l_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;