OracleApps02: Migrate Distribution Set for Supplier

CREATE TABLE APPS.XXAP_DISTRIBUTION_SETS_T
(
DISTRIBUTION_SET_ID NUMBER(15) NOT NULL,
DISTRIBUTION_SET_NAME VARCHAR2(50 BYTE) NOT NULL,
HEADER_DESCRIPTION VARCHAR2(240 BYTE),
TOTAL_PERCENT_DISTRIBUTION NUMBER(19,3),
INACTIVE_DATE DATE,
ATTRIBUTE_CATEGORY_H VARCHAR2(150 BYTE),
ATTRIBUTE1_H VARCHAR2(150 BYTE),
ATTRIBUTE2_H VARCHAR2(150 BYTE),
ATTRIBUTE3_H VARCHAR2(150 BYTE),
ATTRIBUTE4_H VARCHAR2(150 BYTE),
ATTRIBUTE5_H VARCHAR2(150 BYTE),
ATTRIBUTE6_H VARCHAR2(150 BYTE),
ATTRIBUTE7_H VARCHAR2(150 BYTE),
ATTRIBUTE8_H VARCHAR2(150 BYTE),
ATTRIBUTE9_H VARCHAR2(150 BYTE),
ATTRIBUTE10_H VARCHAR2(150 BYTE),
ATTRIBUTE11_H VARCHAR2(150 BYTE),
ATTRIBUTE12_H VARCHAR2(150 BYTE),
ATTRIBUTE13_H VARCHAR2(150 BYTE),
ATTRIBUTE14_H VARCHAR2(150 BYTE),
ATTRIBUTE15_H VARCHAR2(150 BYTE),
ORG_ID_H NUMBER(15),
DISTRIBUTION_SET_LINE_ID NUMBER(15) NOT NULL,
DIST_CODE_COMBINATION_ID NUMBER(15),
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER(15) NOT NULL,
SET_OF_BOOKS_ID NUMBER(15) NOT NULL,
PERCENT_DISTRIBUTION NUMBER,
TYPE_1099 VARCHAR2(10 BYTE),
VAT_CODE VARCHAR2(15 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
LAST_UPDATE_LOGIN NUMBER(15),
CREATION_DATE DATE,
CREATED_BY NUMBER(15),
DISTRIBUTION_SET_LINE_NUMBER NUMBER(15) NOT NULL,
ATTRIBUTE_CATEGORY VARCHAR2(150 BYTE),
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
PROJECT_ACCOUNTING_CONTEXT VARCHAR2(30 BYTE),
TASK_ID NUMBER(15),
PROJECT_ID NUMBER(15),
EXPENDITURE_ORGANIZATION_ID NUMBER(15),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
ORG_ID NUMBER(15) DEFAULT NULL,
AWARD_ID NUMBER(15),
STATUS VARCHAR2(2 BYTE),
ERROR_MSG VARCHAR2(4000 BYTE)
)

CREATE OR REPLACE procedure APPS.xxdm_dist_set_load_p
as
cursor c1
is
select *
from xxap_distribution_sets_t
where error_msg is null and STATUS is null and rownum=1;
lv_distribution_set_id number;
lc_sqlerrm varchar2(4000);

begin
for i in c1
loop
begin
select ap_distribution_sets_s.nextval
into lv_distribution_set_id
from dual;

insert into ap_distribution_sets_all (distribution_set_id,
distribution_set_name,
last_update_date,
last_updated_by,
description,
total_percent_distribution,
inactive_date,
last_update_login,
creation_date,
created_by,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id)
values (lv_distribution_set_id,
i.distribution_set_name,
sysdate,
0,
i.distribution_set_name,
i.total_percent_distribution,
i.inactive_date,
0,
sysdate,
0,
i.attribute_category_h,
i.attribute1_h,
i.attribute2_h,
i.attribute3_h,
i.attribute4_h,
i.attribute5_h,
i.attribute6_h,
i.attribute7_h,
i.attribute8_h,
i.attribute9_h,
i.attribute10_h,
i.attribute11_h,
i.attribute12_h,
i.attribute13_h,
i.attribute14_h,
i.attribute15_h,
101);

insert
into ap_distribution_set_lines_all (distribution_set_id,
dist_code_combination_id,
last_update_date,
last_updated_by,
set_of_books_id,
percent_distribution,
type_1099,
vat_code,
description,
last_update_login,
creation_date,
created_by,
distribution_set_line_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
project_accounting_context,
task_id,
project_id,
expenditure_organization_id,
expenditure_type,
org_id,
award_id)
values (lv_distribution_set_id,
i.dist_code_combination_id,
sysdate,
0,
i.set_of_books_id,
i.percent_distribution,
i.type_1099,
i.vat_code,
i.description,
0,
sysdate,
0,
1,
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
null,
i.project_accounting_context,
i.task_id,
i.project_id,
i.expenditure_organization_id,
i.expenditure_type,
101,
i.award_id);

update xxap_distribution_sets_t
set status = 'S'
where distribution_set_id =i.distribution_set_id;
DBMS_OUTPUT.put_line ('Success load '||lv_distribution_set_id);
exception
when others
then
rollback;
lc_sqlerrm:=sqlerrm;
update xxap_distribution_sets_t
set status = 'E' , ERROR_MSG=lc_sqlerrm
where distribution_set_id =i.distribution_set_id;
DBMS_OUTPUT.put_line ('Error lc_sqlerrm ' || lc_sqlerrm);

end;
end loop;
end xxdm_dist_set_load_p;

--select * from ap_distribution_sets_all ;

select * from ap_distribution_set_lines_all;