This chapter covers the following topics:
Archiving pricing data enables you to copy pricing data from the pricing application tables to archive tables for long-term data storage.
You can archive price list and modifier lines. The archiving process permanently removes the lines from the related price list or modifier list into the archive tables for storage.
When the archived data is no longer required (and does not need to be retained for legal requirements), then you can use the purge feature to purge the data from the archive tables.QP_ARCHIVE_ENTITY_PUB
You can archive and purge price list lines and modifier list lines for the following price list and modifier list types:
Agreement Price List
Standard Price List
Deal
Discount List
Freight and Special Charge List
Promotion
Surcharge List
Archiving and purging records reduces the number of pricing records that the pricing engine queries, potentially improving processing performance.
You can also use APIs to archive and purge multiple pricing entities. For information about the APIs, see: Using API to Archive Pricing Entities and Using API to Purge Pricing Entities.
Related Topics
Oracle Advanced Pricing User's Guide, Archiving and Purging Pricing Entities
You use the Archive Pricing Entities window to archive single modifier lines and price list lines. This action permanently removes the line information from the associated price list or modifier list.
To archive multiple pricing entities that are no longer active or infrequently used, you can use the Archiving Pricing Entity API, QP_ARCHIVE_ENTITY_PUB. When you archive the data, the data from the application tables is inserted into the archiving tables.
The API supports the validations of the pricing entities. If there are any errors in the archive process, then you can view these errors in the log file.
For information about archiving and purging, see: Overview of Archiving and Purging Pricing Entities, Oracle Advanced Pricing User's Guide and Archiving Pricing Entities, Oracle Advanced Pricing User's Guide.
Use the following API to archive data:
Name of the API: Archiving Pricing Entity
Package: QP_ARCHIVE_ENTITY_PUB
For more information about the Archiving Pricing Entities API, see: Oracle Integration Repository.
Refer to the sample code to archive the pricing entities:
Declare
errbuf VARCHAR2(200);
retcode NUMBER;
archive_entity_tbl QP_ARCHIVE_ENTITY_PUB.Archive_Entity_Tbl_Type;
out_archive_entity_tbl QP_ARCHIVE_ENTITY_PUB.Archive_Entity_Tbl_Type;
l_Archive_Entity_rec QP_ARCHIVE_ENTITY_PUB.Archive_Entity_Type;
Begin
fnd_profile.PUT('QP_ARCH_HEADER_QUAL', 'Y');
fnd_profile.PUT('QP_DEBUG', 'Y');
archive_entity_tbl(1).archive_name := 'Ptest11';
archive_entity_tbl(1).source_system_code := 'QP';
archive_entity_tbl(1).entity := 568742;
archive_entity_tbl(1).all_lines := 'Y'; -- To archive all the lines of the entity
archive_entity_tbl(1).entity_type := 'Standard Price List';
archive_entity_tbl(2).archive_name := 'Mtest11';
archive_entity_tbl(2).source_system_code := 'QP';
archive_entity_tbl(2).entity := 568744;
archive_entity_tbl(2).all_lines := 'Y'; -- To archive all the lines of the entity
archive_entity_tbl(2).entity_type := 'Discount List';
QP_ARCHIVE_ENTITY_PUB.archive_entity(archive_entity_tbl,out_archive_entity_tbl);
IF out_archive_entity_tbl.COUNT <> 0 THEN
FOR I IN 1..out_archive_entity_tbl.COUNT LOOP
dbms_output.put_line('-----------------------------------------------------------------------');
dbms_output.put_line('I ---> '|| I);
l_Archive_Entity_rec := out_archive_entity_tbl(I);
dbms_output.put_line('p_archive_name - '|| l_Archive_Entity_rec.archive_name);
dbms_output.put_line('p_archive_name -'|| l_Archive_Entity_rec.entity_type);
dbms_output.put_line('p_source_system_code - '|| l_Archive_Entity_rec.source_system_code);
dbms_output.put_line('p_entity - '|| l_Archive_Entity_rec.entity );
dbms_output.put_line('p_all_lines - '|| l_Archive_Entity_rec.all_lines );
dbms_output.put_line('start_date_active - '|| l_Archive_Entity_rec.start_date_active);
dbms_output.put_line('end_date_active - '|| l_Archive_Entity_rec.end_date_active );
dbms_output.put_line('creation_date - '|| l_Archive_Entity_rec.creation_date );
dbms_output.put_line('created_by - '|| l_Archive_Entity_rec.created_by );
dbms_output.put_line('errbuf - '||l_Archive_Entity_rec.errbuf);
dbms_output.put_line('retcode - '||l_Archive_Entity_rec.retcode);
dbms_output.put_line('COMPLETED SUCCESSFULLY');
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' ERROR '||sqlerrm);
END;
Related Topics
Purging Pricing Entities, Oracle Advanced Pricing User's Guide
Using API to Purge Pricing Entities
You use the Purge Entity window to permanently delete a single archived pricing entity.
To delete multiple archived pricing entities permanently, you can use Purge Pricing Entity API, QP_PURGE_ENTITY_PUB API.
The API supports validations of pricing entities. If there are any errors, the log displays these errors.
For information about archiving and purging, see:Overview of Archiving and Purging Pricing Entities, Oracle Advanced Pricing User's Guide and Purging Pricing Entities, Oracle Advanced Pricing User's Guide
Use the following API to purge pricing entities:
Name of the API:Purge Pricing Entity API
Package:QP_PURGE_ENTITY_PUB API
For more information about the Purge Pricing Entity API, see: Oracle Integration Repository.
Refer to the following sample code to purge the pricing entities:
declare
errbuf VARCHAR2(200);
retcode NUMBER;
x_return_status VARCHAR2(100);
x_return_text VARCHAR2(2000);
purge_entity_tbl QP_PURGE_ENTITY_PUB.Purge_Entity_Tbl_Type;
out_purge_entity_tbl QP_PURGE_ENTITY_PUB.Purge_Entity_Tbl_Type;
l_Purge_Entity_rec QP_PURGE_ENTITY_PUB.Purge_Entity_Type;
Begin
fnd_profile.PUT('QP_DEBUG', 'Y');
purge_entity_tbl(1).source_system_code := 'QP';
purge_entity_tbl(1).archive_name := 'pv5';
purge_entity_tbl(2).source_system_code := 'QP';
purge_entity_tbl(2).entity_type := 'Discount List';
purge_entity_tbl(2).entity := '566751';
IF purge_entity_tbl.COUNT <> 0 THEN
QP_PURGE_ENTITY_PUB.Purge_entity(purge_entity_tbl ,
out_purge_entity_tbl );
END IF;
IF out_purge_entity_tbl.COUNT <> 0 THEN
FOR I IN 1..out_purge_entity_tbl.COUNT LOOP
dbms_output.put_line('archive_name ---> '|| purge_entity_tbl(I).archive_name);
dbms_output.put_line('source_system_code ---> '|| purge_entity_tbl(I).source_system_code);
dbms_output.put_line('errbuf ---> '|| out_purge_entity_tbl(I).errbuf);
dbms_output.put_line('retcode ---> '|| out_purge_entity_tbl(I).retcode);
END LOOP;
END IF;
dbms_output.put_line('COMPLETED SUCCESSFULLY');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' ERROR '||sqlerrm);
END;
Related Topics
Overview of Archiving and Purging Pricing Entities, Oracle Advanced Pricing User's Guide
Archiving Pricing Entities, Oracle Advanced Pricing User's Guide