[Home] [Help]
10: --Name: create_update_sourcing_rules
11: --Pre-reqs:
12: -- None
13: --Modifies:
14: -- This API inserts row into mrp_sr_assignments, mrp_sourcing_rules,
15: -- mrp_sr_receipt_org, mrp_sr_source_org depending on the create or update
16: -- flag from the approval flag.
17: --Locks:
18: -- None.
416: BEGIN
417:
418: ----
419: select sourcing_rule_id into X_temp_sourcing_rule_id
420: from mrp_sourcing_rules where
421: sourcing_rule_name = X_rule_name and
422: sourcing_rule_type =l_sourcing_rule_type and ----
423: nvl(organization_id,-999) = nvl(l_organization_id,-999); ----
424: ----
451:
452: X_progress := '030';
453: IF (x_sourcing_rule_id is NULL) THEN
454: IF (g_po_pdoi_write_to_file = 'Y') THEN
455: PO_DEBUG.put_line(' Inserting Record in Mrp Sourcing Rules');
456: END IF;
457:
458: SELECT MRP_SOURCING_RULES_S.NEXTVAL
459: INTO x_sourcing_rule_id
454: IF (g_po_pdoi_write_to_file = 'Y') THEN
455: PO_DEBUG.put_line(' Inserting Record in Mrp Sourcing Rules');
456: END IF;
457:
458: SELECT MRP_SOURCING_RULES_S.NEXTVAL
459: INTO x_sourcing_rule_id
460: FROM SYS.DUAL;
461:
462: INSERT INTO MRP_SOURCING_RULES(
458: SELECT MRP_SOURCING_RULES_S.NEXTVAL
459: INTO x_sourcing_rule_id
460: FROM SYS.DUAL;
461:
462: INSERT INTO MRP_SOURCING_RULES(
463: sourcing_rule_id,
464: sourcing_rule_name,
465: status,
466: sourcing_rule_type,
747:
748: cursor C1 is
749: SELECT sr_receipt_id
750: FROM mrp_sr_receipt_org msro,
751: mrp_sourcing_rules msr
752: WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
753: AND msr.sourcing_rule_id = x_sourcing_rule_id
754: AND msro.effective_date = x_start_date
755: AND trunc(NVL(msro.disable_date,sysdate)) = trunc(NVL(x_end_date,sysdate));
772:
773: cursor C2 is
774: SELECT sr_receipt_id, msro.effective_date, msro.disable_date
775: FROM mrp_sr_receipt_org msro,
776: mrp_sourcing_rules msr
777: WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
778: AND msr.sourcing_rule_id = x_sourcing_rule_id
779: AND (trunc(msro.effective_date) between trunc(x_start_date) and
780: trunc(x_end_date)
801:
802: X_progress := '010';
803: select sourcing_rule_name, organization_id
804: into x_sourcing_name, x_org
805: from mrp_sourcing_rules
806: where sourcing_rule_id = x_sourcing_rule_id;
807:
808: if (x_org is NULL) then
809: x_org := 'ALL';
870: END IF;
871: SELECT count(*) into x_within_vendor_cnt
872: FROM
873: mrp_sr_receipt_org msro,
874: mrp_sourcing_rules msr,
875: mrp_sr_source_org msso
876: WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
877: AND msro.sr_receipt_id = msso.sr_receipt_id
878: AND msr.sourcing_rule_id = x_sourcing_rule_id
1189:
1190: X_progress := '080';
1191: SELECT count(*) into x_sourcing_rule_within
1192: FROM mrp_sr_receipt_org msro,
1193: mrp_sourcing_rules msr
1194: WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1195: AND msr.sourcing_rule_id = x_sourcing_rule_id
1196: AND x_start_date between msro.effective_date and msro.disable_date
1197: AND x_end_date between msro.effective_date and msro.disable_date;
1654: -- be missed because we are not looping through the cursor to fish out overlapped dates correctly
1655:
1656: BEGIN
1657: SELECT count(*) into x_overlap_count
1658: FROM mrp_sr_receipt_org msro, mrp_sourcing_rules msr
1659: WHERE msr.sourcing_rule_id = msro.sourcing_rule_id
1660: AND msr.sourcing_rule_id = x_sourcing_rule_id
1661: AND trunc(msro.effective_date) > trunc(x_start_date)
1662: AND (
1674: END;
1675:
1676: SELECT sourcing_rule_name, organization_id
1677: INTO x_sourcing_name, x_org
1678: FROM mrp_sourcing_rules
1679: WHERE sourcing_rule_id = x_sourcing_rule_id;
1680:
1681: IF (x_org is NULL) THEN
1682: x_org := 'ALL';