[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT coo.organization_id,
p_mrp_compile_designator,
p_mps_schedule_designator,
p_drp_compile_designator
FROM chv_org_options coo
WHERE nvl(p_ship_to_organization_id,
coo.organization_id) = coo.organization_id;
SELECT dummy,
p_mrp_compile_designator,
p_mps_schedule_designator,
p_drp_compile_designator
FROM dual;
SELECT DISTINCT(paa.vendor_id||paa.vendor_site_id),
p_bucket_pattern_id,
paa.vendor_id,
paa.vendor_site_id,
p_schedule_subtype
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
AND nvl(p_item_id, paa.item_id) = paa.item_id
AND (p_category_set_id is null
OR
paa.item_id in (
select mic.inventory_item_id
from mtl_item_categories mic
where mic.category_set_id = p_category_set_id
and mic.organization_id = x_organization_id
and nvl(p_category_id,mic.category_id) = mic.category_id))
AND nvl(paa.enable_autoschedule_flag,'N') = 'N'
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'))
AND nvl(paa.scheduler_id,-1) =
NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
AND (p_planner_code IS NULL
OR
EXISTS (SELECT 'check if planner exists in mtl_system_items'
FROM mtl_system_items msi,mtl_planners mtp
WHERE msi.planner_code = p_planner_code
AND msi.organization_id = x_organization_id
AND mtp.organization_id = x_organization_id
AND mtp.planner_code = p_planner_code
AND msi.inventory_item_id = paa.item_id))
AND (p_buyer_id IS NULL
OR
EXISTS (SELECT 'check if buyer exists in mtl_system_items'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = paa.item_id
AND msi.organization_id = x_organization_id
AND msi.buyer_id = p_buyer_id));
SELECT DISTINCT(paa.vendor_id||paa.vendor_site_id||decode(p_schedule_type,
'PLAN_SCHEDULE',paa.plan_bucket_pattern_id,
paa.ship_bucket_pattern_id)||decode(p_schedule_type,
'PLAN_SCHEDULE',paa.plan_schedule_type,
paa.ship_schedule_type)),
decode(p_schedule_type,'PLAN_SCHEDULE',paa.plan_bucket_pattern_id,
paa.ship_bucket_pattern_id),
paa.vendor_id,
paa.vendor_site_id,
decode(p_schedule_type, 'PLAN_SCHEDULE', paa.plan_schedule_type,
paa.ship_schedule_type)
FROM po_asl_attributes_val_v paa,
chv_bucket_patterns cbp,
po_vendor_sites_all povs
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
AND nvl(p_item_id, paa.item_id) = paa.item_id
/* Bug 2616988 fixed. added the below three statements so that only those
data pertaining to current operating unit will be picked up.
*/
AND povs.vendor_site_id = paa.vendor_site_id
AND povs.vendor_id = paa.vendor_id
AND povs.org_id = x_org_id
AND (p_category_set_id is null
OR
paa.item_id in (
select mic.inventory_item_id
from mtl_item_categories mic
where mic.category_set_id = p_category_set_id
and mic.organization_id = x_organization_id
and nvl(p_category_id,mic.category_id) = mic.category_id))
AND nvl(paa.enable_autoschedule_flag, 'N') = 'Y'
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'))
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.plan_bucket_pattern_id = cbp.bucket_pattern_id
AND nvl(cbp.inactive_date, sysdate) < sysdate + 1)
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.ship_bucket_pattern_id = cbp.bucket_pattern_id
AND nvl(cbp.inactive_date, sysdate) < sysdate + 1))
AND nvl(paa.scheduler_id,-1) =
NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
AND (p_planner_code IS NULL
OR
EXISTS (SELECT 'check if planner exists in mtl_system_items'
FROM mtl_system_items msi,mtl_planners mtp
WHERE msi.planner_code = p_planner_code
AND msi.inventory_item_id = paa.item_id
AND mtp.organization_id = x_organization_id
AND mtp.planner_code = p_planner_code
AND msi.organization_id = x_organization_id))
AND (p_buyer_id IS NULL
OR
EXISTS (SELECT 'check if buyer exists in mtl_system_items'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = paa.item_id
AND msi.organization_id = x_organization_id
AND msi.buyer_id = p_buyer_id));
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id));
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id);
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
AND paa.item_id = NVL(p_item_id,paa.item_id);
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
AND paa.item_id = NVL(p_item_id,paa.item_id)
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'))
AND paa.enable_autoschedule_flag = 'Y';
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
AND paa.item_id = NVL(p_item_id,paa.item_id)
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'));
SELECT paa.vendor_id,
paa.vendor_site_id
FROM po_asl_attributes_val_v paa,
chv_bucket_patterns cbp
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(using_organization_id = x_organization_id))
AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
AND paa.item_id = NVL(p_item_id,paa.item_id)
AND paa.enable_autoschedule_flag = 'Y'
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'))
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.plan_bucket_pattern_id = cbp.bucket_pattern_id
AND nvl(cbp.inactive_date, sysdate) < sysdate + 1)
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.ship_bucket_pattern_id = cbp.bucket_pattern_id
AND nvl(cbp.inactive_date, sysdate) < sysdate + 1));
SELECT chv_schedule_headers_s.NEXTVAL
INTO x_schedule_id
FROM DUAL;
INSERT INTO chv_schedule_headers(schedule_id,
vendor_id,
vendor_site_id,
schedule_type,
schedule_subtype,
schedule_num,
schedule_revision,
schedule_horizon_start,
schedule_horizon_end,
bucket_pattern_id,
schedule_owner_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
mps_schedule_designator,
mrp_compile_designator,
drp_compile_designator,
schedule_status,
inquiry_flag,
include_future_releases_flag,
last_update_login,
batch_id)
VALUES (x_schedule_id,
x_vendor_id,
x_vendor_site_id,
p_schedule_type,
x_schedule_subtype,
x_schedule_num,
x_schedule_revision,
p_horizon_start_date,
x_horizon_end_date,
x_bucket_pattern_id,
p_owner_id,
SYSDATE, -- last_update_date
x_user_id, -- last_updated_by
SYSDATE, -- creation_date
x_user_id, -- created_by
DECODE(p_multi_org_flag, 'N',
x_organization_id, '',
x_organization_id, ''),
x_mps_schedule_designator,
x_mrp_compile_designator,
x_drp_compile_designator,
'IN_PROCESS', -- schedule_status
DECODE(p_schedule_category, 'SIMULATION',
'Y', 'N'), -- inquiry_flag
p_include_future_releases,
x_login_id, -- last_update_login
p_batch_id);
DELETE from chv_schedule_headers
where schedule_id = x_schedule_id;
SELECT edi_flag
INTO x_transmission_method
FROM ece_tp_headers eth,
ece_tp_details etd,
po_vendor_sites pvs,
chv_schedule_headers csh
WHERE eth.tp_header_id = etd.tp_header_id
AND decode(csh.schedule_type, 'PLAN_SCHEDULE', 'SPSO', 'SSSO') =
etd.document_id
AND eth.tp_header_id = pvs.tp_header_id
AND csh.vendor_site_id = pvs.vendor_site_id
AND csh.schedule_id = x_schedule_id;
SELECT transmission_method
INTO x_transmission_method
FROM ECE_CONTROL ECC,
CHV_SCHEDULE_HEADERS CSH
WHERE CSH.schedule_id = x_schedule_id
AND decode(CSH.schedule_type, 'PLAN_SCHEDULE', 'SPSO', 'SSSO')
= ECC.DOCUMENT_TYPE
AND CSH.vendor_id = ECC.entity_id
AND CSH.vendor_site_id = ECC.entity_site_id
AND ECC.entity_type = 'SUPPLIER'
AND ECC.direction = 'O';
UPDATE chv_schedule_headers
SET communication_code = 'EDI'
WHERE schedule_id = x_schedule_id;
UPDATE chv_schedule_headers
SET communication_code = 'BOTH'
WHERE schedule_id = x_schedule_id;
UPDATE chv_schedule_headers
SET communication_code = 'PRINT'
WHERE schedule_id = x_schedule_id;
SELECT 'Y' INTO x_dummy
FROM mtl_planners mpl
WHERE mpl.planner_code = p_planner_code
AND mpl.organization_id = x_organization_id
AND nvl(mpl.disable_date,sysdate +1 ) > sysdate;
SELECT 'Y' INTO x_dummy
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = x_organization_id
AND msi.buyer_id = p_buyer_id;
SELECT organization_name INTO x_organization_name from org_organization_definitions
where organization_id = x_organization_id;
SELECT vendor_name into x_vendor_name from po_vendors where p_vendor_id = vendor_id;
SELECT vendor_site_code into x_vendor_code from po_vendor_sites where p_vendor_site_id = vendor_site_id and p_vendor_id = vendor_id;
SELECT description into x_item_desc from mtl_system_items where p_item_id = inventory_item_id and organization_id = x_organization_id;
select 'SPSO' || lpad(substr(to_char(ECE_OUTPUT_RUNS_S.nextval),
decode(length(ECE_OUTPUT_RUNS_S.nextval),1,-1,2, -2,3, -3,-4),
4), 4, '0') || '.dat'
into x_ece_file
from dual;
select x_ece_path || x_ece_file
into x_ece_path_file
from dual;
select 'SSSO' || lpad(substr(to_char(ECE_OUTPUT_RUNS_S.nextval),
decode(length(ECE_OUTPUT_RUNS_S.nextval),1,-1,2, -2,3, -3,-4),
4), 4, '0') || '.dat'
into x_ece_file
from dual;
select x_ece_path || x_ece_file
into x_ece_path_file
from dual;
SELECT chv_schedule_headers_s2.NEXTVAL
INTO x_count_l
FROM DUAL;
SELECT NVL(MAX(schedule_revision),0) + 1
INTO x_count_l
FROM chv_schedule_headers
WHERE schedule_num = x_schedule_num;
SELECT paa.using_organization_id,
paa.asl_id,
paa.item_id,
NVL(paa.enable_authorizations_flag, 'N'),
paa.purchasing_unit_of_measure
FROM po_asl_attributes_val_v paa
WHERE ((paa.using_organization_id = -1 and not exists
(SELECT *
FROM po_asl_attributes_val_v paa2
WHERE paa2.using_organization_id = x_organization_id
AND paa2.vendor_id = paa.vendor_id
AND paa2.vendor_site_id = paa.vendor_site_id
AND paa2.item_id = paa.item_id ))
or
(paa.using_organization_id = x_organization_id))
AND paa.asl_id = paa.asl_id
AND paa.vendor_id = x_vendor_id
AND paa.vendor_site_id = x_vendor_site_id
AND nvl(p_item_id, paa.item_id) = paa.item_id
AND exists (select * from mtl_system_items
where inventory_item_id = paa.item_id
and organization_id = x_organization_id) /* Bug 462403 vpawar */
AND (p_category_set_id is null
OR
paa.item_id in (
select mic.inventory_item_id
from mtl_item_categories mic
where mic.category_set_id = p_category_set_id
and mic.organization_id = x_organization_id
and nvl(p_category_id,mic.category_id) = mic.category_id))
AND nvl(p_autoschedule_flag,'N') = nvl(paa.enable_autoschedule_flag, 'N')
AND ((p_schedule_type = 'PLAN_SCHEDULE'
AND paa.enable_plan_schedule_flag = 'Y')
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND paa.enable_ship_schedule_flag = 'Y'))
AND (nvl(p_autoschedule_flag, 'N') = 'N'
OR
(p_autoschedule_flag = 'Y'
AND
((p_schedule_type = 'PLAN_SCHEDULE'
AND x_bucket_pattern_id = paa.plan_bucket_pattern_id)
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND x_bucket_pattern_id = paa.ship_bucket_pattern_id))))
/* Bug 692450 Not checking schedule subtype */
AND (nvl(p_autoschedule_flag, 'N') = 'N'
OR
(p_autoschedule_flag = 'Y'
AND
((p_schedule_type = 'PLAN_SCHEDULE'
AND x_schedule_subtype = paa.plan_schedule_type)
OR
(p_schedule_type = 'SHIP_SCHEDULE'
AND x_schedule_subtype = paa.ship_schedule_type))))
/* Bug 692450 Not checking schedule subtype */
AND nvl(paa.scheduler_id,-1) =
NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
AND (p_planner_code IS NULL
OR
EXISTS (SELECT 'check if planner exists in mtl_system_items'
FROM mtl_system_items msi,mtl_planners mtp
WHERE msi.planner_code = p_planner_code
AND msi.inventory_item_id = paa.item_id
AND mtp.organization_id = x_organization_id
AND mtp.planner_code = p_planner_code
AND msi.organization_id = x_organization_id))
AND (p_buyer_id IS NULL
OR
EXISTS (SELECT 'check if buyer exists in mtl_system_items'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = paa.item_id
AND msi.organization_id = x_organization_id
AND msi.buyer_id = p_buyer_id));
SELECT cso.organization_id
FROM chv_schedule_organizations cso
WHERE cso.batch_id = p_batch_id;
SELECT dummy
FROM sys.dual;
SELECT distinct csi.organization_id
FROM chv_schedule_items csi
WHERE csi.schedule_id = x_old_schedule_id;
SELECT csi.schedule_item_id,
csi.item_id,
decode(csi.item_planning_method,'MRP_PLANNED',3,
'MPS_PLANNED',2,
'DRP_PLANNED',4),
csi.organization_id
FROM chv_schedule_items csi
WHERE csi.schedule_id = x_schedule_id
AND nvl(csi.rebuild_flag, 'N') = 'Y';
SELECT csi.schedule_item_id,
csi.organization_id,
csi.item_id,
decode(csi.item_planning_method,'MRP_PLANNED',3,
'MPS_PLANNED',2,
'DRP_PLANNED',4),
csi.purchasing_unit_of_measure
FROM chv_schedule_items csi
WHERE csi.schedule_id = x_old_schedule_id;
SELECT csi.schedule_item_id,
csi.organization_id,
csi.item_id,
decode(csi.item_planning_method,'MRP_PLANNED',3,
'MPS_PLANNED',2,
'DRP_PLANNED',4),
csi.purchasing_unit_of_measure
FROM chv_schedule_items csi
WHERE csi.schedule_id = x_old_schedule_id
AND csi.organization_id = x_organization_id;
SELECT paa.asl_id,
nvl(paa.enable_authorizations_flag,'N'),
paa.purchasing_unit_of_measure,
max(paa.using_organization_id)
INTO x_asl_id_l,
x_enable_authorizations_flag_l,
x_purch_unit_of_measure_l,
x_using_org_id_l
FROM po_asl_attributes_val_v paa
WHERE paa.vendor_id = x_vendor_id
AND paa.vendor_site_id = x_vendor_site_id
AND paa.item_id = x_item_id_l
AND paa.using_organization_id =
(SELECT MAX(paa2.using_organization_id)
FROM po_asl_attributes_val_v paa2
WHERE decode(paa2.using_organization_id, -1,
x_organization_id, paa2.using_organization_id) =
x_organization_id
AND paa2.vendor_id = x_vendor_id
AND paa2.vendor_site_id = x_vendor_site_id
AND paa2.item_id = x_item_id_l)
GROUP BY paa.asl_id, paa.enable_authorizations_flag,
paa.purchasing_unit_of_measure;
SELECT primary_unit_of_measure
INTO x_primary_unit_of_measure_l
FROM MTL_system_items
WHERE organization_id = x_organization_id
AND inventory_item_id = x_item_id_l;
SELECT paa.asl_id,
nvl(paa.enable_authorizations_flag,'N'),
max(paa.using_organization_id)
INTO x_asl_id_l,
x_enable_authorizations_flag_l,
x_using_org_id_l
FROM po_asl_attributes_val_v paa
WHERE paa.vendor_id = x_vendor_id
AND paa.vendor_site_id = x_vendor_site_id
AND paa.item_id = x_item_id_l
AND paa.using_organization_id =
(SELECT MAX(paa2.using_organization_id)
FROM po_asl_attributes_val_v paa2
WHERE decode(paa2.using_organization_id, -1,
x_organization_id, paa2.using_organization_id) =
x_organization_id
AND paa2.vendor_id = x_vendor_id
AND paa2.vendor_site_id = x_vendor_site_id
AND paa2.item_id = x_item_id_l)
GROUP BY paa.asl_id, paa.enable_authorizations_flag;
/* SELECT decode(p_schedule_category, 'REVISION', x_item_planning_method_l, */
SELECT mrp_planning_code,
primary_unit_of_measure
INTO x_item_planning_method_l,
x_primary_unit_of_measure_l
FROM MTL_system_items
WHERE organization_id = x_organization_id
AND inventory_item_id = x_item_id_l;
SELECT nvl(enable_cum_flag,'N')
INTO x_enable_cum_flag_l
FROM chv_org_options
WHERE organization_id = x_organization_id;
/* Bug 4485196 fixed. added to_char to below select clause as it was failing with
ORA-6502 error on 10G database.
*/
SELECT max(to_char('Y'))
INTO x_enable_cum_flag_l
FROM chv_cum_periods
WHERE organization_id = x_organization_id
AND p_horizon_start_date between
cum_period_start_date and nvl(cum_period_end_date,p_horizon_start_date+1);
SELECT organization_name INTO x_organization_name FROM
org_organization_definitions WHERE
organization_id = x_organization_id;
SELECT chv_schedule_items_s.NEXTVAL
INTO x_schedule_item_id_l
FROM DUAL;
INSERT INTO chv_schedule_items (schedule_id,
schedule_item_id,
organization_id,
item_id,
item_planning_method,
po_header_id,
po_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
rebuild_flag,
item_confirm_status,
starting_cum_quantity,
starting_auth_quantity,
starting_cum_qty_primary,
starting_auth_qty_primary,
last_receipt_transaction_id,
purchasing_unit_of_measure,
primary_unit_of_measure,
last_update_login)
VALUES (x_schedule_id,
x_schedule_item_id_l,
x_organization_id,
x_item_id_l,
x_plan_lookup,
x_po_header_id_l,
x_po_line_id_l,
SYSDATE, -- last_update_date
x_user_id, -- last_updated_by
SYSDATE, -- creation_date
x_user_id, -- created_by
'N', -- rebuild_flag
'IN_PROCESS',
x_cum_quantity_received_l,
0, -- starting_auth_qty to be updated later
x_cum_qty_received_primary_l,
0, -- start_auth_qty_prim to be updated later
x_last_receipt_tranx_id_l,
x_purch_unit_of_measure_l,
x_primary_unit_of_measure_l,
x_login_id); -- last_update_login
update chv_schedule_items
set rebuild_flag = 'N',
item_confirm_status = 'IN_PROCESS',
last_updated_by = x_user_id,
last_update_date = sysdate,
last_update_login = x_login_id,
purchasing_unit_of_measure = x_purch_unit_of_measure_l,
primary_unit_of_measure = x_primary_unit_of_measure_l,
starting_cum_quantity = x_cum_quantity_received_l,
starting_cum_qty_primary=x_cum_qty_received_primary_l,
last_receipt_transaction_id=x_last_receipt_tranx_id_l
where schedule_item_id = x_schedule_item_id_l;
SELECT uom_code
INTO x_primary_uom_code_l
FROM mtl_units_of_measure
WHERE unit_of_measure = x_primary_unit_of_measure_l;
SELECT uom_code
INTO x_purchasing_uom_code_l
FROM mtl_units_of_measure
WHERE unit_of_measure = X_purch_unit_of_measure_l;
SELECT min(poh.po_header_id)
INTO x_po_header_id_l
FROM po_headers poh,
chv_item_orders cio,
po_lines pol
WHERE poh.creation_date =
(SELECT min(poh2.creation_date)
FROM po_headers poh2,
po_lines pol2,
chv_item_orders cio2
WHERE cio2.schedule_id = x_schedule_id
AND poh2.po_header_id = cio2.document_header_id
AND cio2.schedule_item_id = x_schedule_item_id_l
AND poh2.po_header_id = pol2.po_header_id
AND pol2.item_id = x_item_id_l
AND cio2.supply_document_type = 'RELEASE')
AND cio.schedule_id = x_schedule_id
AND poh.po_header_id = cio.document_header_id
AND cio.schedule_item_id = x_schedule_item_id_l
AND cio.supply_document_type = 'RELEASE'
AND pol.item_id = x_item_id_l
AND poh.po_header_id = pol.po_header_id
AND poh.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id ;
SELECT min(pol.po_line_id)
INTO x_po_line_id_l
FROM po_lines pol
WHERE pol.po_header_id = x_po_header_id_l
AND pol.item_id = x_item_id_l;
SELECT count(*)
INTO x_number_of_blanket_agreements
FROM po_asl_documents,
po_headers poh
WHERE asl_id = x_asl_id_l
AND using_organization_id = x_using_org_id_l
AND document_type_code = 'BLANKET'
AND poh.po_header_id = document_header_id
AND nvl(poh.supply_agreement_flag,'N') = 'Y'
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.closed_code,'OPEN') = 'OPEN'
AND poh.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id ;
SELECT document_header_id,
document_line_id
INTO x_po_header_id_l,
x_po_line_id_l
FROM po_asl_documents
, po_headers poh
WHERE document_type_code = 'BLANKET'
AND poh.po_header_id = document_header_id
AND nvl(poh.supply_agreement_flag,'N') = 'Y'
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.closed_code,'OPEN') = 'OPEN'
AND using_organization_id = x_using_org_id_l
AND asl_id = x_asl_id_l
AND poh.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id ;
SELECT min(poh.po_header_id)
INTO x_po_header_id_l
FROM po_headers poh,
po_lines pol,
po_asl_documents pad
WHERE poh.creation_date =
(SELECT min(poh2.creation_date)
FROM po_headers poh2,
po_lines pol2,
po_asl_documents pad2
WHERE poh2.po_header_id = pad2.document_header_id
AND pad2.asl_id = x_asl_id_l
AND pad2.using_organization_id = x_using_org_id_l
AND poh2.po_header_id = pol2.po_header_id
AND pol2.item_id = x_item_id_l
AND pad2.document_type_code = 'BLANKET')
AND pad.asl_id = x_asl_id_l
AND pad.using_organization_id = x_using_org_id_l
AND poh.po_header_id = pol.po_header_id
AND pol.item_id = x_item_id_l
AND pad.document_type_code = 'BLANKET'
AND poh.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id ;
SELECT min(pol.po_line_id)
INTO x_po_line_id_l
FROM po_lines pol,
po_asl_documents
WHERE pol.po_header_id = x_po_header_id_l
AND pol.item_id = x_item_id_l
AND pol.po_header_id = document_header_id
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED';
SELECT min(poh.po_header_id)
INTO x_po_header_id_l
FROM po_headers poh,
po_lines pol
WHERE poh.creation_date =
(SELECT min(poh2.creation_date)
FROM po_headers poh2,
po_lines pol2
WHERE poh2.po_header_id = pol2.po_header_id
AND pol2.item_id = x_item_id_l
AND poh2.type_lookup_code = 'BLANKET'
AND poh2.authorization_status = 'APPROVED'
AND nvl(poh2.cancel_flag,'N') = 'N'
AND nvl(poh2.closed_code,'OPEN') = 'OPEN')
AND poh.po_header_id = pol.po_header_id
AND pol.item_id = x_item_id_l
AND poh.type_lookup_code = 'BLANKET'
AND poh.authorization_status = 'APPROVED'
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.closed_code,'OPEN') = 'OPEN'
AND poh.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id ;
SELECT min(pol.po_line_id)
INTO x_po_line_id_l
FROM po_lines pol
WHERE pol.po_header_id = x_po_header_id_l
AND pol.item_id = x_item_id_l
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pol.closed_code,'OPEN') = 'OPEN';
UPDATE chv_schedule_items
SET po_header_id = x_po_header_id_l,
po_line_id = x_po_line_id_l,
starting_auth_quantity = x_starting_auth_quantity_l,
starting_auth_qty_primary = x_starting_auth_qty_primary_l
WHERE schedule_item_id = x_schedule_item_id_l;
chv_create_authorizations.insert_authorizations(x_organization_id,
x_schedule_id,
x_schedule_item_id_l,
x_asl_id_l,
p_horizon_start_date,
x_horizon_end_date,
x_starting_auth_quantity_l,
x_starting_auth_qty_primary_l,
x_cum_quantity_received_l,
x_cum_qty_received_primary_l,
x_cum_period_end_date_l,
x_purch_unit_of_measure_l,
x_primary_unit_of_measure_l,
x_enable_cum_flag_l);