[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| --- main procedure for insert_gl_line --- |
| |
*======================================================================*/
Procedure otagls (p_user_id in number,
p_login_id in number) IS
--
--
--
--
-- Local Variables
--
l_cost_center_error EXCEPTION;
select
ofh.FINANCE_HEADER_ID,
ofh.LAST_UPDATE_DATE,
ofh.LAST_UPDATED_BY,
ofh.CREATION_DATE,
ofh.CREATED_BY,
ofh.TRANSFER_DATE,
ofh.OBJECT_VERSION_NUMBER,
ofh.PAYMENT_STATUS_FLAG,
ofh.TRANSFER_STATUS,
ofh.TYPE,
ofh.COMMENTS,
ofh.EXTERNAL_REFERENCE,
ofh.INVOICE_ADDRESS,
ofh.INVOICE_CONTACT,
ofh.PAYMENT_METHOD,
ofh.PYM_ATTRIBUTE1,
ofh.PYM_ATTRIBUTE10,
ofh.PYM_ATTRIBUTE11,
ofh.PYM_ATTRIBUTE12,
ofh.PYM_ATTRIBUTE13,
ofh.PYM_ATTRIBUTE14,
ofh.PYM_ATTRIBUTE15,
ofh.PYM_ATTRIBUTE16,
ofh.PYM_ATTRIBUTE17,
ofh.PYM_ATTRIBUTE18,
ofh.PYM_ATTRIBUTE19,
ofh.PYM_ATTRIBUTE2,
ofh.PYM_ATTRIBUTE20,
ofh.PYM_ATTRIBUTE3,
ofh.PYM_ATTRIBUTE4,
ofh.PYM_ATTRIBUTE5,
ofh.PYM_ATTRIBUTE6,
ofh.PYM_ATTRIBUTE7,
ofh.PYM_ATTRIBUTE8,
ofh.PYM_ATTRIBUTE9,
ofh.PYM_INFORMATION_CATEGORY,
ofh.RECEIVABLE_TYPE,
ofh.TRANSFER_MESSAGE,
ofh.VENDOR_ID,
ofh.CONTACT_ID,
ofh.TFH_INFORMATION_CATEGORY,
ofh.TFH_INFORMATION1,
ofh.TFH_INFORMATION2,
ofh.TFH_INFORMATION3,
ofh.TFH_INFORMATION4,
ofh.TFH_INFORMATION5,
ofh.TFH_INFORMATION6,
ofh.TFH_INFORMATION7,
ofh.TFH_INFORMATION8,
ofh.TFH_INFORMATION9,
ofh.TFH_INFORMATION10,
ofh.TFH_INFORMATION11,
ofh.TFH_INFORMATION12,
ofh.TFH_INFORMATION13,
ofh.TFH_INFORMATION14,
ofh.TFH_INFORMATION15,
ofh.TFH_INFORMATION16,
ofh.TFH_INFORMATION17,
ofh.TFH_INFORMATION18,
ofh.TFH_INFORMATION19,
ofh.TFH_INFORMATION20,
ofh.PAYING_COST_CENTER,
ofh.RECEIVING_COST_CENTER,
ofh.CURRENCY_CODE,
ofh.TRANSFER_FROM_SET_OF_BOOKS_ID,
ofh.TRANSFER_TO_SET_OF_BOOKS_ID,
ofh.FROM_SEGMENT1,
ofh.FROM_SEGMENT2,
ofh.FROM_SEGMENT3,
ofh.FROM_SEGMENT4,
ofh.FROM_SEGMENT5,
ofh.FROM_SEGMENT6,
ofh.FROM_SEGMENT7,
ofh.FROM_SEGMENT8,
ofh.FROM_SEGMENT9,
ofh.FROM_SEGMENT10,
ofh.FROM_SEGMENT11,
ofh.FROM_SEGMENT12,
ofh.FROM_SEGMENT13,
ofh.FROM_SEGMENT14,
ofh.FROM_SEGMENT15,
ofh.FROM_SEGMENT16,
ofh.FROM_SEGMENT17,
ofh.FROM_SEGMENT18,
ofh.FROM_SEGMENT19,
ofh.FROM_SEGMENT20,
ofh.FROM_SEGMENT21,
ofh.FROM_SEGMENT22,
ofh.FROM_SEGMENT23,
ofh.FROM_SEGMENT24,
ofh.FROM_SEGMENT25,
ofh.FROM_SEGMENT26,
ofh.FROM_SEGMENT27,
ofh.FROM_SEGMENT28,
ofh.FROM_SEGMENT29,
ofh.FROM_SEGMENT30,
ofh.TO_SEGMENT1,
ofh.TO_SEGMENT2,
ofh.TO_SEGMENT3,
ofh.TO_SEGMENT4,
ofh.TO_SEGMENT5,
ofh.TO_SEGMENT6,
ofh.TO_SEGMENT7,
ofh.TO_SEGMENT8,
ofh.TO_SEGMENT9,
ofh.TO_SEGMENT10,
ofh.TO_SEGMENT11,
ofh.TO_SEGMENT12,
ofh.TO_SEGMENT13,
ofh.TO_SEGMENT14,
ofh.TO_SEGMENT15,
ofh.TO_SEGMENT16,
ofh.TO_SEGMENT17,
ofh.TO_SEGMENT18,
ofh.TO_SEGMENT19,
ofh.TO_SEGMENT20,
ofh.TO_SEGMENT21,
ofh.TO_SEGMENT22,
ofh.TO_SEGMENT23,
ofh.TO_SEGMENT24,
ofh.TO_SEGMENT25,
ofh.TO_SEGMENT26,
ofh.TO_SEGMENT27,
ofh.TO_SEGMENT28,
ofh.TO_SEGMENT29,
ofh.TO_SEGMENT30,
ofh.TRANSFER_FROM_CC_ID,
ofh.TRANSFER_TO_CC_ID
FROM ota_finance_headers ofh
WHERE ofh.TYPE = 'CT'
AND ofh.TRANSFER_STATUS = 'AT'
AND ofh.CANCELLED_FLAG = 'N'
ORDER BY
ofh.finance_header_id,
ofh.paying_cost_center,
ofh.receiving_cost_center,
ofh.currency_code ;
SELECT sum(fl.money_amount)
FROM ota_finance_lines fl,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_events evt,
ota_category_usages ocu,
ota_offerings off
WHERE fl.finance_header_id = l_finance_header_id and
tdb.booking_id = fl.booking_id and
bst.booking_status_type_id = tdb.booking_status_type_id and
evt.event_id = tdb.event_id and
evt.price_basis <> 'N' and
evt.parent_offering_id = off.offering_id and
off.delivery_mode_id = ocu.category_usage_id and
(((ocu.synchronous_flag = 'Y' or (ocu.synchronous_flag = 'N' and
ocu.online_flag = 'N' )) and
bst.type in ('A','C')) or
( (ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is not null and
off.learning_object_id in (
select pfr.learning_object_id from ota_performances pfr
where
pfr.user_id= tdb.delegate_person_id and
pfr.user_type = 'E' and
pfr.lesson_status <> 'N') ) and
bst.type in ('A','C','P','E')) or
((ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is null and tdb.content_player_status is not null)
and bst.type in ('A','C','P','E')) )
and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'N' and
tdb.business_group_id = l_business_group_id;
SELECT null
FROM ota_finance_lines fl,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_events evt
WHERE fl.finance_header_id = l_finance_header_id and
tdb.booking_id = fl.booking_id and
bst.booking_status_type_id = tdb.booking_status_type_id and
evt.event_id = tdb.event_id and
evt.price_basis <> 'N' and
((evt.offering_id is null and
bst.type not in ('A','C')) or
(evt.offering_id is not null and
bst.type not in ('A','C','P','E')))
and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'Y' and
tdb.business_group_id = l_business_group_id; */
SELECT null
FROM ota_finance_lines fl,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_events evt,
ota_category_usages ocu,
ota_offerings off
WHERE fl.finance_header_id = l_finance_header_id and
tdb.booking_id = fl.booking_id and
bst.booking_status_type_id = tdb.booking_status_type_id and
evt.event_id = tdb.event_id and
evt.parent_offering_id = off.offering_id and
off.delivery_mode_id = ocu.category_usage_id and
evt.price_basis <> 'N' and
(((ocu.synchronous_flag = 'Y' or (ocu.synchronous_flag = 'N' and
ocu.online_flag = 'N' )) and
bst.type not in ('A','C')) or
( (ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is not null and
((off.learning_object_id in (
select pfr.learning_object_id from ota_performances pfr
where
pfr.user_id= tdb.delegate_person_id and
pfr.user_type = 'E' and
pfr.lesson_status = 'N')) or
( off.learning_object_id not in(
select pfr.learning_object_id from ota_performances pfr
where
pfr.user_id= tdb.delegate_person_id and
pfr.user_type = 'E')))) and
bst.type in ('A','C','P','W','R','E')) or
((ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is null and tdb.content_player_status is null)
and bst.type in ('A','C','P','W','R','E')) ) and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'N' and
tdb.business_group_id = l_business_group_id;
SELECT Finance_header_id
FROM ota_finance_headers
WHERE Finance_header_id = l_finance_header_id
FOR UPDATE;
SELECT sum(fl.money_amount)
FROM ota_finance_lines fl,
ota_resource_bookings trb,
ota_suppliable_resources tsr
WHERE fl.finance_header_id = l_finance_header_id and
trb.resource_booking_id = fl.resource_booking_id and
trb.required_date_to < (trunc(SYSDATE)+1) and
tsr.supplied_resource_id = trb.supplied_resource_id and
trb.status = 'C' and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'N' and
tsr.business_group_id = l_business_group_id;
SELECT null
FROM ota_finance_lines fl
WHERE fl.finance_header_id = l_finance_header_id and
fl.resource_booking_id is not null and
fl.cancelled_flag = 'N';
SELECT count(booking_id),
count(resource_booking_id)
FROM ota_finance_lines
WHERE finance_header_id=p_finance_header_id and
cancelled_flag = 'N'
GROUP BY finance_header_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into GL interface table for Paying cost center:' || ','
||ota_fh_row.paying_cost_center);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into GL interface table for Recieving cost center,'
||ota_fh_row.receiving_cost_center);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into GL interface table for Paying cost center:' || ','
||ota_fh_row.paying_cost_center);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into GL interface table for Recieving cost center,'
||ota_fh_row.receiving_cost_center);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting in Insert to GL interface ');
INSERT INTO gl_interface
(STATUS
,LEDGER_ID -- Bug#6763652
,SET_OF_BOOKS_ID
,ACCOUNTING_DATE
,CURRENCY_CODE
,DATE_CREATED
,CREATED_BY
,ACTUAL_FLAG
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME
,CURRENCY_CONVERSION_DATE
,ENCUMBRANCE_TYPE_ID
,BUDGET_VERSION_ID
,USER_CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,SEGMENT21
,SEGMENT22
,SEGMENT23
,SEGMENT24
,SEGMENT25
,SEGMENT26
,SEGMENT27
,SEGMENT28
,SEGMENT29
,SEGMENT30
,ENTERED_DR
,ENTERED_CR
,ACCOUNTED_DR
,ACCOUNTED_CR
,TRANSACTION_DATE
,REFERENCE1
,REFERENCE2
,REFERENCE3
,REFERENCE4
,REFERENCE5
,REFERENCE6
,REFERENCE7
,REFERENCE8
,REFERENCE9
,REFERENCE10
,REFERENCE11
,REFERENCE12
,REFERENCE13
,REFERENCE14
,REFERENCE15
,REFERENCE16
,REFERENCE17
,REFERENCE18
,REFERENCE19
,REFERENCE20
,REFERENCE21
,REFERENCE22
,REFERENCE23
,REFERENCE24
,REFERENCE25
,REFERENCE26
,REFERENCE27
,REFERENCE28
,REFERENCE29
,REFERENCE30
,JE_BATCH_ID
,PERIOD_NAME
,JE_HEADER_ID
,JE_LINE_NUM
,CHART_OF_ACCOUNTS_ID
,FUNCTIONAL_CURRENCY_CODE
,CODE_COMBINATION_ID
,DATE_CREATED_IN_GL
,WARNING_CODE
,STATUS_DESCRIPTION
,STAT_AMOUNT
,GROUP_ID
,REQUEST_ID
,SUBLEDGER_DOC_SEQUENCE_ID
,SUBLEDGER_DOC_SEQUENCE_VALUE
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,CONTEXT
,CONTEXT2
,INVOICE_DATE
,TAX_CODE
,INVOICE_IDENTIFIER
,INVOICE_AMOUNT
,CONTEXT3
,USSGL_TRANSACTION_CODE
,DESCR_FLEX_ERROR_MESSAGE
)
VALUES
(
'NEW', -- STATUS --required
p_set_of_books_id, -- LEDGER_ID - new column added in R12 Bug#6763652
p_set_of_books_id, -- SET_OF_BOOKS_ID --required
SYSDATE, -- ACCOUNTING_DATE --required
p_currency_code, -- CURRENCY_CODE --required
SYSDATE, -- DATE_CREATED --required
v_login_id, -- CREATED_BY --required
'A', -- ACTUAL_FLAG --required
'Transfer', -- USER_JE_CATEGORY_NAME --required
'Transfer', -- USER_JE_SOURCE_NAME --required
NULL, -- CURRENCY_CONVERSION_DATE
NULL, -- ENCUMBRANCE_TYPE_ID
NULL, -- BUDGET_VERSION_ID
NULL, -- USER_CURRENCY_CONVERSION_TYPE
NULL, -- CURRENCY_CONVERSION_RATE
NULL, -- SEGMENT1
NULL, -- SEGMENT2
NULL, -- SEGMENT3
NULL, -- SEGMENT4
NULL, -- SEGMENT5
NULL, -- SEGMENT6
NULL, -- SEGMENT7
NULL, -- SEGMENT8
NULL, -- SEGMENT9
NULL, -- SEGMENT10
NULL, -- SEGMENT11
NULL, -- SEGMENT12
NULL, -- SEGMENT13
NULL, -- SEGMENT14
NULL, -- SEGMENT15
NULL, -- SEGMENT16
NULL, -- SEGMENT17
NULL, -- SEGMENT18
NULL, -- SEGMENT19
NULL, -- SEGMENT20
NULL, -- SEGMENT21
NULL, -- SEGMENT22
NULL, -- SEGMENT23
NULL, -- SEGMENT24
NULL, -- SEGMENT25
NULL, -- SEGMENT26
NULL, -- SEGMENT27
NULL, -- SEGMENT28
NULL, -- SEGMENT29
NULL, -- SEGMENT30
p_debited_amount, -- ENTERED_DR
p_credited_amount, -- ENTERED_CR
NULL, -- ACCOUNTED_DR
NULL, -- ACCOUNTED_CR
NULL, -- TRANSACTION_DATE-required NULL by JI
'OTA_GL_BATCH', -- REFERENCE1-batch name ** JTH Previous Value NULL
'Cross Charge Transfer to GL', -- REFERENCE2-batch desc
NULL, -- required NULL by JI
'Cost Transfer', -- REFERENCE4-JE name ** JTH Previous Value NULL
NULL, -- REFERENCE5-JE desc
NULL, -- REFERENCE6-JE ref
NULL, -- REFERENCE7-JE Reversal period
NULL, -- REFERENCE8-JE line desc
NULL, -- required NULL by JI
p_desc, --REFERENCE10-JE line desc
NULL, -- REFERENCE11-required NULL by JI
NULL, -- REFERENCE12-required NULL by JI
NULL, -- REFERENCE13-required NULL by JI
NULL, -- REFERENCE14-required NULL by JI
NULL, -- REFERENCE15-required NULL by JI
NULL, -- REFERENCE16-required NULL by JI
NULL, -- REFERENCE17-required NULL by JI
NULL, -- REFERENCE18-required NULL by JI
NULL, -- REFERENCE19-required NULL by JI
NULL, -- REFERENCE20-required NULL by JI
NULL, -- REFERENCE21
NULL, -- REFERENCE22
NULL, -- REFERENCE23
NULL, -- REFERENCE24
NULL, -- REFERENCE25
NULL, -- REFERENCE26
NULL, -- REFERENCE27
NULL, -- REFERENCE28
NULL, -- REFERENCE29
to_char(p_finance_header_id), -- REFERENCE30
NULL, -- JE_BATCH_ID-required NULL by JI
NULL, -- PERIOD_NAME-enter value only if ACTUAL_FLAG = 'B' (Budget Data)
NULL, -- JE_HEADER_ID-required NULL by JI
NULL, -- JE_LINE_NUM-required NULL by JI
NULL, -- CHART_OF_ACCOUNTS_ID-required NULL by JI
NULL, -- FUNCTIONAL_CURRENCY_CODE-required NULL by JI
p_cc_id, -- CODE_COMBINATION_ID
NULL, -- DATE_CREATED_IN_GL-required NULL by JI
NULL, -- WARNING_CODE-required NULL by JI
NULL, -- STATUS_DESCRIPTION-required NULL by JI
NULL, -- STAT_AMOUNT
NULL, -- GROUP_ID
NULL, -- REQUEST_ID-required NULL by JI
NULL, -- SUBLEDGER_DOC_SEQUENCE_ID-required NULL by JI
NULL, -- SUBLEDGER_DOC_SEQUENCE_VALUE-required NULL by JI
NULL, -- ATTRIBUTE1
NULL, -- ATTRIBUTE2
NULL, -- ATTRIBUTE3
NULL, -- ATTRIBUTE4
NULL, -- ATTRIBUTE5
NULL, -- ATTRIBUTE6
NULL, -- ATTRIBUTE7
NULL, -- ATTRIBUTE8
NULL, -- ATTRIBUTE9
NULL, -- ATTRIBUTE10
NULL, -- ATTRIBUTE11
NULL, -- ATTRIBUTE12
NULL, -- ATTRIBUTE13
NULL, -- ATTRIBUTE14
NULL, -- ATTRIBUTE15
NULL, -- ATTRIBUTE16
NULL, -- ATTRIBUTE17
NULL, -- ATTRIBUTE18
NULL, -- ATTRIBUTE19
NULL, -- ATTRIBUTE20
NULL, -- CONTEXT
NULL, -- CONTEXT2
NULL, -- INVOICE_DATE
NULL, -- TAX_CODE
NULL, -- INVOICE_IDENTIFIER
NULL, -- INVOICE_AMOUNT
NULL, -- CONTEXT3
NULL, -- USSGL_TRANSACTION_CODE
NULL -- DESCR_FLEX_ERROR_MESSAGE-required NULL by JI
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in Inserting into GL interface '||' '||
to_char(p_finance_header_id)||','||l_err_msg);
,p_Transaction_type => 'UPDATE'); */
UPDATE ota_finance_headers
SET last_update_date = SYSDATE
,last_updated_by = v_user_id
,last_update_login = v_login_id
,transfer_status = 'ST'
,external_reference = 'OTA_GL_BATCH'
,transfer_date = SYSDATE
WHERE finance_header_id = p_finance_header_id;
SELECT finance_line_id,
object_version_number,
date_raised,
sequence_number
FROM ota_finance_lines
WHERE finance_header_id = p_finance_header_id and
booking_id in (Select Booking_id
from OTA_DELEGATE_BOOKINGS
WHERE booking_status_type_id in
(Select Booking_status_type_id
FROM OTA_BOOKING_STATUS_TYPES
WHERE Type = 'A')); */
SELECT fl.finance_line_id,
fl.object_version_number,
fl.date_raised,
fl.sequence_number
FROM ota_finance_lines fl,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_events evt,
ota_category_usages ocu,
ota_offerings off
WHERE fl.finance_header_id = p_finance_header_id and
tdb.booking_id = fl.booking_id and
bst.booking_status_type_id = tdb.booking_status_type_id and
evt.event_id = tdb.event_id and
evt.price_basis <> 'N' and
evt.parent_offering_id = off.offering_id and
off.delivery_mode_id = ocu.category_usage_id and
(((ocu.synchronous_flag = 'Y' or (ocu.synchronous_flag = 'N' and
ocu.online_flag = 'N' )) and
bst.type in ('A','C')) or
( (ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is not null and
off.learning_object_id in (
select pfr.learning_object_id from ota_performances pfr
where
pfr.user_id= tdb.delegate_person_id and
pfr.user_type = 'E' and
pfr.lesson_status <> 'N') ) and
bst.type in ('A','C','P','E')) or
((ocu.synchronous_flag = 'N' and ocu.online_flag = 'Y' and
off.learning_object_id is null and tdb.content_player_status is not null)
and bst.type in ('A','C','P','E')) )
and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'N'
FOR UPDATE OF fl.finance_line_id;
SELECT fl.finance_line_id,
fl.object_version_number,
fl.date_raised,
fl.sequence_number
FROM ota_finance_lines fl,
ota_resource_bookings trb
WHERE fl.finance_header_id = p_finance_header_id and
trb.resource_booking_id = fl.resource_booking_id and
trb.required_date_to < (trunc(SYSDATE)+1) and
trb.status = 'C' and
fl.transfer_status = 'AT' and
fl.cancelled_flag = 'N'
FOR UPDATE OF fl.finance_line_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting into Update Finance Line ');
p_transaction_type => 'UPDATE'); */
UPDATE ota_finance_lines
SET last_update_date = SYSDATE
,last_updated_by = v_user_id
,last_update_login = v_login_id
,transfer_status = 'ST'
,transfer_date = SYSDATE
WHERE finance_line_id = fl_rec.finance_line_id;
UPDATE ota_finance_lines
SET last_update_date = SYSDATE
,last_updated_by = v_user_id
,last_update_login = v_login_id
,transfer_status = 'ST'
,transfer_date = SYSDATE
WHERE finance_line_id = fl_rec.finance_line_id;