[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aid.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
aid.invoice_distribution_id,
aid.po_distribution_id,
aid.dist_code_combination_id,
aid.accounting_date,
aid.amount,
aid.base_amount,
aid.quantity_variance,
aid.base_quantity_variance,
aid.amount_variance,
aid.base_amount_variance,
aid.line_type_lookup_code,
ai.invoice_type_lookup_code,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id,
aid.project_id,
aid.invoice_distribution_id line_number
FROM ap_invoice_distributions_all aid,
xla_events_gt e,
ap_invoices_all ai
WHERE aid.bc_event_id = e.event_id
AND e.application_id = p_application_id
AND e.entity_code = 'AP_INVOICES'
AND aid.line_type_lookup_code NOT IN ('ACCRUAL')
AND ai.invoice_id = aid.invoice_id
AND ((e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
OR
(e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source <> 'Receivables')
OR
(e.budgetary_control_flag = 'N' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source = 'Receivables'))
AND e.event_type_code IN ('INVOICE VALIDATED',
'INVOICE CANCELLED',
'INVOICE ADJUSTED',
'CREDIT MEMO VALIDATED',
'CREDIT MEMO CANCELLED',
'CREDIT MEMO ADJUSTED',
'DEBIT MEMO VALIDATED',
'DEBIT MEMO CANCELLED',
'DEBIT MEMO ADJUSTED')
UNION
SELECT asat.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
asat.invoice_distribution_id,
asat.po_distribution_id,
asat.dist_code_combination_id,
asat.accounting_date,
asat.amount,
asat.base_amount,
asat.quantity_variance,
asat.base_quantity_variance,
asat.amount_variance,
asat.base_amount_variance,
asat.line_type_lookup_code,
ai.invoice_type_lookup_code,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id,
asat.project_id,
asat.invoice_distribution_id line_number
FROM ap_self_assessed_tax_dist_all asat,
xla_events_gt e,
ap_invoices_all ai
WHERE asat.bc_event_id = e.event_id
AND e.application_id = p_application_id
AND e.entity_code = 'AP_INVOICES'
AND asat.line_type_lookup_code NOT IN ('ACCRUAL')
AND ai.invoice_id = asat.invoice_id
AND ((e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
OR
(e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source <> 'Receivables')
OR
(e.budgetary_control_flag = 'N' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source = 'Receivables'))
AND e.event_type_code IN ('INVOICE VALIDATED',
'INVOICE CANCELLED',
'INVOICE ADJUSTED',
'CREDIT MEMO VALIDATED',
'CREDIT MEMO CANCELLED',
'CREDIT MEMO ADJUSTED',
'DEBIT MEMO VALIDATED',
'DEBIT MEMO CANCELLED',
'DEBIT MEMO ADJUSTED')
UNION
SELECT aid.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
aid.invoice_distribution_id,
aid.po_distribution_id,
aid.dist_code_combination_id,
aid.accounting_date,
apad.amount,
apad.base_amount,
aid.quantity_variance,
aid.base_quantity_variance,
aid.amount_variance,
aid.base_amount_variance,
aid.line_type_lookup_code,
ai.invoice_type_lookup_code,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id,
aid.project_id,
apad.prepay_app_dist_id line_number
FROM ap_invoice_distributions_all aid,
ap_prepay_app_dists apad,
xla_events_gt e,
ap_invoices_all ai
WHERE aid.bc_event_id = e.event_id
AND e.application_id = p_application_id
AND e.entity_code = 'AP_INVOICES'
AND aid.line_type_lookup_code NOT IN ('ACCRUAL')
AND ai.invoice_id = aid.invoice_id
AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
AND ((e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
OR
(e.budgetary_control_flag = 'Y' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source <> 'Receivables')
OR
(e.budgetary_control_flag = 'N' AND
ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
ai.source = 'Receivables'))
AND e.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
ORDER BY 1, 2;
trace(C_STATE_LEVEL, l_procedure_name, 'Selecting from po_distributions_all');
SELECT pod.code_combination_id
INTO l_po_code_combination_id
FROM po_distributions_all pod
WHERE pod.po_distribution_id = invoice_rec.po_distribution_id;
trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_po_distributions_all:'||p_error_desc);
SELECT ai.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
aphd.payment_hist_dist_id,
aid.dist_code_combination_id,
aph.accounting_date,
aphd.amount,
aphd.paid_base_amount,
aphd.invoice_dist_amount,
aphd.invoice_dist_base_amount,
ai.invoice_type_lookup_code,
aid.po_distribution_id,
aphd.pay_dist_lookup_code,
aca.payment_type_flag,
aca.ce_bank_acct_use_id,
aca.org_id,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id
FROM ap_checks_all aca,
ap_payment_hist_dists aphd,
ap_invoice_distributions_all aid,
ap_payment_history_all aph,
xla_events_gt e,
ap_invoices_all ai
WHERE aphd.accounting_event_id = e.event_id
AND e.application_id = p_application_id
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND aphd.payment_history_id = aph.payment_history_id
AND aid.invoice_id = ai.invoice_id
AND aph.check_id = aca.check_id
-- AND (aca.payment_type_flag IN ('M', 'N', 'Q', 'R') OR
-- (aca.payment_type_flag IN ('A') AND
-- aphd.pay_dist_lookup_code <> 'CASH') OR
-- ai.invoice_type_lookup_code = 'INTEREST')
AND aphd.pay_dist_lookup_code <> 'EXCHANGE RATE VARIANCE'
AND e.entity_code = 'AP_PAYMENTS'
AND e.event_type_code IN ('MANUAL PAYMENT ADJUSTED',
'PAYMENT ADJUSTED',
'PAYMENT CANCELLED',
'PAYMENT CREATED',
'REFUND RECORDED',
'REFUND CANCELLED',
'REFUND ADJUSTED')
UNION
SELECT ai.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
aphd.payment_hist_dist_id,
aid.dist_code_combination_id,
aph.accounting_date,
DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.amount amount,
DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.paid_base_amount paid_base_amount,
DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.invoice_dist_amount invoice_dist_amount,
DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.invoice_dist_base_amount invoice_dist_base_amount,
ai.invoice_type_lookup_code,
aid.po_distribution_id,
aphd.pay_dist_lookup_code,
aca.payment_type_flag,
aca.ce_bank_acct_use_id,
aca.org_id,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id
FROM ap_payment_hist_dists aphd,
ap_invoice_distributions_all aid,
ap_payment_history_all aph,
xla_events_gt e,
ap_invoices_all ai,
fv_treasury_confirmations_all ftc,
ap_checks_all aca
WHERE ftc.event_id = e.event_id
AND aca.payment_instruction_id = ftc.payment_instruction_id
AND aph.check_id = aca.check_id
AND aca.check_id NOT IN (SELECT check_id
FROM fv_voided_checks fvc
WHERE fvc.payment_instruction_id = aca.payment_instruction_id)
AND e.application_id = p_application_id
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND aphd.payment_history_id = aph.payment_history_id
AND aid.invoice_id = ai.invoice_id
AND aca.payment_type_flag IN ('A')
AND aphd.pay_dist_lookup_code = 'CASH'
AND e.entity_code = 'TREASURY_CONFIRMATION'
AND e.event_type_code IN ('TREASURY_CONFIRM', 'TREASURY_BACKOUT')
AND aca.status_lookup_code NOT IN ('VOIDED')
UNION
SELECT ai.invoice_id,
e.event_id,
e.event_type_code,
e.ledger_id,
e.entity_code,
aphd.payment_hist_dist_id,
aid.dist_code_combination_id,
aph.accounting_date,
aphd.amount amount,
aphd.paid_base_amount paid_base_amount,
aphd.invoice_dist_amount invoice_dist_amount,
aphd.invoice_dist_base_amount invoice_dist_base_amount,
ai.invoice_type_lookup_code,
aid.po_distribution_id,
aphd.pay_dist_lookup_code,
aca.payment_type_flag,
aca.ce_bank_acct_use_id,
aca.org_id,
ai.source,
ai.application_id ref_application_id,
ai.product_table ref_product_table,
ai.reference_key1 ref_key1,
ai.reference_key2 ref_key2,
ai.vendor_id
FROM ap_payment_hist_dists aphd,
ap_invoice_distributions_all aid,
ap_payment_history_all aph,
xla_events_gt e,
ap_invoices_all ai,
fv_voided_checks fvc,
ap_checks_all aca
WHERE fvc.event_id = e.event_id
AND aca.payment_instruction_id = fvc.payment_instruction_id
AND fvc.check_id = aca.check_id
AND aph.check_id = aca.check_id
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND aphd.payment_history_id = aph.payment_history_id
AND aid.invoice_id = ai.invoice_id
AND aca.payment_type_flag IN ('A')
AND aphd.pay_dist_lookup_code = 'CASH'
AND aca.status_lookup_code IN ('VOIDED')
AND aph.transaction_type='PAYMENT CANCELLED'
AND e.application_id = p_application_id
AND e.entity_code = 'TREASURY_CONFIRMATION'
AND e.event_type_code IN ('TREASURY_VOID')
ORDER BY 1;
INSERT INTO fv_extract_detail_gt VALUES l_fv_extract_detail(l_index);