[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DESCRIPTION: This API inserts row into po_approved_supplier_list,
po_asl_attributes,po_asl_documents
PARAMETERS: X_interface_header_id, X_interface_line_id -
Sequence number generated from po_headers_interface_s
and po_lines_interface_s.
X_item_id, X_vendor_id, X_po_header_id,
X_po_line_id,X_document_type
Values of the document that is created from
the PDOI interface tables.
X_category_id - Creatgory_id for the Category
X_header_processable_flag - Value is N if there was any
error encountered. Set in the procedure
PO_INTERFACE_ERRORS_SV1.handle_interface_errors
X_po_interface_error_code - This is the code used to populate interface_type
field in po_interface_errors table.
p_sourcing_level
This parameter specifies if the Sourcing Rule /ASL should be Global/Local
and if the assignment should be Item or Item Organization.
=======================================================================*/
PROCEDURE create_po_asl_entries
( x_interface_header_id IN NUMBER,
X_interface_line_id IN NUMBER,
X_item_id IN NUMBER,
X_category_id IN NUMBER,
X_po_header_id IN NUMBER,
X_po_line_id IN NUMBER,
X_document_type IN VARCHAR2,
x_vendor_site_id IN NUMBER, -- GA FPI
X_rel_gen_method IN VARCHAR2,
x_asl_org_id IN NUMBER,
X_header_processable_flag OUT NOCOPY VARCHAR2,
X_po_interface_error_code IN VARCHAR2,
--
p_sourcing_level IN VARCHAR2 DEFAULT NULL
--
)
--
IS
--
x_last_update_date date := sysdate;
x_last_updated_by number := fnd_global.user_id ;
x_last_update_login number := fnd_global.user_id;
SELECT org_id
INTO x_vs_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = x_vendor_site_id;
We need to select the value of inventory organization id only if the value
of sourcing_level is is 'ITEM'. This would happen if the calling program is Approval
Workflow or if POASLGEN AND PDOI call the program with Sourcing Level set to Item
*/
IF(nvl(p_sourcing_level,'ITEM')='ITEM-ORGANIZATION') THEN
l_using_organization_id :=x_asl_org_id;
SELECT inventory_organization_id
INTO x_owning_organization_id
FROM financials_system_params_all
WHERE nvl(org_id,-99) = nvl(x_vs_org_id,-99);
SELECT purchasing_enabled_flag,
outside_operation_flag
INTO x_purchasing_flag,
x_osp_flag
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_owning_organization_id;
||x_osp_flag||'. Insert warning msg');
PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
SELECT status_id
INTO x_asl_status_id
FROM po_asl_statuses
WHERE asl_default_flag = 'Y';
SELECT vendor_id,
type_lookup_code
INTO x_vendor_id,
x_type_lookup_code
FROM po_headers_all --
WHERE po_header_id = X_po_header_id;
SELECT pol.unit_meas_lookup_code,
pol.vendor_product_num
INTO x_purch_uom,
x_vendor_product_num
FROM po_lines_all pol --
WHERE po_line_id = x_po_line_id;
SELECT PO_APPROVED_SUPPLIER_LIST_S.NEXTVAL
INTO x_asl_id
FROM SYS.DUAL;
INSERT INTO PO_APPROVED_SUPPLIER_LIST (
asl_id ,
using_organization_id ,
owning_organization_id ,
vendor_business_type ,
asl_status_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
vendor_id ,
vendor_site_id ,
item_id ,
primary_vendor_item ,
last_update_login ,
request_id
) VALUES (
x_asl_id ,
l_using_organization_id, --
x_owning_organization_id ,
'DIRECT' ,
x_asl_status_id ,
x_last_update_date ,
x_last_updated_by ,
x_creation_date ,
x_created_by ,
x_vendor_id ,
x_vendor_site_id ,
x_item_id ,
x_vendor_product_num ,
x_last_update_login ,
null
);
INSERT INTO po_asl_attributes(
asl_id,
using_organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
document_sourcing_method,
release_generation_method,
enable_plan_schedule_flag,
enable_ship_schedule_flag,
enable_autoschedule_flag,
enable_authorizations_flag,
vendor_id,
vendor_site_id,
purchasing_unit_of_measure,
item_id
) VALUES (
x_asl_id,
l_using_organization_id, --
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
'ASL',
DECODE(X_type_lookup_code, 'BLANKET', X_rel_gen_method, NULL),
'N',
'N',
'N',
'N',
x_vendor_id,
x_vendor_site_id,
x_purch_uom,
x_item_id);
p_action => PO_ASL_SV.G_EVENT_INSERT,
p_calling_from => 'PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries',
p_ackn_note => NULL,
p_autonomous_commit => FND_API.G_FALSE
);
SELECT asl_id
INTO x_asl_id
FROM po_approved_supplier_list pasl
WHERE pasl.vendor_id = x_vendor_id
AND ( pasl.vendor_site_id = x_vendor_site_id
OR ( pasl.vendor_site_id is NULL
AND x_vendor_site_id is NULL))
AND pasl.item_id = x_item_id
AND using_organization_id = l_using_organization_id; --
Bug 2361161 If the ASL entry exists then we update the attributes with the release generation method
passed from the approval window. if the Purchasing UOM has not been entered update that with the value
from the po line
*/
select purchasing_unit_of_measure,
release_generation_method
into x_att_puom,
l_rel_gen_method
from po_asl_attributes
where asl_id = x_asl_id
and using_organization_id =l_using_organization_id --
and vendor_id = x_vendor_id
and vendor_site_id = x_vendor_site_id
and item_id = x_item_id;
UPDATE po_asl_attributes
set release_generation_method = l_rel_gen_method,
purchasing_unit_of_measure = x_att_puom,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = last_update_login
where asl_id = x_asl_id
and using_organization_id =l_using_organization_id --
and vendor_id = x_vendor_id
and vendor_site_id = x_vendor_site_id
and item_id = x_item_id;
SELECT count(*)
INTO x_dummy_count
FROM po_asl_documents
WHERE asl_id = x_asl_id
AND using_organization_id = l_using_organization_id --
AND document_header_id = x_po_header_id
AND document_type_code = x_type_lookup_code;
SELECT nvl(max(sequence_num)+1, 1)
INTO x_sequence_num
FROM po_asl_documents
WHERE asl_id = x_asl_id
AND using_organization_id = l_using_organization_id; --
INSERT INTO PO_ASL_DOCUMENTS(
asl_id,
using_organization_id,
sequence_num,
document_type_code,
document_header_id,
document_line_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
) VALUES (
x_asl_id,
l_using_organization_id, --
x_sequence_num,
x_type_lookup_code,
x_po_header_id,
x_po_line_id,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_creation_date,
x_created_by
);