[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_id INTO l_application_id
FROM psa_bc_application_v
WHERE application_short_name = p_application_short_name;
SELECT event_id,
packet_id,
source_distribution_type,
source_distribution_id_num_1,
source_distribution_id_num_2,
source_distribution_id_num_3,
source_distribution_id_num_4,
source_distribution_id_num_5
FROM psa_bc_report_events_gt;
SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = p_ledgerid;
SELECT period_num
FROM gl_period_statuses ps
WHERE ps.ledger_id = p_para_rec.ledger_id AND
ps.application_id = decode(p_para_rec.application_id,
200, 200,
201, 201,
275, 275,
101) AND
ps.period_name = p_period_name;
SELECT sum(decode(event_class_code, 'INVOICES', 1, 0)) INVOICES,
sum(decode(event_class_code, 'CREDIT MEMOS', 1, 0)) CREDIT_MEMOS,
sum(decode(event_class_code, 'DEBIT MEMOS', 1, 0)) DEBIT_MEMOS,
sum(decode(event_class_code, 'PREPAYMENTS', 1, 0)) PREPAYMENTS,
sum(decode(event_class_code, 'PREPAYMENT APPLICATIONS', 1, 0)) PREPAYMENT_APPS,
sum(decode(event_class_code, 'PO_PA', 1, 0)) PO_PA,
sum(decode(event_class_code, 'REQUISITION', 1, 0)) REQ,
sum(decode(event_class_code, 'RELEASE', 1, 0)) RELEASE
FROM ( SELECT distinct xdl.event_class_code
FROM psa_bc_report_events_gt psagt,
xla_distribution_links xdl
WHERE xdl.application_id = p_para_rec.application_id AND
xdl.event_id = psagt.event_id
);
SELECT
application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_num =
(
SELECT
chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id
)
AND id_flex_code = 'GL#'
AND application_id = 101
AND enabled_flag = 'Y';
SELECT PBA.APPLICATION_ID,
PBA.APPLICATION_SHORT_NAME,
PBA.APPLICATION_NAME,
PAE.EVENT_ID,
PAE.LEDGER_ID,
NULL,
NULL,
PS.PERIOD_NAME GL_PERIOD_NAME,
NULL,
NULL BUDGET_TYPE,
NULL,
NULL,
NULL JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
NULL,
NULL TREASURY_SYMBOL,
NULL,
NULL,
NULL,
NULL JOURNAL_LINE_NUMBER,
NULL CCID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL BALANCE_TYPE,
NULL,
NULL,
NULL,
NULL,
NULL CURRENCY,
'X' FUNDS_CHECK_STATUS_CODE,
'Accounting could not be created' FUNDS_CHECK_STATUS,
NULL,
NULL,
NULL FUNDS_CHECK_LEVEL_MEANING,
NULL,
PAE.ENCODED_MESSAGE RESULT_MESSAGE,
NULL,
NULL BOUNDARY,
NULL DEBIT_CREDIT_INDICATOR,
NULL AMOUNT,
NULL DEBIT_AMOUNT_ACCOUNTED,
NULL CREDT_AMOUNT_ACCOUNTED,
NULL,
NULL,
NULL,
NULL,
NULL ENCUMBRANCE_POSTED_BALANCE,
NULL ENCUMBRANCE_APPROVED_BALANCE,
NULL ENCUMBRANCE_PENDING_BALANCE,
NULL ENCUMBRANCE_TOTAL_BALANCE,
NULL,
NULL,
NULL,
NULL,
NULL AVAILABLE_POSTED_BALANCE,
NULL AVAILABLE_APPROVED_BALANCE,
NULL AVAILABLE_PENDING_BALANCE,
NULL AVAILABLE_TOTAL_BALANCE,
NULL SOURCE_DISTRIBUTION_TYPE,
NULL,
NULL,
NULL,
NULL,
NULL,
PAE.DOCUMENT_REFERENCE DOCUMENT_REFERENCE,
PAE.LINE_REFERENCE LINE_REFERENCE,
NULL,
NULL,
NULL,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
NULL ACCOUNTING_FLEXFIELD,
NULL SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
NULL,
NULL,
PS.PERIOD_NUM,
PS.PERIOD_YEAR,
PS.QUARTER_NUM,
NULL COMM_ENCUM_POSTED_BAL,
NULL COMM_ENCUM_APPROVED_BAL,
NULL COMM_ENCUM_PENDING_BAL,
NULL COMM_ENCUM_TOTAL_BAL,
NULL OBLI_ENCUM_POSTED_BAL,
NULL OBLI_ENCUM_APPROVED_BAL,
NULL OBLI_ENCUM_PENDING_BAL,
NULL OBLI_ENCUM_TOTAL_BAL,
'X' ERROR_SOURCE,
NULL CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
FROM PSA_BC_ACCOUNTING_ERRORS PAE,
PSA_BC_APPLICATION_V PBA,
GL_PERIOD_STATUSES PS
WHERE PBA.application_id = p_para_rec.application_id AND
PAE.ledger_id = p_para_rec.ledger_id AND
PS.ledger_id = p_para_rec.ledger_id AND
PS.application_id = p_para_rec.application_id AND
(PAE.transaction_date between PS.start_date AND PS.end_date) AND
NVL(PAE.message_name, 'X') NOT IN
('XLA_BC_FAILED_HDR', 'XLA_BC_FAILED_LINE', 'XLA_BC_PARTIAL_HDR',
'XLA_BC_PARTIAL_LINE', 'XLA_BC_ADVISORY_HDR', 'XLA_BC_ADVISORY_LINE') AND
((x_source = 'FORM' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PAE.event_id))
OR (x_source = 'CP' AND PS.period_num BETWEEN l_period_start_num AND l_period_end_num));
SELECT min('Y')
FROM (SELECT packet_id
FROM gl_bc_packets
WHERE template_id IS NOT NULL
UNION ALL
SELECT packet_id
FROM gl_bc_packets_hists
WHERE template_id IS NOT NULL) bc
WHERE bc.packet_id IN (SELECT packet_id FROM psa_bc_results_gt);
SELECT application_name,
batch_reference,
document_reference,
vendor_name,
vendor_site_name,
sum(decode(funds_check_status_code, 'A', 1, 0)) approved_count,
sum(decode(funds_check_status_code, 'R', 1, 0)) reject_count,
sum(decode(funds_check_status_code, 'S', 1, 0)) success_count,
sum(decode(funds_check_status_code, 'F', 1, 0)) fail_count,
sum(decode(funds_check_status_code, 'T', 1, 0)) fatal_count,
sum(decode(funds_check_status_code, 'C', 1, 0)) checking_count,
sum(decode(funds_check_status_code, 'P', 1, 0)) reserving_count,
count(*) total_count
FROM psa_bc_results_gt
GROUP BY application_name, batch_reference, document_reference, vendor_name, vendor_site_name;
SELECT treasury_symbol
INTO l_treasury_symbol
FROM fv_treasury_symbols
WHERE treasury_symbol_id in (SELECT treasury_symbol_id
FROM fv_fund_parameters
WHERE fund_value = l_fund_value
AND set_of_books_id = p_para_rec.ledger_id)
AND set_of_books_id = p_para_rec.ledger_id;
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
'Summary' JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PBRV.HEADER_NAME DOCUMENT_REFERENCE,
TO_CHAR(PBRV.JE_LINE_NUM) LINE_REFERENCE,
PBRV.BATCH_NAME BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
JH.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
GL_JE_HEADERS JH
WHERE PBRV.je_header_id = JH.je_header_id(+) AND
PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = 101 AND
PBRV.template_id IS NULL AND
((x_source = 'FORM' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
AI.INVOICE_NUM DOCUMENT_REFERENCE,
AIL.LINE_NUMBER||' - '||
AID.DISTRIBUTION_LINE_NUMBER LINE_REFERENCE,
APB.BATCH_NAME BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
PV.VENDOR_NAME VENDOR_NAME,
PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#',l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
AIL.LINE_NUMBER INV_LINE_NUMBER,
AI.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
AP_INVOICE_DISTRIBUTIONS AID,
AP_BATCHES APB,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES AI,
AP_INVOICE_LINES AIL,
PO_VENDORS PV,
PO_VENDOR_SITES PVS
WHERE PBRV.source_distribution_id_num_1 = AID.invoice_distribution_id AND
AID.invoice_id = AI.invoice_id AND
AID.batch_id = APB.batch_id(+) AND
AI.vendor_id = PV.vendor_id(+) AND
AI.vendor_site_id = PVS.vendor_site_id(+) AND
AID.invoice_id = AIL.invoice_id AND
AID.invoice_line_number = AIL.line_number AND
PBRV.event_id = XDL.event_id AND
PBRV.application_id = XDL.application_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code IN ('INVOICES', 'CREDIT MEMOS', 'DEBIT MEMOS', 'PREPAYMENTS') AND
PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = 200 AND
AIL.line_type_lookup_code IN ('ITEM','FREIGHT','MISCELLANEOUS','PREPAY') AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
NULL JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
AI.INVOICE_NUM DOCUMENT_REFERENCE,
TO_CHAR(AIL.LINE_NUMBER) LINE_REFERENCE,
APB.BATCH_NAME BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
PV.VENDOR_NAME VENDOR_NAME,
PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
AIL.LINE_NUMBER INV_LINE_NUMBER,
AI.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
AP_INVOICE_DISTRIBUTIONS AID,
AP_PREPAY_APP_DISTS APD,
AP_BATCHES APB,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES AI,
AP_INVOICE_LINES AIL,
PO_VENDORS PV,
PO_VENDOR_SITES PVS
WHERE PBRV.source_distribution_id_num_1 = APD.prepay_app_distribution_id AND
AID.invoice_distribution_id = APD.invoice_distribution_id AND
AID.invoice_id = AI.invoice_id AND
AID.batch_id = APB.batch_id(+) AND
AI.vendor_id = PV.vendor_id(+) AND
AI.vendor_site_id = PVS.vendor_site_id(+) AND
AI.invoice_id = AIL.invoice_id AND
PBRV.application_id = XDL.application_id AND
PBRV.event_id = XDL.event_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code IN ('PREPAYMENT APPLICATIONS') AND
PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = 200 AND
AIL.line_type_lookup_code IN ('ITEM','FREIGHT','MISCELLANEOUS','PREPAY') AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
NULL JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PH.SEGMENT1 DOCUMENT_REFERENCE,
PL.LINE_NUM||'-'||
PLL.SHIPMENT_NUM||'-'||
PD.DISTRIBUTION_NUM LINE_REFERENCE,
NULL BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
PV.VENDOR_NAME VENDOR_NAME,
PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
PL.LINE_NUM PO_LINE_NUMBER,
PD.DISTRIBUTION_NUM PO_DIST_LINE_NUMBER,
PLL.SHIPMENT_NUM PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
PO_BC_DISTRIBUTIONS PBD,
XLA_DISTRIBUTION_LINKS XDL,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
PO_HEADERS PH,
PO_LINES PL,
PO_DISTRIBUTIONS PD,
PO_LINE_LOCATIONS PLL
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
PBRV.event_id = PBD.ae_event_id AND
PBRV.application_id = XDL.application_id AND
PBRV.event_id = XDL.event_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code IN ('PO_PA') AND
PBD.header_id = PH.po_header_id AND
PH.vendor_id = PV.vendor_id(+) AND
PH.vendor_site_id = PVS.vendor_site_id(+) AND
PBD.distribution_id = PD.po_distribution_id AND
PD.po_line_id = PL.po_line_id(+) AND
PD.line_location_id = PLL.line_location_id(+) AND
PBRV.application_id = 201 AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
NULL JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PRH.SEGMENT1 DOCUMENT_REFERENCE,
PRL.LINE_NUM||'-'||
PRD.DISTRIBUTION_NUM LINE_REFERENCE,
NULL BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
PV.VENDOR_NAME VENDOR_NAME,
PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
PRL.LINE_NUM REQ_LINE_NUMBER,
PRD.DISTRIBUTION_NUM REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
PO_BC_DISTRIBUTIONS PBD,
XLA_DISTRIBUTION_LINKS XDL,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
PO_REQUISITION_HEADERS PRH,
PO_REQUISITION_LINES PRL,
PO_REQ_DISTRIBUTIONS PRD
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
PBRV.event_id = PBD.ae_event_id AND
PBRV.application_id = XDL.application_id AND
PBRV.event_id = XDL.event_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code IN ('PO_PA','REQUISITION') AND
PBD.header_id = PRH.requisition_header_id AND
PRL.vendor_id = PV.vendor_id(+) AND
PRL.vendor_site_id = PVS.vendor_site_id(+) AND
PBD.distribution_id = PRD.distribution_id AND
PRD.requisition_line_id = PRL.requisition_line_id AND
PBRV.application_id = 201 AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
NULL JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
DECODE(PBD.MAIN_OR_BACKING_CODE,
'M', PH.SEGMENT1 || '-'|| TO_CHAR(PR.RELEASE_NUM),
PH.SEGMENT1) DOCUMENT_REFERENCE,
CASE WHEN (PBD.MAIN_OR_BACKING_CODE = 'M') THEN
PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM ||'-'||PD.DISTRIBUTION_NUM
WHEN (PBD.MAIN_OR_BACKING_CODE <> 'M') AND (PBD.DISTRIBUTION_TYPE = 'PLANNED') THEN
PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM ||'-'||PD.DISTRIBUTION_NUM
ELSE
NULL
END CASE,
NULL BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
PV.VENDOR_NAME VENDOR_NAME,
PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
PO_BC_DISTRIBUTIONS PBD,
XLA_DISTRIBUTION_LINKS XDL,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
PO_HEADERS PH,
PO_RELEASES PR,
PO_LINES PL,
PO_DISTRIBUTIONS PD,
PO_LINE_LOCATIONS PLL
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
decode(pbd.distribution_type,
'REQUISITION', 'PO_REQ_DISTRIBUTIONS_ALL',
'PO_DISTRIBUTIONS_ALL') = xdl.source_distribution_type AND
pbd.ae_event_id = xdl.event_id AND
NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) = XDL.ALLOC_TO_DIST_ID_NUM_1 AND
PBRV.event_id = PBD.ae_event_id AND
PBRV.application_id = XDL.application_id AND
PBRV.event_id = XDL.event_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code IN ('RELEASE') AND
PBD.po_release_id = PR.po_release_id(+) AND
PBD.header_id = PH.po_header_id AND
PH.vendor_id = PV.vendor_id(+) AND
PH.vendor_site_id = PVS.vendor_site_id(+) AND
PBD.distribution_id = PD.po_distribution_id AND
PD.po_line_id = PL.po_line_id(+) AND
PD.line_location_id = PLL.line_location_id(+) AND
PBRV.application_id = 201 AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
text_line,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
segment1,
CASE WHEN (DISTRIBUTION_TYPE='STANDARD') OR
(DISTRIBUTION_TYPE='PLANNED') OR
(DISTRIBUTION_TYPE='AGREEMENT') THEN
line_num||'-'||shipment_num||'-'||distribution_num
WHEN (DISTRIBUTION_TYPE='REQUISITION')THEN
line_num||'-'||distribution_num
ELSE
TO_CHAR(line_num)
END CASE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'O',
NULL,
NULL
bulk collect INTO l_dump
FROM po_online_report_text
WHERE online_report_id = PO_DOCUMENT_FUNDS_GRP.get_online_report_id
AND nvl(show_in_psa_flag, 'N') = 'Y';
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
'Federal Budget' BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
PBRV.JE_CATEGORY_NAME BUDGET_LEVEL,
PBRV.BATCH_NAME,
PBRV.JE_BATCH_NAME TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
NULL JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
DECODE(XLADIST.event_class_code,
'BUDGET_EXECUTION', BE.doc_number,
'RPR_BUDGET_EXECUTION',
RPR.doc_number) DOCUMENT_REFERENCE,
BET.REVISION_NUM LINE_REFERENCE,
NULL BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
XLA_DISTRIBUTION_LINKS XLADIST,
FV_BE_TRX_HDRS BE,
FV_BE_TRX_DTLS BET,
FV_BE_RPR_TRANSACTIONS RPR
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = 8901 AND
PBRV.application_id = XLADIST.application_id AND
PBRV.source_distribution_id_num_1 = XLADIST.source_distribution_id_num_1 AND
PBRV.source_distribution_type = XLADIST.source_distribution_type AND
PBRV.ae_header_id = XLADIST.ae_header_id AND
PBRV.ae_line_num = XLADIST.ae_line_num AND
PBRV.source_distribution_id_num_1 = BET.transaction_id (+) AND
BE.doc_id = BET.doc_id AND
PBRV.source_distribution_id_num_1 = RPR.transaction_id (+) AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_1 DOCUMENT_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_2 LINE_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_3 BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
PBRV.REFERENCE1 PARTY_ID,
PBRV.REFERENCE2 PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
DECODE(PBRV.TEMPLATE_ID,
NULL, 'N', 'Y') SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = p_para_rec.application_id AND
((x_source = 'FORM' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_1 DOCUMENT_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_2 LINE_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_3 BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
DECODE(PBRV.TEMPLATE_ID,
NULL, 'N', 'Y') SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV
WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = p_para_rec.application_id AND
((x_source = 'FORM' AND
(p_para_rec.packet_event_flag = 'P' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
(p_para_rec.packet_event_flag = 'E' AND
EXISTS (SELECT 'x'
FROM PSA_BC_REPORT_EVENTS_GT PSAGT
WHERE PSAGT.event_id = PBRV.event_id AND
NVL(PSAGT.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
NVL(PSAGT.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
NVL(PSAGT.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
NVL(PSAGT.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
NVL(PSAGT.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0))
)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
select EVENT_ID
into l_event_id
from gl_bc_packets
where packet_id in (select packet_id from psa_bc_report_events_gt)
and template_id is NULL
and rownum = 1;
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
PBRV.HEADER_NAME DOCUMENT_REFERENCE, -- Bug 5579424
TO_CHAR(PBRV.AE_HEADER_ID) || '-' ||
TO_CHAR(PBRV.AE_LINE_NUM) LINE_REFERENCE,
PBRV.BATCH_NAME BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV
WHERE PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
and template_id is NULL;
SELECT PBRV.APPLICATION_ID,
PBRV.APPLICATION_SHORT_NAME,
PBRV.APPLICATION_NAME,
PBRV.EVENT_ID,
PBRV.LEDGER_ID,
PBRV.ROW_ID,
PBRV.PACKET_ID,
PBRV.PERIOD_NAME GL_PERIOD_NAME,
PBRV.FUNDING_BUDGET_NAME,
NULL BUDGET_TYPE,
PBRV.BUDGET_VERSION_ID,
PBRV.JE_SOURCE_NAME,
PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
NULL BUDGET_LEVEL,
PBRV.BATCH_NAME,
NULL TREASURY_SYMBOL,
PBRV.JE_BATCH_ID,
PBRV.JE_HEADER_ID,
PBRV.HEADER_NAME,
PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
PBRV.CODE_COMBINATION_ID CCID,
PBRV.USSGL_TRANSACTION_CODE,
PBRV.ACCOUNT_TYPE,
PBRV.ACCOUNT_TYPE_MEANING,
PBRV.ACCOUNT_CATEGORY_CODE,
PBRV.ACCOUNT_SEGMENT_VALUE,
PBRV.ACTUAL_FLAG,
PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
PBRV.AMOUNT_TYPE,
PBRV.AMOUNT_TYPE_MEANING,
PBRV.ENCUMBRANCE_TYPE,
PBRV.TEMPLATE_ID,
PBRV.CURRENCY_CODE CURRENCY,
PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
PBRV.EFFECTIVE_STATUS,
PBRV.FUNDS_CHECK_LEVEL_CODE,
PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
PBRV.RESULT_CODE,
PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
PBRV.BOUNDARY_CODE,
PBRV.BOUNDARY_MEANING BOUNDARY,
PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
PBRV.TRANSACTION_AMOUNT AMOUNT,
PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
PBRV.BUDGET_POSTED_BALANCE,
PBRV.BUDGET_APPROVED_BALANCE,
PBRV.BUDGET_PENDING_BALANCE,
PBRV.BUDGET_TOTAL_BALANCE,
PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
PBRV.ACTUAL_POSTED_BALANCE,
PBRV.ACTUAL_APPROVED_BALANCE,
PBRV.ACTUAL_PENDING_BALANCE,
PBRV.ACTUAL_TOTAL_BALANCE,
PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
XAH.DESCRIPTION DOCUMENT_REFERENCE, -- Bug 5579424
TO_CHAR(PBRV.AE_HEADER_ID) || '-' ||
TO_CHAR(PBRV.AE_LINE_NUM) LINE_REFERENCE,
PBRV.BATCH_NAME BATCH_REFERENCE,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
NULL PARTY_ID,
NULL PARTY_SITE_ID,
NULL VENDOR_NAME,
NULL VENDOR_SITE_NAME,
NULL PAYMENT_FORECAST_LINE_NUMBER,
NULL PA_FLAG,
FND_FLEX_EXT.GET_SEGS('SQLGL',
'GL#', l_coaid,
PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
'N' SUMMARY_ACCOUNT_INDICATOR,
NULL PO_LINE_NUMBER,
NULL PO_DIST_LINE_NUMBER,
NULL PO_SHIP_LINE_NUMBER,
NULL REQ_LINE_NUMBER,
NULL REQ_DIST_LINE_NUMBER,
NULL INV_LINE_NUMBER,
NULL DOCUMENT_SEQUENCE_NUMBER,
PBRV.AE_HEADER_ID,
PBRV.AE_LINE_NUM,
PBRV.PERIOD_NUM,
PBRV.PERIOD_YEAR,
PBRV.QUARTER_NUM,
PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
'O' ERROR_SOURCE,
PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
NULL DOCUMENT_STATUS
BULK COLLECT INTO l_dump
FROM PSA_BC_REPORT_V PBRV,
XLA_AE_HEADERS XAH,
XLA_DISTRIBUTION_LINKS XDL
WHERE PBRV.application_id = XDL.application_id AND
PBRV.event_id = XDL.event_id AND
PBRV.ae_header_id = XDL.ae_header_id AND
PBRV.ae_line_num = XDL.ae_line_num AND
XDL.event_class_code = 'MANUAL' AND
PBRV.application_id = XAH.application_id AND
PBRV.ae_header_id = XAH.ae_header_id AND
PBRV.ledger_id = p_para_rec.ledger_id AND
PBRV.application_id = p_para_rec.application_id AND
((x_source = 'FORM' AND
PBRV.packet_id IN (SELECT packet_id FROM PSA_BC_REPORT_EVENTS_GT)
)
OR
((x_source = 'CP') AND (PBRV.period_num BETWEEN l_period_start_num AND l_period_end_num)));
DELETE from PSA_BC_RESULTS_GT;
INSERT INTO PSA_BC_RESULTS_GT
VALUES l_bc_results_rpt(i);
DELETE PSA_BC_RESULTS_GT GT1
WHERE (GT1.batch_reference, GT1.document_reference , GT1.line_reference, GT1.packet_id) NOT IN
(SELECT GT2.batch_reference, GT2.document_reference, GT2.line_reference, max(GT2.packet_id)
FROM PSA_BC_RESULTS_GT GT2
GROUP BY GT2.batch_reference, GT2.document_reference, GT2.line_reference);
psa_utils.debug_other_string(g_state_level,l_api_name,'Deleted '||sql%rowcount||' rows from psa_bc_results_gt.');
SELECT MIN(PBRG.APPLICATION_ID),
MIN(PBRG.APPLICATION_SHORT_NAME),
MIN(PBRG.APPLICATION_NAME),
MIN(PBRG.EVENT_ID),
MIN(PBRG.LEDGER_ID),
MIN(PBRG.ROW_ID),
MIN(PBRG.PACKET_ID),
MIN(PBRV.PERIOD_NAME),
MIN(BV.BUDGET_NAME),
MIN(BV.BUDGET_TYPE),
MIN(PBRV.BUDGET_VERSION_ID),
MIN(PBRV.JE_SOURCE_NAME),
MIN(PBRV.JE_CATEGORY_NAME),
MIN(PBRG.BUDGET_LEVEL),
MIN(PBRG.JE_BATCH_NAME),
NULL,
MIN(PBRG.JE_BATCH_ID),
MIN(PBRG.JE_HEADER_ID),
MIN(PBRG.JE_HEADER_NAME),
'Summary',
AH.SUMMARY_CODE_COMBINATION_ID,
NULL,
MIN(PBRG.ACCOUNT_TYPE),
MIN(PBRG.ACCOUNT_TYPE_MEANING),
MIN(PBRV.ACCOUNT_CATEGORY_CODE),
MIN(PBRV.ACCOUNT_SEGMENT_VALUE),
MIN(PBRV.ACTUAL_FLAG),
MIN(PBRV.ACTUAL_FLAG_MEANING),
MIN(PBRV.AMOUNT_TYPE),
MIN(PBRV.AMOUNT_TYPE_MEANING),
MIN(PBRV.ENCUMBRANCE_TYPE),
MIN(SB.TEMPLATE_ID),
MIN(PBRV.CURRENCY_CODE),
MIN(PBRV.STATUS_CODE),
MIN(PBRV.STATUS_CODE_MEANING),
MIN(PBRV.EFFECTIVE_STATUS),
MIN(PBRV.FUNDS_CHECK_LEVEL_CODE),
MIN(PBRV.LEVEL_MEANING),
MIN(PBRV.RESULT_CODE),
MIN(PBRV.RESULT_CODE_MEANING),
MIN(PBRV.BOUNDARY_CODE),
MIN(PBRV.BOUNDARY_MEANING),
MIN(PBRV.DR_CR_CODE),
MIN(PBRV.TRANSACTION_AMOUNT),
MIN(PBRV.ACCOUNTED_DR),
MIN(PBRV.ACCOUNTED_CR),
MIN(PBRV.BUDGET_POSTED_BALANCE),
MIN(PBRV.BUDGET_APPROVED_BALANCE),
MIN(PBRV.BUDGET_PENDING_BALANCE),
MIN(PBRV.BUDGET_TOTAL_BALANCE),
MIN(PBRV.ENC_POSTED_BALANCE),
MIN(PBRV.ENC_APPROVED_BALANCE),
MIN(PBRV.ENC_PENDING_BALANCE),
MIN(PBRV.ENC_TOTAL_BALANCE),
MIN(PBRV.ACTUAL_POSTED_BALANCE),
MIN(PBRV.ACTUAL_APPROVED_BALANCE),
MIN(PBRV.ACTUAL_PENDING_BALANCE),
MIN(PBRV.ACTUAL_TOTAL_BALANCE),
MIN(PBRV.AVAIL_POSTED_BALANCE),
MIN(PBRV.AVAIL_APPROVED_BALANCE),
MIN(PBRV.AVAIL_PENDING_BALANCE),
MIN(PBRV.AVAIL_TOTAL_BALANCE),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
PBRG.DOCUMENT_REFERENCE,
'Summary',
PBRG.BATCH_REFERENCE,
NULL,
NULL,
MIN(PBRG.PARTY_ID),
MIN(PBRG.PARTY_SITE_ID),
PBRG.VENDOR_NAME,
PBRG.VENDOR_SITE_NAME,
NULL,
MIN(PBRG.PA_FLAG),
MIN(FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', l_coaid, pbrv.code_combination_id)),
'Y',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
MIN(PBRV.PERIOD_NUM),
MIN(PBRV.PERIOD_YEAR),
MIN(PBRV.QUARTER_NUM),
MIN(PBRV.COMM_ENC_POSTED_BAL),
MIN(PBRV.COMM_ENC_APPROVED_BAL),
MIN(PBRV.COMM_ENC_PENDING_BAL),
MIN(PBRV.COMM_ENC_TOTAL_BAL),
MIN(PBRV.OBLI_ENC_POSTED_BAL),
MIN(PBRV.OBLI_ENC_APPROVED_BAL),
MIN(PBRV.OBLI_ENC_PENDING_BAL),
MIN(PBRV.OBLI_ENC_TOTAL_BAL),
MIN(PBRG.ERROR_SOURCE),
MIN(PBRV.CURRENT_FUNDS_AVAILABLE),
NULL
BULK COLLECT INTO l_sum_dump
FROM PSA_BC_RESULTS_GT PBRG,
GL_PERIOD_STATUSES PS,
GL_SUMMARY_TEMPLATES ST,
GL_ACCOUNT_HIERARCHIES AH,
GL_BUDGETS B,
GL_BUDGET_VERSIONS BV,
GL_SUMMARY_BC_OPTIONS SB,
GL_PERIOD_STATUSES PS2,
PSA_BC_REPORT_V PBRV
WHERE pbrg.ccid IS NOT NULL
AND ah.ledger_id = p_para_rec.ledger_id
AND ah.detail_code_combination_id = PBRG.CCID
AND ps2.ledger_id = p_para_rec.ledger_id
AND ps2.application_id = 101
AND PS2.period_name = pbrg.period_name
AND PS2.start_date >= (SELECT P1.start_date
FROM GL_PERIOD_STATUSES P1
WHERE P1.application_id = ps2.application_id
AND P1.ledger_id = ps2.ledger_id
AND P1.period_name = B.first_valid_period_name)
AND PS2.end_date <= (SELECT P2.end_date
FROM GL_PERIOD_STATUSES P2
WHERE P2.application_id = ps2.application_id
AND P2.ledger_id = ps2.ledger_id
AND P2.period_name = B.last_valid_period_name)
AND st.status = 'F'
AND st.template_id = ah.template_id
AND sb.funding_budget_version_id = BV.budget_version_id
AND st.account_category_code = decode(pbrg.account_type, 'D', 'B', 'C', 'B', 'P')
AND ps.ledger_id = p_para_rec.ledger_id
AND ps.application_id = 101
AND ps.period_name = st.start_actuals_period_name
AND (ps.period_year * 10000 + ps.period_num) <=
(pbrg.period_year * 10000 + pbrg.period_num)
AND SB.template_id = ST.template_id
AND SB.funding_budget_version_id = BV.budget_version_id
AND BV.budget_name = B.budget_name
AND pbrv.code_combination_id = ah.summary_code_combination_id
AND pbrv.packet_id = pbrg.packet_id
GROUP BY AH.SUMMARY_CODE_COMBINATION_ID, PBRG.DOCUMENT_REFERENCE, PBRG.BATCH_REFERENCE, PBRV.PERIOD_NAME,
PBRG.VENDOR_NAME, PBRG.VENDOR_SITE_NAME;
INSERT INTO PSA_BC_RESULTS_GT
VALUES l_bc_summary_rpt(i);
SELECT description into l_meaning
FROM gl_lookups
WHERE lookup_type = 'FUNDS_CHECK_STATUS_CODE' and
lookup_code = l_document_status;
UPDATE psa_bc_results_gt
SET document_status = l_meaning
WHERE nvl(APPLICATION_NAME, '-99') = nvl(x.application_name, '-99') AND
nvl(batch_reference, '-99') = nvl(x.batch_reference, '-99') AND
nvl(document_reference, '-99') = nvl(x.document_reference, '-99') AND
nvl(vendor_name, '-99') = nvl(x.vendor_name, '-99') AND
nvl(vendor_site_name, '-99') = nvl(x.vendor_site_name, '-99');
UPDATE psa_bc_results_gt rg
SET actual_flag_meaning = (SELECT description
FROM gl_lookups
WHERE lookup_code = rg.actual_flag
AND lookup_type = 'BATCH_TYPE');
UPDATE psa_bc_results_gt rg
SET funds_check_status = (SELECT meaning
FROM gl_lookups
WHERE lookup_code = rg.funds_check_status_code
AND lookup_type = 'FUNDS_CHECK_STATUS_CODE');
UPDATE psa_bc_results_gt rg
SET result_message = (SELECT description
FROM gl_lookups
WHERE lookup_code = rg.result_code
AND lookup_type = 'FUNDS_CHECK_RESULT_CODE')
WHERE result_message IS NULL;
UPDATE psa_bc_results_gt rg
SET amount_type_meaning = (SELECT meaning
FROM gl_lookups
WHERE lookup_code = rg.amount_type
AND lookup_type = 'PTD_YTD');
UPDATE psa_bc_results_gt rg
SET boundary = (SELECT meaning
FROM gl_lookups
WHERE lookup_code = rg.boundary_code
AND lookup_type = 'BOUNDARY_TYPE');
UPDATE psa_bc_results_gt rg
SET funds_check_level_meaning = (SELECT meaning
FROM gl_lookups
WHERE lookup_code = rg.funds_check_level_code
AND lookup_type = 'FUNDS_CHECK_LEVEL');
UPDATE psa_bc_results_gt rg
SET account_type_meaning = (SELECT description
FROM gl_lookups
WHERE lookup_code = rg.account_type
AND lookup_type = 'ACCOUNT TYPE');
x_report_query := 'SELECT PBRG.* FROM psa_bc_results_gt PBRG, GL_CODE_COMBINATIONS GLCC
WHERE PBRG.ccid = GLCC.code_combination_id(+) ';
SELECT FND_FLEX_APIS.get_segment_delimiter(101, 'GL#', p_para_rec.chart_of_accts_id) INTO l_delimiter
FROM DUAL;
SELECT name
INTO l_ledger_name
FROM gl_ledgers
WHERE ledger_id = p_para_rec.ledger_id;
SELECT meaning
INTO l_funds_check_status
FROM PSA_LOOKUP_CODES
WHERE lookup_type = 'PSA_BC_FUNDS_CHECK_STATUS'
AND lookup_code = p_para_rec.bc_funds_check_status;
SELECT meaning
INTO l_funds_check_order_by
FROM PSA_LOOKUP_CODES
WHERE lookup_type = 'PSA_BC_FUNDS_CHECK_ORDER_BY'
AND lookup_code = p_para_rec.bc_funds_check_order_by;
SELECT application_short_name
INTO l_application_short_name
FROM psa_bc_application_v
WHERE application_id = p_para_rec.application_id;
select decode(userenv('LANG') ,'US', 32766 , 16332)
into l_amount from dual;
DELETE FROM psa_bc_xml_clob pbxc
WHERE (((SYSDATE - pbxc.creation_date) > 1) OR
(NOT EXISTS (SELECT 'x'
FROM v$session
WHERE audsid = pbxc.session_id
AND Serial# = pbxc.serial_id)));
'No of rows deleted from psa_bc_xml_clob: '||SQL%ROWCOUNT);
SELECT s.audsid, s.serial#
INTO l_session_id, l_serial_id
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND audsid = USERENV('SESSIONID');
INSERT INTO psa_bc_xml_clob(sequence_id, application_id, session_id, serial_id, creation_date, xml)
VALUES (p_sequence_id, p_application_id, l_session_id, l_serial_id, sysdate, p_trxs);
'No of rows inserted into psa_bc_xml_clob: '||SQL%ROWCOUNT);