Metadata and Data Load Procedure

Code Example for Designing a Metadata Load Procedure

create or replace PACKAGE      CUSTOM_AR_ECC_UTIL_PVT AUTHID CURRENT_USER AS
G_AR_ECC_INS_OP CONSTANT VARCHAR2(30) := 'INSERT';
G_AR_ECC_UPD_OP CONSTANT VARCHAR2(30) := 'UPDATE';
G_AR_ECC_DEL_OP CONSTANT VARCHAR2(30) := 'DELETE';
G_AR_ECC_UPS_OP CONSTANT VARCHAR2(30) := 'UPSERT';
G_AR_ECC_QRY_OP CONSTANT VARCHAR2(30) := 'DELETE_BY_QUERY';
CUSTOM_ECC_ATTRIBUTES     CONSTANT VARCHAR2(30) := 'EXECUTE|ATTRIBUTE';
PROCEDURE CUSTOM_GET_ECC_DATA_LOAD_INFO(p_dataset_key IN VARCHAR2,
             p_load_type IN VARCHAR2,
                         p_ds_last_success_run IN DATE,
                         p_languages IN VARCHAR2,
             p_addl_params IN   ecc_sec_field_values,
             x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
                     x_return_status OUT NOCOPY VARCHAR2);
-------------------------------------------------------------------------------------------------------------
PROCEDURE GET_DESC_METADATA_LOAD_INFO (
        p_dataset_key       IN  VARCHAR2,
        p_dataset_attrs     IN  ecc_sec_field_values DEFAULT NULL,
        p_languages         IN  VARCHAR2,
        x_ecc_ds_meta_rec   OUT NOCOPY ecc_ds_meta_rec,
        x_return_status     OUT NOCOPY VARCHAR2
  );
END CUSTOM_AR_ECC_UTIL_PVT;

create or replace PACKAGE BODY      CUSTOM_AR_ECC_UTIL_PVT AS
PROCEDURE CUSTOM_GET_ECC_DATA_LOAD_INFO(p_dataset_key IN VARCHAR2,
             p_load_type IN VARCHAR2,
                         p_ds_last_success_run IN DATE,
                         p_languages IN VARCHAR2,
                         p_addl_params IN       ecc_sec_field_values,
             x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
                     x_return_status OUT NOCOPY VARCHAR2)
IS
v_for_lang_pivot_clause varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_PIVOT_FOR_LANG_CL(p_languages);
v_lang_in       varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_WHERE_CL(p_languages);
l_log_module       CONSTANT VARCHAR2(255) := 'ar.plsql.AR_ECC_UTIL_PVT_CUSTOM.GET_ECC_DATA_LOAD_INFO';
  l_ar_installments  VARCHAR2(30000) := 'SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */
  trx_v.ECC_SPEC_ID, trx_v.COMMENTS CUSTOM_ATTRIBUTE
  FROM ari_ecc_trx_v            trx_v
  where trx_v.language in ('||v_lang_in||')) ';
  li_ar_installments  VARCHAR2(30000) := 'SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */
  trx_v.ECC_SPEC_ID, trx_v.COMMENTS CUSTOM_ATTRIBUTE
  FROM ari_ecc_trx_v            trx_v
  where trx_v.language in ('||v_lang_in||')) ';
query_det_arr  ecc_query_det_arr_type := ecc_query_det_arr_type(null);
l_return_status         VARCHAR2(1);
BEGIN
IF (p_load_type = 'FULL_LOAD' ) THEN
        IF (p_dataset_key='ar-copy') THEN
         query_det_arr.extend(1);
     query_det_arr(1) := ecc_query_det_rec(l_ar_installments,G_AR_ECC_INS_OP);
          x_ecc_ds_meta_rec  :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
          x_return_status  :='S';       
    END IF;
ELSIF(p_load_type ='INCREMENTAL_LOAD') THEN
        IF (p_dataset_key='ar-copy') THEN
         query_det_arr.extend(1);
         query_det_arr(1) := ecc_query_det_rec(li_ar_installments,G_AR_ECC_INS_OP);
          x_ecc_ds_meta_rec  :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
          x_return_status  :='S';       
   END IF;
 END IF;
END CUSTOM_GET_ECC_DATA_LOAD_INFO;
 PROCEDURE GET_DESC_METADATA_LOAD_INFO (
        p_dataset_key       IN  VARCHAR2,
        p_dataset_attrs     IN  ecc_sec_field_values DEFAULT NULL,
        p_languages         IN  VARCHAR2,
        x_ecc_ds_meta_rec   OUT NOCOPY ecc_ds_meta_rec,
        x_return_status     OUT NOCOPY VARCHAR2
  ) IS
  query_det_arr           ecc_query_det_arr_type := ecc_query_det_arr_type(NULL);
  l_dff_query_tbl         FND_ECC_DFF_UTIL.dff_query_tbl;
  vrow                    FND_ECC_DFF_UTIL.dff_query_rec;
  l_return_status         VARCHAR2(1) := 'S';
  l_dff1_cnt              NUMBER := 0;
  l_dff2_cnt              NUMBER := 0;
  l_apps_schema_name      VARCHAR2(10);
  l_log_module       CONSTANT VARCHAR2(255) := 'ar.plsql.AR_ECC_UTIL_PVT_CUSTOM.GET_DESC_METADATA_LOAD_INFO';
  l_ar_metadata  VARCHAR2(30000) := ' SELECT '||'''XYZ_'''||'|| COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME =''RA_CUSTOMER_TRX_ALL_DFV'' ';
   BEGIN
 if p_dataset_key = 'ar-copy' then
         query_det_arr(1) := ecc_query_det_rec(l_ar_metadata,CUSTOM_ECC_ATTRIBUTES);
     x_ecc_ds_meta_rec  :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
     x_return_status  :='S';
 end if;        
 END GET_DESC_METADATA_LOAD_INFO;
END CUSTOM_AR_ECC_UTIL_PVT;

Code Example for Modifying a Data Load Procedure

create or replace PACKAGE      CUSTOM_AR_ECC_UTIL_PVT AUTHID CURRENT_USER AS
G_AR_ECC_INS_OP CONSTANT VARCHAR2(30) := 'INSERT';
G_AR_ECC_UPD_OP CONSTANT VARCHAR2(30) := 'UPDATE';
G_AR_ECC_DEL_OP CONSTANT VARCHAR2(30) := 'DELETE';
G_AR_ECC_UPS_OP CONSTANT VARCHAR2(30) := 'UPSERT';
G_AR_ECC_QRY_OP CONSTANT VARCHAR2(30) := 'DELETE_BY_QUERY';PROCEDURE CUSTOM_GET_ECC_DATA_LOAD_INFO(p_dataset_key IN VARCHAR2,
             p_load_type IN VARCHAR2,
 p_ds_last_success_run IN DATE,
 p_languages IN VARCHAR2,
             p_addl_params IN ecc_sec_field_values,
             x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
     x_return_status OUT NOCOPY VARCHAR2);
------------------------------------------------------------------------------------------------------------- CUSTOM_AR_ECC_UTIL_PVT;

create or replace PACKAGE BODY      CUSTOM_AR_ECC_UTIL_PVT AS
PROCEDURE CUSTOM_GET_ECC_DATA_LOAD_INFO(p_dataset_key IN VARCHAR2,
             p_load_type IN VARCHAR2,
                         p_ds_last_success_run IN DATE,
                         p_languages IN VARCHAR2,
                         p_addl_params IN       ecc_sec_field_values,
             x_ecc_ds_meta_rec OUT NOCOPY ecc_ds_meta_rec,
                     x_return_status OUT NOCOPY VARCHAR2)
IS
v_for_lang_pivot_clause varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_PIVOT_FOR_LANG_CL(p_languages);
v_lang_in       varchar2(400) := FND_ECC_UTIL_MLS_PVT.GEN_ECC_MLS_WHERE_CL(p_languages);
l_log_module       CONSTANT VARCHAR2(255) := 'ar.plsql.AR_ECC_UTIL_PVT_CUSTOM.GET_ECC_DATA_LOAD_INFO';
  l_ar_installments  VARCHAR2(30000) := 'SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */
  trx_v.ECC_SPEC_ID, trx_v.COMMENTS CUSTOM_ATTRIBUTE
  FROM ari_ecc_trx_v            trx_v
  where trx_v.language in ('||v_lang_in||')) ';
  li_ar_installments  VARCHAR2(30000) := 'SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */
  trx_v.ECC_SPEC_ID, trx_v.COMMENTS CUSTOM_ATTRIBUTE
  FROM ari_ecc_trx_v            trx_v
  where trx_v.language in ('||v_lang_in||')) ';
query_det_arr  ecc_query_det_arr_type := ecc_query_det_arr_type(null);
l_return_status         VARCHAR2(1);
BEGIN
IF (p_load_type = 'FULL_LOAD' ) THEN
        
        IF (p_dataset_key='ar-copy') THEN
         query_det_arr.extend(1);
     query_det_arr(1) := ecc_query_det_rec(l_ar_installments,G_AR_ECC_INS_OP);
          x_ecc_ds_meta_rec  :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
          x_return_status  :='S';       
    END IF;
        
ELSIF(p_load_type ='INCREMENTAL_LOAD') THEN
        IF (p_dataset_key='ar-copy') THEN
         query_det_arr.extend(1);
         query_det_arr(6) := ecc_query_det_rec(li_ar_installments,G_AR_ECC_INS_OP);
          x_ecc_ds_meta_rec  :=ecc_ds_meta_rec(p_dataset_key,query_det_arr);
          x_return_status  :='S';       
   END IF;
 END IF;
END CUSTOM_GET_ECC_DATA_LOAD_INFO;
END CUSTOM_AR_ECC_UTIL_PVT;