[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row(
P_ORGANIZATION_ID IN NUMBER,
P_ORGANIZATION_CODE IN JAI_RCV_JOURNAL_ENTRIES.organization_code%TYPE,
P_RECEIPT_NUM IN JAI_RCV_JOURNAL_ENTRIES.receipt_num%TYPE,
P_TRANSACTION_ID IN JAI_RCV_JOURNAL_ENTRIES.transaction_id%TYPE,
P_TRANSACTION_DATE IN JAI_RCV_JOURNAL_ENTRIES.transaction_date%TYPE,
P_SHIPMENT_LINE_ID IN JAI_RCV_JOURNAL_ENTRIES.shipment_line_id%TYPE,
P_ACCT_TYPE IN JAI_RCV_JOURNAL_ENTRIES.acct_type%TYPE,
P_ACCT_NATURE IN JAI_RCV_JOURNAL_ENTRIES.acct_nature%TYPE,
P_SOURCE_NAME IN JAI_RCV_JOURNAL_ENTRIES.source_name%TYPE,
P_CATEGORY_NAME IN JAI_RCV_JOURNAL_ENTRIES.category_name%TYPE,
P_CODE_COMBINATION_ID IN JAI_RCV_JOURNAL_ENTRIES.code_combination_id%TYPE,
P_ENTERED_DR IN JAI_RCV_JOURNAL_ENTRIES.entered_dr%TYPE,
P_ENTERED_CR IN JAI_RCV_JOURNAL_ENTRIES.entered_cr%TYPE,
P_TRANSACTION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.transaction_type%TYPE,
P_PERIOD_NAME IN JAI_RCV_JOURNAL_ENTRIES.period_name%TYPE,
P_CURRENCY_CODE IN JAI_RCV_JOURNAL_ENTRIES.currency_code%TYPE,
P_CURRENCY_CONVERSION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_type%TYPE,
P_CURRENCY_CONVERSION_DATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_date%TYPE,
P_CURRENCY_CONVERSION_RATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_rate%TYPE,
P_SIMULATE_FLAG IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2,
/* two parameters added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
p_reference_name in varchar2 ,
p_reference_id in number
) IS
ld_creation_date DATE;
ln_last_update_login JAI_RCV_JOURNAL_ENTRIES.LAST_UPDATE_LOGIN%TYPE ;
SELECT gd.period_name
FROM gl_ledgers gle, gl_periods gd
WHERE gle.ledger_id = cp_set_of_books_id
AND gd.period_set_name = gle.period_set_name
AND cp_transaction_date BETWEEN gd.start_date and gd.end_date
AND gd.adjustment_period_flag = 'N';
Table Handler coded for JAI_RCV_JOURNAL_ENTRIES table. Update_row of the package was just a skeleton that needs to be modified
whenever it is being used
2 10/11/2004 Vijay Shankar for Bug#4003518, Version:115.1
Modified the INSERT_ROW definition to DEFAULT 'N' for p_simulate_flag parameter. without this, its not a
problem in Oracle8i, however it is problem in 9i and thus the bugfix
3 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.2
added two more parameters in insert_row procedure as part of VAT Implementation
4 28/11/2005 Hjujjuru for the bug 4762433 File version 120.3
added the who columns in the insert of jai_rcv_journals.
Dependencies Due to this bug:-
None
Dependencies
IN60106 + 4245089
----------------------------------------------------------------------------------------------------------------------------*/
ld_creation_date := SYSDATE;
ln_last_update_login := fnd_global.login_id; -- added, Harshita for Bug 4762433
INSERT INTO JAI_RCV_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
ORGANIZATION_CODE,
RECEIPT_NUM,
TRANSACTION_ID,
CREATION_DATE,
TRANSACTION_DATE,
SHIPMENT_LINE_ID,
ACCT_TYPE,
ACCT_NATURE,
SOURCE_NAME,
CATEGORY_NAME,
CODE_COMBINATION_ID,
ENTERED_DR,
ENTERED_CR,
TRANSACTION_TYPE,
PERIOD_NAME,
CREATED_BY,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
-- DUMMY_FLAG,
/* following two parameters added by Vijay Shankar for Bug#4250236(JOURNAL_ENTRY_ID,4245089). VAT Implementation */
reference_name,
reference_id,
-- following 3 parameters added by Harshita for Bug 4762433
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (JAI_RCV_JOURNAL_ENTRIES_S.nextval,
lv_organization_code, -- P_ORGANIZATION_CODE,
P_RECEIPT_NUM,
P_TRANSACTION_ID,
ld_creation_date,
P_TRANSACTION_DATE,
P_SHIPMENT_LINE_ID,
P_ACCT_TYPE,
P_ACCT_NATURE,
P_SOURCE_NAME,
P_CATEGORY_NAME,
P_CODE_COMBINATION_ID,
P_ENTERED_DR,
P_ENTERED_CR,
P_TRANSACTION_TYPE,
lv_period_name,
ln_created_by,
P_CURRENCY_CODE,
P_CURRENCY_CONVERSION_TYPE,
P_CURRENCY_CONVERSION_DATE,
P_CURRENCY_CONVERSION_RATE,
-- p_simulate_flag,
p_reference_name,
p_reference_id,
ln_created_by, -- Harshita for Bug 4762433
ld_creation_date , -- Harshita for Bug 4762433
ln_last_update_login -- Harshita for Bug 4762433
);
p_process_message := 'RCV_JOURNALS_PKG.insert_row->'||SQLERRM||', StmtId->'||lv_statement_id;
END insert_row;
PROCEDURE update_row(
P_ORGANIZATION_CODE IN JAI_RCV_JOURNAL_ENTRIES.organization_code%TYPE DEFAULT NULL,
P_RECEIPT_NUM IN JAI_RCV_JOURNAL_ENTRIES.receipt_num%TYPE DEFAULT NULL,
P_TRANSACTION_ID IN JAI_RCV_JOURNAL_ENTRIES.transaction_id%TYPE DEFAULT NULL,
P_CREATION_DATE IN JAI_RCV_JOURNAL_ENTRIES.creation_date%TYPE DEFAULT NULL,
P_TRANSACTION_DATE IN JAI_RCV_JOURNAL_ENTRIES.transaction_date%TYPE DEFAULT NULL,
P_SHIPMENT_LINE_ID IN JAI_RCV_JOURNAL_ENTRIES.shipment_line_id%TYPE DEFAULT NULL,
P_ACCT_TYPE IN JAI_RCV_JOURNAL_ENTRIES.acct_type%TYPE DEFAULT NULL,
P_ACCT_NATURE IN JAI_RCV_JOURNAL_ENTRIES.acct_nature%TYPE DEFAULT NULL,
P_SOURCE_NAME IN JAI_RCV_JOURNAL_ENTRIES.source_name%TYPE DEFAULT NULL,
P_CATEGORY_NAME IN JAI_RCV_JOURNAL_ENTRIES.category_name%TYPE DEFAULT NULL,
P_CODE_COMBINATION_ID IN JAI_RCV_JOURNAL_ENTRIES.code_combination_id%TYPE DEFAULT NULL,
P_ENTERED_DR IN JAI_RCV_JOURNAL_ENTRIES.entered_dr%TYPE DEFAULT NULL,
P_ENTERED_CR IN JAI_RCV_JOURNAL_ENTRIES.entered_cr%TYPE DEFAULT NULL,
P_TRANSACTION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.transaction_type%TYPE DEFAULT NULL,
P_PERIOD_NAME IN JAI_RCV_JOURNAL_ENTRIES.period_name%TYPE DEFAULT NULL,
P_CREATED_BY IN JAI_RCV_JOURNAL_ENTRIES.created_by%TYPE DEFAULT NULL,
P_CURRENCY_CODE IN JAI_RCV_JOURNAL_ENTRIES.currency_code%TYPE DEFAULT NULL,
P_CURRENCY_CONVERSION_TYPE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_type%TYPE DEFAULT NULL,
P_CURRENCY_CONVERSION_DATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_date%TYPE DEFAULT NULL,
P_CURRENCY_CONVERSION_RATE IN JAI_RCV_JOURNAL_ENTRIES.currency_conversion_rate%TYPE DEFAULT NULL
) IS
BEGIN
UPDATE JAI_RCV_JOURNAL_ENTRIES SET
ORGANIZATION_CODE = nvl(P_ORGANIZATION_CODE, ORGANIZATION_CODE),
RECEIPT_NUM = nvl(P_RECEIPT_NUM, RECEIPT_NUM),
TRANSACTION_ID = nvl(P_TRANSACTION_ID, TRANSACTION_ID),
CREATION_DATE = nvl(P_CREATION_DATE, CREATION_DATE),
TRANSACTION_DATE = nvl(P_TRANSACTION_DATE, TRANSACTION_DATE),
SHIPMENT_LINE_ID = nvl(P_SHIPMENT_LINE_ID, SHIPMENT_LINE_ID),
ACCT_TYPE = nvl(P_ACCT_TYPE, ACCT_TYPE),
ACCT_NATURE = nvl(P_ACCT_NATURE, ACCT_NATURE),
SOURCE_NAME = nvl(P_SOURCE_NAME, SOURCE_NAME),
CATEGORY_NAME = nvl(P_CATEGORY_NAME, CATEGORY_NAME),
CODE_COMBINATION_ID = nvl(P_CODE_COMBINATION_ID, CODE_COMBINATION_ID),
ENTERED_DR = nvl(P_ENTERED_DR, ENTERED_DR),
ENTERED_CR = nvl(P_ENTERED_CR, ENTERED_CR),
TRANSACTION_TYPE = nvl(P_TRANSACTION_TYPE, TRANSACTION_TYPE),
PERIOD_NAME = nvl(P_PERIOD_NAME, PERIOD_NAME),
CREATED_BY = nvl(P_CREATED_BY, CREATED_BY),
CURRENCY_CODE = nvl(P_CURRENCY_CODE, CURRENCY_CODE),
CURRENCY_CONVERSION_TYPE = nvl(P_CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_TYPE),
CURRENCY_CONVERSION_DATE = nvl(P_CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_DATE),
CURRENCY_CONVERSION_RATE = nvl(P_CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_RATE)
WHERE transaction_id = p_transaction_id;
END update_row;
v_last_update_login number;
v_last_update_date date;
v_last_updated_by number;
SELECT actual_flag,
je_source_name,
je_category_name,
period_name,
chart_of_accounts_id,
functional_currency_code,
je_batch_name,
je_batch_description,
je_header_name,
je_line_description,
reference1,
reference2,
reference3,
reference4,
source_doc_quantity
FROM rcv_receiving_sub_ledger
WHERE rcv_transaction_id = p_transaction_id
AND rownum = 1;
SELECT source_document_code,
shipment_line_id,
po_line_location_id,
requisition_line_id
FROM rcv_transactions
WHERE transaction_id = p_transaction_id;
SELECT item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = v_rcv_rec.shipment_line_id;
For price_rec IN (SELECT price_override
FROM po_line_locations_all
WHERE line_location_id = v_rcv_rec.po_line_location_id)
LOOP
v_unit_price := price_rec.price_override;
For price_rec IN (SELECT list_price_per_unit price
FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND organization_id = p_organization_id)
LOOP
v_unit_price := price_rec.price;
For price_rec IN (SELECT unit_price
FROM po_requisition_lines_all
WHERE requisition_line_id = v_rcv_rec.requisition_line_id)
LOOP
v_unit_price := price_rec.unit_price;
INSERT into JAI_RCV_SUBLED_ENTRIES
(SUBLED_ENTRY_ID,rcv_transaction_id,
set_of_books_id,
je_source_name,
je_category_name,
accounting_date,
currency_code,
date_created_in_gl,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
actual_flag,
period_name,
chart_of_accounts_id,
functional_currency_code,
je_batch_name,
je_batch_description,
je_header_name,
je_line_description,
reference1,
reference2,
reference3,
reference4,
source_doc_quantity,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
from_type,
PROGRAM_LOGIN_ID)
VALUES ( JAI_RCV_SUBLED_ENTRIES_S.nextval, p_transaction_id,
v_set_of_books_id,
v_sub_rec.je_source_name,
v_sub_rec.je_category_name,
v_accounting_date,
p_currency_code,
v_sysdate,
p_credit_amount,
p_debit_amount,
v_accounting_date,
p_cc_id,
p_currency_conversion_date,
p_currency_conversion_type,
p_currency_conversion_rate,
v_sub_rec.actual_flag,
v_sub_rec.period_name,
v_sub_rec.chart_of_accounts_id,
v_sub_rec.functional_currency_code,
v_sub_rec.je_batch_name,
v_sub_rec.je_batch_description,
v_sub_rec.je_header_name,
v_sub_rec.je_line_description,
v_sub_rec.reference1,
v_sub_rec.reference2,
v_sub_rec.reference3,
v_sub_rec.reference4,
v_amount,
p_created_by,
v_sysdate,
v_sysdate,
p_created_by,
p_created_by,
'L',
fnd_profile.value('PROG_APPL_ID'));