[Home] [Help]
1132: xla_distribution_links xld, --BUG#7275286
1133: rcv_transactions rt,
1134: po_headers_all poh
1135: ,cst_accrual_accounts ca --BUG#7528609
1136: ,PO_distributions_all pod
1137: WHERE pawo.org_id = p_operating_unit
1138: AND pawo.transaction_source_code = 'PO'
1139: AND pawo.po_transaction_id IS NOT NULL
1140: AND rrs.rcv_transaction_id = pawo.po_transaction_id
1529: pawo.program_id,
1530: pawo.program_update_date
1531: FROM
1532: po_accrual_write_offs_all pawo,
1533: po_distributions_all pod,
1534: po_releases_all pra,
1535: po_headers_all poh,
1536: ap_invoice_distributions_all aida,
1537: financials_system_params_all fsp,
1697: pawo.program_id,
1698: pawo.program_update_date
1699: FROM
1700: po_accrual_write_offs_all pawo,
1701: po_distributions_all pod,
1702: po_releases_all pra,
1703: po_headers_all poh,
1704: ap_invoice_distributions_all aida,
1705: financials_system_params_all fsp,
2181: OR (xlal.accounting_class_code in ('IPV','EXCHANGE_RATE_VARIANCE','TRV','TIPV','TERV'
2182: ,'ACCRUAL','ITEM EXPENSE'))
2183: OR EXISTS ( SELECT 1
2184: FROM po_releases_all pra,
2185: po_distributions_all pod
2186: WHERE pod.po_distribution_id = aida.po_distribution_id
2187: AND pod.po_release_id IS NOT NULL
2188: AND pra.po_release_id = pod.po_release_id
2189: AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
2189: AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
2190: )
2191: OR EXISTS ( SELECT 1
2192: FROM po_headers_all poh,
2193: po_distributions_all pod
2194: WHERE pod.po_distribution_id = aida.po_distribution_id
2195: AND pod.po_release_id IS NULL
2196: AND poh.po_header_id = pod.po_header_id
2197: AND NVL(poh.consigned_consumption_flag,'N') = 'Y'
2490: xla.ae_line_num
2491: FROM AP_MISC_TRANSACTIONS_XLA xla,
2492: ap_invoice_distributions_all aida,
2493: ap_invoices_all apia,
2494: po_distributions_all pod,
2495: po_lines_all pol,
2496: po_headers_all poh
2497: WHERE xla.source_distribution_id_num_1 = aida.invoice_distribution_id
2498: AND aida.org_id = p_operating_unit
2577: xla.ae_line_num
2578: FROM AP_MISC_TRANSACTIONS_XLA xla,
2579: ap_self_assessed_tax_dist_all aida,
2580: ap_invoices_all apia,
2581: po_distributions_all pod,
2582: po_lines_all pol,
2583: po_headers_all poh
2584: WHERE xla.source_distribution_id_num_1 = aida.invoice_distribution_id
2585: AND aida.org_id = p_operating_unit
3433:
3434: CURSOR c_po_dists is
3435: SELECT /*+ LEADING(rt) USE_NL(rrs,pod) */
3436: DISTINCT pod.po_distribution_id
3437: FROM po_distributions_all pod,
3438: rcv_transactions rt,
3439: rcv_receiving_sub_ledger rrs
3440: WHERE pod.accrual_account_id is NOT NULL
3441: AND pod.accrue_on_receipt_flag = 'Y'
3446: /* start added below sql for OPM Receiving data to insert the AP PO data, pmarada, bug6995413 */
3447: UNION
3448: SELECT /*+ LEADING(rt) USE_NL(grat,pod) */
3449: DISTINCT pod.po_distribution_id
3450: FROM po_distributions_all pod,
3451: rcv_transactions rt,
3452: gmf_rcv_accounting_txns grat
3453: WHERE pod.accrual_account_id is NOT NULL
3454: AND pod.accrue_on_receipt_flag = 'Y'
3460: AND grat.org_id = p_operating_unit
3461: /* End OPM Financials */
3462: UNION
3463: SELECT DISTINCT pod.po_distribution_id
3464: FROM po_distributions_all pod,
3465: rcv_accounting_events rae,
3466: rcv_receiving_sub_ledger rrs
3467: WHERE pod.accrual_account_id is NOT NULL
3468: AND pod.accrue_on_receipt_flag = 'Y'
3480: ----------------------
3481: UNION
3482: SELECT DISTINCT aida.po_distribution_id
3483: FROM ap_invoice_distributions_all aida,
3484: po_distributions_all pod
3485: WHERE pod.accrual_account_id is NOT NULL
3486: AND aida.accounting_date between p_from_date AND p_to_date
3487: AND aida.po_distribution_id is NOT NULL
3488: AND aida.po_distribution_id = pod.po_distribution_id
3510: -- Consig can have po distribution ID stamped but should be excluded from AP PO Load
3511: --FP BUG10406951
3512: SELECT DISTINCT cwo.po_distribution_id
3513: FROM cst_write_offs cwo
3514: , po_distributions_all pod
3515: WHERE cwo.transaction_date between p_from_date AND p_to_date
3516: AND cwo.operating_unit_id = p_operating_unit
3517: AND cwo.po_distribution_id = pod.po_distribution_id
3518: AND NOT EXISTS
3531: AND NVL(poh.consigned_consumption_flag,'N') = 'Y'
3532: );
3533:
3534:
3535: TYPE dists_table is TABLE OF po_distributions_all.po_distribution_id%TYPE;
3536:
3537: po_dists_tab dists_table;
3538:
3539: CURSOR c_opm_count IS
3661: xla_transaction_entities_upg xte,
3662: xla_distribution_links xdl,
3663: po_headers_all poh,
3664: po_lines_all pol,
3665: po_distributions_all pod,
3666: cst_accrual_accounts caa,
3667: financials_system_params_all fsp
3668: WHERE fsp.org_id = p_operating_unit
3669: AND xah.ledger_id = fsp.set_of_books_id
3782: xla_transaction_entities_upg xte,
3783: xla_distribution_links xdl,
3784: po_headers_all poh,
3785: po_lines_all pol,
3786: po_distributions_all pod,
3787: cst_accrual_accounts caa,
3788: financials_system_params_all fsp
3789: WHERE fsp.org_id = p_operating_unit
3790: AND xah.ledger_id = fsp.set_of_books_id
3946: xla_ae_headers xah,
3947: xla_ae_lines xal,
3948: xla_distribution_links xdl,
3949: po_lines_all pol,
3950: po_distributions_all pod,
3951: cst_accrual_accounts caa,
3952: financials_system_params_all fsp,
3953: xla_transaction_entities_upg xte,
3954: po_headers_all poh /* -- Changes to pick Vendor from PO instead of APIA. Bug 7312170 */
4038: l_build_id,
4039: l_request_id
4040: FROM cst_write_offs cwo,
4041: --{ Immunization for po_vendor merge
4042: po_distributions_all pod,
4043: po_headers_all poh
4044: --}
4045: WHERE cwo.po_distribution_id is NOT NULL
4046: AND cwo.operating_unit_id = p_operating_unit