[Home] [Help]
91: g_num_segs NUMBER;
92:
93: g_acct_seg_index NUMBER;
94:
95: g_ledger_id gl_bc_packets.ledger_id%TYPE;
96:
97: g_packet_id gl_bc_packets.packet_id%TYPE;
98:
99: g_fcmode VARCHAR2(1);
93: g_acct_seg_index NUMBER;
94:
95: g_ledger_id gl_bc_packets.ledger_id%TYPE;
96:
97: g_packet_id gl_bc_packets.packet_id%TYPE;
98:
99: g_fcmode VARCHAR2(1);
100:
101: g_partial_resv_flag VARCHAR2(1);
99: g_fcmode VARCHAR2(1);
100:
101: g_partial_resv_flag VARCHAR2(1);
102:
103: g_return_code gl_bc_packets.result_code%TYPE;
104:
105: gms_retcode gl_bc_packets.result_code%TYPE;
106:
107: g_psa_grantcheck BOOLEAN;
101: g_partial_resv_flag VARCHAR2(1);
102:
103: g_return_code gl_bc_packets.result_code%TYPE;
104:
105: gms_retcode gl_bc_packets.result_code%TYPE;
106:
107: g_psa_grantcheck BOOLEAN;
108:
109: g_psa_pacheck BOOLEAN;
111: g_cbc_enabled BOOLEAN;
112:
113: g_cbc_retcode NUMBER;
114:
115: g_packet_id_ursvd gl_bc_packets.packet_id%TYPE;
116:
117: g_ussgl_option_flag BOOLEAN;
118:
119: g_budgetary_enc_flag VARCHAR2(1);
207: FUNCTION glxfor RETURN BOOLEAN;
208:
209: FUNCTION glxfrs RETURN BOOLEAN;
210:
211: FUNCTION glrchk(post_control IN gl_bc_packets.result_code%TYPE) RETURN BOOLEAN;
212:
213: FUNCTION glxfje RETURN BOOLEAN;
214:
215: FUNCTION glxfuf RETURN BOOLEAN;
263: /* FND_FLEX_APIS */
264: /* */
265: /* GL Tables which are being used include : */
266: /* */
267: /* GL_BC_PACKETS */
268: /* GL_BC_PACKET_ARRIVAL_ORDER */
269: /* GL_BC_OPTIONS */
270: /* GL_BC_OPTION_DETAILS */
271: /* GL_BC_PERIOD_MAP */
461: p_return_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
462:
463: cursor det_override_reqd IS
464: select 'x'
465: from gl_bc_packets bp
466: where bp.packet_id = g_packet_id
467: and bp.result_code between 'F00' and 'F19'
468: and bp.ussgl_link_to_parent_id is null
469: and bp.template_id is null
473: (
474: select 'If Partial Resv disallowed then all non-generated ' ||
475: 'detail lines that failed with any validation errors ' ||
476: 'or because of Funds Availability'
477: from gl_bc_packets pk
478: where pk.packet_id = g_packet_id
479: and pk.template_id is null
480: and pk.result_code like 'F%'
481: and ((g_partial_resv_flag = 'N'
488: );
489:
490: cursor ussgl_override_reqd is
491: select 'x'
492: from gl_bc_packets bp
493: where bp.packet_id = g_packet_id
494: and bp.result_code between 'F00' and 'F19'
495: and bp.ussgl_link_to_parent_id is not null
496: and exists
495: and bp.ussgl_link_to_parent_id is not null
496: and exists
497: (
498: select 'Corresp Original Transaction which was Overridden'
499: from gl_bc_packets pk
500: where pk.packet_id = g_packet_id
501: and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
502: and pk.result_code = 'P21'
503: );
1371: PRAGMA AUTONOMOUS_TRANSACTION;
1372:
1373: CURSOR source_cat IS
1374: SELECT distinct je_source_name, je_category_name
1375: FROM gl_bc_packets
1376: WHERE packet_id = decode(g_fcmode, 'U', g_packet_id_ursvd, g_packet_id);
1377:
1378: l_option_selected NUMBER;
1379:
1761: from dual
1762: where exists
1763: (
1764: select 'Transaction with USSGL Code'
1765: from gl_bc_packets bp
1766: where bp.packet_id = g_packet_id
1767: and bp.ussgl_transaction_code is not null
1768: );
1769:
1772: from dual
1773: where exists
1774: (
1775: select 'Associated Generated Row from existing GL Batch'
1776: from gl_bc_packets bp
1777: where bp.packet_id = g_packet_id
1778: and bp.je_batch_id is not null
1779: and bp.je_batch_id >= 0
1780: and bp.ussgl_transaction_code is not null
1835:
1836: close append_je;
1837:
1838:
1839: UPDATE GL_BC_PACKETS BP
1840: SET BP.ussgl_parent_id = GL_USSGL_PARENT_S.NEXTVAL
1841: WHERE
1842: BP.packet_id = g_packet_id
1843: AND BP.ussgl_transaction_code IS NOT NULL;
1842: BP.packet_id = g_packet_id
1843: AND BP.ussgl_transaction_code IS NOT NULL;
1844:
1845: -- =========================== FND LOG ===========================
1846: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
1847: -- ========================= FND LOG =============================
1848:
1849: if g_append_je_flag then
1850:
1852: -- ## Bug: 1387967/2178715 Federal AR |
1853: -- ## Drill down of USSGL generated transactions to AR. |
1854: -- ## ----------------------------------------------------------+
1855:
1856: UPDATE GL_BC_PACKETS BP
1857: SET
1858: ( BP.reference1,
1859: BP.reference2,
1860: BP.reference3,
1885:
1886: end if;
1887:
1888: -- =========================== FND LOG ===========================
1889: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
1890: psa_utils.debug_other_string(g_state_level,l_full_path, 'Calling glxfkf' );
1891: -- ========================= FND LOG =============================
1892:
1893: -- Retrieve Flex Info for the Flex Structure
1898: return(FALSE);
1899: end if;
1900:
1901:
1902: -- Insertion of USSGL transactions into gl_bc_packets
1903:
1904: -- When the USSGL Option is set, this process is executed prior to setting
1905: -- up of the denormalized columns and this applies to Funds Check and
1906: -- Funds Reservation. For all packet transactions with a USSGL transaction
1966: -- the transaction is created and the CCID is initialized to the negative
1967: -- value of the originating transaction's CCID. These transactions are
1968: -- then inserted into the Code Combinations table with new CCIDs
1969:
1970: sql_ussgl := 'insert into gl_bc_packets (packet_id, ' ||
1971: 'ledger_id, ' ||
1972: 'je_source_name, ' ||
1973: 'je_category_name, ' ||
1974: 'code_combination_id, ' ||
2148: 'gl_ussgl_transaction_codes uc, ' ||
2149: 'gl_ussgl_account_pairs guap, ' ||
2150: 'gl_code_combinations ccg, ' ||
2151: 'gl_code_combinations cco, ' ||
2152: 'gl_bc_packets bp, ' ||
2153: 'psa_event_types pet ' ||
2154: 'where lu.lookup_type = ''DR_CR'' ' ||
2155: 'and pet.je_source (+) = bp.je_source_name ' ||
2156: -- modified for bug 4167009
2184: 'gl_ussgl_transaction_codes uc, ' ||
2185: 'gl_ussgl_account_pairs guap, ' ||
2186: 'gl_code_combinations ccg, ' ||
2187: 'gl_code_combinations cco, ' ||
2188: 'gl_bc_packets bp ' ||
2189: 'where lu.lookup_type = ''DR_CR'' ' ||
2190: 'and uc.chart_of_accounts_id = ' ||
2191: 'guap.chart_of_accounts_id ' ||
2192: 'and uc.ussgl_transaction_code = ' ||
2266:
2267: sql_ussgl := sql_ussgl ||
2268: 'bp.code_combination_id ' ||
2269: 'from gl_code_combinations cc, ' ||
2270: 'gl_bc_packets bp ' ||
2271: 'where cc.code_combination_id = -1 * bp.code_combination_id ' ||
2272: 'and bp.packet_id = ' || g_packet_id || ' ' ||
2273: 'and bp.code_combination_id < 0 ' ||
2274: 'and bp.account_segment_value is not null';
2312:
2313:
2314: -- Update the CCID of the USSGL transaction in the Packet
2315:
2316: update gl_bc_packets bp
2317: set bp.code_combination_id = l_ccid_out
2318: where bp.code_combination_id = l_ccid
2319: and bp.account_segment_value=seg_val(g_acct_seg_index);
2320:
2318: where bp.code_combination_id = l_ccid
2319: and bp.account_segment_value=seg_val(g_acct_seg_index);
2320:
2321: -- =========================== FND LOG ===========================
2322: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets -> ' || SQL%ROWCOUNT);
2323: -- ========================= FND LOG =============================
2324:
2325: else
2326: exit;
2530:
2531: FUNCTION glxfiu RETURN BOOLEAN IS
2532:
2533: cursor pkt_id is
2534: select gl_bc_packets_s.nextval
2535: from dual;
2536:
2537: l_dummy VARCHAR2(80);
2538:
2554: -- =========================== FND LOG ===========================
2555: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_packet_id -> ' || g_packet_id );
2556: -- ========================= FND LOG =============================
2557:
2558: -- Insert Unreservation Packet into gl_bc_packets
2559: insert into gl_bc_packets
2560: (packet_id,
2561: ledger_id,
2562: je_source_name,
2555: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_packet_id -> ' || g_packet_id );
2556: -- ========================= FND LOG =============================
2557:
2558: -- Insert Unreservation Packet into gl_bc_packets
2559: insert into gl_bc_packets
2560: (packet_id,
2561: ledger_id,
2562: je_source_name,
2563: je_category_name,
2662: g_serial_id,
2663: g_resp_appl_id
2664: from psa_option_details_gt od,
2665: gl_budget_assignments ba,
2666: gl_bc_packets bp,
2667: gl_budorg_bc_options bo
2668: where (od.je_source_name || ';' || od.je_category_name =
2669: bp.je_source_name || ';' ||bp.je_category_name )
2670: and od.packet_id = bp.packet_id
2704: and bp.template_id is null
2705: and bp.status_code = 'A';
2706:
2707: -- =========================== FND LOG ===========================
2708: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert gl_bc_packets -> ' || SQL%ROWCOUNT );
2709: -- ========================= FND LOG =============================
2710:
2711: BEGIN
2712:
2720: (
2721: SELECT
2722: 'Record with non-null USSGL transaction code'
2723: FROM
2724: GL_BC_PACKETS BP
2725: WHERE
2726: BP.packet_id = g_packet_id_ursvd
2727: AND BP.ussgl_transaction_code IS NOT NULL
2728: );
2847:
2848:
2849: begin
2850:
2851: update gl_bc_packets bp
2852: set bp.funding_budget_version_id =
2853: (select decode(pk.actual_flag, 'B', pk.budget_version_id,
2854: bo.funding_budget_version_id)
2855: from gl_budget_assignments ba,
2855: from gl_budget_assignments ba,
2856: gl_budgets b,
2857: gl_budget_versions bv,
2858: gl_period_statuses ps,
2859: gl_bc_packets pk,
2860: gl_budorg_bc_options bo
2861: where
2862: ba.ledger_id(+) = g_ledger_id
2863: and ba.currency_code(+) = decode(PK.currency_code,
2895: WHEN OVERLAPPING_BUDGET THEN
2896:
2897: -- =========================== FND LOG ===========================
2898: psa_utils.debug_other_string(g_state_level,l_full_path, ' There are multiple overlapping budgets assigned to account');
2899: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated failed');
2900: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the status code = F/R based on g_fcmode');
2901: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the result code= F77/F80 based on overlapping budget');
2902: -- ========================= FND LOG =============================
2903:
2900: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the status code = F/R based on g_fcmode');
2901: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the result code= F77/F80 based on overlapping budget');
2902: -- ========================= FND LOG =============================
2903:
2904: update gl_bc_packets bp
2905: set STATUS_CODE = DECODE(g_fcmode,'C','F','R'),
2906: RESULT_CODE=
2907: ( select DECODE(count(bo.FUNDING_BUDGET_VERSION_ID),1,'F77','F80')
2908: from
2905: set STATUS_CODE = DECODE(g_fcmode,'C','F','R'),
2906: RESULT_CODE=
2907: ( select DECODE(count(bo.FUNDING_BUDGET_VERSION_ID),1,'F77','F80')
2908: from
2909: gl_bc_packets pk,
2910: gl_budget_assignments ba,
2911: gl_budorg_bc_options bo
2912: where
2913: pk.rowid=bp.rowid
2927: end;
2928:
2929:
2930: -- =========================== FND LOG ===========================
2931: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
2932: -- ========================= FND LOG =============================
2933:
2934: update gl_bc_packets bp
2935: set (bp.automatic_encumbrance_flag,
2930: -- =========================== FND LOG ===========================
2931: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
2932: -- ========================= FND LOG =============================
2933:
2934: update gl_bc_packets bp
2935: set (bp.automatic_encumbrance_flag,
2936: bp.funds_check_level_code,
2937: bp.tolerance_percentage,
2938: bp.tolerance_amount,
3001: gl_budget_period_ranges br,
3002: gl_period_statuses ps,
3003: gl_code_combinations cc,
3004: psa_option_details_gt od,
3005: gl_bc_packets pk
3006: where uc.chart_of_accounts_id (+) = g_coa_id
3007: and uc.ussgl_transaction_code (+) =
3008: nvl(pk.ussgl_transaction_code, -1)
3009: and bv.budget_version_id (+) = nvl(pk.budget_version_id, -1)
3023: where bp.packet_id = g_packet_id
3024: and bp.template_id is null;
3025:
3026: -- =========================== FND LOG ===========================
3027: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
3028: -- ========================= FND LOG =============================
3029:
3030: update gl_bc_packets bp
3031: set (funds_check_level_code,
3026: -- =========================== FND LOG ===========================
3027: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
3028: -- ========================= FND LOG =============================
3029:
3030: update gl_bc_packets bp
3031: set (funds_check_level_code,
3032: amount_type,
3033: boundary_code) = (select
3034: nvl(min(decode(bo.funds_check_level_code, 'D',
3035: nvl(od.funds_check_level_code, 'D'),
3036: nvl(bo.funds_check_level_code, 'N'))), 'N'),
3037: min(bo.amount_type),
3038: min(bo.boundary_code)
3039: from gl_bc_packets pk,
3040: psa_option_details_gt od,
3041: gl_budget_assignments ba,
3042: gl_budorg_bc_options bo
3043: where pk.rowid = bp.rowid
3055: and bp.funding_budget_version_id is not null;
3056:
3057:
3058: -- =========================== FND LOG ===========================
3059: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
3060: -- ========================= FND LOG =============================
3061:
3062:
3063: /*----------------------------------------------------------------------+
3067: | no budget assignments... |
3068: +----------------------------------------------------------------------*/
3069:
3070: if(nvl(g_enable_efc_flag,'N')='Y') THEN
3071: update gl_bc_packets bp
3072: set bp.funds_check_level_code = 'B'
3073: where bp.packet_id = g_packet_id
3074: and bp.template_id is null
3075: and bp.funds_check_level_code = 'N'
3126: -- ensures that if a template is Proprietary, Summarization ignore any
3127: -- Budgetary Detail Accounts that may fall into the same Rollup Structure,
3128: -- and vice-versa
3129:
3130: insert into gl_bc_packets (packet_id,
3131: ledger_id,
3132: je_source_name,
3133: je_category_name,
3134: code_combination_id,
3214: from psa_option_details_gt od,
3215: gl_period_statuses ps,
3216: gl_summary_templates st,
3217: gl_account_hierarchies ah,
3218: gl_bc_packets bp,
3219: gl_summary_bc_options sb,
3220: gl_budgets b,
3221: gl_budget_versions bv,
3222: gl_period_statuses ps2
3278:
3279: if SQL%FOUND then
3280: -- =========================== FND LOG ===========================
3281: psa_utils.debug_other_string(g_state_level,l_full_path,
3282: ' Insert gl_bc_packets - summ trans ' || SQL%ROWCOUNT );
3283:
3284: psa_utils.debug_other_string(g_state_level,l_full_path,
3285: ' g_summarized_flag -> TRUE' );
3286: -- ========================= FND LOG =============================
3409:
3410: -- Bug 3574935
3411:
3412: --Bug 6823089 ..
3413: --l_max_packet_id gl_bc_packets.packet_id%type;
3414:
3415: -- Bug 5644702
3416: l_effective_period_num gl_period_statuses.effective_period_num%TYPE;
3417: l_period_name gl_period_statuses.period_name%TYPE;
3433: -- Bug 3574935 .. Start
3434: -- Obtain the maximum packet_id less than the current arrival sequence from
3435: -- table gl_bc_packet_arrival_order. This will then be used in the following
3436: -- UPDATE statement to help improve performance of the query. Making use of
3437: -- packet_id condition in the subquery makes index gl_bc_packets_n2 more
3438: -- selective and reduces number of rows processed during access to the table
3439: -- gl_bc_packet_arrival_order
3440:
3441: -- Bug 4651919 .. Start
3456: -- psa_utils.debug_other_string(g_state_level,l_full_path, ' l_max_packet_id -> '|| l_max_packet_id );
3457: -- ========================= FND LOG =============================
3458:
3459: -- Lock dummy table gl_bc_dual in Row Share Mode to ensure Read Consistency
3460: -- between gl_bc_packets and gl_balances in the next two balance update
3461: -- SQLs. This scheme requires posting to lock gl_bc_dual in exclusive mode
3462: -- before it commits, and to wait in a sleep cycle of 15 seconds until all
3463: -- the Funds Check processes release the locks. This prevents the Funds
3464: -- Checker from counting the Balances twice, in case Posting Commits in
3487: LOCK TABLE gl_bc_dual IN ROW SHARE MODE NOWAIT;
3488: end if;
3489:
3490:
3491: -- Update Approved and Pending Balances in gl_bc_packets
3492: --
3493: -- Transactions in the gl_bc_packets that would affect the transactions in
3494: -- this packet are :
3495: --
3489:
3490:
3491: -- Update Approved and Pending Balances in gl_bc_packets
3492: --
3493: -- Transactions in the gl_bc_packets that would affect the transactions in
3494: -- this packet are :
3495: --
3496: -- all approved packets, for the same Set of Books, that arrived earlier
3497: --
3554: -- EOB = Last Period in Latest Open Year of Budget
3555:
3556:
3557: update
3558: gl_bc_packets bp
3559: set (bp.budget_approved_balance,
3560: bp.actual_approved_balance,
3561: bp.encumbrance_approved_balance,
3562: bp.budget_pending_balance,
3593: )
3594: from gl_period_statuses ps,
3595: gl_budgets bd,
3596: gl_budget_versions bv,
3597: gl_bc_packets pk,
3598: gl_bc_packet_arrival_order ao
3599: where ps.application_id = 101
3600: and ps.ledger_id = g_ledger_id
3601: and ps.period_name = bd.last_valid_period_name
3681:
3682:
3683: -- =========================== FND LOG ===========================
3684: psa_utils.debug_other_string(g_state_level,l_full_path,
3685: ' Update approved and pending balance in gl_bc_packets ' || SQL%ROWCOUNT );
3686: -- ========================= FND LOG =============================
3687:
3688:
3689: -- Update Posted Balances in gl_bc_packets
3685: ' Update approved and pending balance in gl_bc_packets ' || SQL%ROWCOUNT );
3686: -- ========================= FND LOG =============================
3687:
3688:
3689: -- Update Posted Balances in gl_bc_packets
3690: --
3691: -- For Actuals, we subtract the begin balances of the first period of the
3692: -- transaction year, i.e YTD Funds Available includes actual activities
3693: -- accumulated during the current transaction year
3710: -- ------------------------------------ ----------- -------------------
3711: -- SORT AGGREGATE
3712: -- NESTED LOOPS
3713: -- NESTED LOOPS
3714: -- TABLE ACCESS BY ROWID GL_BC_PACKETS
3715: -- INDEX RANGE SCAN GL_BC_PACKETS_N1
3716: -- INDEX RANGE SCAN GL_BC_PERIOD_MAP_U2
3717: -- TABLE ACCESS BY ROWID GL_BALANCES
3718: -- INDEX RANGE SCAN GL_BALANCES_N1
3711: -- SORT AGGREGATE
3712: -- NESTED LOOPS
3713: -- NESTED LOOPS
3714: -- TABLE ACCESS BY ROWID GL_BC_PACKETS
3715: -- INDEX RANGE SCAN GL_BC_PACKETS_N1
3716: -- INDEX RANGE SCAN GL_BC_PERIOD_MAP_U2
3717: -- TABLE ACCESS BY ROWID GL_BALANCES
3718: -- INDEX RANGE SCAN GL_BALANCES_N1
3719: --
3751:
3752: -- Bugfix 2231059
3753:
3754: update
3755: gl_bc_packets bp
3756: set (bp.budget_posted_balance,
3757: bp.actual_posted_balance,
3758: bp.encumbrance_posted_balance) =
3759: (
3883:
3884:
3885: -- =========================== FND LOG ===========================
3886: psa_utils.debug_other_string(g_state_level,l_full_path,
3887: ' Update posted balance in gl_bc_packets ' || SQL%ROWCOUNT );
3888: -- ========================= FND LOG =============================
3889:
3890: -- Commit to release Lock on gl_bc_dual
3891: commit;
4042: 'P38', 1,
4043: 'P39', 1))), 0, 'S', 1, 'A'),
4044: count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
4045: 'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
4046: from gl_bc_packets bp
4047: where bp.packet_id = g_packet_id
4048: and bp.template_id is null;
4049:
4050: l_ret_code gl_bc_packets.result_code%type;
4046: from gl_bc_packets bp
4047: where bp.packet_id = g_packet_id
4048: and bp.template_id is null;
4049:
4050: l_ret_code gl_bc_packets.result_code%type;
4051:
4052: -- Bug 5571064 .. Start
4053:
4054: CURSOR c_get_failed_distributions(p_packet_id IN NUMBER) IS
4052: -- Bug 5571064 .. Start
4053:
4054: CURSOR c_get_failed_distributions(p_packet_id IN NUMBER) IS
4055: SELECT distinct bc.source_distribution_id_num_1
4056: FROM gl_bc_packets bc
4057: WHERE bc.packet_id = p_packet_id
4058: AND bc.result_code like 'F%';
4059:
4060: TYPE source_dist_id_num_1_tbl_type IS TABLE OF gl_bc_packets.source_distribution_id_num_1%type INDEX BY binary_integer;
4056: FROM gl_bc_packets bc
4057: WHERE bc.packet_id = p_packet_id
4058: AND bc.result_code like 'F%';
4059:
4060: TYPE source_dist_id_num_1_tbl_type IS TABLE OF gl_bc_packets.source_distribution_id_num_1%type INDEX BY binary_integer;
4061:
4062: l_source_dist_id_num_1_tbl source_dist_id_num_1_tbl_type;
4063:
4064: -- Bug 5571064 .. End
4075: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFRC - START' );
4076: -- ========================= FND LOG =============================
4077:
4078: -- Update Result Code for all transactions in Packet
4079: update gl_bc_packets bp
4080: set bp.result_code =
4081: decode(bp.actual_flag || decode(bp.currency_code, g_func_curr_code,
4082: null, '1'), 'B1', 'P03',
4083: decode(bp.actual_flag || decode(bp.budget_version_id,
4185:
4186:
4187:
4188: -- =========================== FND LOG ===========================
4189: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update Result Code gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
4190: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_fcmode -> ' || g_fcmode);
4191: -- ========================= FND LOG =============================
4192:
4193: /* -------------------------------------------------------------------+
4197: +-----------------------------------------------------------------*/
4198:
4199: if(nvl(g_enable_efc_flag,'N')='Y') THEN
4200:
4201: UPDATE gl_bc_packets bp
4202: set result_code='F78'
4203: WHERE bp.packet_id = g_packet_id
4204: AND bp.result_code like 'F%'
4205: AND bp.funding_budget_version_id IS NULL
4225:
4226: -- Update Result Code for Detail Transactions when Summary Transactions
4227: -- fail Funds Check and Checking is Absolute
4228:
4229: update gl_bc_packets bp
4230: set bp.result_code =
4231: decode(bp.account_category_code || substr(bp.result_code, 1, 1),
4232: 'PP', 'F01', 'PF', 'F04', 'BP', 'F11', 'BF', 'F14')
4233: where bp.packet_id = g_packet_id
4238: (
4239: select
4240:
4241: 'Summary Row exists and fails Funds Check; Absolute'
4242: from gl_bc_packets pk,
4243: gl_account_hierarchies ah
4244: where ah.ledger_id = bp.ledger_id
4245: and ah.summary_code_combination_id = pk.code_combination_id
4246: and ah.detail_code_combination_id = bp.code_combination_id
4256: and pk.result_code in ('F02', 'F12')
4257: );
4258:
4259: -- =========================== FND LOG ===========================
4260: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
4261: -- ========================= FND LOG =============================
4262:
4263: -- Update Result Code for Detail Transactions when Summary Transactions
4264: -- fail Funds Check and Checking is Advisory
4262:
4263: -- Update Result Code for Detail Transactions when Summary Transactions
4264: -- fail Funds Check and Checking is Advisory
4265:
4266: update gl_bc_packets bp
4267: set bp.result_code =
4268: decode(bp.account_category_code, 'P', 'P22', 'B', 'P27')
4269: where bp.packet_id = g_packet_id
4270: and bp.template_id is null
4274: select
4275:
4276: 'Summary Row exists and fails Funds Check; Advisory'
4277: from gl_account_hierarchies ah,
4278: gl_bc_packets pk
4279: where ah.ledger_id = bp.ledger_id
4280: and ah.summary_code_combination_id = pk.code_combination_id
4281: and ah.detail_code_combination_id = bp.code_combination_id
4282: and pk.packet_id = bp.packet_id
4291: and pk.result_code in ('P20', 'P25')
4292: );
4293:
4294: -- =========================== FND LOG ===========================
4295: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
4296: -- ========================= FND LOG =============================
4297:
4298: end if;
4299:
4307: -- =========================== FND LOG ===========================
4308: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_ussgl_option_flag -> TRUE');
4309: -- ========================= FND LOG =============================
4310:
4311: update gl_bc_packets bp
4312: set bp.result_code =
4313: decode(bp.ussgl_transaction_code, null,
4314: decode(bp.account_category_code, 'P', 'F06', 'B', 'F15'),
4315: 'F05')
4321: and exists
4322: (
4323: select 'One or more Proprietary/Budgetary counterparts of ' ||
4324: 'this transaction exists and fails Funds Check'
4325: from gl_bc_packets pk
4326: where pk.packet_id = g_packet_id
4327: and pk.template_id is null
4328: and pk.result_code like 'F%'
4329: and (pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
4330: or pk.ussgl_link_to_parent_id in (bp.ussgl_link_to_parent_id, bp.ussgl_parent_id))
4331: );
4332:
4333: -- =========================== FND LOG ===========================
4334: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 4 updated -> ' || SQL%ROWCOUNT || ' rows');
4335: -- ========================= FND LOG =============================
4336:
4337: end if;
4338:
4364: -- ========================= FND LOG ===========================
4365: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside Ist IF ');
4366: -- ========================= FND LOG ===========================
4367:
4368: update gl_bc_packets bp
4369: set bp.result_code = 'P23'
4370: where bp.packet_id = g_packet_id
4371: and bp.result_code like 'F%'
4372: and bp.template_id is not null;
4371: and bp.result_code like 'F%'
4372: and bp.template_id is not null;
4373:
4374: -- ========================= FND LOG ===========================
4375: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 5 updated -> ' || SQL%ROWCOUNT || ' rows');
4376: -- ========================= FND LOG ===========================
4377:
4378: end if;
4379:
4376: -- ========================= FND LOG ===========================
4377:
4378: end if;
4379:
4380: update gl_bc_packets bp
4381: set bp.status_code = decode(bp.status_code || l_ret_code,
4382: 'PF', 'R',
4383: 'CF', 'F',
4384: decode(bp.status_code || substr(bp.result_code, 1, 1),
4389: bp.last_update_date = sysdate
4390: where bp.packet_id = g_packet_id;
4391:
4392: -- ========================= FND LOG ===========================
4393: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 6 updated -> ' || SQL%ROWCOUNT || ' rows');
4394: -- ========================= FND LOG ===========================
4395:
4396: END IF;
4397:
4402: OPEN c_get_failed_distributions (g_packet_id);
4403: FETCH c_get_failed_distributions bulk collect into l_source_dist_id_num_1_tbl;
4404:
4405: FORALL I IN 1..l_source_dist_id_num_1_tbl.count
4406: UPDATE gl_bc_packets pk
4407: SET result_code ='F77'
4408: WHERE pk.packet_id = g_packet_id
4409: AND pk.source_distribution_id_num_1 = l_source_dist_id_num_1_tbl(I)
4410: AND pk.result_code like 'P%';
4410: AND pk.result_code like 'P%';
4411:
4412: -- ========================= FND LOG ===========================
4413: psa_utils.debug_other_string(g_state_level,l_full_path,
4414: ' update gl_bc_packets 6.1, result_code to F77 for same packet and same distribution updated -> ' || SQL%ROWCOUNT || ' rows');
4415: -- ========================= FND LOG ===========================
4416:
4417: CLOSE c_get_failed_distributions;
4418:
4416:
4417: CLOSE c_get_failed_distributions;
4418:
4419: -- Bug 3553142
4420: -- If there is an advisory warning on any row in gl_bc_packets, all passed rows should indicate
4421: -- that one or more related lines have advisory warnings.
4422: -- Created 2 new LOOKUP_CODEs P12, P17
4423:
4424: UPDATE gl_bc_packets pk
4420: -- If there is an advisory warning on any row in gl_bc_packets, all passed rows should indicate
4421: -- that one or more related lines have advisory warnings.
4422: -- Created 2 new LOOKUP_CODEs P12, P17
4423:
4424: UPDATE gl_bc_packets pk
4425: SET result_code = 'P12'
4426: WHERE pk.packet_id = g_packet_id
4427: AND result_code = 'P10'
4428: AND exists (SELECT 'x'
4425: SET result_code = 'P12'
4426: WHERE pk.packet_id = g_packet_id
4427: AND result_code = 'P10'
4428: AND exists (SELECT 'x'
4429: FROM gl_bc_packets bc
4430: WHERE bc.packet_id = pk.packet_id
4431: AND bc.result_code = 'P20');
4432:
4433: -- ========================= FND LOG ===========================
4430: WHERE bc.packet_id = pk.packet_id
4431: AND bc.result_code = 'P20');
4432:
4433: -- ========================= FND LOG ===========================
4434: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 7 updated -> ' || SQL%ROWCOUNT || ' rows');
4435: -- ========================= FND LOG ===========================
4436:
4437:
4438: UPDATE gl_bc_packets pk
4434: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 7 updated -> ' || SQL%ROWCOUNT || ' rows');
4435: -- ========================= FND LOG ===========================
4436:
4437:
4438: UPDATE gl_bc_packets pk
4439: SET result_code = 'P17'
4440: WHERE pk.packet_id = g_packet_id
4441: AND result_code = 'P15'
4442: AND exists (SELECT 'x'
4439: SET result_code = 'P17'
4440: WHERE pk.packet_id = g_packet_id
4441: AND result_code = 'P15'
4442: AND exists (SELECT 'x'
4443: FROM gl_bc_packets bc
4444: WHERE bc.packet_id = pk.packet_id
4445: AND bc.result_code = 'P25');
4446:
4447: -- ========================= FND LOG ===========================
4444: WHERE bc.packet_id = pk.packet_id
4445: AND bc.result_code = 'P25');
4446:
4447: -- ========================= FND LOG ===========================
4448: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 8 updated -> ' || SQL%ROWCOUNT || ' rows');
4449: -- ========================= FND LOG ===========================
4450:
4451:
4452: -- =========================== FND LOG ===========================
4638: pa_stmt VARCHAR2(400);
4639: cur_pa INTEGER;
4640: ignore INTEGER;
4641:
4642: pa_retcode gl_bc_packets.result_code%TYPE;
4643: err_msg VARCHAR2(1024);
4644: err_stg VARCHAR2(1024);
4645:
4646: -- ========================= FND LOG ===========================
4736: -- ========================= FND LOG ===========================
4737:
4738: -- Update Result Code for Detail Transactions
4739:
4740: update gl_bc_packets bp
4741: set bp.result_code = 'P21'
4742: where bp.packet_id = g_packet_id
4743: and bp.result_code between 'F00' and 'F19'
4744: and bp.ussgl_link_to_parent_id is null
4749: (
4750: select 'If Partial Resv disallowed then all non-generated ' ||
4751: 'detail lines that failed with any validation errors ' ||
4752: 'or because of Funds Availability'
4753: from gl_bc_packets pk
4754: where pk.packet_id = g_packet_id
4755: and pk.template_id is null
4756: and pk.result_code like 'F%'
4757: and ((g_partial_resv_flag = 'N'
4763: and pk.result_code between 'F20' and 'F29'))
4764: );
4765:
4766: -- ========================= FND LOG ===========================
4767: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets1 -> ' || SQL%ROWCOUNT);
4768: -- ========================= FND LOG ===========================
4769:
4770: -- ========================= FND LOG ===========================
4771: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_calling_prog_flag -> ' || g_calling_prog_flag);
4775: IF (g_calling_prog_flag = 'G') THEN
4776:
4777: -- Update Result Code for Generated Transactions
4778:
4779: update gl_bc_packets bp
4780: set bp.result_code = 'P26'
4781: where bp.packet_id = g_packet_id
4782: and bp.result_code between 'F00' and 'F19'
4783: and bp.ussgl_link_to_parent_id is not null
4783: and bp.ussgl_link_to_parent_id is not null
4784: and exists
4785: (
4786: select 'Corresp Original Transaction which was Overridden'
4787: from gl_bc_packets pk
4788: where pk.packet_id = g_packet_id
4789: and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
4790: and pk.result_code = 'P21'
4791: );
4792:
4793: END IF;
4794:
4795: -- ========================= FND LOG ===========================
4796: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets2 -> ' || SQL%ROWCOUNT);
4797: psa_utils.debug_other_string(g_state_level,l_full_path, ' RETURN -> TRUE ');
4798: -- ========================= FND LOG ===========================
4799:
4800: return(TRUE);
4882: 'P38', 1,
4883: 'P39', 1))), 0, 'S', 1, 'A'),
4884: count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
4885: 'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
4886: from gl_bc_packets bp
4887: where bp.packet_id = g_packet_id
4888: and bp.template_id is null;
4889:
4890: -- ========================= FND LOG ===========================
4946: -- Checking C (Checking) Pxx S (Passed Check)
4947: -- Checking C (Checking) Fxx F (Failed Check)
4948:
4949: if g_calling_prog_flag = 'G' then
4950: update gl_bc_packets bp
4951: set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
4952: 'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
4953: 'CF', 'F',
4954: decode(bp.status_code ||
4956: 'PF', 'R', 'CP', 'S', 'CF', 'F', 'T')),
4957: bp.last_update_date = sysdate
4958: where bp.packet_id = g_packet_id;
4959: else
4960: update gl_bc_packets bp
4961: set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
4962: 'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
4963: 'CF', 'F',
4964: decode(bp.status_code ||
4969: where bp.packet_id = g_packet_id;
4970: end if;
4971:
4972: -- ========================= FND LOG ===========================
4973: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
4974: -- ========================= FND LOG ===========================
4975:
4976: -- Update the Result Code of all Summary Transactions that fail Funds
4977: -- Reservation to 'P23', when each of their corresponding details got
5003: -- ========================= FND LOG ===========================
5004: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside Ist IF ');
5005: -- ========================= FND LOG ===========================
5006:
5007: update gl_bc_packets bp
5008: set bp.result_code = 'P23'
5009: where bp.packet_id = g_packet_id
5010: and bp.result_code like 'F%'
5011: and bp.template_id is not null;
5010: and bp.result_code like 'F%'
5011: and bp.template_id is not null;
5012:
5013: -- ========================= FND LOG ===========================
5014: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
5015: -- ========================= FND LOG ===========================
5016:
5017: end if;
5018:
5053: -- ========================= FND LOG ===========================
5054: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside IInd IF ');
5055: -- ========================= FND LOG ===========================
5056:
5057: update gl_bc_packets bp
5058: set (bp.entered_dr,
5059: bp.entered_cr,
5060: bp.accounted_dr,
5061: bp.accounted_cr,
5069: nvl(max(pk.status_code), 'R'),
5070: decode(max(pk.status_code), null, bp.result_code,
5071: decode(bp.status_code, 'A', bp.result_code, 'P23'))
5072: from gl_account_hierarchies ah,
5073: gl_bc_packets pk
5074: where ah.ledger_id = g_ledger_id
5075: and ah.template_id = bp.template_id
5076: and ah.summary_code_combination_id = bp.code_combination_id
5077: and ah.detail_code_combination_id = pk.code_combination_id
5090: where bp.packet_id = g_packet_id
5091: and bp.template_id is not null;
5092:
5093: -- ========================= FND LOG ===========================
5094: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
5095: -- ========================= FND LOG ===========================
5096:
5097: end;
5098: end if;
5153: -- If Standard Budgetary Control failed, CBC Journals shoud not be committed.
5154: -- This is achieved by calling function IGC_CBC_GL_FC_PKG.reconcile_glzcbc
5155: --
5156:
5157: FUNCTION glrchk(post_control IN gl_bc_packets.result_code%TYPE)
5158: RETURN BOOLEAN IS
5159:
5160: -- ========================= FND LOG ===========================
5161: l_full_path VARCHAR2(100);
5243:
5244: DECLARE
5245:
5246: gms_stmt VARCHAR2(400);
5247: gl_retcode gl_bc_packets.result_code%TYPE;
5248: gms_control gl_bc_packets.result_code%TYPE;
5249:
5250: BEGIN
5251: -- ========================= FND LOG ===========================
5244: DECLARE
5245:
5246: gms_stmt VARCHAR2(400);
5247: gl_retcode gl_bc_packets.result_code%TYPE;
5248: gms_control gl_bc_packets.result_code%TYPE;
5249:
5250: BEGIN
5251: -- ========================= FND LOG ===========================
5252: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLRCHK(GMS Reconcile) --> START ');
5409: -- Assumption :
5410: --
5411: -- This Module assumes that the packet being processed only includes
5412: -- Transactions from 1 single GL Journal Batch for performance reasons.
5413: -- This is how we populate gl_bc_packets currently in all GL Implementations
5414:
5415: FUNCTION glxfje RETURN BOOLEAN IS
5416:
5417: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5413: -- This is how we populate gl_bc_packets currently in all GL Implementations
5414:
5415: FUNCTION glxfje RETURN BOOLEAN IS
5416:
5417: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5418: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5419: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5420: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5421:
5414:
5415: FUNCTION glxfje RETURN BOOLEAN IS
5416:
5417: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5418: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5419: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5420: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5421:
5422:
5415: FUNCTION glxfje RETURN BOOLEAN IS
5416:
5417: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5418: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5419: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5420: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5421:
5422:
5423: cursor orig_bat is
5416:
5417: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5418: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5419: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5420: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5421:
5422:
5423: cursor orig_bat is
5424: select max(bp.je_batch_id),
5423: cursor orig_bat is
5424: select max(bp.je_batch_id),
5425: max(bp.actual_flag),
5426: max(bp.je_line_num) -- bug 5139224
5427: from gl_bc_packets bp
5428: where bp.packet_id = g_packet_id
5429: and bp.template_id is null
5430: and bp.ussgl_link_to_parent_id is null;
5431:
5430: and bp.ussgl_link_to_parent_id is null;
5431:
5432: cursor generated_bat is
5433: select distinct bp.je_batch_id
5434: from gl_bc_packets bp
5435: where bp.packet_id = g_packet_id
5436: and bp.ussgl_link_to_parent_id is not null;
5437:
5438: cursor batch_id is
5582: BP.ussgl_link_to_parent_id,
5583: 'glxfje() generated: ' || g_packet_id
5584: from gl_period_statuses ps,
5585: gl_je_headers jh,
5586: gl_bc_packets bp
5587: where ps.application_id = 101
5588: and ps.ledger_id = g_ledger_id
5589: and ps.period_name = bp.period_name
5590: and jh.je_header_id = bp.je_header_id
5683: FROM
5684: GL_PERIOD_STATUSES PS,
5685: GL_JE_LINES JL,
5686: GL_JE_LINES JL1,
5687: GL_BC_PACKETS BP
5688: WHERE
5689: PS.application_id = 101
5690: AND PS.ledger_id = g_ledger_id
5691: AND PS.period_name = BP.period_name
5712: delete from gl_je_lines jl
5713: where jl.je_header_id in
5714: (
5715: select distinct bp.je_header_id
5716: from gl_bc_packets bp
5717: where bp.packet_id = g_packet_id
5718: and bp.ussgl_link_to_parent_id IS NOT NULL
5719: )
5720: and jl.reference_10 = 'glxfje() generated: ' || g_packet_id_ursvd;
5749: decode(g_fcmode, 'U', -1, 1),
5750: nvl(jh.running_total_accounted_cr, 0) +
5751: sum(nvl(bp.accounted_cr, 0)) *
5752: decode(g_fcmode, 'U', -1, 1)
5753: from gl_bc_packets bp
5754: where bp.packet_id = g_packet_id
5755: and bp.je_batch_id = jh.je_batch_id
5756: and bp.je_header_id = jh.je_header_id
5757: and bp.ussgl_link_to_parent_id is not null
5758: )
5759: where jh.je_header_id in
5760: (
5761: select distinct je_header_id
5762: from gl_bc_packets bp1
5763: where bp1.packet_id = g_packet_id
5764: and bp1.ussgl_link_to_parent_id is not null
5765: );
5766:
5794: nvl(jb.running_total_accounted_cr, 0) +
5795: sum(nvl(bp.accounted_cr, 0)) * decode(g_fcmode, 'U', -1, 1),
5796: decode(g_fcmode, 'U', 'R', 'P'),
5797: decode(g_fcmode, 'U', null, jb.packet_id)
5798: from gl_bc_packets bp
5799: where bp.packet_id = g_packet_id
5800: and bp.je_batch_id = jb.je_batch_id
5801: and bp.ussgl_link_to_parent_id is not null
5802: )
5926: min(jb.chart_of_accounts_id),
5927: min(jb.period_set_name),
5928: min(jb.accounted_period_type)
5929: from gl_period_statuses ps,
5930: gl_bc_packets bp,
5931: gl_je_batches jb
5932: where ps.application_id = 101
5933: and ps.ledger_id = g_ledger_id
5934: and ps.period_name = bp.period_name
6042: and jh.je_header_id = x.je_header_id
6043: and exists
6044: (
6045: select 'JE headers with associated generated transactions'
6046: from gl_bc_packets bp
6047: where bp.packet_id = g_packet_id
6048: and bp.je_batch_id = l_je_batch_id
6049: and bp.je_header_id = jh.je_header_id
6050: and bp.ussgl_link_to_parent_id is not null
6069: select sum(nvl(bp.entered_dr, 0)),
6070: sum(nvl(bp.entered_cr, 0)),
6071: sum(nvl(bp.accounted_dr, 0)),
6072: sum(nvl(bp.accounted_cr, 0))
6073: from gl_bc_packets bp
6074: where bp.packet_id = g_packet_id
6075: and bp.je_batch_id = l_je_batch_id
6076: and bp.je_header_id = to_number(jh.attribute1)
6077: and bp.ussgl_link_to_parent_id is not null
6128: ' ',
6129: 'Y',
6130: 'glxfje() generated: ' || g_packet_id
6131: from gl_je_headers jh,
6132: gl_bc_packets bp
6133: where jh.je_batch_id = l_gen_batch_id
6134: and jh.attribute1 = to_char(bp.je_header_id)
6135: and bp.packet_id = g_packet_id
6136: and bp.ussgl_link_to_parent_id is not null;
6202: decode(min(JL.context),min(JL.context3),null,min(JL.attribute9)),
6203: decode(min(JL.context),min(JL.context3),null,min(JL.attribute10))
6204: FROM
6205: GL_JE_HEADERS JH,
6206: GL_BC_PACKETS BP1,
6207: GL_BC_PACKETS BP,
6208: GL_JE_LINES JL
6209: WHERE
6210: JH.je_batch_id = l_gen_batch_id
6203: decode(min(JL.context),min(JL.context3),null,min(JL.attribute10))
6204: FROM
6205: GL_JE_HEADERS JH,
6206: GL_BC_PACKETS BP1,
6207: GL_BC_PACKETS BP,
6208: GL_JE_LINES JL
6209: WHERE
6210: JH.je_batch_id = l_gen_batch_id
6211: AND JH.attribute1 = to_char(BP.je_header_id)
6231: -- Update je_batch_id of all associated generated transactions from the
6232: -- ID of the Originating Batch to that of the newly created batch so that
6233: -- posting deletes only these packet rows
6234:
6235: update gl_bc_packets bp
6236: set bp.je_batch_id = l_gen_batch_id
6237: where bp.packet_id = g_packet_id
6238: and bp.ussgl_link_to_parent_id is not null;
6239:
6238: and bp.ussgl_link_to_parent_id is not null;
6239:
6240: -- ========================= FND LOG ===========================
6241: psa_utils.debug_other_string(g_state_level,l_full_path,
6242: ' update gl_bc_packets - je_bacth_id - ' || SQL%ROWCOUNT);
6243: -- ========================= FND LOG ===========================
6244:
6245: -- Clean up attribute1 in gl_je_headers (contains header id of the
6246: -- originating line)
6275: where jl.je_header_id in
6276: (
6277: select distinct jh.je_header_id
6278: from gl_je_headers jh,
6279: gl_bc_packets bp
6280: where jh.je_batch_id = bp.je_batch_id
6281: and bp.packet_id = g_packet_id
6282: and bp.ussgl_link_to_parent_id is not null
6283: )
6293: delete from gl_je_headers jh
6294: where jh.je_batch_id in
6295: (
6296: select distinct bp.je_batch_id
6297: from gl_bc_packets bp
6298: where bp.packet_id = g_packet_id
6299: and bp.ussgl_link_to_parent_id is not null
6300: );
6301:
6375: -- to rollback Funds Reserved after the Funds Checker call. This must be
6376: -- called before any commit that would otherwise confirm the final Funds
6377: -- Check Status of the packet
6378:
6379: -- This Module deletes all transaction lines of a packet in gl_bc_packets and
6380: -- the associated Arrival Order record in gl_bc_packet_arrival_order
6381:
6382: -- This Module also deletes the corresponding records for a packet being
6383: -- Unreserved
6405: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFPP1 --> START ');
6406: -- ========================= FND LOG ===========================
6407:
6408: -- Delete Packet Transactions
6409: delete from gl_bc_packets bp
6410: where bp.packet_id in (p_packetid, p_packetid_ursvd);
6411:
6412: -- ========================= FND LOG ===========================
6413: psa_utils.debug_other_string(g_state_level,l_full_path,
6410: where bp.packet_id in (p_packetid, p_packetid_ursvd);
6411:
6412: -- ========================= FND LOG ===========================
6413: psa_utils.debug_other_string(g_state_level,l_full_path,
6414: ' delete from gl_bc_packets ' || SQL%ROWCOUNT);
6415: -- ========================= FND LOG ===========================
6416:
6417: -- Delete Packet Arrival Order Record
6418: delete from gl_bc_packet_arrival_order ao
6451: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFPP2 --> START ');
6452: -- ========================= FND LOG ===========================
6453:
6454: -- Delete Packet Transactions
6455: delete from gl_bc_packets bp
6456: where bp.event_id = p_eventid;
6457:
6458: -- ========================= FND LOG ===========================
6459: psa_utils.debug_other_string(g_state_level,l_full_path,
6456: where bp.event_id = p_eventid;
6457:
6458: -- ========================= FND LOG ===========================
6459: psa_utils.debug_other_string(g_state_level,l_full_path,
6460: ' delete from gl_bc_packets ' || SQL%ROWCOUNT);
6461: -- ========================= FND LOG ===========================
6462:
6463: -- Delete Packet Arrival Order Record
6464: delete from gl_bc_packet_arrival_order ao
6462:
6463: -- Delete Packet Arrival Order Record
6464: delete from gl_bc_packet_arrival_order ao
6465: where ao.packet_id in (select packet_id
6466: from gl_bc_packets
6467: where event_id = p_eventid);
6468:
6469: -- ========================= FND LOG ===========================
6470: psa_utils.debug_other_string(g_state_level,l_full_path,
6471: ' delete from gl_bc_packet_arrival_order ' || SQL%ROWCOUNT);
6472: -- ========================= FND LOG ===========================
6473:
6474: -- Delete History Record
6475: delete from gl_bc_packets_hists bp
6476: where bp.event_id = p_eventid;
6477:
6478: -- ========================= FND LOG ===========================
6479: psa_utils.debug_other_string(g_state_level,l_full_path,
6476: where bp.event_id = p_eventid;
6477:
6478: -- ========================= FND LOG ===========================
6479: psa_utils.debug_other_string(g_state_level,l_full_path,
6480: ' delete from gl_bc_packets_hists ' || SQL%ROWCOUNT);
6481: -- ========================= FND LOG ===========================
6482:
6483: EXCEPTION
6484:
6523: psa_utils.debug_other_string(g_state_level,l_full_path, ' glxfuf -> START ');
6524: -- ========================= FND LOG ===========================
6525:
6526: -- Update Status Code for the Packet Transactions
6527: update gl_bc_packets bp
6528: set bp.status_code = 'T'
6529: where bp.packet_id = g_packet_id;
6530:
6531: -- ========================= FND LOG ===========================
6531: -- ========================= FND LOG ===========================
6532: psa_utils.debug_other_string(g_state_level,l_full_path,
6533: ' g_packet_id -> ' || g_packet_id );
6534: psa_utils.debug_other_string(g_state_level,l_full_path,
6535: ' update gl_bc_packets with T -> ' || SQL%ROWCOUNT );
6536: -- ========================= FND LOG ===========================
6537:
6538: -- Update Affect Funds Flag
6539: update gl_bc_packet_arrival_order ao
6659: AND status = 'VALID';
6660:
6661: CURSOR c_packet_count(c_packet_id IN NUMBER) is
6662: SELECT count(*) pkt_cnt
6663: FROM gl_bc_packets
6664: WHERE packet_id = c_packet_id;
6665:
6666: cursor c_batch_id (c_packet_id IN NUMBER) is
6667: SELECT 'Y' batch_id
6664: WHERE packet_id = c_packet_id;
6665:
6666: cursor c_batch_id (c_packet_id IN NUMBER) is
6667: SELECT 'Y' batch_id
6668: FROM GL_BC_PACKETS
6669: WHERE packet_id = c_packet_id
6670: AND je_batch_id IS NOT NULL
6671: AND rownum = 1;
6672:
6674: l_packet_count c_packet_count%rowtype;
6675: l_batch_id c_batch_id%rowtype;
6676:
6677: fv_prepay_stmt VARCHAR2(2000);
6678: p_packet_id gl_bc_packets.packet_id%type;
6679: p_status NUMBER(15);
6680: l_full_path VARCHAR2(100);
6681:
6682: BEGIN
7082: from dual
7083: where exists
7084: (
7085: select 'Associated Generated Row from existing GL Batch'
7086: from gl_bc_packets bp
7087: where bp.packet_id = g_packet_id
7088: and bp.je_batch_id is not null
7089: and bp.je_batch_id >= 0
7090: and bp.ussgl_transaction_code is not null
7260:
7261: /*=======================================================================+
7262: | Function : OPTIMIZE_PACKETS |
7263: | Description : Invoked by bc_optimizer rountine. This acts as a pvt |
7264: | function. Function deletes rows from gl_bc_packets and |
7265: | inserts them in gl_bc_packets_hists. Only rows with |
7266: | status_code R, S, F, T, P, C are deleted. |
7267: +=======================================================================*/
7268:
7261: /*=======================================================================+
7262: | Function : OPTIMIZE_PACKETS |
7263: | Description : Invoked by bc_optimizer rountine. This acts as a pvt |
7264: | function. Function deletes rows from gl_bc_packets and |
7265: | inserts them in gl_bc_packets_hists. Only rows with |
7266: | status_code R, S, F, T, P, C are deleted. |
7267: +=======================================================================*/
7268:
7269: PROCEDURE optimize_packets (p_ledger_id IN NUMBER, p_purge_days IN NUMBER) IS
7271: BEGIN
7272: l_full_path := g_path||'Optimize_Packets';
7273:
7274: /*-----------------------------------------------------------------------+
7275: | New criteria for deleting rows from gl_bc_packets is as below: |
7276: | |
7277: | Status_Code: |
7278: | =========== |
7279: | R, S, F, T - All rows for the p_ledger_id |
7276: | |
7277: | Status_Code: |
7278: | =========== |
7279: | R, S, F, T - All rows for the p_ledger_id |
7280: | - These rows should get inserted in gl_bc_packets_hists |
7281: | |
7282: | P, C - All rows for the p_ledger_id for which session has |
7283: | expired or which are older than 5 days (120 hours) |
7284: | - These rows should not be stored in gl_bc_packets_hists |
7280: | - These rows should get inserted in gl_bc_packets_hists |
7281: | |
7282: | P, C - All rows for the p_ledger_id for which session has |
7283: | expired or which are older than 5 days (120 hours) |
7284: | - These rows should not be stored in gl_bc_packets_hists |
7285: | |
7286: +----------------------------------------------------------------------*/
7287:
7288:
7285: | |
7286: +----------------------------------------------------------------------*/
7287:
7288:
7289: DELETE from gl_bc_packets Q
7290: where
7291: Q.status_code IN ('P', 'C')
7292: and ((((sysdate - Q.last_update_date)*24) > 48) OR
7293: (NOT EXISTS (SELECT 'x'
7295: WHERE audsid = Q.session_id
7296: and Serial# = Q.serial_id)));
7297:
7298: -- =========================== FND LOG ===========================
7299: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 1 deleted ' || SQL%ROWCOUNT || ' rows');
7300: -- ========================= FND LOG =============================
7301:
7302:
7303: DELETE from gl_bc_packets Q
7299: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 1 deleted ' || SQL%ROWCOUNT || ' rows');
7300: -- ========================= FND LOG =============================
7301:
7302:
7303: DELETE from gl_bc_packets Q
7304: where
7305: Q.ledger_id = p_ledger_id
7306: and Q.status_code in ('R','S','F', 'T') returning
7307: PACKET_ID,
7395: GROUP_ID
7396: bulk collect into g_bc_pkts_hist;
7397:
7398: -- =========================== FND LOG ===========================
7399: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 2 deleted ' || SQL%ROWCOUNT || ' rows');
7400: -- ========================= FND LOG =============================
7401:
7402:
7403: FORALL i IN 1..g_bc_pkts_hist.count
7400: -- ========================= FND LOG =============================
7401:
7402:
7403: FORALL i IN 1..g_bc_pkts_hist.count
7404: insert into gl_bc_packets_hists
7405: values g_bc_pkts_hist(i);
7406:
7407: if p_purge_days > 0 then
7408:
7474:
7475: /*=======================================================================+
7476: | Function : BC_PURGE_HIST |
7477: | Description : Invoked by SRS "Budgetary Control History Purge" |
7478: | Deletes rows from gl_bc_packets_hists depending upon |
7479: | the criteria selected by user while running SRS |
7480: +=======================================================================*/
7481:
7482: PROCEDURE bc_purge_hist (err_buf OUT NOCOPY VARCHAR2,
7501: psa_utils.debug_other_string(g_state_level,l_full_path, ' l_purge_date -> ' || TO_CHAR(l_purge_date, 'DD-MON-YYYY HH24:MI:SS'));
7502: -- ========================= FND LOG =============================
7503:
7504:
7505: l_stmt := 'delete from gl_bc_packets_hists '||
7506: 'where (last_update_date < :purge_date) '||
7507: ' and ledger_id = :p_ledger_id ';
7508:
7509: l_status_code := CASE p_purge_mode||p_purge_statuses
7535: END bc_purge_hist;
7536:
7537: /*=======================================================================+
7538: | Function : GET_PACKET_ID |
7539: | Description : Returns the next packet_id using gl_bc_packets_s seq |
7540: +=======================================================================*/
7541:
7542: FUNCTION get_packet_id RETURN NUMBER IS
7543: l_pkt_id gl_bc_packets.packet_id%type;
7539: | Description : Returns the next packet_id using gl_bc_packets_s seq |
7540: +=======================================================================*/
7541:
7542: FUNCTION get_packet_id RETURN NUMBER IS
7543: l_pkt_id gl_bc_packets.packet_id%type;
7544: l_full_path VARCHAR2(100);
7545: BEGIN
7546:
7547: l_full_path := g_path||'Get_Packet_Id';
7545: BEGIN
7546:
7547: l_full_path := g_path||'Get_Packet_Id';
7548:
7549: select gl_bc_packets_s.nextval into l_pkt_id
7550: from dual;
7551:
7552: -- =========================== FND LOG ===========================
7553: psa_utils.debug_other_string(g_state_level,l_full_path, ' l_pkt_id -> ' || l_pkt_id);
7557: END get_packet_id;
7558:
7559: /*=======================================================================+
7560: | Function : POPULATE_BC_PKTS |
7561: | Description : Inserts data in gl_bc_packets using the plsql table |
7562: | passed as parameter. Commits in autonomous mode. |
7563: +=======================================================================*/
7564:
7565: FUNCTION populate_bc_pkts (p_bc_pkts IN BC_PKTS_REC) RETURN BOOLEAN IS
7572: BEGIN
7573:
7574: l_full_path := g_path||'Populate_Bc_pkts';
7575:
7576: -- Now that plsql table is populated, insert data in gl_bc_packets.
7577:
7578: FORALL i IN 1..p_bc_pkts.count
7579: INSERT INTO gl_bc_packets
7580: VALUES p_bc_pkts(i);
7575:
7576: -- Now that plsql table is populated, insert data in gl_bc_packets.
7577:
7578: FORALL i IN 1..p_bc_pkts.count
7579: INSERT INTO gl_bc_packets
7580: VALUES p_bc_pkts(i);
7581:
7582: commit;
7583:
7731: l_source_name gl_je_headers.je_source%TYPE;
7732: l_period_name gl_je_batches.default_period_name%TYPE;
7733: l_je_batch_id gl_je_batches.je_batch_id%TYPE;
7734: l_batch_name gl_je_batches.name%TYPE;
7735: l_packet_id gl_bc_packets.packet_id%TYPE;
7736: l_main_stmt varchar2(4000);
7737: l_tmp_stmt varchar2(4000);
7738: l_action_stmt varchar2(4000);
7739: l_je_stmt varchar2(4000);
7748: l_date varchar2(50);
7749: l_header boolean;
7750: l_je_first boolean;
7751: l_ledger_id gl_automatic_posting_options.ledger_id%TYPE;
7752: l_session_id gl_bc_packets.session_id%type;
7753: l_serial_id gl_bc_packets.serial_id%type;
7754: l_seg_ccid varchar2(200);
7755: l_je_header_name gl_je_headers.name%TYPE;
7756: l_je_header_id gl_je_headers.je_header_id%TYPE;
7749: l_header boolean;
7750: l_je_first boolean;
7751: l_ledger_id gl_automatic_posting_options.ledger_id%TYPE;
7752: l_session_id gl_bc_packets.session_id%type;
7753: l_serial_id gl_bc_packets.serial_id%type;
7754: l_seg_ccid varchar2(200);
7755: l_je_header_name gl_je_headers.name%TYPE;
7756: l_je_header_id gl_je_headers.je_header_id%TYPE;
7757: l_je_line_num gl_je_lines.je_line_num%TYPE;
7757: l_je_line_num gl_je_lines.je_line_num%TYPE;
7758: l_entered_dr gl_je_lines.entered_dr%TYPE;
7759: l_entered_cr gl_je_lines.entered_cr%TYPE;
7760: l_line_description gl_lookups.description%TYPE;
7761: l_line_result_code gl_bc_packets.result_code%TYPE;
7762: l_ccid gl_je_lines.code_combination_id%TYPE;
7763: l_rowid varchar2(100);
7764: l_priority gl_lookups.meaning%TYPE;
7765: l_je_seg_stmt varchar2(4000);
7765: l_je_seg_stmt varchar2(4000);
7766: l_je_sum_flex varchar2(4000);
7767: l_je_bud_stmt varchar2(4000);
7768: l_je_bud_flex varchar2(4000);
7769: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7770: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7771: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7772: l_je_bud_desc gl_lookups.description%TYPE;
7773: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7766: l_je_sum_flex varchar2(4000);
7767: l_je_bud_stmt varchar2(4000);
7768: l_je_bud_flex varchar2(4000);
7769: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7770: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7771: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7772: l_je_bud_desc gl_lookups.description%TYPE;
7773: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7774: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7767: l_je_bud_stmt varchar2(4000);
7768: l_je_bud_flex varchar2(4000);
7769: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7770: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7771: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7772: l_je_bud_desc gl_lookups.description%TYPE;
7773: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7774: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7775: l_je_bud_seg_stmt varchar2(4000);
7770: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7771: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7772: l_je_bud_desc gl_lookups.description%TYPE;
7773: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7774: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7775: l_je_bud_seg_stmt varchar2(4000);
7776: l_je_bud_sum_flex varchar2(4000);
7777:
7778: -- XML variables
8012: -- =========================== XML OUT =============================
8013:
8014:
8015: -- Get the session_id and serial# for the current session
8016: -- These columns will then be inserted in gl_bc_packets.
8017:
8018: -- ====== FND LOG ======
8019: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking get_session_details() ');
8020: -- ====== FND LOG ======
8338: l_je_stmt := l_je_stmt||', SUBSTRB(h.name,1,20), h.je_header_id, l.je_line_num, l.entered_dr, '||
8339: 'l.entered_cr, lk.description, p.result_code, l.code_combination_id, '||
8340: 'p.rowid '||
8341: 'FROM gl_je_lines l, gl_je_headers h, gl_code_combinations c, '||
8342: 'gl_lookups lk, gl_bc_packets p '||
8343: 'WHERE p.je_batch_id = '||l_failed_bc_pkts(x).je_batch_id||
8344: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8345: ' and p.ledger_id = '||l_ledger_id||
8346: ' and p.je_header_id = h.je_header_id'||
8442:
8443: END LOOP; --End of segments loop
8444:
8445: l_je_seg_stmt := l_je_seg_stmt||' FROM gl_code_combinations c, gl_account_hierarchies h, '||
8446: ' gl_bc_packets p where h.detail_code_combination_id = '||
8447: l_ccid||' and p.code_combination_id = h.summary_code_combination_id '||
8448: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8449: ' and c.code_combination_id = p.code_combination_id';
8450:
8504: nvl(ussgl_parent_id, 0)
8505: INTO
8506: l_ussgl_parent_id
8507: FROM
8508: gl_bc_packets
8509: WHERE
8510: rowid = l_rowid;
8511: EXCEPTION
8512: WHEN OTHERS THEN
8510: rowid = l_rowid;
8511: EXCEPTION
8512: WHEN OTHERS THEN
8513: -- =========================== FND LOG ===========================
8514: psa_utils.debug_other_string(g_excep_level,l_full_path, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8515: fnd_file.put_line(fnd_file.log, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8516: -- ========================= FND LOG =============================
8517: END;
8518:
8511: EXCEPTION
8512: WHEN OTHERS THEN
8513: -- =========================== FND LOG ===========================
8514: psa_utils.debug_other_string(g_excep_level,l_full_path, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8515: fnd_file.put_line(fnd_file.log, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8516: -- ========================= FND LOG =============================
8517: END;
8518:
8519: l_je_bud_stmt := l_je_bud_stmt||' , p.entered_dr, p.entered_cr, p.result_code, l.description, '||
8516: -- ========================= FND LOG =============================
8517: END;
8518:
8519: l_je_bud_stmt := l_je_bud_stmt||' , p.entered_dr, p.entered_cr, p.result_code, l.description, '||
8520: ' c.code_combination_id FROM gl_code_combinations c, gl_bc_packets p, '||
8521: ' gl_lookups l WHERE p.ussgl_link_to_parent_id = '||l_ussgl_parent_id||
8522: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8523: ' and c.code_combination_id = p.code_combination_id'||
8524: ' and p.result_code between ''F00'' AND ''F30'' and '||
8579:
8580: END LOOP; --End of segments loop
8581:
8582: l_je_bud_seg_stmt := l_je_bud_seg_stmt||' FROM gl_code_combinations c, gl_account_hierarchies h, '||
8583: ' gl_bc_packets p where h.detail_code_combination_id = '||
8584: l_je_bud_ccid||' and p.code_combination_id = h.summary_code_combination_id '||
8585: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8586: ' and c.code_combination_id = p.code_combination_id';
8587:
8697: l_full_path VARCHAR2(100);
8698: BEGIN
8699: l_full_path := g_path||'Glsibc';
8700:
8701: INSERT INTO GL_BC_PACKETS
8702: (packet_id,
8703: ledger_id,
8704: je_source_name,
8705: je_category_name,
8779: min(BP.serial_id),
8780: min(BP.application_id)
8781: FROM
8782: GL_ACCOUNT_HIERARCHIES AH,
8783: GL_BC_PACKETS BP,
8784: GL_BC_PACKET_ARRIVAL_ORDER AO,
8785: GL_SUMMARY_TEMPLATES ST,
8786: GL_SUMMARY_BC_OPTIONS SB,
8787: GL_BUDGETS B,
8836: HAVING
8837: sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
8838:
8839: -- =========================== FND LOG ===========================
8840: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
8841: -- ========================= FND LOG =============================
8842:
8843: END glsibc;
8844:
8857:
8858: -- =========================== FND LOG ===========================
8859: fnd_file.put_line(fnd_file.log, 'p_curr_temp_id = '||p_curr_temp_id);
8860: fnd_file.put_line(fnd_file.log, 'p_ledger_id = '||p_ledger_id);
8861: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Inserting into GL_BC_PACKETS ...');
8862: -- =========================== FND LOG =============================
8863:
8864: INSERT INTO GL_BC_PACKETS
8865: (packet_id,
8860: fnd_file.put_line(fnd_file.log, 'p_ledger_id = '||p_ledger_id);
8861: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Inserting into GL_BC_PACKETS ...');
8862: -- =========================== FND LOG =============================
8863:
8864: INSERT INTO GL_BC_PACKETS
8865: (packet_id,
8866: ledger_id,
8867: je_source_name,
8868: je_category_name,
8928: min(BP.serial_id),
8929: min(BP.application_id)
8930: FROM
8931: GL_ACCOUNT_HIERARCHIES AH,
8932: GL_BC_PACKETS BP,
8933: GL_BC_PACKET_ARRIVAL_ORDER AO,
8934: GL_SUMMARY_TEMPLATES ST,
8935: GL_SUMMARY_BC_OPTIONS SB,
8936: GL_BUDGETS B,
8951: AND AO.affect_funds_flag = 'Y'
8952: AND ST.template_id = p_curr_temp_id
8953: AND NOT EXISTS
8954: ( Select 'Y'
8955: From GL_BC_PACKETS BP2
8956: Where BP2.ledger_id = p_ledger_id
8957: And BP2.template_id = p_curr_temp_id
8958: And BP2.code_combination_id = AH.summary_code_combination_id
8959: And BP2.packet_id = BP.packet_id
9001: HAVING
9002: sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
9003:
9004: -- =========================== FND LOG ===========================
9005: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9006: -- ========================= FND LOG =============================
9007:
9008:
9009: -- =========================== FND LOG ===========================
9006: -- ========================= FND LOG =============================
9007:
9008:
9009: -- =========================== FND LOG ===========================
9010: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9011: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9012: -- =========================== FND LOG =============================
9013:
9014: UPDATE gl_bc_packets bp2
9007:
9008:
9009: -- =========================== FND LOG ===========================
9010: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9011: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9012: -- =========================== FND LOG =============================
9013:
9014: UPDATE gl_bc_packets bp2
9015: SET (entered_dr, entered_cr, accounted_dr, accounted_cr,
9010: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9011: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9012: -- =========================== FND LOG =============================
9013:
9014: UPDATE gl_bc_packets bp2
9015: SET (entered_dr, entered_cr, accounted_dr, accounted_cr,
9016: effect_on_funds_code) =
9017: (SELECT SUM (NVL (bp.entered_dr, 0)), SUM (NVL (bp.entered_cr,0)),
9018: SUM (NVL (bp.accounted_dr, 0)),
9042: -1, 'D',
9043: 'I'
9044: )
9045: )
9046: FROM gl_bc_packets bp,
9047: gl_account_hierarchies ah,
9048: gl_bc_packet_arrival_order ao,
9049: gl_summary_templates st,
9050: gl_summary_bc_options sb,
9100: FROM gl_code_combinations
9101: WHERE template_id = p_curr_temp_id);
9102:
9103: -- =========================== FND LOG ===========================
9104: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9105: -- ========================= FND LOG =============================
9106:
9107:
9108:
9110: | The Delete statement will be executed here ALWAYS |
9111: +---------------------------------------------------*/
9112:
9113: -- =========================== FND LOG ===========================
9114: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Deleting from GL_BC_PACKETS ...');
9115: -- =========================== FND LOG =============================
9116:
9117:
9118: DELETE FROM gl_bc_packets bp
9114: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Deleting from GL_BC_PACKETS ...');
9115: -- =========================== FND LOG =============================
9116:
9117:
9118: DELETE FROM gl_bc_packets bp
9119: WHERE bp.ledger_id = p_ledger_id
9120: AND bp.template_id = p_curr_temp_id
9121: AND bp.packet_id IN (
9122: SELECT ao.packet_id
9130: AND ah.template_id = p_curr_temp_id
9131: AND ah.summary_code_combination_id = bp.code_combination_id);
9132:
9133: -- =========================== FND LOG ===========================
9134: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9135: -- ========================= FND LOG =============================
9136:
9137: -- =========================== FND LOG ===========================
9138: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9134: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9135: -- ========================= FND LOG =============================
9136:
9137: -- =========================== FND LOG ===========================
9138: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9139: -- =========================== FND LOG =============================
9140:
9141: END glsfbc;
9142:
9185: l_full_path VARCHAR2(100);
9186: BEGIN
9187: l_full_path := g_path||'Populate_Group_Id';
9188:
9189: UPDATE gl_bc_packets
9190: SET group_id = p_grp_id,
9191: je_batch_name = p_je_batch_name
9192: WHERE ae_header_id IN (SELECT ae_header_id
9193: FROM xla_ae_headers
9194: WHERE group_id = p_grp_id
9195: and application_id = p_application_id);
9196:
9197: -- =========================== FND LOG ===========================
9198: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9199: -- ========================= FND LOG =============================
9200:
9201: END populate_group_id;
9202:
9481:
9482: FUNCTION budgetary_control (p_ledgerid IN NUMBER,
9483: p_return_code OUT NOCOPY VARCHAR2) return BOOLEAN IS
9484:
9485: l_session_id gl_bc_packets.session_id%type;
9486: l_serial_id gl_bc_packets.serial_id%type;
9487:
9488: l_packet_id gl_bc_packets.packet_id%type;
9489: l_bc_pkts bc_pkts_rec;
9482: FUNCTION budgetary_control (p_ledgerid IN NUMBER,
9483: p_return_code OUT NOCOPY VARCHAR2) return BOOLEAN IS
9484:
9485: l_session_id gl_bc_packets.session_id%type;
9486: l_serial_id gl_bc_packets.serial_id%type;
9487:
9488: l_packet_id gl_bc_packets.packet_id%type;
9489: l_bc_pkts bc_pkts_rec;
9490: l_packets num_rec;
9484:
9485: l_session_id gl_bc_packets.session_id%type;
9486: l_serial_id gl_bc_packets.serial_id%type;
9487:
9488: l_packet_id gl_bc_packets.packet_id%type;
9489: l_bc_pkts bc_pkts_rec;
9490: l_packets num_rec;
9491: l_ret_code VARCHAR2(1);
9492: l_bc_ret_code VARCHAR2(1);
9496: l_p_status_cnt NUMBER(5);
9497: l_t_status_cnt NUMBER(5);
9498: l_je_source_name xla_subledgers.je_source_name%type;
9499: invalid_je_source_name EXCEPTION;
9500: gl_bc_packets_empty EXCEPTION;
9501:
9502: -- ========================= FND LOG ===========================
9503: l_full_path VARCHAR2(100);
9504: -- ========================= FND LOG ===========================
9514: sum(decode(status_code, 'S', 1, 0)) success_cnt,
9515: sum(decode(status_code, 'A', 1, 0)) approved_cnt,
9516: sum(decode(status_code, 'F', 1, 0)) failed_cnt,
9517: sum(decode(status_code, 'R', 1, 0)) rejected_cnt
9518: FROM gl_bc_packets
9519: WHERE packet_id = p_packet_id
9520: GROUP BY ae_header_id;
9521:
9522: CURSOR get_ledger_category (p_ledgerid IN NUMBER) IS
9756: IS
9757: SELECT 'Funds Failure for hierarchy'
9758: FROM DUAL
9759: WHERE EXISTS
9760: (SELECT 'X' FROM GL_BC_PACKETS
9761: WHERE (ae_header_id, ae_line_num, event_id)
9762: IN (select ae_header_id, ae_line_num, event_id
9763: from psa_bc_alloc_gt
9764: where hierarchy_id = p_hierarchy_id
9799: 'Y', 'P',
9800: 'F'
9801: )
9802: )
9803: FROM gl_bc_packets bp
9804: WHERE bp.packet_id = p_packet_id AND bp.template_id IS NULL;
9805:
9806: -- Bug 5397349 .. Start
9807: CURSOR c_get_result_codes (p_packet_id IN NUMBER) IS
9805:
9806: -- Bug 5397349 .. Start
9807: CURSOR c_get_result_codes (p_packet_id IN NUMBER) IS
9808: SELECT result_code, ae_header_id, ae_line_num
9809: FROM gl_bc_packets
9810: WHERE packet_id = p_packet_id;
9811:
9812: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
9813: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
9808: SELECT result_code, ae_header_id, ae_line_num
9809: FROM gl_bc_packets
9810: WHERE packet_id = p_packet_id;
9811:
9812: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
9813: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
9814: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
9815:
9816: l_result_code_tbl result_code_tbl_type;
9809: FROM gl_bc_packets
9810: WHERE packet_id = p_packet_id;
9811:
9812: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
9813: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
9814: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
9815:
9816: l_result_code_tbl result_code_tbl_type;
9817: l_xla_hdr_tbl xla_hdr_tbl_type;
9810: WHERE packet_id = p_packet_id;
9811:
9812: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
9813: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
9814: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
9815:
9816: l_result_code_tbl result_code_tbl_type;
9817: l_xla_hdr_tbl xla_hdr_tbl_type;
9818: l_xla_line_tbl xla_line_tbl_type;
9942:
9943: l_packets := num_rec();
9944:
9945: -- Get the session_id and serial# for the current session
9946: -- These columns will then be inserted in gl_bc_packets.
9947:
9948: -- ====== FND LOG ======
9949: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking get_session_details() ');
9950: -- ====== FND LOG ======
10123: --=================== Allocation Attributes first level of validation Logic End ===========================
10124:
10125:
10126: -- Now select the event_id and other information to be inserted
10127: -- in gl_bc_packets in plsql table. We will select all event_id
10128: -- to be inserted at one go for performance reasons. I have selected all columns
10129: -- from gl_bc_packets and put NULL for columns which should not be populated. This is to
10130: -- overcome a limitation with FORALL clause later in the code.
10131:
10125:
10126: -- Now select the event_id and other information to be inserted
10127: -- in gl_bc_packets in plsql table. We will select all event_id
10128: -- to be inserted at one go for performance reasons. I have selected all columns
10129: -- from gl_bc_packets and put NULL for columns which should not be populated. This is to
10130: -- overcome a limitation with FORALL clause later in the code.
10131:
10132:
10133: -- ====== FND LOG ======
10256: -- ====== FND LOG ======
10257:
10258: IF (l_ledger_category = 'PRIMARY') OR (psa_bc_xla_pvt.g_application_id = 602) THEN
10259: -- ==================== FND LOG ===========================
10260: psa_utils.debug_other_string(g_state_level,l_full_path, ' l_ledger_category = PRIMARY OR (psa_bc_xla_pvt.g_application_id = 602 --> raise gl_bc_packets_empty');
10261: -- ==================== FND LOG ===========================
10262:
10263: raise gl_bc_packets_empty;
10264: ELSE
10259: -- ==================== FND LOG ===========================
10260: psa_utils.debug_other_string(g_state_level,l_full_path, ' l_ledger_category = PRIMARY OR (psa_bc_xla_pvt.g_application_id = 602 --> raise gl_bc_packets_empty');
10261: -- ==================== FND LOG ===========================
10262:
10263: raise gl_bc_packets_empty;
10264: ELSE
10265:
10266: -- ====== FND LOG ======
10267: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Do not process for Secondary Ledger ');
10307: -- ====== FND LOG ======
10308:
10309: END LOOP;
10310:
10311: -- Insert autonomous procedure populate_bc_pkts to insert data in gl_bc_packets
10312:
10313: -- ====== FND LOG ======
10314: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking populate_bc_pkts() ');
10315: -- ====== FND LOG ======
10382: -- ====== FND LOG ======
10383:
10384: IF (y.success_cnt = y.total_cnt) OR (y.approved_cnt = y.total_cnt) THEN
10385: SELECT nvl(min('A'), 'S') into l_xla_hdr_status
10386: FROM gl_bc_packets
10387: WHERE packet_id = l_packets(i) and
10388: ae_header_id = y.ae_header_id and
10389: result_code IN ('P20', 'P22', 'P25', 'P27', 'P31', 'P35', 'P36', 'P37',
10390: 'P38', 'P39');
10414: ELSE
10415: UPDATE xla_ae_headers_gt
10416: SET funds_status_code = l_ret_code
10417: WHERE ae_header_id IN (SELECT ae_header_id
10418: FROM gl_bc_packets
10419: WHERE packet_id = l_packets(i)) and
10420: ledger_id = p_ledgerid;
10421:
10422: -- ====== FND LOG ======
10557: --================== Allocation Attributes second level of validation Logic start =================
10558: /* Now we need to check that the transaction rows which are put for funds operation,
10559: have been successfully processed by funds checker. If atleast one transaction row
10560: for a hierarchy fails funds operation, we will fail all the related transaction rows in
10561: GL_BC_PACKETS with the status F77. */
10562:
10563: IF (l_alloc_used = 'Y') THEN
10564: FOR h in c_get_hierarchy_id
10565: LOOP
10568: l_serial_id);
10569: FETCH c_chk_funds_hier INTO dummy;
10570: IF (c_chk_funds_hier%FOUND) THEN
10571: CLOSE c_chk_funds_hier;
10572: -- update the statuses of gl_bc_packets
10573: -- related rows to 'F' Failed or 'R' Rejected.
10574: UPDATE gl_bc_packets
10575: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10576: 'M', 'F',
10570: IF (c_chk_funds_hier%FOUND) THEN
10571: CLOSE c_chk_funds_hier;
10572: -- update the statuses of gl_bc_packets
10573: -- related rows to 'F' Failed or 'R' Rejected.
10574: UPDATE gl_bc_packets
10575: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10576: 'M', 'F',
10577: 'R', 'R',
10578: 'P', 'R')
10586: AND session_id = l_session_id
10587: AND serial_id = l_serial_id;
10588: -- ====== FND LOG ======
10589: psa_utils.debug_other_string(g_state_level, l_full_path, 'BCTRL -> Updated '
10590: ||sql%rowcount||' rows of GL_BC_PACKETS with F77 status.');
10591: -- ====== FND LOG ======
10592:
10593: ELSE
10594: CLOSE c_chk_funds_hier;
10600: 'M', 'F',
10601: 'R', 'F',
10602: 'P', 'F')
10603: WHERE ae_header_id IN (SELECT ae_header_id
10604: FROM gl_bc_packets
10605: WHERE result_code = 'F77'
10606: AND session_id = l_session_id
10607: AND serial_id = l_serial_id) and
10608: ledger_id = p_ledgerid;
10613:
10614: UPDATE xla_validation_lines_gt vl
10615: SET vl.funds_status_code = 'F77'
10616: WHERE vl.ae_header_id IN (SELECT ae_header_id
10617: FROM gl_bc_packets
10618: WHERE result_code = 'F77'
10619: AND session_id = l_session_id
10620: AND serial_id = l_serial_id) and
10621: vl.ledger_id = p_ledgerid;
10681:
10682: return TRUE;
10683:
10684: EXCEPTION
10685: WHEN GL_BC_PACKETS_EMPTY THEN
10686: IF (NOT g_xla_debug) THEN
10687: -- ====== FND LOG ======
10688: psa_utils.debug_other_string(g_error_level,l_full_path, ' BCTRL -> XLA_AE_HEADERS_GT DUMP');
10689: psa_utils.debug_other_string(g_error_level,l_full_path, ' -------------------- ');
10779: select count(*) into l_var_1
10780: from xla_psa_bc_lines_v;
10781: -- ====== FND LOG ======
10782: psa_utils.debug_other_string(g_error_level,l_full_path, ' XLA_PSA_BC_LINES_V returns '||l_var_1||' rows. ');
10783: psa_utils.debug_other_string(g_error_level,l_full_path, ' Error: Populated 0 rows in gl_bc_packets.');
10784: psa_utils.debug_other_string(g_error_level,l_full_path, ' RETURN -> FALSE');
10785: -- ====== FND LOG ======
10786: p_return_code := 'T';
10787: return FALSE;
10810:
10811:
10812: CURSOR c_success_evt_exists IS
10813: SELECT 'Successful event exists in the current packet'
10814: FROM gl_bc_packets
10815: WHERE event_id IN (SELECT event_id
10816: FROM psa_bc_xla_events_gt
10817: )
10818: AND application_id = PSA_BC_XLA_PVT.g_application_id
10838: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> p_failed_evnt_array.COUNT: '|| p_failed_evnt_array.COUNT);
10839: -- ====== FND LOG ======
10840:
10841: FORALL i IN 1..p_failed_ldgr_array.COUNT
10842: UPDATE gl_bc_packets
10843: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10844: 'M', 'F',
10845: 'R', 'R',
10846: 'P', 'R')
10852:
10853: l_f81_cnt := SQL%ROWCOUNT;
10854:
10855: -- ====== FND LOG ======
10856: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f81_cnt||
10857: ' rows to F81 status.');
10858: -- ====== FND LOG ======
10859:
10860:
10858: -- ====== FND LOG ======
10859:
10860:
10861: FORALL j IN 1..p_failed_evnt_array.COUNT
10862: UPDATE gl_bc_packets
10863: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10864: 'M', 'F',
10865: 'R', 'R',
10866: 'P', 'R')
10871:
10872: l_f82_cnt := SQL%ROWCOUNT;
10873:
10874: -- ====== FND LOG ======
10875: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f82_cnt||
10876: ' rows to F82 status.');
10877: -- ====== FND LOG ======
10878:
10879: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
10875: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f82_cnt||
10876: ' rows to F82 status.');
10877: -- ====== FND LOG ======
10878:
10879: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
10880: -- row/s to failure then there was a genuine failure in GL_BC_PACKETS
10881: -- and we should not overwrite that failure with XLA_ERROR for the event.
10882: -- We need to retain the original failure status reported and inform the same
10883: -- back to the calling transaction.
10876: ' rows to F82 status.');
10877: -- ====== FND LOG ======
10878:
10879: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
10880: -- row/s to failure then there was a genuine failure in GL_BC_PACKETS
10881: -- and we should not overwrite that failure with XLA_ERROR for the event.
10882: -- We need to retain the original failure status reported and inform the same
10883: -- back to the calling transaction.
10884:
10911: UPDATE gl_bc_packet_arrival_order
10912: SET affect_funds_flag = 'N'
10913: WHERE affect_funds_flag = 'Y'
10914: AND packet_id IN ( SELECT packet_id
10915: FROM gl_bc_packets bc
10916: WHERE event_id IN ( SELECT event_id
10917: FROM psa_bc_xla_events_gt
10918: )
10919: AND application_id = PSA_BC_XLA_PVT.g_application_id