[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 ece_cdmo_header_v eih,
ece_tp_details etd,
hz_cust_acct_sites cas
WHERE eih.bill_to_address_id = cas.cust_acct_site_id
AND cas.tp_header_id = etd.tp_header_id
AND etd.document_type = Update_AR.Document_Type
AND eih.transaction_id = Update_AR.Transaction_ID;
SELECT etd.edi_flag,etd.print_flag
INTO l_EDI_flag,
l_Print_flag
FROM
ra_customer_trx rct,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
ece_tp_headers eth,
ece_tp_details etd
WHERE
rct.bill_to_site_use_id = csu.site_use_id and
csu.cust_acct_site_id = cas.cust_acct_site_id and
cas.tp_header_id = eth.tp_header_id and
eth.tp_header_id = etd.tp_header_id and
rct.CUSTOMER_TRX_ID = update_ar.transaction_id and
etd.document_type = update_ar.document_type;
'ECE_NO_ROW_SELECTED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'EDI FLAG, PRINT FLAG',
'TABLE_NAME',
'ECE_CDMO_HEADER_V, ECE_TP_DETAILS, HZ_CUST_ACCT_SITES' );
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;
SELECT RT.ADDRESS_ID
FROM RA_CUSTOMER_TRX RCT,HZ_CUST_ACCT_SITES A,RA_REMIT_TOS RT,
HZ_PARTY_SITES HPS,HZ_LOCATIONS LOC
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';
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
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
FROM HZ_CUST_ACCT_SITES HCAS,
HZ_LOCATIONS LOC,
HZ_PARTY_SITES HPS
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;
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;
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;
SELECT NVL(TAX_ORIGINAL,0),
NVL(FREIGHT_ORIGINAL,0),
NVL(AMOUNT_LINE_ITEMS_ORIGINAL,0),
NVL(AMOUNT_DUE_ORIGINAL,0),
NVL(AMOUNT_DUE_REMAINING,0)
INTO amount_tax_due, amount_freight_due,
amount_line_items_due, total_amount_due,
total_amount_remaining
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;
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';
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');
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';
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';
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';
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';
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';
CURSOR discount IS SELECT DISCOUNT_PERCENT,
DISCOUNT_DAYS,
DISCOUNT_DATE,
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;
PROCEDURE UPDATE_HEADER_WITH_LINE (
p_customer_trx_id IN NUMBER) IS
nPos1 pls_integer;
select TO_CHAR(gross_weight),
TO_CHAR(net_weight),
weight_uom_code_int,
TO_CHAR(volume),
volume_uom_code_int,
TO_CHAR(shipment_number) ,
booking_number,
bill_of_lading_number
into l_gross_weight,
l_net_weight,
l_weight_uom_code,
l_volume,
l_volume_uom_code,
l_shipment_number,
l_booking_number,
l_bill_of_lading
from ece_cdmo_line_v
where transaction_id = p_customer_trx_id
and sales_order_number is not null
and rownum=1;
END UPDATE_HEADER_WITH_LINE;