[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT GL_PER.period_name
INTO x_gl_information.period_name
FROM gl_period_statuses GL_PER,
gl_period_statuses PO_PER
WHERE PO_PER.application_id = G_PO_APPLICATION_ID
AND PO_PER.set_of_books_id = p_set_of_books_id
AND trunc(PO_PER.start_date) <= trunc(p_event_date)
AND trunc(PO_PER.end_date) >= trunc(p_event_date)
AND PO_PER.closing_status = 'O'
AND PO_PER.period_name = GL_PER.period_name
AND GL_PER.set_of_books_id = p_set_of_books_id
AND GL_PER.application_id = G_GL_APPLICATION_ID
AND trunc(GL_PER.start_date) <= trunc(p_event_date)
AND trunc(GL_PER.end_date) >= trunc(p_event_date)
AND GL_PER.closing_status in ('O', 'F')
AND GL_PER.adjustment_period_flag <> 'Y';
SELECT period_name
INTO x_gl_information.period_name
FROM gl_period_statuses
WHERE application_id = G_PO_APPLICATION_ID
AND set_of_books_id = p_set_of_books_id
AND trunc(start_date) <= trunc(p_event_date)
AND trunc(end_date) >= trunc(p_event_date)
AND closing_status = 'O'
AND adjustment_period_flag <> 'Y';
SELECT p_set_of_books_id,
nvl(chart_of_accounts_id, 0),
currency_code
INTO x_gl_information.set_of_books_id,
x_gl_information.chart_of_accounts_id,
x_gl_information.currency_code
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = p_set_of_books_id;
SELECT user_je_category_name
INTO x_gl_information.user_je_category_name
FROM GL_JE_CATEGORIES
WHERE je_category_name = 'Receiving';
SELECT user_je_source_name
INTO x_gl_information.user_je_source_name
FROM GL_JE_SOURCES
WHERE je_source_name = 'Purchasing';
PROCEDURE Insert_SubLedgerLines(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_ae_line IN RCV_AE_REC_TYPE,
p_glinfo IN RCV_AE_GLINFO_REC_TYPE
) IS
L_USER_CURR_CONV_TYPE VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'Insert_SubLedgerLines';
INSERT_RRSL_ERROR EXCEPTION;
SELECT user_conversion_type
INTO L_USER_CURR_CONV_TYPE
FROM RCV_ACCOUNTING_EVENTS RAE,
gl_daily_conversion_types GLCT
WHERE RAE.CURRENCY_CONVERSION_TYPE = GLCT.conversion_type
AND RAE.ACCOUNTING_EVENT_ID = p_rcv_ae_line.accounting_event_id;
SELECT LEGAL_ENTITY
INTO l_legal_entity
FROM CST_ACCT_INFO_V
WHERE ORGANIZATION_ID = p_rcv_ae_line.organization_id;
INSERT INTO RCV_RECEIVING_SUB_LEDGER (
RCV_SUB_LEDGER_ID,
ACCOUNTING_EVENT_ID,
ACCOUNTING_LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RCV_TRANSACTION_ID,
ACTUAL_FLAG,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
ACCOUNTING_DATE,
CODE_COMBINATION_ID,
ACCOUNTED_DR,
ACCOUNTED_CR,
ENTERED_DR,
ENTERED_CR,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
USER_CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
TRANSACTION_DATE,
PERIOD_NAME,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY_CODE,
SET_OF_BOOKS_ID,
ENCUMBRANCE_TYPE_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
SOURCE_DOC_QUANTITY,
ACCRUAL_METHOD_FLAG,
ENTERED_REC_TAX,
ENTERED_NR_TAX,
ACCOUNTED_REC_TAX,
ACCOUNTED_NR_TAX,
USSGL_TRANSACTION_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID )
VALUES (
DECODE(p_rcv_ae_line.actual_flag,'E',-1,1) * RCV_RECEIVING_SUB_LEDGER_S.NEXTVAL,
P_RCV_AE_LINE.ACCOUNTING_EVENT_ID,
P_RCV_AE_LINE.DEBIT_LINE_TYPE,
SYSDATE,
P_RCV_AE_LINE.LAST_UPDATED_BY,
SYSDATE,
P_RCV_AE_LINE.CREATED_BY,
P_RCV_AE_LINE.LAST_UPDATE_LOGIN,
P_RCV_AE_LINE.RCV_TRANSACTION_ID,
P_RCV_AE_LINE.ACTUAL_FLAG,
'Purchasing',
'Accrual',
TRUNC(l_accounting_date),
P_RCV_AE_LINE.DEBIT_ACCOUNT,
P_RCV_AE_LINE.ACCOUNTED_DR,
NULL,
P_RCV_AE_LINE.ENTERED_DR,
NULL,
P_RCV_AE_LINE.CURRENCY_CODE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_DATE,
L_USER_CURR_CONV_TYPE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_RATE,
P_RCV_AE_LINE.TRANSACTION_DATE,
P_GLINFO.PERIOD_NAME,
P_GLINFO.CHART_OF_ACCOUNTS_ID,
P_GLINFO.CURRENCY_CODE,
P_GLINFO.SET_OF_BOOKS_ID,
NULL,
'PO',
P_RCV_AE_LINE.DOC_HEADER_ID,
P_RCV_AE_LINE.DOC_DISTRIBUTION_ID,
P_RCV_AE_LINE.DOC_NUMBER,
P_RCV_AE_LINE.PRIMARY_QUANTITY,
'O',
P_RCV_AE_LINE.ENTERED_REC_TAX,
P_RCV_AE_LINE.ENTERED_NR_TAX,
P_RCV_AE_LINE.ACCOUNTED_REC_TAX,
P_RCV_AE_LINE.ACCOUNTED_NR_TAX,
decode(P_RCV_AE_LINE.DEBIT_LINE_TYPE,
RECEIVING_INSPECTION, NULL,
P_RCV_AE_LINE.USSGL_TRANSACTION_CODE),
P_RCV_AE_LINE.REQUEST_ID,
P_RCV_AE_LINE.PROGRAM_APPLICATION_ID,
P_RCV_AE_LINE.PROGRAM_ID )
RETURNING RCV_SUB_LEDGER_ID INTO L_RCV_SUB_LEDGER_ID;
RAISE INSERT_RRSL_ERROR;
INSERT INTO RCV_RECEIVING_SUB_LEDGER (
RCV_SUB_LEDGER_ID,
ACCOUNTING_EVENT_ID,
ACCOUNTING_LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RCV_TRANSACTION_ID,
ACTUAL_FLAG,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
ACCOUNTING_DATE,
CODE_COMBINATION_ID,
ACCOUNTED_DR,
ACCOUNTED_CR,
ENTERED_DR,
ENTERED_CR,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
USER_CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
TRANSACTION_DATE,
PERIOD_NAME,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY_CODE,
SET_OF_BOOKS_ID,
ENCUMBRANCE_TYPE_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
SOURCE_DOC_QUANTITY,
ACCRUAL_METHOD_FLAG,
ENTERED_REC_TAX,
ENTERED_NR_TAX,
ACCOUNTED_REC_TAX,
ACCOUNTED_NR_TAX,
USSGL_TRANSACTION_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID )
VALUES (
DECODE(p_rcv_ae_line.actual_flag,'E',-1,1) * RCV_RECEIVING_SUB_LEDGER_S.NEXTVAL,
P_RCV_AE_LINE.ACCOUNTING_EVENT_ID,
P_RCV_AE_LINE.CREDIT_LINE_TYPE,
SYSDATE,
P_RCV_AE_LINE.LAST_UPDATED_BY,
SYSDATE,
P_RCV_AE_LINE.CREATED_BY,
P_RCV_AE_LINE.LAST_UPDATE_LOGIN,
P_RCV_AE_LINE.RCV_TRANSACTION_ID,
P_RCV_AE_LINE.ACTUAL_FLAG,
'Purchasing',
'Accrual',
TRUNC(l_accounting_date),
P_RCV_AE_LINE.CREDIT_ACCOUNT,
NULL,
P_RCV_AE_LINE.ACCOUNTED_CR,
NULL,
P_RCV_AE_LINE.ENTERED_CR,
P_RCV_AE_LINE.CURRENCY_CODE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_DATE,
L_USER_CURR_CONV_TYPE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_RATE,
P_RCV_AE_LINE.TRANSACTION_DATE,
P_GLINFO.PERIOD_NAME,
P_GLINFO.CHART_OF_ACCOUNTS_ID,
P_GLINFO.CURRENCY_CODE,
P_GLINFO.SET_OF_BOOKS_ID,
NULL,
'PO',
P_RCV_AE_LINE.DOC_HEADER_ID,
P_RCV_AE_LINE.DOC_DISTRIBUTION_ID,
P_RCV_AE_LINE.DOC_NUMBER,
P_RCV_AE_LINE.PRIMARY_QUANTITY,
'O',
P_RCV_AE_LINE.ENTERED_REC_TAX,
P_RCV_AE_LINE.ENTERED_NR_TAX,
P_RCV_AE_LINE.ACCOUNTED_REC_TAX,
P_RCV_AE_LINE.ACCOUNTED_NR_TAX,
decode(P_RCV_AE_LINE.CREDIT_LINE_TYPE,
RECEIVING_INSPECTION, NULL,
P_RCV_AE_LINE.USSGL_TRANSACTION_CODE),
P_RCV_AE_LINE.REQUEST_ID,
P_RCV_AE_LINE.PROGRAM_APPLICATION_ID,
P_RCV_AE_LINE.PROGRAM_ID )
RETURNING RCV_SUB_LEDGER_ID INTO L_RCV_SUB_LEDGER_ID;
RAISE INSERT_RRSL_ERROR;
INSERT INTO RCV_RECEIVING_SUB_LEDGER (
RCV_SUB_LEDGER_ID,
ACCOUNTING_EVENT_ID,
ACCOUNTING_LINE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RCV_TRANSACTION_ID,
ACTUAL_FLAG,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
ACCOUNTING_DATE,
CODE_COMBINATION_ID,
ACCOUNTED_DR,
ACCOUNTED_CR,
ENTERED_DR,
ENTERED_CR,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
USER_CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
TRANSACTION_DATE,
PERIOD_NAME,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY_CODE,
SET_OF_BOOKS_ID,
ENCUMBRANCE_TYPE_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
SOURCE_DOC_QUANTITY,
ACCRUAL_METHOD_FLAG,
ENTERED_REC_TAX,
ENTERED_NR_TAX,
ACCOUNTED_REC_TAX,
ACCOUNTED_NR_TAX,
USSGL_TRANSACTION_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID )
VALUES (
DECODE(p_rcv_ae_line.actual_flag,'E',-1,1) * RCV_RECEIVING_SUB_LEDGER_S.NEXTVAL,
P_RCV_AE_LINE.ACCOUNTING_EVENT_ID,
LC_ABSORPTION,
SYSDATE,
P_RCV_AE_LINE.LAST_UPDATED_BY,
SYSDATE,
P_RCV_AE_LINE.CREATED_BY,
P_RCV_AE_LINE.LAST_UPDATE_LOGIN,
P_RCV_AE_LINE.RCV_TRANSACTION_ID,
P_RCV_AE_LINE.ACTUAL_FLAG,
'Purchasing',
'Accrual',
TRUNC(l_accounting_date),
P_RCV_AE_LINE.LCM_ACCOUNT_ID,
L_ACT_DR,
L_ACT_CR,
L_ENT_DR,
L_ENT_CR,
P_RCV_AE_LINE.CURRENCY_CODE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_DATE,
L_USER_CURR_CONV_TYPE,
P_RCV_AE_LINE.CURRENCY_CONVERSION_RATE,
P_RCV_AE_LINE.TRANSACTION_DATE,
P_GLINFO.PERIOD_NAME,
P_GLINFO.CHART_OF_ACCOUNTS_ID,
P_GLINFO.CURRENCY_CODE,
P_GLINFO.SET_OF_BOOKS_ID,
NULL,
'PO',
P_RCV_AE_LINE.DOC_HEADER_ID,
P_RCV_AE_LINE.DOC_DISTRIBUTION_ID,
P_RCV_AE_LINE.DOC_NUMBER,
P_RCV_AE_LINE.PRIMARY_QUANTITY,
'O',
P_RCV_AE_LINE.ENTERED_REC_TAX,
P_RCV_AE_LINE.ENTERED_NR_TAX,
P_RCV_AE_LINE.ACCOUNTED_REC_TAX,
P_RCV_AE_LINE.ACCOUNTED_NR_TAX,
decode(P_RCV_AE_LINE.CREDIT_LINE_TYPE,
RECEIVING_INSPECTION, NULL,
P_RCV_AE_LINE.USSGL_TRANSACTION_CODE),
P_RCV_AE_LINE.REQUEST_ID,
P_RCV_AE_LINE.PROGRAM_APPLICATION_ID,
P_RCV_AE_LINE.PROGRAM_ID )
RETURNING RCV_SUB_LEDGER_ID INTO L_RCV_SUB_LEDGER_ID;
RAISE INSERT_RRSL_ERROR;
WHEN INSERT_RRSL_ERROR THEN
IF G_DEBUG = 'Y' THEN
IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
l_api_message := 'Error inserting into RCV_RECEIVING_SUB_LEDGER ';
,'Insert_SubLedgerLines : '||l_stmt_num||' : '||l_api_message);
'Error inserting into RCV_RECEIVING_SUB_LEDGER '||
SQLERRM
);
,'Insert_SubLedgerLines : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
END Insert_SubLedgerLines;
SELECT
ACCOUNTING_EVENT_ID,
RCV_TRANSACTION_ID,
DECODE(TRX_FLOW_HEADER_ID, NULL, 'N', 'Y'),
PO_HEADER_ID,
PO_DISTRIBUTION_ID,
ORG_ID,
ORGANIZATION_ID,
SET_OF_BOOKS_ID,
TRANSACTION_DATE,
EVENT_TYPE_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
SOURCE_DOC_QUANTITY, -- Document Quantity is used to create distributions
UNIT_PRICE,
/* Support for Landed Cost Management */
UNIT_LANDED_COST,
PRIOR_UNIT_PRICE,
TRANSACTION_AMOUNT,
NR_TAX,
REC_TAX,
NR_TAX_AMOUNT,
REC_TAX_AMOUNT,
NVL(PRIOR_NR_TAX, 0),
NVL(PRIOR_REC_TAX, 0),
DEBIT_ACCOUNT_ID,
CREDIT_ACCOUNT_ID,
/* Support for Landed Cost Management */
LCM_ACCOUNT_ID,
PROCUREMENT_ORG_FLAG,
INVENTORY_ITEM_ID,
USSGL_TRANSACTION_CODE,
GL_GROUP_ID,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PRIMARY_QUANTITY
INTO
L_RCV_AE_LINE.ACCOUNTING_EVENT_ID,
L_RCV_AE_LINE.RCV_TRANSACTION_ID,
L_RCV_AE_LINE.GLOBAL_PROC_FLAG,
L_RCV_AE_LINE.DOC_HEADER_ID,
L_RCV_AE_LINE.DOC_DISTRIBUTION_ID,
L_RCV_AE_LINE.ORG_ID,
L_RCV_AE_LINE.ORGANIZATION_ID,
L_RCV_AE_LINE.SET_OF_BOOKS_ID,
L_RCV_AE_LINE.TRANSACTION_DATE,
L_RCV_AE_LINE.EVENT_TYPE_ID,
L_RCV_AE_LINE.CURRENCY_CODE,
L_RCV_AE_LINE.CURRENCY_CONVERSION_RATE,
L_RCV_AE_LINE.CURRENCY_CONVERSION_TYPE,
L_RCV_AE_LINE.CURRENCY_CONVERSION_DATE,
L_RCV_AE_LINE.PRIMARY_QUANTITY,
L_RCV_AE_LINE.UNIT_PRICE,
/* Support for Landed Cost Management */
L_RCV_AE_LINE.UNIT_LANDED_COST,
L_RCV_AE_LINE.PRIOR_UNIT_PRICE,
L_RCV_AE_LINE.TRANSACTION_AMOUNT,
L_RCV_AE_LINE.NR_TAX,
L_RCV_AE_LINE.REC_TAX,
L_NR_TAX_AMOUNT,
L_REC_TAX_AMOUNT,
L_PRIOR_NR_TAX,
L_PRIOR_REC_TAX,
L_RCV_AE_LINE.DEBIT_ACCOUNT,
L_RCV_AE_LINE.CREDIT_ACCOUNT,
/* Support for Landed Cost Management */
L_RCV_AE_LINE.LCM_ACCOUNT_ID,
L_RCV_AE_LINE.PROCUREMENT_ORG_FLAG,
L_RCV_AE_LINE.INVENTORY_ITEM_ID,
L_RCV_AE_LINE.USSGL_TRANSACTION_CODE,
L_RCV_AE_LINE.GL_GROUP_ID,
L_RCV_AE_LINE.CREATED_BY,
L_RCV_AE_LINE.LAST_UPDATED_BY,
L_RCV_AE_LINE.LAST_UPDATE_LOGIN,
L_RCV_AE_LINE.REQUEST_ID,
L_RCV_AE_LINE.PROGRAM_APPLICATION_ID,
L_RCV_AE_LINE.PROGRAM_ID,
l_primary_qty
FROM
RCV_ACCOUNTING_EVENTS
WHERE
ACCOUNTING_EVENT_ID = p_accounting_event_id;
SELECT
DECODE( DROPSHIP_TYPE_CODE, 1, 'Y', 2, 'Y', 'N' )
INTO
l_drop_ship
FROM
RCV_TRANSACTIONS
WHERE
TRANSACTION_ID = L_RCV_AE_LINE.RCV_TRANSACTION_ID;
SELECT
SEGMENT1
INTO
L_RCV_AE_LINE.DOC_NUMBER
FROM
PO_HEADERS
WHERE
PO_HEADER_ID = L_RCV_AE_LINE.DOC_HEADER_ID;
SELECT
substrb(POL.ITEM_DESCRIPTION,1,100)
INTO
L_RCV_AE_LINE.ITEM_DESCRIPTION
FROM
PO_LINES POL,
PO_DISTRIBUTIONS POD
WHERE
POD.PO_DISTRIBUTION_ID = L_RCV_AE_LINE.DOC_DISTRIBUTION_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID;
SELECT LEGAL_ENTITY
INTO l_legal_entity
FROM CST_ACCT_INFO_V
WHERE ORGANIZATION_ID = l_rcv_ae_line.organization_id;
SELECT
CURRENCY_CODE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION
INTO
L_CURR_REC.DOCUMENT_CURRENCY,
L_CURR_REC.MIN_ACCT_UNIT_DOC,
L_CURR_REC.PRECISION_DOC
FROM
FND_CURRENCIES
WHERE
CURRENCY_CODE = L_RCV_AE_LINE.CURRENCY_CODE;
SELECT
CURRENCY_CODE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION
INTO
L_CURR_REC.FUNCTIONAL_CURRENCY,
L_CURR_REC.MIN_ACCT_UNIT_FUNC,
L_CURR_REC.PRECISION_FUNC
FROM
FND_CURRENCIES
WHERE
CURRENCY_CODE = L_GLINFO.CURRENCY_CODE;
SELECT nvl(PARENT_TRANSACTION_ID, -1)
INTO l_parent_rcv_txn_id
FROM RCV_TRANSACTIONS
WHERE transaction_id = L_RCV_AE_LINE.RCV_TRANSACTION_ID;
SELECT DESTINATION_TYPE_CODE
INTO l_destination_type_code
FROM PO_DISTRIBUTIONS
WHERE PO_DISTRIBUTION_ID = L_RCV_AE_LINE.DOC_DISTRIBUTION_ID;
SELECT TRANSACTION_TYPE
INTO l_parent_txn_type
FROM RCV_TRANSACTIONS
WHERE transaction_id = l_parent_rcv_txn_id;
Insert_SubLedgerLines(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rcv_ae_line => l_rcv_ae_line,
p_glinfo => l_glinfo
);
l_api_message := 'Error inserting into RCV_RECEIVING_SUB_LEDGER';
SELECT nvl(purch_encumbrance_flag, 'N')
INTO l_trx_info.ENCUMBRANCE_FLAG
FROM financials_system_params_all
WHERE set_of_books_id = l_rcv_ae_line.set_of_books_id
AND org_id = l_rcv_ae_line.org_id;