[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date DATE;
x_last_update_login NUMBER;
x_last_updated_by NUMBER;
SELECT VENDOR_ID,
VENDOR_RANK,
nvl(SPLIT,0),
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM PO_AUTOSOURCE_VENDORS
WHERE AUTOSOURCE_RULE_ID = x_autosource_rule_id;
SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
FROM SYS.DUAL;
x_last_update_date,
x_last_update_login,
x_last_updated_by,
x_creation_date,
x_created_by,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15
;
INSERT INTO MRP_SR_SOURCE_ORG(
sr_source_id,
sr_receipt_id,
vendor_id,
source_type,
allocation_percent,
rank,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
x_sr_source_id,
x_sr_receipt_id,
x_vendor_id,
3, -- source_type
decode(x_split_multiplier, 1, x_split,
decode(x_add_percent, 'N', round(x_split*x_split_multiplier),
decode(x_vendor_rank, 1, round(x_split*x_split_multiplier)+1,
round(x_split*x_split_multiplier)))),
x_vendor_rank,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15
);
x_last_update_date,
x_last_update_login,
x_last_updated_by,
x_created_by,
x_creation_date,
x_usr_upgrade_docs,
x_asl_id);
x_last_update_date DATE,
x_last_update_login NUMBER,
x_last_updated_by NUMBER,
x_created_by NUMBER,
x_creation_date DATE,
x_usr_upgrade_docs VARCHAR2,
x_asl_id IN OUT NOCOPY NUMBER
) IS
x_progress VARCHAR2(30) := '';
SELECT PO_APPROVED_SUPPLIER_LIST_S.NEXTVAL
FROM SYS.DUAL;
select mtl.organization_id
into x_owning_organization_id
from mtl_system_items msi, mtl_parameters mtl,
financials_system_parameters fsp
where msi.inventory_item_id = x_item_id
and msi.organization_id = fsp.inventory_organization_id
and msi.organization_id = mtl.master_organization_id
and msi.organization_id = mtl.organization_id;
select mtl.organization_id
into x_owning_organization_id
from mtl_system_items msi, mtl_parameters mtl
where msi.inventory_item_id = x_item_id
and msi.organization_id = mtl.master_organization_id
and msi.organization_id = mtl.organization_id;
/* select mtl.organization_id
into x_owning_organization_id
from mtl_system_items msi, mtl_parameters mtl
where msi.inventory_item_id = x_item_id
and msi.organization_id = mtl.master_organization_id
and msi.organization_id = mtl.organization_id;
fnd_file.put_line(fnd_file.log, 'x_last_update_date '||to_char(x_last_update_date));
fnd_file.put_line(fnd_file.log, 'update by '|| to_char(x_last_updated_by) );
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 ,
item_id ,
last_update_login ,
request_id
) VALUES (
x_asl_id ,
-1,
x_owning_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_item_id ,
x_last_update_login ,
-99
);
SELECT doc_generation_method
INTO x_release_generation_method
FROM po_autosource_documents_all pad,
po_autosource_rules par
WHERE pad.autosource_rule_id = par.autosource_rule_id
AND par.item_id = x_item_id
AND pad.vendor_id = x_vendor_id
AND par.start_date <= sysdate
AND par.end_date > sysdate
AND rownum < 2
AND pad.sequence_num =
(SELECT min(sequence_num)
FROM po_autosource_documents_all pad2,
po_headers_all poh
WHERE pad2.autosource_rule_id = pad.autosource_rule_id
AND pad2.vendor_id = x_vendor_id
AND pad2.document_type_code = 'BLANKET'
AND pad2.document_header_id = poh.po_header_id
AND nvl(poh.start_date, sysdate-1) <= sysdate
AND nvl(poh.end_date, sysdate+1) > sysdate);
SELECT pad.doc_generation_method
INTO x_release_generation_method
FROM po_autosource_rules par,
po_autosource_documents_all pad
WHERE par.item_id = x_item_id
AND pad.vendor_id = x_vendor_id
AND rownum < 2
AND par.autosource_rule_id = pad.autosource_rule_id
AND par.start_date =
(SELECT min(par3.start_date)
FROM po_autosource_documents_all pad3,
po_autosource_rules par3,
po_headers_all poh3
WHERE par3.autosource_rule_id = pad3.autosource_rule_id
AND pad3.vendor_id = x_vendor_id
AND par3.item_id = x_item_id
AND pad3.document_header_id = poh3.po_header_id
AND nvl(poh3.start_date, sysdate-1) <= sysdate
AND nvl(poh3.end_date, sysdate+1) > sysdate
AND par3.start_date > sysdate
AND pad3.document_type_code = 'BLANKET')
AND pad.sequence_num =
(SELECT min(sequence_num)
FROM po_autosource_documents_all pad2,
po_headers_all poh2
WHERE pad2.autosource_rule_id = pad.autosource_rule_id
AND pad2.vendor_id = x_vendor_id
AND pad2.document_type_code = 'BLANKET'
AND pad2.document_header_id = poh2.po_header_id
AND nvl(poh2.start_date, sysdate-1) <= sysdate
AND nvl(poh2.end_date, sysdate+1) > sysdate);
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,
item_id
) VALUES (
x_asl_id,
-1,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
'ASL',
x_release_generation_method,
'N',
'N',
'N',
'N',
x_vendor_id,
x_item_id
);
SELECT asl_id
INTO x_asl_id
FROM po_approved_supplier_list pasl
WHERE pasl.vendor_id = x_vendor_id
AND pasl.item_id = x_item_id
AND using_organization_id = -1;
x_last_update_date DATE;
x_last_update_login NUMBER;
x_last_updated_by NUMBER;
SELECT pad.document_type_code,
pad.document_header_id,
pad.document_line_id,
pad.last_update_date,
pad.last_updated_by,
pad.last_update_login,
pad.creation_date,
pad.created_by,
pad.doc_generation_method,
pad.org_id
FROM PO_AUTOSOURCE_DOCUMENTS_all pad,
PO_HEADERS_all poh
WHERE pad.autosource_rule_id = x_autosource_rule_id
AND pad.vendor_id = x_vendor_id
AND poh.po_header_id = pad.document_header_id
AND sysdate >= nvl(poh.start_date, sysdate-1)
AND sysdate < nvl(poh.end_date, sysdate+1)
ORDER BY pad.sequence_num ;
SELECT request_id
INTO x_request_id
FROM po_approved_supplier_list
WHERE asl_id = x_asl_id;
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_creation_date,
x_created_by,
x_doc_generation_method,
x_org_id;
SELECT count(*)
INTO x_dummy_count
FROM po_asl_documents
WHERE asl_id = x_asl_id
AND using_organization_id = -1
AND document_header_id = x_document_header_id
AND document_type_code = x_document_type_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 = -1;
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,
org_id
) VALUES (
x_asl_id,
-1,
x_sequence_num,
x_document_type_code,
x_document_header_id,
x_document_line_id,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_creation_date,
x_created_by,
x_org_id
);
UPDATE po_asl_attributes
SET release_generation_method = x_doc_generation_method,
last_updated_by = x_last_updated_by,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login
WHERE asl_id = x_asl_id
AND using_organization_id = -1;