[Home] [Help]
42: IS
43: BEGIN
44: INSERT INTO poa_edw_sup_perf_inc(primary_key)
45: SELECT TO_NUMBER(SUBSTR(sup_perf_pk, 1, INSTR(sup_perf_pk, '-' )-1))
46: FROM POA_EDW_SUP_PERF_FSTG
47: where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
48: COLLECTION_STATUS = 'INVALID CURRENCY';
49: IF (sql%rowcount > 0) THEN
50: g_retcode := 1;
64: -- collection status of either rate not available or invalid currency.
65: PROCEDURE DELETE_STG_MISSING_RATES
66: IS
67: BEGIN
68: DELETE FROM POA_EDW_SUP_PERF_FSTG
69: WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
70: OR COLLECTION_STATUS = 'INVALID CURRENCY'
71: AND INSTANCE_FK = (SELECT INSTANCE_CODE
72: FROM EDW_LOCAL_INSTANCE);
108: IF(fnd_profile.value('POA_TARGET_PRICE_TXN') = 'Y') then
109:
110: edw_log.put_line('***The best price is calculated for target prices***');
111:
112: Insert Into POA_EDW_SUP_PERF_FSTG(
113: DUNS_FK,
114: UNSPSC_FK,
115: SIC_CODE_FK,
116: AMT_PURCHASED_G,
310: ELSE
311:
312: edw_log.put_line('***The target prices are set to NULL***');
313:
314: Insert Into POA_EDW_SUP_PERF_FSTG(
315: DUNS_FK,
316: UNSPSC_FK,
317: SIC_CODE_FK,
318: AMT_PURCHASED_G,
669: p_to_date IN Varchar2) IS
670:
671:
672: l_fact_name Varchar2(30) :='POA_EDW_SUP_PERF_F';
673: l_staging_table Varchar2(30) :='POA_EDW_SUP_PERF_FSTG';
674: l_exception_msg Varchar2(2000):=Null;
675: l_from_date Date:=Null;
676: l_to_date Date:=Null;
677: l_seq_id1 NUMBER := -1;
701: FROM (select TO_NUMBER(SUBSTR(sup_perf_pk, 1,
702: INSTR(sup_perf_pk, '-' )-1))
703: Line_location_id,
704: Collection_Status
705: from POA_EDW_SUP_PERF_FSTG
706: where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
707: COLLECTION_STATUS = 'INVALID CURRENCY') fstg,
708: po_line_locations_all pll,
709: PO_HEADERS_ALL POH,