CREATE TABLE xxcus.XX_PO_HEADERS_STG
(
INTERFACE_HEADER_ID NUMBER ,
BATCH_ID NUMBER,
INTERFACE_SOURCE_CODE VARCHAR2(25),
PROCESS_CODE VARCHAR2(25),
ACTION VARCHAR2(25),
GROUP_CODE VARCHAR2(25),
ORG_ID NUMBER,
DOCUMENT_TYPE_CODE VARCHAR2(25),
DOCUMENT_SUBTYPE VARCHAR2(25),
DOCUMENT_NUM VARCHAR2(20),
PO_HEADER_ID NUMBER,
RELEASE_NUM NUMBER,
PO_RELEASE_ID NUMBER,
RELEASE_DATE DATE,
CURRENCY_CODE VARCHAR2(15),
RATE_TYPE VARCHAR2(30),
RATE_TYPE_CODE VARCHAR2(30),
RATE_DATE DATE,
RATE NUMBER,
AGENT_NAME VARCHAR2(240),
AGENT_ID NUMBER,
VENDOR_NAME VARCHAR2(240),
VENDOR_ID NUMBER,
VENDOR_SITE_CODE VARCHAR2(15),
VENDOR_SITE_ID NUMBER,
VENDOR_CONTACT VARCHAR2(240),
VENDOR_CONTACT_ID NUMBER,
SHIP_TO_LOCATION VARCHAR2(60),
SHIP_TO_LOCATION_ID NUMBER,
BILL_TO_LOCATION VARCHAR2(60),
BILL_TO_LOCATION_ID NUMBER,
PAYMENT_TERMS VARCHAR2(50),
TERMS_ID NUMBER,
FREIGHT_CARRIER VARCHAR2(25),
FOB VARCHAR2(25),
FREIGHT_TERMS VARCHAR2(25),
APPROVAL_STATUS VARCHAR2(25),
APPROVED_DATE DATE,
REVISED_DATE DATE,
REVISION_NUM NUMBER,
NOTE_TO_VENDOR VARCHAR2(480),
NOTE_TO_RECEIVER VARCHAR2(480),
CONFIRMING_ORDER_FLAG VARCHAR2(1),
COMMENTS VARCHAR2(240),
ACCEPTANCE_REQUIRED_FLAG VARCHAR2(1),
ACCEPTANCE_DUE_DATE DATE,
AMOUNT_AGREED NUMBER,
AMOUNT_LIMIT NUMBER,
MIN_RELEASE_AMOUNT NUMBER,
EFFECTIVE_DATE DATE,
EXPIRATION_DATE DATE,
PRINT_COUNT NUMBER,
PRINTED_DATE DATE,
FIRM_FLAG VARCHAR2(25),
FROZEN_FLAG VARCHAR2(1),
CLOSED_CODE VARCHAR2(25),
CLOSED_DATE DATE,
REPLY_DATE DATE,
REPLY_METHOD VARCHAR2(25),
RFQ_CLOSE_DATE DATE,
QUOTE_WARNING_DELAY NUMBER,
VENDOR_DOC_NUM VARCHAR2(25),
APPROVAL_REQUIRED_FLAG VARCHAR2(1),
VENDOR_LIST VARCHAR2(25),
VENDOR_LIST_HEADER_ID NUMBER,
FROM_HEADER_ID NUMBER,
FROM_TYPE_LOOKUP_CODE VARCHAR2(25),
USSGL_TRANSACTION_CODE VARCHAR2(30),
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150),
ATTRIBUTE2 VARCHAR2(150),
ATTRIBUTE3 VARCHAR2(150),
ATTRIBUTE4 VARCHAR2(150),
ATTRIBUTE5 VARCHAR2(150),
ATTRIBUTE6 VARCHAR2(150),
ATTRIBUTE7 VARCHAR2(150),
ATTRIBUTE8 VARCHAR2(150),
ATTRIBUTE9 VARCHAR2(150),
ATTRIBUTE10 VARCHAR2(150),
ATTRIBUTE11 VARCHAR2(150),
ATTRIBUTE12 VARCHAR2(150),
ATTRIBUTE13 VARCHAR2(150),
ATTRIBUTE14 VARCHAR2(150),
ATTRIBUTE15 VARCHAR2(150),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
REFERENCE_NUM VARCHAR2(25),
LOAD_SOURCING_RULES_FLAG VARCHAR2(1),
VENDOR_NUM VARCHAR2(30),
FROM_RFQ_NUM VARCHAR2(25),
WF_GROUP_ID NUMBER,
PCARD_ID NUMBER(15),
PAY_ON_CODE VARCHAR2(25),
GLOBAL_AGREEMENT_FLAG VARCHAR2(1),
CONSUME_REQ_DEMAND_FLAG VARCHAR2(1),
SHIPPING_CONTROL VARCHAR2(30),
ENCUMBRANCE_REQUIRED_FLAG VARCHAR2(1),
AMOUNT_TO_ENCUMBER NUMBER,
CHANGE_SUMMARY VARCHAR2(2000),
BUDGET_ACCOUNT_SEGMENT1 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT2 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT3 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT4 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT5 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT6 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT7 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT8 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT9 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT10 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT11 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT12 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT13 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT14 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT15 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT16 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT17 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT18 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT19 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT20 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT21 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT22 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT23 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT24 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT25 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT26 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT27 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT28 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT29 VARCHAR2(25),
BUDGET_ACCOUNT_SEGMENT30 VARCHAR2(25),
BUDGET_ACCOUNT VARCHAR2(2000),
BUDGET_ACCOUNT_ID NUMBER,
GL_ENCUMBERED_DATE DATE,
GL_ENCUMBERED_PERIOD_NAME VARCHAR2(15),
process_flag VARCHAR2(1),
error_message VARCHAR2(1000)
);
Line Staging Table Creation
CREATE TABLE cust.XX_PO_LINES_STG
(
INTERFACE_LINE_ID NUMBER ,
INTERFACE_HEADER_ID NUMBER ,
ACTION VARCHAR2(25),
GROUP_CODE VARCHAR2(25),
LINE_NUM NUMBER,
PO_LINE_ID NUMBER,
SHIPMENT_NUM NUMBER,
LINE_LOCATION_ID NUMBER,
SHIPMENT_TYPE VARCHAR2(25),
REQUISITION_LINE_ID NUMBER,
DOCUMENT_NUM NUMBER,
RELEASE_NUM NUMBER,
PO_HEADER_ID NUMBER,
PO_RELEASE_ID NUMBER,
SOURCE_SHIPMENT_ID NUMBER,
CONTRACT_NUM VARCHAR2(25),
LINE_TYPE VARCHAR2(25),
LINE_TYPE_ID NUMBER,
ITEM VARCHAR2(1000),
ITEM_ID NUMBER,
ITEM_REVISION VARCHAR2(3),
CATEGORY VARCHAR2(2000),
CATEGORY_ID NUMBER,
ITEM_DESCRIPTION VARCHAR2(240),
VENDOR_PRODUCT_NUM VARCHAR2(25),
UOM_CODE VARCHAR2(3),
UNIT_OF_MEASURE VARCHAR2(25),
QUANTITY NUMBER,
COMMITTED_AMOUNT NUMBER,
MIN_ORDER_QUANTITY NUMBER,
MAX_ORDER_QUANTITY NUMBER,
UNIT_PRICE NUMBER,
LIST_PRICE_PER_UNIT NUMBER,
MARKET_PRICE NUMBER,
ALLOW_PRICE_OVERRIDE_FLAG VARCHAR2(1),
NOT_TO_EXCEED_PRICE NUMBER,
NEGOTIATED_BY_PREPARER_FLAG VARCHAR2(1),
UN_NUMBER VARCHAR2(25),
UN_NUMBER_ID NUMBER,
HAZARD_CLASS VARCHAR2(40),
HAZARD_CLASS_ID NUMBER,
NOTE_TO_VENDOR VARCHAR2(480),
TRANSACTION_REASON_CODE VARCHAR2(25),
TAXABLE_FLAG VARCHAR2(1),
TAX_NAME VARCHAR2(15),
TYPE_1099 VARCHAR2(10),
CAPITAL_EXPENSE_FLAG VARCHAR2(1),
INSPECTION_REQUIRED_FLAG VARCHAR2(1),
RECEIPT_REQUIRED_FLAG VARCHAR2(1),
PAYMENT_TERMS VARCHAR2(50),
TERMS_ID NUMBER,
PRICE_TYPE VARCHAR2(25),
MIN_RELEASE_AMOUNT NUMBER,
PRICE_BREAK_LOOKUP_CODE VARCHAR2(25),
USSGL_TRANSACTION_CODE VARCHAR2(30),
CLOSED_CODE VARCHAR2(25),
CLOSED_REASON VARCHAR2(240),
CLOSED_DATE DATE,
CLOSED_BY NUMBER,
INVOICE_CLOSE_TOLEARNCE NUMBER,
RECEIVE_CLOSE_TOLERANCE NUMBER,
FIRM_FLAG VARCHAR2(25),
DAYS_EARLY_RECEIPT_ALLOWED NUMBER,
DAYS_LATE_RECEIPT_ALLOWED NUMBER,
ENFORCE_SHIP_TO_LOCATION_CODE VARCHAR2(25),
ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1),
RECEIVING_ROUTING VARCHAR2(30),
RECEIVING_ROUTING_ID NUMBER,
QTY_RCV_TOLERANCE NUMBER,
OVER_TOLERANCE_ERROR_FLAG VARCHAR2(25),
QTY_RCV_EXCEPTION_CODE VARCHAR2(25),
RECEIPT_DAYS_EXCEPTION_CODE VARCHAR2(25),
SHIP_TO_ORGANIZATION_CODE VARCHAR2(3),
SHIP_TO_ORGANIZATION_ID NUMBER,
SHIP_TO_LOCATION VARCHAR2(60),
SHIP_TO_LOCATION_ID NUMBER,
NEED_BY_DATE DATE,
PROMISED_DATE DATE,
ACCRUE_ON_RECEIPT_FLAG VARCHAR2(1),
LEAD_TIME NUMBER,
LEAD_TIME_UNIT VARCHAR2(25),
PRICE_DISCOUNT NUMBER,
FREIGHT_CARRIER VARCHAR2(80),
FOB VARCHAR2(25),
FREIGHT_TERMS VARCHAR2(25),
EFFECTIVE_DATE DATE,
EXPIRATION_DATE DATE,
FROM_HEADER_ID NUMBER,
FROM_LINE_ID NUMBER,
FROM_LINE_LOCATION_ID NUMBER,
LINE_ATTRIBUTE_CATEGORY_LINES VARCHAR2(30),
LINE_ATTRIBUTE1 VARCHAR2(150),
LINE_ATTRIBUTE2 VARCHAR2(150),
LINE_ATTRIBUTE3 VARCHAR2(150),
LINE_ATTRIBUTE4 VARCHAR2(150),
LINE_ATTRIBUTE5 VARCHAR2(150),
LINE_ATTRIBUTE6 VARCHAR2(150),
LINE_ATTRIBUTE7 VARCHAR2(150),
LINE_ATTRIBUTE8 VARCHAR2(150),
LINE_ATTRIBUTE9 VARCHAR2(150),
LINE_ATTRIBUTE10 VARCHAR2(150),
LINE_ATTRIBUTE11 VARCHAR2(150),
LINE_ATTRIBUTE12 VARCHAR2(150),
LINE_ATTRIBUTE13 VARCHAR2(150),
LINE_ATTRIBUTE14 VARCHAR2(150),
LINE_ATTRIBUTE15 VARCHAR2(150),
SHIPMENT_ATTRIBUTE_CATEGORY VARCHAR2(30),
SHIPMENT_ATTRIBUTE1 VARCHAR2(150),
SHIPMENT_ATTRIBUTE2 VARCHAR2(150),
SHIPMENT_ATTRIBUTE3 VARCHAR2(150),
SHIPMENT_ATTRIBUTE4 VARCHAR2(150),
SHIPMENT_ATTRIBUTE5 VARCHAR2(150),
SHIPMENT_ATTRIBUTE6 VARCHAR2(150),
SHIPMENT_ATTRIBUTE7 VARCHAR2(150),
SHIPMENT_ATTRIBUTE8 VARCHAR2(150),
SHIPMENT_ATTRIBUTE9 VARCHAR2(150),
SHIPMENT_ATTRIBUTE10 VARCHAR2(150),
SHIPMENT_ATTRIBUTE11 VARCHAR2(150),
SHIPMENT_ATTRIBUTE12 VARCHAR2(150),
SHIPMENT_ATTRIBUTE13 VARCHAR2(150),
SHIPMENT_ATTRIBUTE14 VARCHAR2(150),
SHIPMENT_ATTRIBUTE15 VARCHAR2(150),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
INVOICE_CLOSE_TOLERANCE NUMBER,
ORGANIZATION_ID NUMBER,
ITEM_ATTRIBUTE_CATEGORY VARCHAR2(30),
ITEM_ATTRIBUTE1 VARCHAR2(150),
ITEM_ATTRIBUTE2 VARCHAR2(150),
ITEM_ATTRIBUTE3 VARCHAR2(150),
ITEM_ATTRIBUTE4 VARCHAR2(150),
ITEM_ATTRIBUTE5 VARCHAR2(150),
ITEM_ATTRIBUTE6 VARCHAR2(150),
ITEM_ATTRIBUTE7 VARCHAR2(150),
ITEM_ATTRIBUTE8 VARCHAR2(150),
ITEM_ATTRIBUTE9 VARCHAR2(150),
ITEM_ATTRIBUTE10 VARCHAR2(150),
ITEM_ATTRIBUTE11 VARCHAR2(150),
ITEM_ATTRIBUTE12 VARCHAR2(150),
ITEM_ATTRIBUTE13 VARCHAR2(150),
ITEM_ATTRIBUTE14 VARCHAR2(150),
ITEM_ATTRIBUTE15 VARCHAR2(150),
UNIT_WEIGHT NUMBER,
WEIGHT_UOM_CODE VARCHAR2(3),
VOLUME_UOM_CODE VARCHAR2(3),
UNIT_VOLUME NUMBER,
TEMPLATE_ID NUMBER,
TEMPLATE_NAME VARCHAR2(30),
LINE_REFERENCE_NUM VARCHAR2(25),
SOURCING_RULE_NAME VARCHAR2(50),
TAX_STATUS_INDICATOR VARCHAR2(30),
PROCESS_CODE VARCHAR2(25),
PRICE_CHG_ACCEPT_FLAG VARCHAR2(1),
PRICE_BREAK_FLAG VARCHAR2(1),
PRICE_UPDATE_TOLERANCE NUMBER,
TAX_USER_OVERRIDE_FLAG VARCHAR2(1),
TAX_CODE_ID NUMBER(15),
NOTE_TO_RECEIVER VARCHAR2(480),
OKE_CONTRACT_HEADER_ID NUMBER,
OKE_CONTRACT_HEADER_NUM VARCHAR2(120),
OKE_CONTRACT_VERSION_ID NUMBER,
SECONDARY_UNIT_OF_MEASURE VARCHAR2(25),
SECONDARY_UOM_CODE VARCHAR2(3),
SECONDARY_QUANTITY NUMBER,
PREFERRED_GRADE VARCHAR2(25),
VMI_FLAG VARCHAR2(1),
AUCTION_HEADER_ID NUMBER,
AUCTION_LINE_NUMBER NUMBER,
AUCTION_DISPLAY_NUMBER VARCHAR2(40),
BID_NUMBER NUMBER,
BID_LINE_NUMBER NUMBER,
ORIG_FROM_REQ_FLAG VARCHAR2(1),
CONSIGNED_FLAG VARCHAR2(1),
SUPPLIER_REF_NUMBER VARCHAR2(150),
CONTRACT_ID NUMBER,
JOB_ID NUMBER,
AMOUNT NUMBER,
JOB_NAME VARCHAR2(240),
CONTRACTOR_FIRST_NAME VARCHAR2(240),
CONTRACTOR_LAST_NAME VARCHAR2(240),
DROP_SHIP_FLAG VARCHAR2(1),
BASE_UNIT_PRICE NUMBER,
TRANSACTION_FLOW_HEADER_ID NUMBER,
JOB_BUSINESS_GROUP_ID NUMBER(15),
JOB_BUSINESS_GROUP_NAME VARCHAR2(240)
— DOCUMENT_NUM VARCHAr2(100)
);
Distributions Staging Table Creation
CREATE TABLE cust.XX_PO_DISTRIBUTIONS_STG
(
INTERFACE_HEADER_ID NUMBER ,
INTERFACE_LINE_ID NUMBER ,
INTERFACE_DISTRIBUTION_ID NUMBER ,
PO_HEADER_ID NUMBER,
PO_RELEASE_ID NUMBER,
PO_LINE_ID NUMBER,
LINE_LOCATION_ID NUMBER,
PO_DISTRIBUTION_ID NUMBER,
DISTRIBUTION_NUM NUMBER,
SOURCE_DISTRIBUTION_ID NUMBER,
ORG_ID NUMBER,
QUANTITY_ORDERED NUMBER,
QUANTITY_DELIVERED NUMBER,
QUANTITY_BILLED NUMBER,
QUANTITY_CANCELLED NUMBER,
RATE_DATE DATE,
RATE NUMBER,
DELIVER_TO_LOCATION VARCHAR2(60),
DELIVER_TO_LOCATION_ID NUMBER,
DELIVER_TO_PERSON_FULL_NAME VARCHAR2(240),
DELIVER_TO_PERSON_ID NUMBER,
DESTINATION_TYPE VARCHAR2(25),
DESTINATION_TYPE_CODE VARCHAR2(25),
DESTINATION_ORGANIZATION VARCHAR2(60),
DESTINATION_ORGANIZATION_ID NUMBER,
DESTINATION_SUBINVENTORY VARCHAR2(10),
DESTINATION_CONTEXT VARCHAR2(30),
SET_OF_BOOKS VARCHAR2(30),
SET_OF_BOOKS_ID NUMBER,
CHARGE_ACCOUNT VARCHAR2(2000),
CHARGE_ACCOUNT_ID NUMBER,
BUDGET_ACCOUNT VARCHAR2(2000),
BUDGET_ACCOUNT_ID NUMBER,
ACCURAL_ACCOUNT VARCHAR2(2000),
ACCRUAL_ACCOUNT_ID NUMBER,
VARIANCE_ACCOUNT VARCHAR2(2000),
VARIANCE_ACCOUNT_ID NUMBER,
AMOUNT_BILLED NUMBER,
ACCRUE_ON_RECEIPT_FLAG VARCHAR2(1),
ACCRUED_FLAG VARCHAR2(1),
PREVENT_ENCUMBRANCE_FLAG VARCHAR2(1),
ENCUMBERED_FLAG VARCHAR2(1),
ENCUMBERED_AMOUNT NUMBER,
UNENCUMBERED_QUANTITY NUMBER,
UNENCUMBERED_AMOUNT NUMBER,
FAILED_FUNDS VARCHAR2(25),
FAILED_FUNDS_LOOKUP_CODE VARCHAR2(25),
GL_ENCUMBERED_DATE DATE,
GL_ENCUMBERED_PERIOD_NAME VARCHAR2(15),
GL_CANCELLED_DATE DATE,
GL_CLOSED_DATE DATE,
REQ_HEADER_REFERENCE_NUM VARCHAR2(25),
REQ_LINE_REFERENCE_NUM VARCHAR2(25),
REQ_DISTRIBUTION_ID NUMBER,
WIP_ENTITY VARCHAR2(240),
WIP_ENTITY_ID NUMBER,
WIP_OPERATION_SEQ_NUM NUMBER,
WIP_RESOURCE_SEQ_NUM NUMBER,
WIP_REPETITIVE_SCHEDULE VARCHAR2(240),
WIP_REPETITIVE_SCHEDULE_ID NUMBER,
WIP_LINE_CODE VARCHAR2(10),
WIP_LINE_ID NUMBER,
BOM_RESOURCE_CODE VARCHAR2(10),
BOM_RESOURCE_ID NUMBER,
USSGL_TRANSACTION_CODE VARCHAR2(30),
GOVERNMENT_CONTEXT VARCHAR2(30),
PROJECT VARCHAR2(30),
PROJECT_ID NUMBER,
TASK VARCHAR2(20),
TASK_ID NUMBER,
EXPENDITURE VARCHAR2(60),
EXPENDITURE_TYPE VARCHAR2(30),
PROJECT_ACCOUNTING_CONTEXT VARCHAR2(30),
EXPENDITURE_ORGANIZATION VARCHAR2(60),
EXPENDITURE_ORGANIZATION_ID NUMBER,
PROJECT_RELEATED_FLAG VARCHAR2(1),
EXPENDITURE_ITEM_DATE DATE,
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150),
ATTRIBUTE2 VARCHAR2(150),
ATTRIBUTE3 VARCHAR2(150),
ATTRIBUTE4 VARCHAR2(150),
ATTRIBUTE5 VARCHAR2(150),
ATTRIBUTE6 VARCHAR2(150),
ATTRIBUTE7 VARCHAR2(150),
ATTRIBUTE8 VARCHAR2(150),
ATTRIBUTE9 VARCHAR2(150),
ATTRIBUTE10 VARCHAR2(150),
ATTRIBUTE11 VARCHAR2(150),
ATTRIBUTE12 VARCHAR2(150),
ATTRIBUTE13 VARCHAR2(150),
ATTRIBUTE14 VARCHAR2(150),
ATTRIBUTE15 VARCHAR2(150),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
END_ITEM_UNIT_NUMBER VARCHAR2(30),
RECOVERABLE_TAX NUMBER,
NONRECOVERABLE_TAX NUMBER,
RECOVERY_RATE NUMBER,
TAX_RECOVERY_OVERRIDE_FLAG VARCHAR2(1),
AWARD_ID NUMBER(15),
CHARGE_ACCOUNT_SEGMENT1 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT2 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT3 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT4 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT5 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT6 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT7 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT8 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT9 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT10 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT11 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT12 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT13 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT14 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT15 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT16 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT17 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT18 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT19 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT20 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT21 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT22 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT23 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT24 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT25 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT26 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT27 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT28 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT29 VARCHAR2(25),
CHARGE_ACCOUNT_SEGMENT30 VARCHAR2(25),
OKE_CONTRACT_LINE_ID NUMBER,
OKE_CONTRACT_LINE_NUM VARCHAR2(150),
OKE_CONTRACT_DELIVERABLE_ID NUMBER,
OKE_CONTRACT_DELIVERABLE_NUM VARCHAR2(150),
AWARD_NUMBER VARCHAR2(15),
AMOUNT_ORDERED NUMBER,
INVOICE_ADJUSTMENT_FLAG VARCHAR2(1),
DEST_CHARGE_ACCOUNT_ID NUMBER,
DEST_VARIANCE_ACCOUNT_ID NUMBER,
DOCUMENT_NUM VARCHAr2(100),
LINE_NUM NUMBER,
SHIPMENT_NUM NUMBER
);
Creating synonym
create synonym XX_PO_DISTRIBUTIONS_STG FOR cust.XX_PO_DISTRIBUTIONS_STG;
create synonym XX_PO_LINES_STG FOR cust.XX_PO_LINES_STG;
create synonym XX_PO_HEADERS_STG FOR cust.XX_PO_HEADERS_STG;
SQL Loader for Header
OPTIONS(SKIP=2)
LOAD DATA
INFILE *
REPLACE INTO TABLE XX_PO_HEADERS_STG
fields terminated by ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
trailing nullcols
(
INTERFACE_SOURCE_CODE
,PROCESS_CODE
,ACTION
,DOCUMENT_TYPE_CODE
,DOCUMENT_SUBTYPE
,DOCUMENT_NUM
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE “replace(:VENDOR_SITE_CODE,chr(13),”)”
)
SQL Loader for Lines
OPTIONS(SKIP=2)
LOAD DATA
INFILE *
REPLACE INTO TABLE XX_PO_LINES_STG
fields terminated by ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
trailing nullcols
(
ACTION
,LINE_NUM
,SHIPMENT_NUM
,SHIPMENT_TYPE
,DOCUMENT_NUM
,LINE_TYPE
,ITEM
,CATEGORY
,ITEM_DESCRIPTION
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,NEED_BY_DATE
)
SQL Loader for Distributions
OPTIONS(SKIP=1)
LOAD DATA
INFILE *
REPLACE INTO TABLE XX_PO_DISTRIBUTIONS_STG
fields terminated by ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
trailing nullcols
(
QUANTITY_ORDERED
,QUANTITY_DELIVERED
,QUANTITY_BILLED
,QUANTITY_CANCELLED
,DISTRIBUTION_NUM
,LINE_NUM
,SHIPMENT_NUM
,DOCUMENT_NUM
,DESTINATION_TYPE
,DESTINATION_ORGANIZATION
,CHARGE_ACCOUNT “replace(:CHARGE_ACCOUNT,chr(13),”)”
)
Validation Package Spec
CREATE OR REPLACE PACKAGE XX_PO_PUR_ORD_INT_PKG
is
procedure pur_ord(x_errbuf out varchar2
,x_retcode out varchar2
);
end XX_PO_PUR_ORD_INT_PKG;
/
Validation Package Body
CREATE OR REPLACE PACKAGE BODY XX_PO_PUR_ORD_INT_PKG
is
g_request_id number := fnd_profile.value(‘CONC_REQUEST_ID’);
g_user_id number := fnd_profile.value(‘USER_ID’);
g_date date := sysdate;
procedure dis_log(p_message in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_message);
end;
procedure pur_ord(x_errbuf out varchar2
,x_retcode out varchar2
)
is
cursor cur_hea
is
select a.rowid row_id,a.* from
XX_PO_HEADERS_STG a;
cursor cur_line(p_doc_num in varchar2)
is
select * from
XX_PO_LINES_STG
where document_num = p_doc_num;
cursor cur_dist(p_doc_num in varchar2,
p_line_num in varchar2,
p_ship_num in varchar2)
is
select * from
XX_PO_DISTRIBUTIONS_STG
WHERE DOCUMENT_NUM = p_doc_num
AND LINE_NUM = p_line_num
AND SHIPMENT_NUM = p_ship_num;
v_hea_rec po_headers_interface%rowtype;
v_line_rec po_lines_interface%rowtype;
v_dist_rec po_distributions_interface%rowtype;
v_error_message varchar2(1000);
v_err_msg varchar2(1000);
v_process_flag varchar2(1);
v_po_exists EXCEPTION;
v_count number;
begin
for rec_hea in cur_hea
loop — Header
savepoint v_save_point;
v_process_flag := ‘S’;
v_error_message := null;
v_hea_rec := null;
v_err_msg := null;
begin
begin
select pot.lookup_code
into v_hea_rec.DOCUMENT_TYPE_CODE
from po_lookup_codes pot,
po_lookup_codes pol
where pot.lookup_type=’PO TYPE’
and pot.displayed_field = pol.description
and pol.lookup_type=’BB8 PO TYPE MAPPING’
and pol.displayed_field = rec_hea.DOCUMENT_TYPE_CODE;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid Document type -> ‘||rec_hea.DOCUMENT_TYPE_CODE;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in Document type validation -> ‘||rec_hea.DOCUMENT_TYPE_CODE||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
— po is already exists
select count(1)
into v_count
from po_headers_all
where segment1 = rec_hea.DOCUMENT_NUM
and TYPE_LOOKUP_CODE = v_hea_rec.DOCUMENT_TYPE_CODE
and org_id = fnd_profile.value(‘ORG_ID’);
if v_count > 0
then
raise v_po_exists;
end if;
— vendor is valid
begin
select vendor_id
into v_hea_rec.vendor_id
from po_vendors
where vendor_name = rec_hea.VENDOR_NAME;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid vendor -> ‘||rec_hea.VENDOR_NAME;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in vendor validation -> ‘||rec_hea.VENDOR_NAME||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
dis_log(‘Vendor id -> ‘||v_hea_rec.vendor_id);
dis_log(‘VENDOR_SITE_CODE-> ‘||rec_hea.VENDOR_SITE_CODE);
dis_log(‘org id -> ‘||fnd_profile.value(‘ORG_ID’));
— vendor site is valid
begin
select vendor_site_id
into v_hea_rec.vendor_site_id
from po_vendor_sites_all
where vendor_id = v_hea_rec.vendor_id
and vendor_site_code = rec_hea.VENDOR_SITE_CODE
and org_id = fnd_profile.value(‘ORG_ID’);
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid vendor site -> ‘||rec_hea.VENDOR_SITE_CODE;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in vendor site validation -> ‘||rec_hea.VENDOR_SITE_CODE||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
— buyer
begin
select agent_id
into v_hea_rec.agent_id
from po_agents poa
,per_all_people_f papf
where poa.agent_id = papf.person_id
and papf.full_name = rec_hea.AGENT_NAME
and sysdate between papf.effective_start_date and papf.effective_end_date;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid buyer -> ‘||rec_hea.AGENT_NAME;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in buyer validation -> ‘||rec_hea.AGENT_NAME||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
select PO_HEADERS_INTERFACE_S.nextval
into v_hea_rec.interface_header_id
from dual;
v_hea_rec.INTERFACE_SOURCE_CODE := rec_hea.INTERFACE_SOURCE_CODE;
v_hea_rec.PROCESS_CODE := rec_hea.PROCESS_CODE;
v_hea_rec.ACTION := rec_hea.ACTION;
— v_hea_rec.DOCUMENT_TYPE_CODE := rec_hea.DOCUMENT_TYPE_CODE;
v_hea_rec.DOCUMENT_NUM := rec_hea.DOCUMENT_NUM;
— v_hea_rec.AGENT_NAME := rec_hea.AGENT_NAME;
— v_hea_rec.VENDOR_NAME := rec_hea.VENDOR_NAME;
— v_hea_rec.VENDOR_SITE_CODE := rec_hea.VENDOR_SITE_CODE;
v_hea_rec.org_id := fnd_profile.value(‘ORG_ID’);
v_hea_rec.REQUEST_ID := g_request_id;
v_hea_rec.creation_date := sysdate;
v_hea_rec.created_by := g_user_id;
v_hea_rec.last_update_date := g_date;
v_hea_rec.last_updated_by := g_user_id;
for rec_line in cur_line(rec_hea.document_num)
loop — lines
v_line_rec := null;
select PO_LINES_INTERFACE_S.nextval
into v_line_rec.interface_line_id
from dual;
— line type
begin
select line_type_id
into v_line_rec.line_type_id
from po_line_types
where LINE_TYPE = rec_line.LINE_TYPE;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid line type -> ‘||rec_line.LINE_TYPE;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in line type validation -> ‘||rec_line.LINE_TYPE||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
— ship to org
begin
select organization_id
into v_line_rec.ship_to_organization_id
from org_organization_definitions
where organization_code = rec_line.SHIP_TO_ORGANIZATION_CODE;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid organization -> ‘||rec_line.SHIP_TO_ORGANIZATION_CODE;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in line type validation -> ‘||rec_line.SHIP_TO_ORGANIZATION_CODE||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
— item
begin
select msi.inventory_item_id
,mic.category_id
,msi.primary_uom_code
,msi.description
into v_line_rec.item_id
,v_line_rec.category_id
,v_line_rec.uom_code
,v_line_rec.item_description
from mtl_system_items_b msi,
mtl_item_categories mic,
mtl_category_sets mcs
where msi.organization_id = v_line_rec.ship_to_organization_id
and msi.segment1 = rec_line.item
and mic.inventory_item_id = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and mic.category_set_id = mcs.category_set_id
and mcs.CATEGORY_SET_NAME = ‘Purchasing’;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid item -> ‘||rec_line.item;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘excception in item validation -> ‘||rec_line.item||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
— qty
— price
— need by date
if nvl(rec_line.QUANTITY,0) = 0 or rec_line.UNIT_PRICE is null
or rec_line.NEED_BY_DATE is null
then
v_process_flag := ‘E’;
v_error_message := ‘Qty or unit price or need by date is null’;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end if;
v_line_rec.interface_header_id := v_hea_rec.interface_header_id;
v_line_rec.ACTION := rec_line.ACTION;
v_line_rec.LINE_NUM := rec_line.LINE_NUM;
v_line_rec.SHIPMENT_NUM := rec_line.SHIPMENT_NUM;
v_line_rec.SHIPMENT_TYPE := rec_line.SHIPMENT_TYPE;
— v_line_rec.LINE_TYPE := rec_line.LINE_TYPE;
— v_line_rec.ITEM := rec_line.ITEM;
— v_line_rec.CATEGORY := rec_line.CATEGORY;
— v_line_rec.ITEM_DESCRIPTION := rec_line.ITEM_DESCRIPTION;
v_line_rec.QUANTITY := rec_line.QUANTITY;
v_line_rec.UNIT_PRICE := rec_line.UNIT_PRICE;
— v_line_rec.SHIP_TO_ORGANIZATION_CODE := rec_line.SHIP_TO_ORGANIZATION_CODE;
v_line_rec.NEED_BY_DATE := rec_line.NEED_BY_DATE;
v_line_rec.REQUEST_ID := g_request_id;
v_line_rec.creation_date := sysdate;
v_line_rec.created_by := g_user_id;
v_line_rec.last_update_date := g_date;
v_line_rec.last_updated_by := g_user_id;
for rec_dist in cur_dist(p_doc_num => rec_line.document_num,
p_line_num => rec_line.line_num,
p_ship_num => rec_line.SHIPMENT_NUM
)
loop — dist
v_dist_rec := null;
begin
select lookup_code
into v_dist_rec.destination_type_code
from po_lookup_codes
where lookup_type = ‘DESTINATION TYPE’
and displayed_field = rec_dist.DESTINATION_TYPE;
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid dest type -> ‘||rec_dist.DESTINATION_TYPE;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘exception in dest type validation -> ‘||rec_dist.DESTINATION_TYPE||’ ‘||sqlerrm;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end;
if rec_dist.QUANTITY_ORDERED is null
then
v_process_flag := ‘E’;
v_error_message := ‘qty ordered is null’;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
end if;
begin
select code_combination_id
into v_dist_rec.charge_account_id
from gl_code_combinations_kfv gcc
,gl_sets_of_books gsb
where concatenated_segments = rec_dist.CHARGE_ACCOUNT
and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
and set_of_books_id = fnd_profile.value(‘GL_SET_OF_BKS_ID’);
exception
when no_data_found then
v_process_flag := ‘E’;
v_error_message := ‘Invalid charge account -> ‘||rec_dist.CHARGE_ACCOUNT;
v_err_msg := v_err_msg||’ ‘||v_error_message;
dis_log(v_error_message);
when others then
v_process_flag := ‘E’;
v_error_message := ‘exception in charge account validation -> ‘||rec_dist.CHARGE_ACCOUNT||’ ‘||sqlerrm;
dis_log(v_error_message);
end ;
— charge accont
if v_process_flag = ‘S’
then
select
PO_DISTRIBUTIONS_INTERFACE_S.nextval
into v_dist_rec.interface_distribution_id
from dual;
v_dist_rec.interface_header_id := v_hea_rec.interface_header_id;
v_dist_rec.interface_line_id := v_line_rec.interface_line_id;
v_dist_rec.QUANTITY_ORDERED := rec_dist.QUANTITY_ORDERED;
v_dist_rec.QUANTITY_DELIVERED := rec_dist.QUANTITY_DELIVERED;
v_dist_rec.QUANTITY_BILLED := rec_dist.QUANTITY_BILLED;
v_dist_rec.QUANTITY_CANCELLED := rec_dist.QUANTITY_CANCELLED;
v_dist_rec.DISTRIBUTION_NUM := rec_dist.DISTRIBUTION_NUM;
–v_dist_rec.DESTINATION_TYPE := rec_dist.DESTINATION_TYPE;
v_dist_rec.DESTINATION_ORGANIZATION := rec_dist.DESTINATION_ORGANIZATION;
— v_dist_rec.CHARGE_ACCOUNT := rec_dist.CHARGE_ACCOUNT;
v_dist_rec.org_id := fnd_profile.value(‘ORG_ID’);
v_dist_rec.REQUEST_ID := g_request_id;
v_dist_rec.creation_date := sysdate;
v_dist_rec.created_by := g_user_id;
v_dist_rec.last_update_date := g_date;
v_dist_rec.last_updated_by := g_user_id;
insert into po_distributions_interface
values v_dist_rec;
end if;
end loop; — dist
if v_process_flag =’S’
then
insert into po_lines_interface
values v_line_rec;
end if;
end loop; — lines
if v_process_flag =’S’
then
insert into po_headers_interface
values v_hea_rec;
else
rollback to v_save_point;
end if;
update XX_PO_HEADERS_STG
set process_flag = v_process_flag
,error_message = v_err_msg
where rowid = rec_hea.row_id;
exception
when v_po_exists then
v_process_flag := ‘E’;
v_error_message := ‘PO is already exists ‘;
dis_log(v_error_message);
end;
end loop; — Header
exception
when others then
dis_log(‘Exception in prcedure-> ‘||sqlerrm);
end pur_ord;
end XX_PO_PUR_ORD_INT_PKG;
/
