[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE create_update_sourcing_rule (
p_interface_header_id IN NUMBER,
p_interface_line_id IN NUMBER,
p_item_id IN NUMBER,
p_vendor_id IN NUMBER,
p_po_header_id IN NUMBER,
p_po_line_id IN NUMBER,
p_document_type IN VARCHAR2,
p_approval_status IN VARCHAR2,
p_rule_name IN VARCHAR2,
p_rule_name_prefix IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_create_update_code IN VARCHAR2,
p_organization_id IN NUMBER,
p_assignment_type_id IN NUMBER,
p_po_interface_error_code IN VARCHAR2,
x_header_processable_flag IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
----
p_assignment_set_id IN NUMBER DEFAULT NULL,
p_vendor_site_id IN NUMBER DEFAULT NULL
----
) IS
X_process_flag varchar2(1) := 'Y';
((p_create_update_code = 'CREATE')
OR (p_create_update_code ='CREATE_UPDATE'))) THEN
IF (g_po_pdoi_write_to_file = 'Y') THEN
PO_DEBUG.put_line(' Creating call rule for the item ...');
END IF; -- p_create_update_code is create
(p_create_update_code ='CREATE_UPDATE')) THEN
--validate to throw errors on some sure overlap failure cases
PO_SOURCING_RULES_SV.validate_update_sourcing_rule(
x_interface_header_id =>p_interface_header_id,
x_interface_line_id =>p_interface_line_id,
x_sourcing_rule_id =>x_sourcing_rule_id,
x_start_date =>p_start_date,
x_end_date =>p_end_date,
x_assignment_type_id =>p_assignment_type_id,
x_organization_id =>p_organization_id,
x_assignment_set_id =>x_assignment_set_id,
x_process_flag =>x_process_flag,
x_running_status =>l_running_status,
x_header_processable_flag =>x_header_processable_flag,
x_po_interface_error_code =>p_po_interface_error_code);
PO_SOURCING_RULES_SV.update_sourcing_rule(
x_interface_header_id =>p_interface_header_id,
x_interface_line_id =>p_interface_line_id,
x_item_id =>p_item_id,
x_vendor_id =>p_vendor_id,
x_po_header_id =>p_po_header_id,
x_po_line_id =>p_po_line_id,
x_document_type =>p_document_type,
x_sourcing_rule_id =>x_sourcing_rule_id,
x_temp_sourcing_rule_id =>x_temp_sourcing_rule_id,
x_start_Date =>p_start_Date,
x_end_date =>p_end_date,
x_organization_id =>p_organization_id,
x_assignment_type_id =>p_assignment_type_id,
x_assignment_set_id =>x_assignment_set_id,
x_running_status =>l_running_status,
x_header_processable_flag =>x_header_processable_flag,
x_po_interface_error_code =>p_po_interface_error_code,
----
p_vendor_site_id =>p_vendor_site_id
----
);
END IF; -- X_create_update_flag is update
po_message_s.sql_error('create_update_sourcing_rule', x_progress, sqlcode);
END create_update_sourcing_rule;
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 sourcing_rule_id into X_temp_sourcing_rule_id
from mrp_sourcing_rules where
sourcing_rule_name = X_rule_name and
sourcing_rule_type =l_sourcing_rule_type and ----
nvl(organization_id,-999) = nvl(l_organization_id,-999); ----
SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3) */
sourcing_rule_id
INTO x_sourcing_rule_id
FROM mrp_sr_assignments
WHERE inventory_item_id = X_item_id
AND assignment_set_id = x_assignment_set_id
AND sourcing_rule_type =l_sourcing_rule_type
AND assignment_type=nvl(x_assignment_type_id,3)
AND decode(x_assignment_type_id,6,organization_id,-1)=decode(x_assignment_type_id,6,l_organization_id,-1);
PO_DEBUG.put_line(' Inserting Record in Mrp Sourcing Rules');
SELECT MRP_SOURCING_RULES_S.NEXTVAL
INTO x_sourcing_rule_id
FROM SYS.DUAL;
INSERT INTO MRP_SOURCING_RULES(
sourcing_rule_id,
sourcing_rule_name,
status,
sourcing_rule_type,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
planning_active
) VALUES (
x_sourcing_rule_id,
nvl(X_rule_name_prefix,'PURCH')||'_'||to_char(x_sourcing_rule_id),----
1, -- status
l_sourcing_rule_type, --
l_organization_id, --
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
1 -- planning_active (1=ACTIVE)
);
PO_DEBUG.put_line(' Inserting Record in Mrp Sr Receipt Org');
SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
INTO x_sr_receipt_id
FROM SYS.DUAL;
INSERT INTO MRP_SR_RECEIPT_ORG(
sr_receipt_id,
sourcing_rule_id,
effective_date,
disable_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
receipt_organization_id----
) VALUES (
x_sr_receipt_id,
x_sourcing_rule_id,
x_start_date,
x_end_date,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
l_organization_id----
);
PO_DEBUG.put_line(' Inserting Record in Mrp Sr Source Org');
SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
INTO x_sr_source_id
FROM SYS.DUAL;
INSERT INTO MRP_SR_SOURCE_ORG(
sr_source_id,
sr_receipt_id,
vendor_id,
vendor_site_id,
source_type,
allocation_percent,
rank,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
) VALUES (
x_sr_source_id,
x_sr_receipt_id,
x_vendor_id,
x_vendor_site_id,
3, -- source_type
100, -- bug 605898, allocation_percent should be 100 instead of 0
1, -- rank should be 1
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login
);
SELECT 'Item Exists'
INTO l_item_exists
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = l_inv_org_id;
PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
SELECT MRP_SR_ASSIGNMENTS_S.NEXTVAL
INTO x_assignment_id
FROM SYS.DUAL;
INSERT INTO MRP_SR_ASSIGNMENTS(
assignment_id,
assignment_type,
sourcing_rule_id,
sourcing_rule_type,
assignment_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
inventory_item_id
) VALUES (
x_assignment_id,
NVL(x_assignment_type_id,3), ----
x_sourcing_rule_id,
l_sourcing_rule_type, -- sourcing_rule_type (1=SOURCING RULE)
x_assignment_set_id,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
-- Bug 3692799: organization_id should be null
-- when assignment_type is 3 (item assignment)
l_organization_id, ----
x_item_id
);
* This will prevent us to call the update_sourcing_rule
* procedure.
*/
x_process_flag := 'N';
PROCEDURE update_sourcing_rule (X_interface_header_id IN NUMBER,
X_interface_line_id IN NUMBER,
X_item_id IN NUMBER,
X_vendor_id IN NUMBER,
X_po_header_id IN NUMBER,
X_po_line_id IN NUMBER,
X_document_type IN VARCHAR2,
x_sourcing_rule_id IN NUMBER,
x_temp_sourcing_rule_id IN NUMBER,
X_start_date IN DATE,
X_end_date IN DATE,
X_organization_id IN NUMBER,
X_assignment_type_id IN NUMBER,
x_assignment_set_id IN NUMBER,
X_running_status IN OUT NOCOPY VARCHAR2, --
X_header_processable_flag IN OUT NOCOPY VARCHAR2,
X_po_interface_error_code IN VARCHAR2,
----
p_vendor_site_id IN NUMBER DEFAULT NULL
----
) IS
X_progress VARCHAR2(3) := NULL;
x_last_update_date date := sysdate;
x_last_updated_by number := fnd_global.user_id ;
x_last_update_login number := fnd_global.user_id;
/* This select statement first finds out if the combination of X_item_id and
X_vendor_id already exists in PO_AUTOSOURCE_VENDORS */
/* cursor c1 is to make sure if we can find the exact
match in the sourcing rule effectivity dates */
cursor C1 is
SELECT sr_receipt_id
FROM mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
AND msr.sourcing_rule_id = x_sourcing_rule_id
AND msro.effective_date = x_start_date
AND trunc(NVL(msro.disable_date,sysdate)) = trunc(NVL(x_end_date,sysdate));
SELECT sr_receipt_id, msro.effective_date, msro.disable_date
FROM mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
AND msr.sourcing_rule_id = x_sourcing_rule_id
AND (trunc(msro.effective_date) between trunc(x_start_date) and
trunc(x_end_date)
OR trunc(NVL(msro.disable_date,sysdate)) between
trunc(x_start_date) and
trunc(x_end_date));
select sourcing_rule_name, organization_id
into x_sourcing_name, x_org
from mrp_sourcing_rules
where sourcing_rule_id = x_sourcing_rule_id;
SELECT count(*) into x_within_vendor_cnt
FROM
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr,
mrp_sr_source_org msso
WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = x_sourcing_rule_id
AND x_start_date between msro.effective_date and msro.disable_date
AND x_end_date between msro.effective_date and msro.disable_date
AND msso.vendor_id = x_vendor_id
AND (msso.vendor_site_id = x_vendor_site_id or
(msso.vendor_site_id is NULL and x_vendor_site_id is null));
SELECT count(*)
INTO x_assignment_count
FROM mrp_sr_assignments
WHERE sourcing_rule_id = x_sourcing_rule_id;
SELECT count(*)
INTO x_item_assignment_count
FROM mrp_sr_assignments
WHERE sourcing_rule_id = x_sourcing_rule_id
/* Bug 2160710 solved. Added the below two 'AND' conditions */
AND inventory_item_id = X_item_id
AND assignment_set_id = X_assignment_set_id ;
* is coming in irrespective of the vendor needs to updated.
* Now we need to consider whether this sourcing rule is assigned
* to any other item. We can update only if there is either no
* assignment or if there is an assignment to an item, it should
* be this item on the blanket in this assignment set. If not,
* we should not be changing since this would mean that we are
* updating a sourcing rule used by other planner. This check is
* done by getting X_assignment_count which is the number of times
* this sourcing rule is assigned to any item in any assignment set.
* x_item_assignment_count gets the number of times it is assigned
* to the item in the default assignment set(which can be 0 or 1).
* If they are the same, then it means that this sourcing rule is
* assigned only to this item in this assignment set only and
* hence can be changed. FPH change
*/
--IF (x_dummy_count > 0) and (x_within_vendor_cnt = 0 ) THEN
IF ((x_within_vendor_cnt = 0) AND
((x_assignment_count > 1) OR
(x_assignment_count =1 and x_item_assignment_count <> 1 ))) THEN
-- insert into po interface errors
IF (g_po_pdoi_write_to_file = 'Y') THEN
PO_DEBUG.put_line(' The existing sourcing rule is assgned elsewhere and does not
match with the vendor provided in the blanket');
select count(*)
into x_vendor_count
from mrp_sr_source_org
where sr_receipt_id = x_sr_receipt_id
and (
(vendor_id <> X_vendor_id)
or
( (vendor_id = X_vendor_id)
and
(nvl(vendor_site_id, x_vendor_site_id) <> x_vendor_site_id)
)
);
select count(*)
into x_cnt_srdate
from mrp_sr_receipt_org
where x_end_date between
effective_date and disable_date
and sourcing_rule_id = x_sourcing_rule_id;
/* Update the Effective End date */
if (x_start_date = x_effective_date) then
X_progress := '070';
update mrp_sr_receipt_org
set disable_date = x_end_date
where sr_receipt_id = x_sr_receipt_id;
/* Update the current record's disable date
to start date - 1 */
update mrp_sr_receipt_org
set disable_date = x_start_date - 1
where sr_receipt_id = x_sr_receipt_id;
SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
INTO x_sr_receipt_id
FROM SYS.DUAL;
INSERT INTO MRP_SR_RECEIPT_ORG(
sr_receipt_id,
sourcing_rule_id,
effective_date,
disable_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
receipt_organization_id----
)
VALUES (
x_sr_receipt_id,
x_sourcing_rule_id,
x_start_date,
x_end_date,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
l_organization_id----
);
SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
INTO x_sr_source_id
FROM SYS.DUAL;
SELECT nvl(max(rank),0) +1
INTO x_vendor_rank
FROM MRP_SR_SOURCE_ORG MSSO
WHERE sr_receipt_id = x_sr_receipt_id;
INSERT INTO MRP_SR_SOURCE_ORG(
sr_source_id,
sr_receipt_id,
vendor_id,
vendor_site_id,
source_type,
allocation_percent,
rank,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
VALUES (
x_sr_source_id,
x_sr_receipt_id,
x_vendor_id,
x_vendor_site_id,
3, -- source_type
100,
x_vendor_rank,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login );
insert into mrp_sr_receipt_org */
IF (g_po_pdoi_write_to_file = 'Y') THEN
PO_DEBUG.put_line(' Check to see if the effectivity dates of new sourcing rule falls within the existing sourcing rules effectivity dates');
PO_DEBUG.put_line(' Inserting Record MSRO for existing rule');
SELECT count(*) into x_sourcing_rule_within
FROM mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
AND msr.sourcing_rule_id = x_sourcing_rule_id
AND x_start_date between msro.effective_date and msro.disable_date
AND x_end_date between msro.effective_date and msro.disable_date;
SELECT MRP_SR_RECEIPT_ORG_S.NEXTVAL
INTO x_sr_receipt_id
FROM SYS.DUAL;
INSERT INTO MRP_SR_RECEIPT_ORG(
sr_receipt_id,
sourcing_rule_id,
effective_date,
disable_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
receipt_organization_id ----
)
VALUES (
x_sr_receipt_id,
x_sourcing_rule_id,
x_start_date,
x_end_date,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
l_organization_id----
);
SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
INTO x_sr_source_id
FROM SYS.DUAL;
SELECT nvl(max(rank),0) +1
INTO x_vendor_rank
FROM MRP_SR_SOURCE_ORG MSSO
WHERE sr_receipt_id = x_sr_receipt_id;
PO_DEBUG.put_line(' Inserting Record MSSO for existing rule');
INSERT INTO MRP_SR_SOURCE_ORG(
sr_source_id,
sr_receipt_id,
vendor_id,
vendor_site_id,
source_type,
allocation_percent,
rank,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES (
x_sr_source_id,
x_sr_receipt_id,
x_vendor_id,
x_vendor_site_id,
3, -- source_type
100,
x_vendor_rank,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login
);
SELECT count(*)
INTO x_vendor_count_on_sr
FROM mrp_sr_source_org msso
WHERE
sr_receipt_id = x_sr_receipt_id
AND vendor_id = x_vendor_id
AND (vendor_site_id = x_vendor_site_id
OR (vendor_site_id is NULL
AND x_vendor_site_id is NULL));
SELECT MRP_SR_SOURCE_ORG_S.NEXTVAL
INTO x_sr_source_id
FROM SYS.DUAL;
SELECT nvl(max(rank),0) +1
INTO x_vendor_rank
FROM MRP_SR_SOURCE_ORG MSSO
WHERE sr_receipt_id = x_sr_receipt_id;
INSERT INTO MRP_SR_SOURCE_ORG(
sr_source_id,
sr_receipt_id,
vendor_id,
vendor_site_id,
source_type,
allocation_percent,
rank,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
) VALUES (
x_sr_source_id,
x_sr_receipt_id,
x_vendor_id,
x_vendor_site_id,
3, -- source_type
0,
x_vendor_rank,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login
);
SELECT 'Item Exists'
INTO l_item_exists
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = l_inv_org_id;
PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
INSERT INTO MRP_SR_ASSIGNMENTS(
assignment_id,
assignment_type,
sourcing_rule_id,
sourcing_rule_type,
assignment_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
inventory_item_id
) select
MRP_SR_ASSIGNMENTS_S.NEXTVAL,
nvl(x_assignment_type_id,3), ----
x_sourcing_rule_id,
l_sourcing_rule_type, ----
x_assignment_set_id,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
-- Bug 3692799: organization_id should be null
-- when assignment_type is 3 (item assignment)
l_organization_id, ----
x_item_id
from dual where not exists
(select 'The item has to be attached to the assignment set' from
mrp_sr_assignments where
sourcing_rule_id=X_sourcing_rule_id --FPH
and inventory_item_id= X_item_id);
po_message_s.sql_error('update_sourcing_rule', x_progress, sqlcode);
END update_sourcing_rule;
PROCEDURE validate_update_sourcing_rule (X_interface_header_id IN NUMBER,
X_interface_line_id IN NUMBER,
X_sourcing_rule_id IN NUMBER,
X_start_date IN DATE,
X_end_date IN DATE,
X_assignment_type_id IN NUMBER,
X_organization_id IN NUMBER,
x_assignment_set_id IN OUT NOCOPY NUMBER,
X_process_flag IN OUT NOCOPY VARCHAR2,
X_running_status IN OUT NOCOPY VARCHAR2, --
X_header_processable_flag IN OUT NOCOPY VARCHAR2,
X_po_interface_error_code IN VARCHAR2) IS
x_overlap_count NUMBER:= 0;
SELECT count(*) into x_overlap_count
FROM mrp_sr_receipt_org msro, mrp_sourcing_rules msr
WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
AND msr.sourcing_rule_id = x_sourcing_rule_id
AND trunc(msro.effective_date) > trunc(x_start_date)
AND (
(trunc(NVL(msro.disable_date,sysdate)) between
trunc(x_start_date) and trunc(x_end_date)
)
or
( trunc(x_end_date) between
trunc(msro.effective_Date) and trunc(NVL(msro.disable_date,sysdate))
)
);
SELECT sourcing_rule_name, organization_id
INTO x_sourcing_name, x_org
FROM mrp_sourcing_rules
WHERE sourcing_rule_id = x_sourcing_rule_id;
END validate_update_sourcing_rule;