[Home] [Help]
2759: -- preserving the guarantee that the output values are ordered
2760: -- corresponding to the order of the input values
2761:
2762: -- bug3546894
2763: -- Use an indexed column to store the sequence in PO_SESSION_GT. We will also
2764: -- save the same in PL/SQL table l_sequence_tbl
2765: FORALL i IN 1 .. p_agreement_id_tbl.COUNT
2766: INSERT INTO PO_SESSION_GT TEMP
2767: ( key
2762: -- bug3546894
2763: -- Use an indexed column to store the sequence in PO_SESSION_GT. We will also
2764: -- save the same in PL/SQL table l_sequence_tbl
2765: FORALL i IN 1 .. p_agreement_id_tbl.COUNT
2766: INSERT INTO PO_SESSION_GT TEMP
2767: ( key
2768: , num1
2769: , index_num1
2770: )
2770: )
2771: VALUES
2772: ( l_procedure_id
2773: , p_agreement_id_tbl(i)
2774: , PO_SESSION_GT_S.NEXTVAL
2775: )
2776: RETURNING TEMP.index_num1
2777: BULK COLLECT INTO l_sequence_tbl
2778: ;
2789: --SQL Why: Callers that are sourcing a Requisition against a BPA need to
2790: -- know whether the BPA is encumbered or not, because it determines
2791: -- the setting of the Reqs prevent_encumbrance_flag.
2792:
2793: UPDATE PO_SESSION_GT TEMP
2794: SET char1 =
2795: (SELECT POH.encumbrance_required_flag
2796: FROM PO_HEADERS POH
2797: WHERE POH.po_header_id = TEMP.num1
2805: PO_DEBUG.debug_stmt(l_log_head,l_progress,
2806: 'Encumbrance information updated successfully');
2807:
2808: SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
2809: FROM PO_SESSION_GT WHERE key = l_procedure_id
2810: ;
2811:
2812: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_SESSION_GT'
2813: , PO_DEBUG.g_rowid_tbl
2808: SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
2809: FROM PO_SESSION_GT WHERE key = l_procedure_id
2810: ;
2811:
2812: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_SESSION_GT'
2813: , PO_DEBUG.g_rowid_tbl
2814: , po_tbl_varchar30('key','num1','char1','num2')
2815: );
2816:
2822: -- table, with the ordering preserved.
2823:
2824: -- bug3546894
2825: -- we want to obtain the result in the same order as we insert into
2826: -- PO_SESSION_GT in the first place.
2827: FORALL i IN 1..l_sequence_tbl.COUNT
2828: UPDATE PO_SESSION_GT
2829: SET char1 = NVL(char1,'N')
2830: WHERE key = l_procedure_id
2824: -- bug3546894
2825: -- we want to obtain the result in the same order as we insert into
2826: -- PO_SESSION_GT in the first place.
2827: FORALL i IN 1..l_sequence_tbl.COUNT
2828: UPDATE PO_SESSION_GT
2829: SET char1 = NVL(char1,'N')
2830: WHERE key = l_procedure_id
2831: AND index_num1 = l_sequence_tbl(i)
2832: RETURNING char1
4417: -- Make a copy for each value in the adjustment status table.
4418: -- Use the scratchpad to do this in bulk.
4419:
4420: ----------------------------------------------------------------
4421: -- PO_SESSION_GT column mapping
4422: --
4423: -- num1 distribution id
4424: ----------------------------------------------------------------
4425:
4427:
4428: l_progress := '080';
4429:
4430: FORALL i IN 1 .. l_dist_id_tbl.COUNT
4431: INSERT INTO PO_SESSION_GT TEMP ( key, num1 )
4432: VALUES ( l_dist_id_key, l_dist_id_tbl(i) )
4433: ;
4434:
4435: l_progress := '090';
4526: FROM
4527: PO_REQ_DISTRIBUTIONS_ALL PRD
4528: , PO_REQUISITION_LINES_ALL PRL
4529: , PO_REQUISITION_HEADERS_ALL PRH
4530: , PO_SESSION_GT DIST_IDS
4531: WHERE PRH.requisition_header_id = PRL.requisition_header_id --JOIN
4532: AND PRL.requisition_line_id = PRD.requisition_line_id --JOIN
4533: AND PRD.distribution_id = DIST_IDS.num1 --JOIN
4534: AND DIST_IDS.key = l_dist_id_key
4661: PO_DISTRIBUTIONS_ALL POD
4662: , PO_LINE_LOCATIONS_ALL POLL
4663: , PO_LINES_ALL POL
4664: , PO_HEADERS_ALL POH
4665: , PO_SESSION_GT DIST_IDS
4666: WHERE POH.po_header_id = POD.po_header_id --JOIN
4667: AND POL.po_line_id(+) = POD.po_line_id --JOIN
4668: -- the distributions of PAs don't have associated lines
4669: AND POLL.line_location_id(+) = POD.line_location_id --JOIN