PO Temporary Tables:
CREATE TABLE xxcus.CB9_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),
DOC_NUM NUMBER(5),
process_flag varchar2(1),
error_message varchar2(1000)
);
create table xxcus.cb9_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)
);
create table xxcus.cb9_po_dist_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
);
create synonym xx_PO_HEADERS_STG for xxcus.xx_PO_HEADERS_STG;
create synonym xx_po_lines_stg for xxcus.xx_po_lines_stg;
create synonym xx_po_dist_stg for xxcus.xx_po_dist_stg;
Headers Control File:
LOAD DATA
INFILE *
REPLACE INTO TABLE xx_PO_HEADERS_STG
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INTERFACE_SOURCE_CODE
,PROCESS_CODE
,ACTION
,GROUP_CODE
,DOCUMENT_TYPE_CODE
,DOCUMENT_NUM
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,VENDOR_CONTACT
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,PAYMENT_TERMS
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
,COMMENTS "replace(:COMMENTS,chr(13),'')"
)
Lines Control File:
LOAD DATA
INFILE *
REPLACE INTO TABLE xx_po_lines_stg
fields terminated by ','
trailing nullcols
(
ACTION
,GROUP_CODE
,LINE_NUM
,SHIPMENT_NUM
,SHIPMENT_TYPE
,DOCUMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_REVISION
,CATEGORY
,ITEM_DESCRIPTION
,VENDOR_PRODUCT_NUM
,UNIT_OF_MEASURE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_LOCATION
,NEED_BY_DATE "replace(:NEED_BY_DATE,chr(13),'')"
,SHIP_TO_ORGANIZATION_CODE "replace(:SHIP_TO_ORGANIZATION_CODE,chr(13),'')"
)
Distribution Control File:
LOAD DATA
INFILE *
REPLACE INTO TABLE xx_po_dist_stg
fields terminated by ','
trailing nullcols
(
DISTRIBUTION_NUM
,QUANTITY_ORDERED
,QUANTITY_DELIVERED
,LINE_NUM
,SHIPMENT_NUM
,DOCUMENT_NUM
,DESTINATION_ORGANIZATION
,CHARGE_ACCOUNT
,destination_type "replace(:destination_type,chr(13),'')"
)
SQL*Loader Script:
sqlldr apps/apps control='$XX_TOP/bin/CB9_PO_HEADERS_LOAD.ctl' data='$XX_TOP/inbound/CB9_PO_HEADERS_DATA.csv' log='$XX_TOP/inbound/CB9_PO_HEADERS_DATA.log' bad='$XX_TOP/inbound/CB9_PO_HEADERS_DATA.bad'
sqlldr apps/apps control='$XX_TOP/bin/CB9_PO_LINES_LOAD.ctl' data='$XX_TOP/inbound/CB9_PO_LINES_DATA.csv' log='$XX_TOP/inbound/CB9_PO_LINES_DATA.log' bad='$XX_TOP/inbound/CB9_PO_LINES_DATA.bad'
sqlldr apps/apps control='$XX_TOP/bin/CB9_PO_DIST_LOAD.ctl' data='$XX_TOP/inbound/CB9_PO_DISTRIBUTIONS_DATA.csv' log='$XX_TOP/inbound/CB9_PO_DISTRIBUTIONS_DATA.log' bad='$XX_TOP/inbound/CB9_PO_DISTRIBUTIONS_DATA.bad'
exit 0
PO PKG Specification:
create or replace package xx_po_pur_ord_pkg
/*
#*************************************************************************************************************************************
#* - XXX Corporation -
#*
#*************************************************************************************************************************************
#*************************************************************************************************************************************
#* Project : XX PRoject
#* Application : XX Custom Application
#* Title : XX_po_pur_ord_pkg.pkh
#* Program Name : XX PO Purchase order interface
#* Description Purpose: This program used to validate data and insert into interface tables.
#* $Revision : 1.0
#* Utility : Sql file
#* Created by : Ram
#* Creation Date : 09-Jan-2012
#* Called By : NA
#* Parameters :
#* p_errbuf Out parameter
#* p_retcode Out Parameter
#* Dependency : NA
#* Frequency : 1
#* Related documents : MD 70_Purchase Order Interface
#* Tables/views accessed:
#*
#* Table Name SELECT Insert Update Delete
#* --------------------- ------ ------ ------ ------
#* X
#*
#* Change History :
#*====================================================================================================================================
#* Date |Name |Ticket |Remarks
#*====================================================================================================================================
#* 09-Jan-2012 |Ram | |Initial Version
#* 09-May-2012 |Ram | |Made cahnages on Buyer validation
#*
#*************************************************************************************************************************************
*/
is
procedure main(x_errbuf out varchar2,x_retcode out varchar2);
end xx_po_pur_ord_pkg;
PO Pkg Body:
create or replace package body xx_po_pur_ord_pkg
/*
#*************************************************************************************************************************************
#* - XXX Corporation -
#*
#*************************************************************************************************************************************
#*************************************************************************************************************************************
#* Project : XX PRoject
#* Application : XX Custom Application
#* Title : XX_po_pur_ord_pkg.pkh
#* Program Name : XX PO Purchase order interface
#* Description Purpose: This program used to validate data and insert into interface tables.
#* $Revision : 1.0
#* Utility : Sql file
#* Created by : Ram
#* Creation Date : 09-Jan-2012
#* Called By : NA
#* Parameters :
#* p_errbuf Out parameter
#* p_retcode Out Parameter
#* Dependency : NA
#* Frequency : 1
#* Related documents : MD 70_Purchase Order Interface
#* Tables/views accessed:
#*
#* Table Name SELECT Insert Update Delete
#* --------------------- ------ ------ ------ ------
#* X
#*
#* Change History :
#*====================================================================================================================================
#* Date |Name |Ticket |Remarks
#*====================================================================================================================================
#* 09-Jan-2012 |Ram | |Initial Version
#* 09-May-2012 |Ram | |Made cahnages on Buyer validation
#*
#*************************************************************************************************************************************
*/
is
g_date date := sysdate;
g_user_id number := fnd_profile.value('USER_ID');
g_org_id number := FND_PROFILE.value('ORG_ID');
Procedure dis_output
is
cursor cur_sus
is
select *
from xx_PO_HEADERS_STG
where process_flag = 'S';
cursor cur_err
is
select *
from xx_PO_HEADERS_STG
where process_flag = 'E';
v_sus_count number;
v_err_count number;
v_tot_count number;
begin
select count(1)
into v_err_count
from xx_PO_HEADERS_STG
where process_flag = 'E';
select count(1)
into v_sus_count
from xx_PO_HEADERS_STG
where process_flag = 'S';
v_tot_count := v_sus_count+v_err_count;
fnd_file.put_line(fnd_file.output,'Total Records : '||v_tot_count);
fnd_file.put_line(fnd_file.output,'Total Success Records : '||v_sus_count);
fnd_file.put_line(fnd_file.output,'Total Error Records : '||v_err_count);
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'Success records');
fnd_file.put_line(fnd_file.output,'---------------');
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'Vendor Name PO Number');
fnd_file.put_line(fnd_file.output,'----------- ---------');
for rec_sus in cur_sus
loop
fnd_file.put_line(fnd_file.output,rpad(rec_sus.vendor_name,40)||rec_sus.document_num);
end loop;
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'Error records');
fnd_file.put_line(fnd_file.output,'-------------');
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'Vendor Name PO Number Error message');
fnd_file.put_line(fnd_file.output,'----------- --------- -------------');
for rec_err in cur_err
loop
fnd_file.put_line(fnd_file.output,rpad(rec_err.vendor_name,40)||rpad(rec_err.document_num,11)||rec_err.Error_message);
end loop;
end;
procedure main(x_errbuf out varchar2,x_retcode out varchar2)
is
cursor cur_hea
is
select *
from xx_PO_HEADERS_STG
where nvl(process_flag,'X') <> 'S'
and exists
(
select 1
from hr_operating_units a,
po_lookup_codes b
where organization_id = fnd_profile.value('ORG_ID')
and a.name = b.displayed_field
and b.lookup_type = 'PO OU LIMITATION'
-- and name = 'Vision Operations'
);
cursor cur_line(p_po_number in varchar2)
is
select * from xx_po_lines_stg
where document_num= p_po_number;
cursor cur_dist(
p_po_number in varchar2
,p_line_num in number
,p_shipment_num in number
)
is
select * from
xx_po_dist_stg
where document_num= p_po_number
and line_num = p_line_num
and shipment_num= p_shipment_num;
v_hea_rec po_headers_interface%rowtype;
v_lin_rec po_lines_interface%rowtype;
v_dis_rec po_distributions_interface%rowtype;
v_process_flag varchar2(1) := 'S';
v_error_message varchar2(1000);
v_err_msg varchar2(1000);
begin
fnd_file.put_line(fnd_file.log,'After begin');
for rec_hea in cur_hea
loop -- Header
fnd_file.put_line(fnd_file.log,'Header loop start');
/*
select lookup_code
into v_hea_rec.DOCUMENT_TYPE_CODE
from po_lookup_codes
where lookup_type='PO TYPE'
and description = decode(rec_hea.DOCUMENT_TYPE_CODE,'Standard PO','Standard Purchase Order');
*/
begin
select a.lookup_code
--into v_hea_rec.DOCUMENT_TYPE_CODE
from po_lookup_codes a,
po_lookup_codes b
where a.lookup_type='PO TYPE'
and b.lookup_type = 'PO TYPE MAPPING'
and a.description = b.description
and b.displayed_field = rec_hea.DOCUMENT_TYPE_CODE;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message := 'Invalid po type -> '||rec_hea.DOCUMENT_TYPE_CODE;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid po type -> '||rec_hea.DOCUMENT_TYPE_CODE||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Validate vendor
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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid vendor -> '||rec_hea.VENDOR_NAME||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Vendor Site
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 org_id = fnd_profile.value('ORG_ID')
and vendor_site_code = rec_hea.VENDOR_SITE_CODE;
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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid vendor site -> '||rec_hea.VENDOR_SITE_CODE||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Validate 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 full_name = rec_hea.AGENT_NAME
and sysdate between effective_start_date and effective_end_date;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message := 'Invalid Agent -> '||rec_hea.AGENT_NAME;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid Agent -> '||rec_hea.AGENT_NAME||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
v_hea_rec.document_num := rec_hea.document_num;
v_hea_rec.INTERFACE_SOURCE_CODE := rec_hea.INTERFACE_SOURCE_CODE;
v_hea_rec.ACTION := rec_hea.ACTION;
v_hea_rec.PROCESS_CODE := rec_hea.PROCESS_CODE;
-- v_hea_rec.DOCUMENT_TYPE_CODE := rec_hea.DOCUMENT_TYPE_CODE;
-- 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 := g_org_id;
v_hea_rec.creation_date := g_date;
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;
fnd_file.put_line(fnd_file.log,'Initlize header values');
select PO_HEADERS_INTERFACE_S.nextval
into v_hea_rec.INTERFACE_HEADER_ID
from dual;
for rec_line in cur_line(rec_hea.document_num)
loop
fnd_file.put_line(fnd_file.log,'lines loop');
-- Validate line type
begin
select line_type_id
into v_lin_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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid line type -> '||rec_line.LINE_TYPE||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Validate ship to org
begin
select organization_id
into v_lin_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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid organization -> '||rec_line.SHIP_TO_ORGANIZATION_CODE||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- validate item
begin
select inventory_item_id
,description
,primary_uom_code
into v_lin_rec.item_id
,v_lin_rec.ITEM_DESCRIPTION
,v_lin_rec.uom_code
from mtl_system_items_b
where organization_id = v_lin_rec.ship_to_organization_id
and segment1 = rec_line.ITEM;
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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid item -> '||rec_line.ITEM||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Validate category
begin
select category_id
into v_lin_rec.category_id
from mtl_categories_kfv
where CONCATENATED_SEGMENTS = rec_line.CATEGORY;
exception
when no_data_found then
v_process_flag := 'E';
v_error_message := 'Invalid category -> '||rec_line.ITEM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid category -> '||rec_line.ITEM||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
if rec_line.UNIT_PRICE is null
or rec_line.quantity is null
or rec_line.NEED_BY_DATE is null
then
v_process_flag := 'E';
v_error_message := 'Qty or price od need by date is null';
v_err_msg := v_err_msg||' '||v_error_message;
end if;
v_lin_rec.LINE_NUM := rec_line.LINE_NUM;
v_lin_rec.SHIPMENT_NUM := rec_line.SHIPMENT_NUM;
-- v_lin_rec.LINE_TYPE := rec_line.LINE_TYPE;
-- v_lin_rec.ITEM := rec_line.ITEM;
-- v_lin_rec.ITEM_DESCRIPTION := rec_line.ITEM_DESCRIPTION;
-- v_lin_rec.CATEGORY := rec_line.CATEGORY;
-- v_lin_rec.UNIT_OF_MEASURE := rec_line.UNIT_OF_MEASURE;
v_lin_rec.UNIT_PRICE := rec_line.UNIT_PRICE;
v_lin_rec.QUANTITY := rec_line.QUANTITY;
-- v_lin_rec.SHIP_TO_ORGANIZATION_CODE := rec_line.SHIP_TO_ORGANIZATION_CODE;
v_lin_rec.SHIP_TO_LOCATION := rec_line.SHIP_TO_LOCATION;
v_lin_rec.NEED_BY_DATE := rec_line.NEED_BY_DATE ;
v_lin_rec.INTERFACE_HEADER_ID := v_hea_rec.INTERFACE_HEADER_ID;
-- v_lin_rec.ORG_ID := g_org_id;
fnd_file.put_line(fnd_file.log,'initialize lines ');
select PO_LINES_INTERFACE_S.nextval
into v_lin_rec.INTERFACE_LINE_ID
from dual;
for rec_dist in cur_dist(p_po_number => rec_hea.document_num
,p_line_num => rec_line.line_num
,p_shipment_num => rec_line.shipment_num
)
loop
begin
select lookup_code
into v_dis_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 Destination -> '||rec_dist.DESTINATION_TYPE;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid Destination -> '||rec_dist.DESTINATION_TYPE||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
-- Validate charge account
begin
select code_combination_id
into v_dis_rec.charge_account_id
from gl_code_combinations_kfv a,
gl_Sets_of_books b
where concatenated_segments = rec_dist.CHARGE_ACCOUNT
and a.chart_of_accounts_id = b.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;
fnd_file.put_line(fnd_file.log,v_error_message);
when others then
v_process_flag := 'E';
v_error_message := 'Invalid charge account -> '||rec_dist.CHARGE_ACCOUNT||''||SQLERRM;
v_err_msg := v_err_msg||' '||v_error_message;
fnd_file.put_line(fnd_file.log,v_error_message);
end;
fnd_file.put_line(fnd_file.log,'Dist loop ');
v_dis_rec.DISTRIBUTION_NUM := rec_dist.DISTRIBUTION_NUM;
v_dis_rec.QUANTITY_ORDERED := rec_dist.QUANTITY_ORDERED;
v_dis_rec.QUANTITY_DELIVERED := rec_dist.QUANTITY_DELIVERED;
-- v_dis_rec.DESTINATION_TYPE := rec_dist.DESTINATION_TYPE;
-- v_dis_rec.CHARGE_ACCOUNT := rec_dist.CHARGE_ACCOUNT;
v_dis_rec.INTERFACE_HEADER_ID := v_hea_rec.INTERFACE_HEADER_ID;
v_dis_rec.INTERFACE_LINE_ID := v_lin_rec.INTERFACE_LINE_ID;
v_dis_rec.ORG_ID := g_org_id;
fnd_file.put_line(fnd_file.log,'initialize Dist');
select PO_DISTRIBUTIONS_S.nextval
into v_dis_rec.INTERFACE_DISTRIBUTION_ID
from dual;
if v_process_flag = 'S'
then
insert into po_distributions_interface
values v_dis_rec;
end if;
end loop; -- Dist
if v_process_flag = 'S'
then
insert into po_lines_interface
values v_lin_rec;
end if;
end loop; -- Lines
if v_process_flag = 'S'
then
insert into po_headers_interface
values v_hea_rec;
end if;
update CB9_PO_HEADERS_STG
set process_flag = v_process_flag
,error_message = v_err_msg
where document_num = rec_hea.document_num;
end loop; -- Header
fnd_file.put_line(fnd_file.log,'End loop');
commit;
dis_output;
exception
when others then
fnd_file.put_line(fnd_file.log,'Exception in main procedure -> '||SQLERRM);
end main;
end xx_po_pur_ord_pkg;
No comments:
Post a Comment