[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct(HZP.party_id)
INTO l_person_id
FROM HZ_PARTIES HZP,
FND_USER U,
PER_ALL_PEOPLE_F PAP,
(SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
WHERE CURR.user_id = U.user_id
AND U.employee_id = PAP.person_id
AND PAP.party_id = HZP.party_id;
SELECT FTB.batch_number batch_number,
INIT_P.party_name initiator,
XLE.name init_le,
GL.name from_ledger_name,
FCV.name entered_currency,
FTB.exchange_rate_type exchange_rate_type,
LKUP.meaning batch_status,
FTB.description batch_description,
FTB.note batch_note,
FTTVL.trx_type_name transaction_type,
FTB.gl_date gl_date,
FTB.batch_date batch_date,
FTB.reject_allow_flag reject_allow_flag,
FTB_ORIG.batch_number original_batch_number,
FTB_REV.batch_number reversed_batch_number,
FTB.initiator_source initiator_source,
FTB.attribute1,
FTB.attribute2,
FTB.attribute3,
FTB.attribute4,
FTB.attribute5,
FTB.attribute6,
FTB.attribute7,
FTB.attribute8,
FTB.attribute9,
FTB.attribute10,
FTB.attribute11,
FTB.attribute12,
FTB.attribute13,
FTB.attribute14,
FTB.attribute15,
FTB.attribute_category,
FTH.trx_number,
RECI_P.party_name recipient,
XLE1.name recipient_le,
GL1.name to_ledger_name,
LKUP1.meaning trx_status,
FTH.init_amount_cr initiator_credit,
FTH.init_amount_dr initiator_debit,
FTH.reci_amount_cr recipient_credit,
FTH.reci_amount_dr recipient_debit,
FTH.ar_invoice_number ar_invoice_number,
FTH.invoice_flag invoice_flag,
FTH.approval_date,
FTH_ORIG.trx_number orig_trx_number,
FTH_REV.trx_number reversed_trx_number,
FTH.initiator_instance_flag,
FTH.recipient_instance_flag,
FTH.reject_reason reject_reason,
FTH.description header_description,
FTH.attribute1,
FTH.attribute2,
FTH.attribute3,
FTH.attribute4,
FTH.attribute5,
FTH.attribute6,
FTH.attribute7,
FTH.attribute8,
FTH.attribute9,
FTH.attribute10,
FTH.attribute11,
FTH.attribute12,
FTH.attribute13,
FTH.attribute14,
FTH.attribute15,
FTH.attribute_category
FROM FUN_TRX_BATCHES FTB,
FUN_TRX_HEADERS FTH,
XLE_ENTITY_PROFILES XLE,
FND_LOOKUP_VALUES LKUP,
HZ_PARTIES INIT_P,
FUN_TRX_TYPES_VL FTTVL,
FND_CURRENCIES_VL FCV,
GL_LEDGERS GL,
FUN_TRX_BATCHES FTB_ORIG,
FUN_TRX_BATCHES FTB_REV,
GL_LEDGERS GL1,
FUN_TRX_HEADERS FTH_ORIG,
FUN_TRX_HEADERS FTH_REV,
HZ_PARTIES RECI_P,
XLE_ENTITY_PROFILES XLE1,
FND_LOOKUP_VALUES LKUP1,
FND_GRANTS FG,
FND_OBJECT_INSTANCE_SETS FOIS,
HZ_RELATIONSHIPS HZR,
HZ_ORG_CONTACTS HZC,
HZ_ORG_CONTACT_ROLES HZCR
WHERE FTH.batch_id(+) = FTB.batch_id
AND XLE.legal_entity_id = FTB.from_le_id
AND LKUP.lookup_type = ''FUN_BATCH_STATUS''
AND LKUP.lookup_code = FTB.status
AND LKUP.VIEW_APPLICATION_ID = 435
AND LKUP.language=USERENV(''LANG'')
AND LKUP.security_group_id=fnd_global.lookup_security_group(LKUP.lookup_type,435)
AND FTB.status IN (''NEW'', ''SENT'', ''ERROR'', ''COMPLETE'')
AND INIT_P.party_id = FTB.initiator_id
AND FTTVL.trx_type_id = FTB.trx_type_id
AND RECI_P.party_id(+) = FTH.recipient_id
AND XLE1.legal_entity_id(+) = FTH.to_le_id
AND FCV.currency_code = FTB.currency_code
AND LKUP1.lookup_type(+) = ''FUN_TRX_STATUS''
AND LKUP1.lookup_code(+) = FTH.status
AND LKUP1.view_application_id = 435
AND LKUP1.security_group_id=fnd_global.lookup_security_group(LKUP1.lookup_type,435)
AND LKUP1.language = USERENV(''LANG'')
AND GL.ledger_id = FTB.from_ledger_id
AND FTB_ORIG.batch_id(+) = FTB.original_batch_id
AND FTB_REV.batch_id(+) = FTB.reversed_batch_id
AND GL1.ledger_id(+) = FTH.to_ledger_id
AND FTH_ORIG.trx_id(+) = FTH.original_trx_id
AND FTH_REV.trx_id(+) = FTH.reversed_trx_id
AND FG.grantee_key = ''' || l_grantee_key
|| '''
AND FG.parameter1 = TO_CHAR(FTB.initiator_id)
AND FG.instance_set_id = FOIS.instance_set_id
AND FOIS.instance_set_name = ''FUN_TRX_BATCHES_SET''
AND HZR.RELATIONSHIP_CODE = ''CONTACT_OF''
AND HZR.RELATIONSHIP_TYPE = ''CONTACT''
AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
AND HZCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
AND HZR.DIRECTIONAL_FLAG = ''F''
AND HZR.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
AND HZR.OBJECT_TABLE_NAME = ''HZ_PARTIES''
AND HZR.SUBJECT_TYPE = ''PERSON''
AND HZR.OBJECT_ID = INIT_P.PARTY_ID
AND HZR.STATUS = ''A''
AND HZR.SUBJECT_ID= ' || l_person_id;
SELECT distinct(HZP.party_id)
INTO l_person_id
FROM HZ_PARTIES HZP,
FND_USER U,
PER_ALL_PEOPLE_F PAP,
(SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
WHERE CURR.user_id = U.user_id
AND U.employee_id = PAP.person_id
AND PAP.party_id = HZP.party_id;
SELECT application_column_name
INTO l_acc_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_para_rec.coa_initiator
AND segment_attribute_type = 'GL_ACCOUNT'
AND attribute_value = 'Y';
SELECT fun_trx_entry_util.get_concatenated_account(fdl.ccid) account,
''OUTBOUND'' batch_type,
lkup.meaning account_type,
FTB.batch_number batch_number,
FTH.trx_number trx_number,
lkup1.meaning batch_status,
FTB.batch_date batch_date,
INIT_P.party_name initiator,
RECI_P.party_name recipient,
lkup2.meaning trx_status,
FTTVL.trx_type_name transaction_type,
FTB.gl_date gl_date,
GLP.period_name gl_period,
FCV.name entered_currency,
FDL.amount_cr entered_credit,
fdl.amount_dr entered_debit,
fdl.description description,
FTH.invoice_flag invoice_flag,
NVL2(FTH.original_trx_id,NVL2(FTH.reversed_trx_id,0,FTH_REV.trx_number),FTH_ORIG.trx_number) reverse_reference,
DECODE(NVL(FTH.original_trx_id,0),0,''N'',''Y'') reverse_trx_flag,
DECODE(NVL(FTH.reversed_trx_id,0),0,''N'',''Y'') reversed_flag,
FTH.ar_invoice_number ar_invoice_number,
FTH_ORIG.trx_number orig_trx_number,
FTH_REV.trx_number rev_trx_number,
FTB_ORIG.batch_number orig_batch_number,
FTB_REV.batch_number rev_batch_number,
FTB.reject_allow_flag reject_allow_flag,
FTB.initiator_source initiator_source,
FTB.description batch_description,
FTB.attribute1,
FTB.attribute2,
FTB.attribute3,
FTB.attribute4,
FTB.attribute5,
FTB.attribute6,
FTB.attribute7,
FTB.attribute8,
FTB.attribute9,
FTB.attribute10,
FTB.attribute11,
FTB.attribute12,
FTB.attribute13,
FTB.attribute14,
FTB.attribute15,
FTB.attribute_category,
fdl.dist_number distribution_number,
fdl.party_type_flag party_type_flag,
fdl.dist_type_flag dist_type_flag,
fdl.auto_generate_flag auto_generate_flag,
fdl.attribute1,
fdl.attribute2,
fdl.attribute3,
fdl.attribute4,
fdl.attribute5,
fdl.attribute6,
fdl.attribute7,
fdl.attribute8,
fdl.attribute9,
fdl.attribute10,
fdl.attribute11,
fdl.attribute12,
fdl.attribute13,
fdl.attribute14,
fdl.attribute15,
fdl.attribute_category,
XLE.name init_le,
XLE1.name recipient_le,
GL0.name from_ledger_name,
GL1.name to_ledger_name,
FTH.init_amount_cr initiator_credit,
FTH.init_amount_dr initiator_debit,
FTH.reci_amount_cr recipient_credit,
FTH.reci_amount_dr recipient_debit,
FTH.approval_date approval_date,
FTH.initiator_instance_flag initiator_instance_flag,
FTH.recipient_instance_flag recipient_instance_flag,
FTH.reject_reason reject_reason,
FTH.description header_description,
FTH.attribute1,
FTH.attribute2,
FTH.attribute3,
FTH.attribute4,
FTH.attribute5,
FTH.attribute6,
FTH.attribute7,
FTH.attribute8,
FTH.attribute9,
FTH.attribute10,
FTH.attribute11,
FTH.attribute12,
FTH.attribute13,
FTH.attribute14,
FTH.attribute15,
FTH.attribute_category
FROM fun_dist_lines fdl,
gl_code_combinations glcc,
fnd_lookups lkup,
fnd_lookup_values lkup1,
fnd_lookup_values lkup2,
FUN_TRX_BATCHES FTB,
FUN_TRX_BATCHES FTB_REV,
FUN_TRX_BATCHES FTB_ORIG,
HZ_PARTIES INIT_P,
HZ_PARTIES RECI_P,
FUN_TRX_HEADERS FTH,
FUN_TRX_HEADERS FTH_REV,
FUN_TRX_HEADERS FTH_ORIG,
FUN_TRX_TYPES_VL FTTVL,
GL_PERIODS GLP,
FND_CURRENCIES_VL FCV,
FUN_TRX_LINES FTL,
GL_LEDGERS GL0,
GL_LEDGERS GL1,
XLE_ENTITY_PROFILES XLE,
XLE_ENTITY_PROFILES XLE1,
FND_GRANTS FG,
FND_OBJECT_INSTANCE_SETS FOIS,
HZ_RELATIONSHIPS HZR,
HZ_ORG_CONTACTS HZC,
HZ_ORG_CONTACT_ROLES HZCR
WHERE fdl.ccid = glcc.code_combination_id
AND glcc.account_type = lkup.lookup_code
AND lkup.lookup_type =''ACCOUNT_TYPE''
AND lkup.lookup_code IN (''A'',''E'',''L'',''R'', ''O'')
AND lkup1.lookup_type =''FUN_BATCH_STATUS''
AND lkup1.view_application_id = 435
AND lkup1.security_group_id = fnd_global.lookup_security_group(lkup1.lookup_type,435)
AND lkup1.language = USERENV(''LANG'')
AND lkup1.lookup_code = FTB.status
AND lkup1.lookup_code IN (''NEW'',''SENT'',''ERROR'',''COMPLETE'')
AND INIT_P.party_id = FTB.initiator_id
AND RECI_P.party_id(+) = FTH.recipient_id
AND lkup2.lookup_code = FTH.status
AND lkup2.view_application_id = 435
AND lkup2.security_group_id = fnd_global.lookup_security_group(lkup2.lookup_type,435)
AND lkup2.language = USERENV(''LANG'')
AND LKUP2.lookup_type = ''FUN_TRX_STATUS''
AND FTH.batch_id(+) = FTB.batch_id
AND FTTVL.trx_type_id = FTB.trx_type_id
AND FTB.gl_date BETWEEN GLP.start_date AND GLP.end_date
AND FCV.currency_code = FTB.currency_code
AND FTL.trx_id = FTH.trx_id
AND FDL.line_id = FTL.line_id
AND FDL.party_type_flag = ''I''
AND FTH.original_trx_id=FTH_ORIG.trx_id(+)
AND FTB.original_batch_id=FTB_ORIG.batch_id(+)
AND FTH.reversed_trx_id=FTH_REV.trx_id(+)
AND FTB.reversed_batch_id=FTB_REV.batch_id(+)
AND XLE.legal_entity_id = FTB.from_le_id
AND XLE1.legal_entity_id(+) = FTH.to_le_id
AND GL0.ledger_id = FTB.from_ledger_id
AND GL1.ledger_id(+) = FTH.to_ledger_id
AND GLP.period_set_name =GL0.period_set_name
AND GLP.period_type = GL0.accounted_period_type
AND fdl.dist_type_flag IN (''R'',''L'')
AND FG.grantee_key = ''' || l_grantee_key
|| '''
AND FG.parameter1 = TO_CHAR(FTB.initiator_id)
AND FG.instance_set_id = FOIS.instance_set_id
AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
AND FOIS.instance_set_name = ''FUN_TRX_BATCHES_SET''
AND HZR.RELATIONSHIP_CODE = ''CONTACT_OF''
AND HZR.RELATIONSHIP_TYPE = ''CONTACT''
AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
AND HZCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
AND HZR.DIRECTIONAL_FLAG = ''F''
AND HZR.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
AND HZR.OBJECT_TABLE_NAME = ''HZ_PARTIES''
AND HZR.SUBJECT_TYPE = ''PERSON''
AND HZR.direction_code=''P''
AND HZR.OBJECT_ID = INIT_P.PARTY_ID
AND HZR.STATUS = ''A''
AND HZR.SUBJECT_ID='|| l_person_id;
SELECT distinct(HZP.party_id)
INTO l_person_id
FROM HZ_PARTIES HZP,
FND_USER U,
PER_ALL_PEOPLE_F PAP,
(SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
WHERE CURR.user_id = U.user_id
AND U.employee_id = PAP.person_id
AND PAP.party_id = HZP.party_id;
SELECT FTB.batch_number batch_number,
INIT_P.party_name initiator,
XLE.name init_le,
GL.name from_ledger_name,
FCV.name entered_currency,
FTB.exchange_rate_type exchange_rate_type,
LKUP.meaning batch_status,
FTB.description batch_description,
FTB.note batch_note,
FTTVL.trx_type_name transaction_type,
FTB.gl_date gl_date,
FTB.batch_date batch_date,
FTB.reject_allow_flag reject_allow_flag,
FTB_ORIG.batch_number original_batch_number,
FTB_REV.batch_number reversed_batch_number,
FTB.initiator_source initiator_source,
FTB.attribute1,
FTB.attribute2,
FTB.attribute3,
FTB.attribute4,
FTB.attribute5,
FTB.attribute6,
FTB.attribute7,
FTB.attribute8,
FTB.attribute9,
FTB.attribute10,
FTB.attribute11,
FTB.attribute12,
FTB.attribute13,
FTB.attribute14,
FTB.attribute15,
FTB.attribute_category,
FTH.trx_number,
RECI_P.party_name recipient,
XLE1.name recipient_le,
GL1.name to_ledger_name,
LKUP1.meaning trx_status,
FTH.init_amount_cr initiator_credit,
FTH.init_amount_dr initiator_debit,
FTH.reci_amount_cr recipient_credit,
FTH.reci_amount_dr recipient_debit,
FTH.ar_invoice_number ar_invoice_number,
FTH.invoice_flag invoice_flag,
FTH.approval_date,
FTH_ORIG.trx_number orig_trx_number,
FTH_REV.trx_number reversed_trx_number,
FTH.initiator_instance_flag,
FTH.recipient_instance_flag,
FTH.reject_reason reject_reason,
FTH.description header_description,
FTH.attribute1,
FTH.attribute2,
FTH.attribute3,
FTH.attribute4,
FTH.attribute5,
FTH.attribute6,
FTH.attribute7,
FTH.attribute8,
FTH.attribute9,
FTH.attribute10,
FTH.attribute11,
FTH.attribute12,
FTH.attribute13,
FTH.attribute14,
FTH.attribute15,
FTH.attribute_category
FROM FUN_TRX_BATCHES FTB,
FUN_TRX_HEADERS FTH,
XLE_ENTITY_PROFILES XLE,
FND_LOOKUP_VALUES LKUP,
HZ_PARTIES INIT_P,
FUN_TRX_TYPES_VL FTTVL,
FND_CURRENCIES_VL FCV,
GL_LEDGERS GL,
FUN_TRX_BATCHES FTB_ORIG,
FUN_TRX_BATCHES FTB_REV,
GL_LEDGERS GL1,
FUN_TRX_HEADERS FTH_ORIG,
FUN_TRX_HEADERS FTH_REV,
HZ_PARTIES RECI_P,
XLE_ENTITY_PROFILES XLE1,
FND_LOOKUP_VALUES LKUP1,
FND_GRANTS FG,
FND_OBJECT_INSTANCE_SETS FOIS
WHERE FTH.batch_id(+) = FTB.batch_id
AND XLE.legal_entity_id = FTB.from_le_id
AND LKUP.lookup_type = ''FUN_BATCH_STATUS''
AND LKUP.lookup_code = FTB.status
AND LKUP.view_application_id = 435
AND LKUP.security_group_id=fnd_global.lookup_security_group(lkup.lookup_type,435)
AND LKUP.language = USERENV(''LANG'')
AND FTH.status IN (''RECEIVED'', ''APPROVED'', ''REJECTED'',
''COMPLETE'', ''XFER_RECI_GL'', ''XFER_AR'', ''XFER_INI_GL'')
AND INIT_P.party_id = FTB.initiator_id
AND FTTVL.trx_type_id = FTB.trx_type_id
AND RECI_P.party_id(+) = FTH.recipient_id
AND XLE1.legal_entity_id(+) = FTH.to_le_id
AND FCV.currency_code = FTB.currency_code
AND LKUP1.lookup_type(+) = ''FUN_TRX_STATUS''
AND LKUP1.view_application_id = 435
AND LKUP1.security_group_id=fnd_global.lookup_security_group(lkup1.lookup_type,435)
AND LKUP1.language = USERENV(''LANG'')
AND LKUP1.lookup_code(+) = FTH.status
AND GL.ledger_id = FTB.from_ledger_id
AND FTB_ORIG.batch_id(+) = FTB.original_batch_id
AND FTB_REV.batch_id(+) = FTB.reversed_batch_id
AND GL1.ledger_id(+) = FTH.to_ledger_id
AND FTH_ORIG.trx_id(+) = FTH.original_trx_id
AND FTH_REV.trx_id(+) = FTH.reversed_trx_id
AND FG.parameter1 = TO_CHAR(FTH.recipient_id)
AND FG.instance_set_id = FOIS.instance_set_id
AND FOIS.instance_set_name = ''FUN_TRX_HEADERS_SET''
AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
AND FG.grantee_key = ''' || l_grantee_key || '''';
SELECT distinct(HZP.party_id)
INTO l_person_id
FROM HZ_PARTIES HZP,
FND_USER U,
PER_ALL_PEOPLE_F PAP,
(SELECT FND_GLOBAL.user_id() AS user_id FROM DUAL) CURR
WHERE CURR.user_id = U.user_id
AND U.employee_id = PAP.person_id
AND PAP.party_id = HZP.party_id;
SELECT application_column_name
INTO l_acc_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_para_rec.coa_recipient
AND segment_attribute_type = 'GL_ACCOUNT'
AND attribute_value = 'Y';
SELECT fun_trx_entry_util.get_concatenated_account(fdl.ccid) account,
''INBOUND'' batch_type,
lkup.meaning account_type,
FTB.batch_number batch_number,
FTH.trx_number trx_number,
lkup1.meaning batch_status,
FTB.batch_date batch_date,
INIT_P.party_name initiator,
RECI_P.party_name recipient,
lkup2.meaning trx_status,
FTTVL.trx_type_name transaction_type,
FTB.gl_date gl_date,
GLP.period_name gl_period,
FCV.name entered_currency,
FDL.amount_cr entered_credit,
fdl.amount_dr entered_debit,
fdl.description description,
FTH.invoice_flag invoice_flag,
NVL2(FTH.original_trx_id,NVL2(FTH.reversed_trx_id,0,FTH_REV.trx_number),FTH_ORIG.trx_number) reverse_reference,
DECODE(NVL(FTH.original_trx_id,0),0,''N'',''Y'') reverse_trx_flag,
DECODE(NVL(FTH.reversed_trx_id,0),0,''N'',''Y'') reversed_flag,
FTH.ar_invoice_number ar_invoice_number,
FTH_ORIG.trx_number orig_trx_number,
FTH_REV.trx_number rev_trx_number,
FTB_ORIG.batch_number orig_batch_number,
FTB_REV.batch_number rev_batch_number,
FTB.reject_allow_flag reject_allow_flag,
FTB.initiator_source initiator_source,
FTB.description batch_description,
FTB.attribute1,
FTB.attribute2,
FTB.attribute3,
FTB.attribute4,
FTB.attribute5,
FTB.attribute6,
FTB.attribute7,
FTB.attribute8,
FTB.attribute9,
FTB.attribute10,
FTB.attribute11,
FTB.attribute12,
FTB.attribute13,
FTB.attribute14,
FTB.attribute15,
FTB.attribute_category,
fdl.dist_number distribution_number,
fdl.party_type_flag party_type_flag,
fdl.dist_type_flag dist_type_flag,
fdl.auto_generate_flag auto_generate_flag,
fdl.attribute1,
fdl.attribute2,
fdl.attribute3,
fdl.attribute4,
fdl.attribute5,
fdl.attribute6,
fdl.attribute7,
fdl.attribute8,
fdl.attribute9,
fdl.attribute10,
fdl.attribute11,
fdl.attribute12,
fdl.attribute13,
fdl.attribute14,
fdl.attribute15,
fdl.attribute_category,
XLE.name init_le,
XLE1.name recipient_le,
GL0.name from_ledger_name,
GL1.name to_ledger_name,
FTH.init_amount_cr initiator_credit,
FTH.init_amount_dr initiator_debit,
FTH.reci_amount_cr recipient_credit,
FTH.reci_amount_dr recipient_debit,
FTH.approval_date approval_date,
FTH.initiator_instance_flag initiator_instance_flag,
FTH.recipient_instance_flag recipient_instance_flag,
FTH.reject_reason reject_reason,
FTH.description header_description,
FTH.attribute1,
FTH.attribute2,
FTH.attribute3,
FTH.attribute4,
FTH.attribute5,
FTH.attribute6,
FTH.attribute7,
FTH.attribute8,
FTH.attribute9,
FTH.attribute10,
FTH.attribute11,
FTH.attribute12,
FTH.attribute13,
FTH.attribute14,
FTH.attribute15,
FTH.attribute_category
FROM fun_dist_lines fdl,
gl_code_combinations glcc,
fnd_lookups lkup,
fnd_lookup_values lkup1,
fnd_lookup_values lkup2,
FUN_TRX_BATCHES FTB,
FUN_TRX_BATCHES FTB_REV,
FUN_TRX_BATCHES FTB_ORIG,
HZ_PARTIES INIT_P,
HZ_PARTIES RECI_P,
FUN_TRX_HEADERS FTH,
FUN_TRX_HEADERS FTH_REV,
FUN_TRX_HEADERS FTH_ORIG,
FUN_TRX_TYPES_VL FTTVL,
GL_PERIODS GLP,
FND_CURRENCIES_VL FCV,
FUN_TRX_LINES FTL,
GL_LEDGERS GL0,
GL_LEDGERS GL1,
XLE_ENTITY_PROFILES XLE,
XLE_ENTITY_PROFILES XLE1,
FND_GRANTS FG,
FND_OBJECT_INSTANCE_SETS FOIS
WHERE fdl.ccid = glcc.code_combination_id
AND glcc.account_type = lkup.lookup_code
AND lkup.lookup_type =''ACCOUNT_TYPE''
AND lkup.lookup_code IN (''A'',''E'',''L'',''R'', ''O'')
AND lkup1.lookup_type =''FUN_BATCH_STATUS''
AND lkup1.lookup_code = FTB.status
AND lkup1.view_application_id = 435
AND lkup1.security_group_id =fnd_global.lookup_security_group(lkup1.lookup_type,435)
AND lkup1.language = USERENV(''LANG'')
AND INIT_P.party_id = FTB.initiator_id
AND RECI_P.party_id(+) = FTH.recipient_id
AND lkup2.lookup_code = FTH.status
AND LKUP2.lookup_type = ''FUN_TRX_STATUS''
AND lkup2.view_application_id = 435
AND lkup2.security_group_id =fnd_global.lookup_security_group(lkup2.lookup_type,435)
AND lkup2.language = USERENV(''LANG'')
AND LKUP2.lookup_code IN (''RECEIVED'',''APPROVED'',''REJECTED'',''COMPLETE'',''XFER_RECI_GL'',''XFER_AR'',''XFER_INI_GL'')
AND FTH.batch_id(+) = FTB.batch_id
AND FTTVL.trx_type_id = FTB.trx_type_id
AND FTB.gl_date BETWEEN GLP.start_date AND GLP.end_date
AND FCV.currency_code = FTB.currency_code
AND FTL.trx_id = FTH.trx_id
AND FDL.party_type_flag =''R''
AND FDL.line_id = FTL.line_id
AND FTH.original_trx_id=FTH_ORIG.trx_id(+)
AND FTB.original_batch_id=FTB_ORIG.batch_id(+)
AND FTH.reversed_trx_id=FTH_REV.trx_id(+)
AND FTB.reversed_batch_id=FTB_REV.batch_id(+)
AND XLE.legal_entity_id = FTB.from_le_id
AND XLE1.legal_entity_id = FTH.to_le_id
AND fdl.dist_type_flag IN (''P'',''L'')
AND GL0.ledger_id = FTB.from_ledger_id
AND GLP.period_set_name =GL0.period_set_name
AND GLP.period_type = GL0.accounted_period_type
AND GL1.ledger_id(+) = FTH.to_ledger_id
AND FG.parameter1 = TO_CHAR(FTH.recipient_id)
AND FG.instance_set_id = FOIS.instance_set_id
AND FOIS.instance_set_name = ''FUN_TRX_HEADERS_SET''
AND NVL(FG.end_date, SYSDATE+1) > SYSDATE
AND FG.grantee_key = ''' || l_grantee_key||'''';
SELECT party_name
INTO l_initiator_name
FROM HZ_PARTIES
WHERE party_id = p_para_rec.initiator_id;
SELECT party_name
INTO l_recipient_name
FROM HZ_PARTIES
WHERE party_id = p_para_rec.recipient_id;
SELECT meaning
INTO l_batch_status
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'FUN_BATCH_STATUS'
AND view_application_id = 435
AND security_group_id =fnd_global.lookup_security_group(lookup_type,435)
AND language = USERENV('LANG')
AND lookup_code = p_para_rec.batch_status;
SELECT meaning
INTO l_trx_status
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'FUN_TRX_STATUS'
AND view_application_id = 435
AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
AND language = USERENV('LANG')
AND lookup_code = p_para_rec.transaction_status;
SELECT trx_type_name
INTO l_trx_type
FROM FUN_TRX_TYPES_VL
WHERE trx_type_id = p_para_rec.trx_type_id;
SELECT name
INTO l_currency
FROM FND_CURRENCIES_VL
WHERE currency_code = p_para_rec.currency_code;
SELECT meaning
INTO l_batch_status
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'FUN_BATCH_STATUS'
AND view_application_id = 435
AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
AND language = USERENV('LANG')
AND lookup_code = p_para_rec.batch_status;
SELECT meaning
INTO l_trx_status
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'FUN_TRX_STATUS'
AND view_application_id = 435
AND security_group_id=fnd_global.lookup_security_group(lookup_type,435)
AND language = USERENV('LANG')
AND lookup_code = p_para_rec.transaction_status;
SELECT trx_type_name
INTO l_trx_type
FROM FUN_TRX_TYPES_VL
WHERE trx_type_id = p_para_rec.trx_type_id;
SELECT name
INTO l_currency
FROM FND_CURRENCIES_VL
WHERE currency_code = p_para_rec.currency_code;
SELECT meaning
INTO l_acc_type
FROM FND_LOOKUPS
WHERE lookup_code = p_para_rec.account_type
AND lookup_type ='ACCOUNT_TYPE';
SELECT name
INTO l_transact_ledger
FROM gl_ledgers
WHERE ledger_id = p_para_rec.transact_ledger;
SELECT name
INTO l_trading_ledger
FROM gl_ledgers
WHERE ledger_id = p_para_rec.trading_ledger;