[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = iRun_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL INTO iRun_id
FROM DUAL;
SELECT SYSDATE INTO dTransaction_date
FROM DUAL;
SELECT COUNT(*) INTO xHeaderCount
FROM ece_ar_trx_headers
WHERE run_id = iRun_id;
DELETE FROM ece_output
WHERE run_id = iRun_id;
'ECE_NO_ROW_DELETED',
'PROGRESS_LEVEL',
xProgress,
'TABLE_NAME',
'ECE_OUTPUT' );
PROCEDURE Update_AR ( Document_Type IN VARCHAR2,
Transaction_ID IN NUMBER,
Installment_Number IN NUMBER,
Multiple_Installments_Flag IN VARCHAR2,
Maximum_Installment_Number IN NUMBER,
Update_Date IN DATE )
IS
l_Update_Value VARCHAR2(20);
ec_debug.push('ECE_AR_TRANSACTION.UPDATE_AR');
ec_debug.pl ( 3, 'Update_Date: ',Update_Date );
SELECT edi_flag,
print_flag
INTO l_EDI_flag,
l_Print_flag
FROM ra_customer_trx rct,
ece_tp_details etd,
hz_cust_acct_sites rad,
hz_cust_site_uses rsu
WHERE rct.bill_to_site_use_id = rsu.site_use_id
AND rsu.cust_acct_site_id = rad.cust_acct_site_id
AND rad.tp_header_id = etd.tp_header_id
AND etd.document_type = Update_AR.Document_Type
AND rct.customer_trx_id = Update_AR.Transaction_ID;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'EDI FLAG, PRINT FLAG',
'TABLE_NAME',
'RA_CUSTOMER_TRX, ECE_TP_DETAILS, HZ_CUST_ACCT_SITES,HZ_CUST_SITE_USES' );
l_Update_Value := 'EP';
l_Update_Value := 'ED';
l_Update_Value := 'PR';
ec_debug.pl ( 3, 'L_UPDATE_VALUE: ',l_Update_Value );
UPDATE ra_customer_trx
SET last_update_date = SYSDATE,
printing_pending = DECODE (Document_Type,
'CM', 'N',
'OACM', 'N',
DECODE (Maximum_Installment_Number,
Installment_Number, 'N',
NULL, 'N',
1, 'N',
'Y')),
printing_count = NVL(printing_count,0) + 1,
printing_last_printed = SYSDATE,
printing_original_date = DECODE (NVL(printing_count,0),
0, SYSDATE,
printing_original_date ),
last_printed_sequence_num = DECODE (Multiple_Installments_Flag,
'N',NULL,
GREATEST(NVL(last_printed_sequence_num,0),
Installment_Number)),
edi_processed_flag = 'Y',
edi_processed_status = l_Update_Value
WHERE customer_trx_id = Update_AR.Transaction_ID;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'EDI PROCESSED',
'TABLE_NAME',
'RA_CUSTOMER_TRX' );
ec_debug.pop('ECE_AR_TRANSACTION.UPDATE_AR');
END Update_AR;
SELECT remit_to_address_id INTO l_Remit_To_Address_ID
FROM RA_CUSTOMER_TRX
WHERE CUSTOMER_TRX_ID = Get_Remit_Address.Customer_Trx_ID;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'REMIT TO ADDRESS ID',
'TABLE_NAME',
'RA_CUSTOMER_TRX' );
SELECT rt.address_id
FROM ra_customer_trx rct,
hz_cust_acct_sites a,
hz_party_sites hps,
hz_locations loc,
ra_remit_tos rt
WHERE rct.customer_trx_id = Get_Remit_Address.Customer_Trx_ID
AND rct.bill_to_address_id = a.cust_acct_site_id AND
a.party_site_id = hps.party_site_id AND
hps.location_id = loc.location_id
AND rt.status = 'A'
AND NVL(a.status,'A') = 'A'
AND rt.country = loc.COUNTRY
AND ( loc.state = NVL(rt.state, loc.state)
OR ( loc.state IS NULL
AND rt.state IS NULL
)
OR ( loc.state IS NULL
AND loc.postal_code <= NVL(rt.postal_code_high, loc.postal_code)
AND loc.postal_code >= NVL(rt.postal_code_low, loc.postal_code)
AND ( postal_code_low IS NOT NULL
OR postal_code_high IS NOT NULL
)
)
)
AND ( ( loc.postal_code <= NVL(rt.postal_code_high, loc.postal_code)
AND loc.postal_code >= NVL(rt.postal_code_low, loc.postal_code)
)
OR ( loc.postal_code IS NULL
AND rt.postal_code_low IS NULL
AND rt.postal_code_high IS NULL
)
)
ORDER BY rt.state,
rt.postal_code_low,
rt.postal_code_high;
SELECT MIN(address_id)
INTO l_Remit_To_Address_ID
FROM ra_remit_tos
WHERE status = 'A'
AND state = 'DEFAULT'
AND country = 'DEFAULT';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'MINIMUM ADDRESS ID',
'TABLE_NAME',
'RA_REMIT_TOS' );
SELECT loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.county,
loc.state,
loc.province,
loc.country,
loc.postal_code,
hcas.orig_system_reference,
substr(loc.address_lines_phonetic,1,50), --2291130
hcas.ece_tp_location_code --2386848
INTO Remit_To_Address1,
Remit_To_Address2,
Remit_To_Address3,
Remit_To_Address4,
Remit_To_City,
Remit_To_County,
Remit_to_state,
Remit_To_Province,
Remit_To_Country,
Remit_To_Postal_Code,
Remit_To_Code_Int,
Remit_to_customer_name, --2291130
Remit_to_edi_location_code
FROM hz_cust_acct_sites hcas,
hz_party_sites hps,
hz_locations loc
WHERE hps.party_site_id = hcas.party_site_id
AND hps.location_id = loc.location_id
AND hcas.cust_acct_site_id = l_Remit_To_Address_ID;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'RA ADDRESS',
'TABLE_NAME',
'HZ_CUST_ACCT_SITES' );
SELECT rct.term_id,
fc.precision,
rctt.accounting_affect_flag,
rctt.type,
rt.first_installment_code,
DECODE(rctt.type,
'CM', 'N',
'OACM', 'N',
DECODE(COUNT(*),
0, 'N',
1, 'N',
'Y')),
MAX(rtl.sequence_num),
MIN(rtl.sequence_num)
INTO l_Term_ID,
l_Currency_Precision,
l_Payment_Schedule_Exists,
l_Type,
l_First_Installment_Code,
Multiple_Installments_Flag,
Maximum_Installment_Number,
l_Minimum_Installment_Number
FROM ra_customer_trx rct,
ra_cust_trx_types rctt,
ra_terms_lines rtl,
ra_terms rt,
fnd_currencies fc
WHERE rct.customer_trx_id = Get_Payment.Customer_Trx_ID
AND rct.invoice_currency_code = fc.currency_code
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.term_id = rt.term_id (+)
AND rt.term_id = rtl.term_id (+)
GROUP BY rct.term_id,
fc.precision,
rctt.accounting_affect_flag,
rctt.type,
rt.first_installment_code;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'PAYMENT SCHEDULE',
'TABLE_NAME',
'RA_CUSTOMER_TRX, RA_CUST_TRX_TYPES, RA_TERMS_LINES, RA_TERMS, FND_CURRENCIES' );
SELECT NVL(MIN(rtl.relative_amount),1),
NVL(MIN(rt.base_amount),1)
INTO l_Term_Relative_Amount,
l_Term_Base_Amount
FROM ra_terms rt,
ra_terms_lines rtl
WHERE rt.term_id = l_Term_ID
AND rt.term_id = rtl.term_id
AND rtl.sequence_num = Get_Payment.Installment_Number;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'AMOUNT',
'TABLE_NAME',
'RA_TERMS, RA_TERMS_LINES' );
SELECT NVL(tax_original,0),
NVL(freight_original,0),
NVL(amount_line_items_original,0),
NVL(amount_due_original,0)
INTO Amount_Tax_Due,
Amount_Freight_Due,
Amount_Line_Items_Due,
Total_Amount_Due
FROM ar_payment_schedules
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND DECODE(l_Type,
'CM', Get_Payment.Installment_Number,
'OACM', Get_Payment.Installment_Number,
NVL(terms_sequence_number,
Get_Payment.Installment_Number)) = Get_Payment.Installment_Number;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'PAYMENT SCHEDULE',
'TABLE_NAME',
'AR_PAYMENT_SCHEDULES' );
SELECT NVL(SUM((NVL(rctl.quantity_invoiced,
rctl.quantity_credited) *
rctl.unit_selling_price) *
l_Term_Relative_Amount /
l_Term_Base_Amount),
0)
INTO Amount_Charges_Due
FROM ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id = Get_Payment.Customer_Trx_ID
AND rctl.line_type = 'CHARGES';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'CHARGE AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT ROUND(SUM(extended_amount *
l_Term_Relative_Amount /
l_Term_Base_Amount),
l_Currency_Precision)
INTO l_Amount_Line_Items_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type NOT IN ('TAX','FREIGHT','CHARGES');
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'LINE ITEM AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT ROUND(SUM(extended_amount *
l_Term_Relative_Amount /
l_Term_Base_Amount),
l_Currency_Precision)
INTO l_Amount_Charges_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type = 'CHARGES';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'CHARGE AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT SUM(extended_amount)
INTO l_Amount_Tax_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type = 'TAX';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'TAX AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT SUM(extended_amount)
INTO l_Amount_Freight_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type = 'FREIGHT';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'FREIGHT AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT ROUND(SUM(extended_amount *
l_Term_Relative_Amount /
l_Term_Base_Amount),
l_Currency_Precision)
INTO l_Amount_Tax_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type = 'TAX';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'TAX AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT ROUND(SUM(extended_amount *
l_Term_Relative_Amount /
l_Term_Base_Amount),
l_Currency_Precision)
INTO l_Amount_Freight_Due
FROM ra_customer_trx_lines
WHERE customer_trx_id = Get_Payment.Customer_Trx_ID
AND line_type = 'FREIGHT';
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'FREIGHT AMOUNT DUE',
'TABLE_NAME',
'RA_CUSTOMER_TRX_LINES' );
SELECT discount_percent,
discount_days,
nvl(discount_date,Get_Term_Discount.Invoice_date + discount_days), --Bug 2389231
discount_day_of_month,
discount_months_forward
FROM ra_terms_lines_discounts
WHERE term_id = Get_Term_Discount.Term_ID
AND sequence_num = Get_Term_Discount.Term_Sequence_Number;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'DISCOUNT',
'TABLE_NAME',
'RA_TERMS_LINES_DISCOUNTS' );
select GLB.CURRENCY_CODE Base_Currency_Code
into base_currency_code
from AR_SYSTEM_PARAMETERS ASP, GL_SETS_OF_BOOKS GLB
where ASP.SET_OF_BOOKS_ID = GLB.SET_OF_BOOKS_ID ;
cHeader_select VARCHAR2(32000);
cHeader_1_select VARCHAR2(32000);
cAlw_chg_h_select VARCHAR2(32000);
cAlw_chg_l_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cLine_t_select VARCHAR2(32000);
cHeader_delete1 VARCHAR2(32000);
cHeader_1_delete1 VARCHAR2(32000);
cAlw_chg_h_delete1 VARCHAR2(32000);
cAlw_chg_l_delete1 VARCHAR2(32000);
cLine_delete1 VARCHAR2(32000);
cLine_t_delete1 VARCHAR2(32000);
cHeader_delete2 VARCHAR2(32000);
cHeader_1_delete2 VARCHAR2(32000);
cAlw_chg_h_delete2 VARCHAR2(32000);
cAlw_chg_l_delete2 VARCHAR2(32000);
cLine_delete2 VARCHAR2(32000);
cLine_t_delete2 VARCHAR2(32000);
SELECT MIN(eel.external_level)
INTO cAlw_chg_h_output_level
FROM ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
AND eit.transaction_type = cTransaction_type
AND eit.interface_table_id = elm.interface_table_id
AND elm.external_level_id = eel.external_level_id
AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
FROM ece_mappings em1
WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'MINIMUM OUTPUT LEVEL',
'TABLE_NAME',
'ECE_INTERFACE_TABLES' );
SELECT MAX(eel.external_level)
INTO cAlw_chg_l_output_level
FROM ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
AND eit.transaction_type = cTransaction_type
AND eit.interface_table_id = elm.interface_table_id
AND elm.external_level_id = eel.external_level_id
AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
FROM ece_mappings em1
WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'MAXIMUM OUTPUT LEVEL',
'TABLE_NAME',
'ECE_INTERFACE_TABLES' );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
cHeader_X_Interface,
l_Header_tbl,
c_Header_common_key_name,
cHeader_select,
cHeader_from,
cHeader_where );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cHeader_1_Interface,
cHeader_1_X_Interface,
l_Header_1_tbl,
c_Header_1_common_key_name,
cHeader_1_select,
cHeader_1_from,
cHeader_1_where );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cAlw_chg_Interface,
cAlw_chg_X_Interface,
l_alw_chg_h_tbl,
c_Alw_chg_common_key_name,
cAlw_chg_h_select,
cAlw_chg_h_from,
cAlw_chg_h_where,
cAlw_chg_h_output_level );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cLine_Interface,
cLine_X_Interface,
l_Line_tbl,
c_Line_common_key_name,
cLine_select,
cLine_from, cLine_where );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cLine_t_Interface,
cLine_t_X_Interface,
l_Line_t_tbl,
c_Line_t_common_key_name,
cLine_t_select,
cLine_t_from,
cLine_t_where );
ece_flatfile_pvt.select_clause ( cTransaction_Type,
cCommunication_Method,
cAlw_chg_Interface,
cAlw_chg_X_Interface,
l_alw_chg_l_tbl,
c_Alw_chg_common_key_name,
cAlw_chg_l_select,
cAlw_chg_l_from,
cAlw_chg_l_where,
cAlw_chg_l_output_level );
cHeader_select := cHeader_select ||
',' ||
cHeader_Interface ||
'.ROWID, ' ||
cHeader_X_Interface ||
'.ROWID, ' ||
cHeader_Interface ||
'.TRANSACTION_ID';
ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
cHeader_1_select := cHeader_1_select ||
',' ||
cHeader_1_Interface ||
'.ROWID, ' ||
cHeader_1_X_Interface ||
'.ROWID, ' ||
cHeader_1_Interface ||
'.TRANSACTION_ID';
ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
cAlw_chg_h_select := cAlw_chg_h_select ||
',' ||
cAlw_chg_Interface ||
'.ROWID, ' ||
cAlw_chg_X_Interface ||
'.ROWID';
ec_debug.pl ( 3, 'cAlw_chg_h_select: ',cAlw_chg_h_select );
cLine_select := cLine_select ||
',' ||
cLine_Interface ||
'.ROWID,' ||
cLine_X_Interface ||
'.ROWID';
ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
cLine_t_select := cLine_t_select ||
',' ||
cLine_t_Interface ||
'.ROWID,' ||
cLine_t_X_Interface ||
'.ROWID';
ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
cAlw_chg_l_select := cAlw_chg_l_select ||
',' ||
cAlw_chg_Interface ||
'.ROWID, ' ||
cAlw_chg_X_Interface ||
'.ROWID';
ec_debug.pl ( 3, 'cAlw_chg_l_select: ',cAlw_chg_l_select );
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where ||
' ORDER BY ' || cHeader_Interface || '.BILL_TO_CUSTOMER_NAME,' || /*Bug 2464584*/
cHeader_Interface || '.BILL_TO_CUSTOMER_LOCATION ' ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
cHeader_1_select := cHeader_1_select ||
cHeader_1_from ||
cHeader_1_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
cAlw_chg_h_select := cAlw_chg_h_select ||
cAlw_chg_h_from ||
cAlw_chg_h_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cAlw_chg_h_select: ',cAlw_chg_h_select );
cLine_select := cLine_select ||
cLine_from ||
cLine_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
cLine_t_select := cLine_t_select ||
cLine_t_from ||
cLine_t_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
cAlw_chg_l_select := cAlw_chg_l_select ||
cAlw_chg_l_from ||
cAlw_chg_l_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cAlw_chg_l_select: ',cAlw_chg_l_select );
cHeader_delete1 := 'DELETE FROM ' ||
cHeader_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cHeader_delete1: ',cHeader_delete1 );
cHeader_1_delete1 := 'DELETE FROM ' ||
cHeader_1_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cHeader_1_delete1: ',cHeader_1_delete1 );
cAlw_chg_h_delete1 := 'DELETE FROM ' ||
cAlw_chg_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cAlw_chg_h_delete1: ',cAlw_chg_h_delete1 );
cLine_delete1 := 'DELETE FROM ' ||
cLine_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cLine_delete1: ',cLine_delete1 );
cLine_t_delete1 := 'DELETE FROM ' ||
cLine_t_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cLine_t_delete1: ',cLine_t_delete1 );
cAlw_chg_l_delete1 := 'DELETE FROM ' ||
cAlw_chg_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cAlw_chg_l_delete1: ',cAlw_chg_l_delete1 );
cHeader_delete2 := 'DELETE FROM ' ||
cHeader_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cHeader_delete2: ',cHeader_delete2 );
cHeader_1_delete2 := 'DELETE FROM ' ||
cHeader_1_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cHeader_1_delete2: ',cHeader_1_delete2 );
cAlw_chg_h_delete2 := 'DELETE FROM ' ||
cAlw_chg_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cAlw_chg_h_delete2: ',cAlw_chg_h_delete2 );
cLine_delete2 := 'DELETE FROM ' ||
cLine_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cLine_delete2: ',cLine_delete2 );
cLine_t_delete2 := 'DELETE FROM ' ||
cLine_t_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cLine_t_delete2: ',cLine_t_delete2 );
cAlw_chg_l_delete2 := 'DELETE FROM ' ||
cAlw_chg_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl ( 3, 'cAlw_chg_l_delete2: ',cAlw_chg_l_delete2 );
*** Open a cursor for each of the SELECT call
***/
xProgress := '2212-60';
*** Parse each of the SELECT and DELETE statement
***/
xProgress := '2230-60';
cHeader_select,
dbms_sql.native );
cHeader_select);
cHeader_1_select,
dbms_sql.native );
cHeader_1_select);
cAlw_chg_h_select,
dbms_sql.native );
cAlw_chg_h_select );
cLine_select,
dbms_sql.native );
cLine_select );
cLine_t_select,
dbms_sql.native );
cLine_t_select );
cAlw_chg_l_select,
dbms_sql.native );
cAlw_chg_l_select );
cHeader_delete1,
dbms_sql.native );
cHeader_delete1 );
cHeader_1_delete1,
dbms_sql.native );
cHeader_1_delete1 );
cAlw_chg_h_delete1,
dbms_sql.native );
cAlw_chg_h_delete1 );
cLine_delete1,
dbms_sql.native );
cLine_delete1 );
cLine_t_delete1,
dbms_sql.native );
cLine_t_delete1 );
cAlw_chg_l_delete1,
dbms_sql.native );
cAlw_chg_l_delete1 );
cHeader_delete2,
dbms_sql.native );
cHeader_delete2 );
cHeader_1_delete2,
dbms_sql.native );
cHeader_1_delete2 );
cAlw_chg_h_delete2,
dbms_sql.native );
cAlw_chg_h_delete2 );
cLine_delete2,
dbms_sql.native );
cLine_delete2 );
cLine_t_delete2,
dbms_sql.native );
cLine_t_delete2 );
cAlw_chg_l_delete2,
dbms_sql.native );
cAlw_chg_l_delete2 );
*** Define TYPE for every columns in the SELECT statement
***/
xProgress := '2270-60';
cHeader_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Header Level)
***/
xProgress := '2280-60';
cHeader_1_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Header 1 Level)
***/
xProgress := '2292-60';
cAlw_chg_h_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Allowance Charges Header Level)
***/
xProgress := '2310-60';
cLine_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Line Level)
***/
xProgress := '2330-60';
cLine_t_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Line Level)
***/
xProgress := '2350-60';
cAlw_chg_l_select,
ece_flatfile_pvt.G_MaxColWidth );
*** Need rowid for delete (Allowance Charges Detail Level)
***/
xProgress := '2370-60';
/*** To complete the SELECT statement,
*** we will need values for the join condition.
***/
xProgress := '2380-60';
*** place holders (foreign keys) in Header_detail_Select,
*** Line_select and Line_detail_Select
*** ------------------------------------------------------------***/
/*** -- set values into binding variables
***/
xProgress := '2452-60';
**** -- Use rowid for delete
****/
xProgress := '2580-60';
**** -- Use rowid for delete
****/
xProgress := '2830-60';
**** -- Use rowid for delete
****/
xProgress := '2840-60';
/*** -- this commit is to make sure all data is deleted from interface tables
***/
xProgress := '2900-60';
cHeader_select VARCHAR2( 32000);
cHeader_1_select VARCHAR2( 32000);
cAlw_chg_select VARCHAR2( 32000);
cLine_select VARCHAR2( 32000);
cLine_t_select VARCHAR2( 32000);
l_last_update_date DATE;
SELECT
RTRIM(WTP.VEHICLE_NUM_PREFIX, '0123456789') EQUIPMENT_PREFIX ,
SUBSTR(WTP.VEHICLE_NUMBER, NVL(LENGTH(RTRIM(WTP.VEHICLE_NUMBER, '0123456789')), 0)+1) EQUIPMENT_NUMBER,
SUBSTR(WTP.ROUTING_INSTRUCTIONS, 1, 150) ROUTING_INSTRUCTIONS,
WDI.SEQUENCE_NUMBER PACKING_SLIP_NUMBER
FROM
WSH_DELIVERY_DETAILS
WDD ,
WSH_DELIVERY_LEGS
WDL,
WSH_TRIP_STOPS
WTS,
WSH_TRIPS
WTP,
WSH_DOCUMENT_INSTANCES
WDI,
RA_CUSTOMER_TRX_LINES
RCTL
WHERE
TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE6) =
WDD.SOURCE_LINE_ID AND
TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE3) =
WDL.DELIVERY_ID AND
WDL.PICK_UP_STOP_ID = WTS.STOP_ID AND
WTS.TRIP_ID = WTP.TRIP_ID AND
NVL(TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE3),0) =
WDI.ENTITY_ID AND
RCTL.customer_trx_id = tx_id
AND RCTL.INTERFACE_LINE_CONTEXT =
fnd_profile.value('ONT_SOURCE_CODE')
AND ROWNUM = 1;
SELECT MIN(eel.external_level)
INTO l_alw_chg_output_level
FROM ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
WHERE eit.interface_table_name = 'ECE_AR_TRX_ALLOWANCE_CHARGES'
AND eit.transaction_type = cTransaction_type
AND eit.interface_table_id = elm.interface_table_id
AND elm.external_level_id = eel.external_level_id
AND eel.map_id = (SELECT NVL(cMap_id, MAX(em1.map_id))
FROM ece_mappings em1
WHERE em1.map_code like 'EC_'||RTRIM(LTRIM(NVL(cTransaction_type,'%')))||'_FF');
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'MINIMUM OUTPUT LEVEL',
'TABLE_NAME',
'ECE_INTERFACE_TABLES' );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
cHeader_select,
cHeader_from,
cHeader_where );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cHeader_1_Interface,
l_header_1_tbl,
cHeader_1_select,
cHeader_1_from,
cHeader_1_where );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cAlw_chg_Interface,
l_alw_chg_tbl,
cAlw_chg_select,
cAlw_chg_from,
cAlw_chg_where );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
cLine_select,
cLine_from ,
cLine_where );
ece_extract_utils_pub.select_clause ( cTransaction_Type,
cCommunication_Method,
cLine_t_Interface,
l_line_t_tbl,
cLine_t_select,
cLine_t_from ,
cLine_t_where );
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where ||
' ORDER BY BILL_TO_CUSTOMER_NAME,BILL_TO_CUSTOMER_LOCATION'; /* Bug 2464584 */
cHeader_1_select := cHeader_1_select ||
cHeader_1_from ||
cHeader_1_where;
cAlw_chg_select := cAlw_chg_select ||
cAlw_chg_from ||
cAlw_chg_where;
cLine_select := cLine_select ||
cLine_from ||
cLine_where;
cLine_t_select := cLine_t_select ||
cLine_t_from ||
cLine_t_where;
ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
ec_debug.pl ( 3, 'cHeader_1_select: ',cHeader_1_select );
ec_debug.pl ( 3, 'cAlw_chg_select: ',cAlw_chg_select );
ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
ec_debug.pl ( 3, 'cLine_t_select: ',cLine_t_select );
cHeader_select,
dbms_sql.native );
cHeader_select );
cHeader_1_select,
dbms_sql.native );
cHeader_1_select );
cAlw_chg_select,
dbms_sql.native );
cAlw_chg_select );
cLine_select,
dbms_sql.native );
cLine_select );
cLine_t_select,
dbms_sql.native );
cLine_t_select );
cHeader_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_header_tbl );
cHeader_1_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_header_1_tbl );
cAlw_chg_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_alw_chg_tbl );
cLine_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_line_tbl );
cLine_t_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_line_t_tbl );
select bill_to_contact_id,ship_to_contact_id,sold_to_contact_id
into l_bill_to_contact_id,l_ship_to_contact_id,l_sold_to_contact_id
from ra_customer_trx
where customer_trx_id=l_header_tbl(nPos1).value;
SELECT
substrb(PARTY.PERSON_LAST_NAME,1,50),
substrb(PARTY.PERSON_FIRST_NAME,1,40),
ORG_CONT.JOB_TITLE
into l_bill_to_contact_last_name,l_bill_to_contact_first_name,l_bill_to_contact_job_title
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT
WHERE
ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_bill_to_contact_id;
/* select last_name,first_name,job_title
into l_bill_to_contact_last_name,l_bill_to_contact_first_name,l_bill_to_contact_job_title
from ra_contacts
where contact_id=l_bill_to_contact_id; */
SELECT
substrb(PARTY.PERSON_LAST_NAME,1,50),
substrb(PARTY.PERSON_FIRST_NAME,1,40),
ORG_CONT.JOB_TITLE
into l_ship_to_contact_last_name,l_ship_to_contact_first_name,l_ship_to_contact_job_title
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_PARTIES REL_PARTY
WHERE
ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_ship_to_contact_id;
select last_name,first_name,job_title
into l_ship_to_contact_last_name,l_ship_to_contact_first_name,l_ship_to_contact_job_title
from ra_contacts
where contact_id=l_ship_to_contact_id; */
SELECT
substrb(PARTY.PERSON_LAST_NAME,1,50),
substrb(PARTY.PERSON_FIRST_NAME,1,40),
ORG_CONT.JOB_TITLE
into l_sold_to_contact_last_name,l_sold_to_contact_first_name,l_sold_to_contact_job_title
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_PARTIES REL_PARTY
WHERE
ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_sold_to_contact_id;
/* select last_name,first_name,job_title
into l_sold_to_contact_last_name,l_sold_to_contact_first_name,l_sold_to_contact_job_title
from ra_contacts
where contact_id=l_sold_to_contact_id; */
ece_ar_transaction.Update_AR ( l_header_tbl(nPos3).value,
l_header_tbl(nPos1).value,
l_header_tbl(nPos2).value,
l_Multiple_Installments_Flag,
l_Maximum_Installment_Number,
l_last_update_date );
SELECT ece_ar_trx_headers_s.nextval
INTO l_header_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
l_header_fkey );
UPDATE ece_ar_trx_headers
SET maximum_installment_number = l_Maximum_Installment_Number,
amount_tax_due = l_Amount_Tax_Due,
amount_charges_due = l_Amount_Charges_Due,
amount_freight_due = l_Amount_Freight_Due,
amount_line_items_due = l_Amount_Line_Items_Due,
total_amount_due = l_total_amount_due,
Discount_Percent1 = l_Discount_Percent1,
Discount_Days1 = l_Discount_Days1,
Discount_Date1 = l_Discount_Date1,
Discount_Day_Of_Month1 = l_Discount_Day_Of_Month1,
Discount_Months_Forward1 = l_Discount_Months_Forward1,
Discount_Percent2 = l_Discount_Percent2,
Discount_Days2 = l_Discount_Days2,
Discount_Date2 = l_Discount_Date2,
Discount_Day_Of_Month2 = l_Discount_Day_Of_Month2,
Discount_Months_Forward2 = l_Discount_Months_Forward2,
Discount_Percent3 = l_Discount_Percent3,
Discount_Days3 = l_Discount_Days3,
Discount_Date3 = l_Discount_Date3,
Discount_Day_Of_Month3 = l_Discount_Day_Of_Month3,
Discount_Months_Forward3 = l_Discount_Months_Forward3,
remit_to_code_ext = l_remit_to_edi_location_code, --2386848
remit_to_code_int = l_remit_to_code_int,
/* ship_to_customer_code_ext = l_ship_to_customer_code_ext, --2386848
sold_to_customer_code_ext = l_sold_to_customer_code_ext, */
bill_to_customer_code_ext = l_header_tbl(nPos7).value,
bill_to_tp_reference_ext1 = l_bill_to_tp_reference_ext1,
bill_to_tp_reference_ext2 = l_bill_to_tp_reference_ext2,
/* ship_to_tp_reference_ext1 = l_ship_to_tp_reference_ext1, --2386848
ship_to_tp_reference_ext2 = l_ship_to_tp_reference_ext2,
sold_to_tp_reference_ext1 = l_sold_to_tp_reference_ext1,
sold_to_tp_reference_ext2 = l_sold_to_tp_reference_ext2,
remit_to_tp_reference_ext1 = l_remit_to_tp_reference_ext1,
remit_to_tp_reference_ext2 = l_remit_to_tp_reference_ext2, */
tp_document_purpose_code = 'OR',
remit_to_customer_name = l_remit_to_customer_name, --2291130
bill_to_contact_last_name = l_bill_to_contact_last_name,
bill_to_contact_first_name = l_bill_to_contact_first_name,
bill_to_contact_job_title = l_bill_to_contact_job_title,
ship_to_contact_last_name = l_ship_to_contact_last_name,
ship_to_contact_first_name = l_ship_to_contact_first_name,
ship_to_contact_job_title = l_ship_to_contact_job_title,
sold_to_contact_last_name = l_sold_to_contact_last_name,
sold_to_contact_first_name = l_sold_to_contact_first_name,
sold_to_contact_job_title = l_sold_to_contact_job_title
WHERE transaction_record_id = l_header_fkey;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'AMOUNT, DISCOUNT AND LOCATIONS',
'TABLE_NAME',
'ECE_AR_TRX_HEADERS' );
SELECT delivery_id
INTO l_delivery_id
FROM wsh_deliveries
WHERE name = l_delivery_name;
SELECT ece_ar_trx_header_1_s.nextval
INTO l_header_1_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cHeader_1_Interface,
l_header_1_tbl,
l_header_1_fkey );
UPDATE ECE_AR_TRX_HEADERS
SET ship_from_code_int = l_header_1_tbl(nPos5).value,
ship_from_code_ext1 = l_header_1_tbl(nPos5).ext_val1,
ship_from_code_ext2 = l_header_1_tbl(nPos5).ext_val2,
ship_from_code_ext3 = l_header_1_tbl(nPos5).ext_val3,
ship_from_code_ext4 = l_header_1_tbl(nPos5).ext_val4,
ship_from_code_ext5 = l_header_1_tbl(nPos5).ext_val5
WHERE transaction_record_id = l_header_fkey;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'SHIP FROM CODE',
'TABLE_NAME',
'ECE_AR_TRX_HEADERS' );
SELECT ece_ar_trx_allowance_charges_s.nextval
INTO l_alw_chg_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cAlw_chg_Interface,
l_alw_chg_tbl,
l_alw_chg_fkey );
SELECT ece_ar_trx_lines_s.nextval
INTO l_line_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
l_line_fkey );
UPDATE ece_ar_trx_lines
SET line_item_number = l_line_item_number,
line_item_attrib_category = l_line_item_attrib_category,
line_item_attribute1 = l_line_item_attribute1,
line_item_attribute2 = l_line_item_attribute2,
line_item_attribute3 = l_line_item_attribute3,
line_item_attribute4 = l_line_item_attribute4,
line_item_attribute5 = l_line_item_attribute5,
line_item_attribute6 = l_line_item_attribute6,
line_item_attribute7 = l_line_item_attribute7,
line_item_attribute8 = l_line_item_attribute8,
line_item_attribute9 = l_line_item_attribute9,
line_item_attribute10 = l_line_item_attribute10,
line_item_attribute11 = l_line_item_attribute11,
line_item_attribute12 = l_line_item_attribute12,
line_item_attribute13 = l_line_item_attribute13,
line_item_attribute14 = l_line_item_attribute14,
line_item_attribute15 = l_line_item_attribute15
WHERE transaction_record_id = l_line_fkey;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'LINE ITEM',
'TABLE_NAME',
'ECE_AR_TRX_LINES' );
SELECT ece_ar_trx_line_tax_s.nextval
INTO l_line_t_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cLine_t_Interface,
l_line_t_tbl,
l_line_t_fkey );
UPDATE ece_ar_trx_headers
SET net_weight = l_net_weight,
gross_weight = l_gross_weight,
volume = l_volume,
weight_uom_code_int = l_weight_uom_code,
volume_uom_code_int = l_volume_uom_code,
booking_number = l_booking_number
WHERE transaction_record_id = l_header_fkey;
select weight_uom_code_ext1,
weight_uom_code_ext2,
weight_uom_code_ext3,
weight_uom_code_ext4,
weight_uom_code_ext5
into
l_weight_uom_code_ext1,
l_weight_uom_code_ext2,
l_weight_uom_code_ext3,
l_weight_uom_code_ext4,
l_weight_uom_code_ext5
from
ece_ar_trx_lines
where weight_uom_code_int = l_weight_uom_code
and rownum < 2;
select volume_uom_code_ext1,
volume_uom_code_ext2,
volume_uom_code_ext3,
volume_uom_code_ext4,
volume_uom_code_ext5
into
l_volume_uom_code_ext1,
l_volume_uom_code_ext2,
l_volume_uom_code_ext3,
l_volume_uom_code_ext4,
l_volume_uom_code_ext5
from
ece_ar_trx_lines
where volume_uom_code_int = l_volume_uom_code
and rownum < 2;
update ece_ar_trx_headers
set
volume_uom_code_ext1 = l_volume_uom_code_ext1,
volume_uom_code_ext2 = l_volume_uom_code_ext2,
volume_uom_code_ext3 = l_volume_uom_code_ext3,
volume_uom_code_ext4 = l_volume_uom_code_ext4,
volume_uom_code_ext5 = l_volume_uom_code_ext5,
weight_uom_code_ext1 = l_weight_uom_code_ext1,
weight_uom_code_ext2 = l_weight_uom_code_ext2,
weight_uom_code_ext3 = l_weight_uom_code_ext3,
weight_uom_code_ext4 = l_weight_uom_code_ext4,
weight_uom_code_ext5 = l_weight_uom_code_ext5
where
transaction_record_id = l_header_fkey;