[Home] [Help]
4: /*
5: NAME
6: populate_npcontract -
7: DESCRIPTION
8: main function for populating poa_bis_savings fact table
9: for Oracle Purchasing with non-contract and potential
10: contract purchases information
11: */
12:
163: POA_LOG.debug_line('Populate_noncontract: entered');
164: POA_LOG.debug_line(' ');
165:
166:
167: /* Delete from poa_bis_savings all rows which will is approved
168: * and was modified in the date range specified.
169: * These rows will be reinserted with the new modified information.
170: */
171:
171:
172:
173: x_progress := '015';
174:
175: DELETE FROM poa_bis_savings poa
176: WHERE distribution_transaction_id IN
177: (SELECT primary_key FROM poa_edw_po_dist_inc WHERE batch_id = p_batch_no);
178:
179: /* Insert rows for POs created for one-time items in which no blankets
180: * exists (non-contracts)
181: */
182:
183: x_progress := '020';
184: INSERT INTO poa_bis_savings
185: ( purchase_amount
186: , contract_amount
187: , non_contract_amount
188: , pot_contract_amount
351: * each of these rows, we still need to calculate their potential
352: * savings.
353: */
354:
355: INSERT INTO poa_bis_savings
356: ( purchase_amount
357: , contract_amount
358: , non_contract_amount
359: , pot_contract_amount
835:
836:
837: x_progress := '040';
838:
839: SELECT count(*) INTO v_rowcount FROM poa_bis_savings
840: WHERE distribution_transaction_id = v_po_distribution_id;
841:
842: --If a row already exists, then delete it and reinsert the
843: --row with the updated information
845: IF (v_rowcount > 0) THEN
846: -- POA_LOG.debug_line(' v_rowcount is: ' || v_rowcount);
847:
848: x_progress := '050';
849: DELETE FROM poa_bis_savings
850: WHERE distribution_transaction_id = v_po_distribution_id;
851: END IF;
852:
853: x_progress := '060';
857: else
858: v_npcontract_purchase_amount2 := NULL;
859: end if;
860:
861: INSERT INTO poa_bis_savings
862: ( purchase_amount
863: , contract_amount
864: , non_contract_amount
865: , pot_contract_amount