[Home] [Help]
474: -- None.
475: --Function:
476: -- Derive ACRN for Award and Modification
477: -- Check if call is for Award or Modification
478: -- Populate the po_session_gt table
479: -- Check if input ACRN is not null, then call is_valid_acrn to validate the ACRN.
480: -- If ACRN passed is null, then derive ACRN from fnd_lookups PO_ACRN.
481: -- Get the ACRN value and validate with other ACRNS for the PO.
482: --Parameters:
557: END IF;
558:
559: l_key := PO_CORE_S.get_session_gt_nextval;
560: /*
561: Inserting the data passed to the procedure into the PO_SESSION_GT table
562: KEY - SESSION KEY
563: NUM1 - DISTRIBUTION ID
564: CHAR1 - ACCOUNT NUMBER
565: CHAR2 - LOA VALUE
568: CHAR4 - Change status
569: CHAR5 - ACRN
570:
571: */
572: INSERT INTO po_session_gt
573: (KEY,num1,num2,char1,char2,char3,char4,char5
574: )
575: WITH test_tab AS
576: (SELECT dist.dist_num,
652: cascade_acrn_values(l_key);
653:
654: ELSIF(l_doc_type='AWARD' AND P_REGEN_Y_N <> 'N') THEN
655: --Regenerate in Award means clean up all the acrn values given by the user and then regenerate from the lov
656: update po_session_gt set char5 = null where key = l_key;
657: ----Generate ACRN from LOV values
658: generate_acrn_from_lov(l_key);
659: --Post Cascade the values as the generate acrn generates Distinct values only
660: cascade_acrn_values(l_key);
661:
662: ELSIF(l_doc_type='MOD' AND P_REGEN_Y_N <> 'N') THEN
663: --Regenerate in Award means clean up all the acrn values given by the user and then regenerate from the lov
664: --rows with NOCHANGE char4 (change_status) should be ignored, as those come from the base document
665: update po_session_gt set char5 = null where key = l_key and char4 not in ('NOCHANGE');
666:
667: --Pre Cascade Values as they are given by user
668: cascade_acrn_values(l_key);
669:
679: -- Ordered by Num2 (rownumber of dist id table )to ensure that
680: -- the acrns are returned in the same order as They were inserted.
681: select char5 bulk collect
682: into l_acrn_tbl
683: from po_session_gt
684: where
685: key = l_key
686: order by num2;
687: X_ACRN_TBL := l_ACRN_TBL;
684: where
685: key = l_key
686: order by num2;
687: X_ACRN_TBL := l_ACRN_TBL;
688: DELETE from PO_SESSION_GT where key = l_key;
689:
690: EXCEPTION
691: WHEN OTHERS THEN
692: X_RETURN_STATUS := 'E';
693: x_error := SQLERRM;
694:
695: END BUILD_ACRN;
696: -------------------------------------------------------------------------------
697: /*This procedure will update the po_session gt with the ACRN values pulled from
698: the lookup table for distinct remaining generatable values. It works on the
699: the values present for the session key provided as the parameter*/
700: PROCEDURE generate_acrn_from_lov(l_key number)
701: is
699: the values present for the session key provided as the parameter*/
700: PROCEDURE generate_acrn_from_lov(l_key number)
701: is
702: BEGIN
703: UPDATE po_session_gt gt2
704: SET char5 =
705: (SELECT acrn
706: FROM
707: (SELECT avail_acrn.acrn,
711: from
712: (SELECT num1 dist_num,
713: char3 gen_val,
714: char5 acrn
715: FROM po_session_gt gt
716: WHERE KEY = l_key
717: AND char5 IS NULL
718: and not exists(
719: select l_key from po_session_gt gtx where gtx.char3 = gt.char3 and gtx.num1>gt.num1
715: FROM po_session_gt gt
716: WHERE KEY = l_key
717: AND char5 IS NULL
718: and not exists(
719: select l_key from po_session_gt gtx where gtx.char3 = gt.char3 and gtx.num1>gt.num1
720: and KEY=l_key
721: )
722: ORDER BY dist_num
723: ))avail_dist,
732: AND (end_date_active IS NULL
733: OR end_date_active >=sysdate)
734: AND( meaning NOT IN
735: (SELECT DISTINCT TO_CHAR(NVL(char5,0))
736: FROM po_session_gt acrn_test
737: WHERE KEY = l_key
738: ))
739: ORDER BY to_number(lookup_code)
740: )
751: */
752: PROCEDURE cascade_acrn_values(l_key number)
753: is
754: Begin
755: UPDATE po_session_gt gt2
756: SET char5 =
757: (SELECT ACRN
758: FROM
759: (SELECT DISTINCT not_provided.gen_val,
760: provided.acrn
761: FROM
762: (SELECT char5 acrn,
763: char3 GEN_VAL
764: FROM po_session_gt
765: WHERE KEY = l_key
766: AND char5 IS NULL
767: ) not_provided
768: LEFT OUTER JOIN
767: ) not_provided
768: LEFT OUTER JOIN
769: (SELECT DISTINCT char5 acrn,
770: char3 GEN_VAL
771: FROM po_session_gt gt1
772: WHERE KEY = l_key
773: AND CHAR5 IS NOT NULL
774: AND NOT EXISTS
775: (SELECT 1
772: WHERE KEY = l_key
773: AND CHAR5 IS NOT NULL
774: AND NOT EXISTS
775: (SELECT 1
776: FROM PO_SESSION_GT GT2
777: WHERE KEY = l_key
778: AND CHAR5 IS NOT NULL
779: AND GT1.CHAR3 = GT2.CHAR3
780: AND GT1.ROWID > GT2.ROWID
2038: IF (PO_LOG.d_proc) THEN
2039: PO_LOG.proc_end(d_module);
2040: END IF;
2041:
2042: -- INSERT INTO PO_SESSION GT values for all the records you got from
2043: -- UI.
2044: /*
2045: Inserting the data passed to the procedure into the PO_SESSION_GT table
2046: KEY - SESSION KEY
2041:
2042: -- INSERT INTO PO_SESSION GT values for all the records you got from
2043: -- UI.
2044: /*
2045: Inserting the data passed to the procedure into the PO_SESSION_GT table
2046: KEY - SESSION KEY
2047: NUM1 - DISTRIBUTION ID
2048: CHAR1 - ACCOUNT NUMBER
2049: CHAR2 - LOA VALUE
2055: */
2056:
2057:
2058:
2059: INSERT INTO po_session_gt
2060: (KEY,num1,char1,char2,char3,char4,char5)
2061: WITH test_tab AS
2062: (SELECT dist.dist_num,
2063: dist.rn rnum,
2092: PO_LOG.stmt(d_module,d_position,'No Of records ',l_count);
2093: END IF;
2094:
2095:
2096: -- INSERT INTO PO_SESSION_GT all the values available from po_distributions_merge_v
2097: -- THIS IS TO MAKE SURE THAT WE WILL CONSIDER ALL DISTRIBUTIONS FOR THAT DOCUMENT.
2098: INSERT INTO po_session_gt
2099: (KEY,
2100: num1,
2094:
2095:
2096: -- INSERT INTO PO_SESSION_GT all the values available from po_distributions_merge_v
2097: -- THIS IS TO MAKE SURE THAT WE WILL CONSIDER ALL DISTRIBUTIONS FOR THAT DOCUMENT.
2098: INSERT INTO po_session_gt
2099: (KEY,
2100: num1,
2101: char1,
2102: char2,
2128: char4,
2129: Nvl(char5, '')
2130: bulk collect INTO l_dist_id_tbl, l_code_id_tbl, l_loa_tbl, l_change_stat_tbl,
2131: l_acrn_tbl
2132: FROM po_session_gt gt1
2133: WHERE KEY = l_key
2134: AND NOT EXISTS (SELECT 1
2135: FROM po_session_gt gt2
2136: WHERE KEY = l_key
2131: l_acrn_tbl
2132: FROM po_session_gt gt1
2133: WHERE KEY = l_key
2134: AND NOT EXISTS (SELECT 1
2135: FROM po_session_gt gt2
2136: WHERE KEY = l_key
2137: AND gt1.ROWID > gt2.ROWID
2138: AND gt1.num1 = gt2.num1);
2139: -- Calling build acrn values.
2156: X_RETURN_STATUS=>L_RETURN_STATUS,
2157: X_RETURN_MESSAGE=> L_RETURN_MESSAGE,
2158: X_MESSAGE_TOKEN =>L_MESSAGE_TOKEN);
2159:
2160: delete from po_session_gt where key = l_key;
2161:
2162: l_key := PO_CORE_S.get_session_gt_nextval;
2163:
2164: INSERT INTO po_session_gt
2160: delete from po_session_gt where key = l_key;
2161:
2162: l_key := PO_CORE_S.get_session_gt_nextval;
2163:
2164: INSERT INTO po_session_gt
2165: (KEY,num1,num2,char1,char2,char3,char4,char5)
2166: WITH test_tab AS
2167: (SELECT dist.dist_num,
2168: dist.rn rnum,
2204: for i in 1 .. P_DIST_ID_TBL.count LOOP
2205:
2206: select char5
2207: into l_acrn
2208: from po_session_gt
2209: where
2210: key = l_key
2211: and num1 = P_DIST_ID_TBL(i);
2212: O_ACRN_TBL.extend;
2218: X_RETURN_MESSAGE := L_RETURN_MESSAGE;
2219: X_MESSAGE_TOKEN := L_MESSAGE_TOKEN;
2220:
2221:
2222: delete from po_session_gt where key = l_key;
2223:
2224: IF (PO_LOG.d_proc) THEN
2225: PO_LOG.proc_end(d_module);
2226: END IF;