[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;
274: /* FND_FLEX_APIS */
275: /* */
276: /* GL Tables which are being used include : */
277: /* */
278: /* GL_BC_PACKETS */
279: /* GL_BC_PACKET_ARRIVAL_ORDER */
280: /* GL_BC_OPTIONS */
281: /* GL_BC_OPTION_DETAILS */
282: /* GL_BC_PERIOD_MAP */
472: p_return_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
473:
474: cursor det_override_reqd IS
475: select 'x'
476: from gl_bc_packets bp
477: where bp.packet_id = g_packet_id
478: and bp.result_code between 'F00' and 'F19'
479: and bp.ussgl_link_to_parent_id is null
480: and bp.template_id is null
484: (
485: select 'If Partial Resv disallowed then all non-generated ' ||
486: 'detail lines that failed with any validation errors ' ||
487: 'or because of Funds Availability'
488: from gl_bc_packets pk
489: where pk.packet_id = g_packet_id
490: and pk.template_id is null
491: and pk.result_code like 'F%'
492: and ((g_partial_resv_flag = 'N'
499: );
500:
501: cursor ussgl_override_reqd is
502: select 'x'
503: from gl_bc_packets bp
504: where bp.packet_id = g_packet_id
505: and bp.result_code between 'F00' and 'F19'
506: and bp.ussgl_link_to_parent_id is not null
507: and exists
506: and bp.ussgl_link_to_parent_id is not null
507: and exists
508: (
509: select 'Corresp Original Transaction which was Overridden'
510: from gl_bc_packets pk
511: where pk.packet_id = g_packet_id
512: and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
513: and pk.result_code = 'P21'
514: );
1382: PRAGMA AUTONOMOUS_TRANSACTION;
1383:
1384: CURSOR source_cat IS
1385: SELECT distinct je_source_name, je_category_name
1386: FROM gl_bc_packets
1387: WHERE packet_id = decode(g_fcmode, 'U', g_packet_id_ursvd, g_packet_id);
1388:
1389: l_option_selected NUMBER;
1390:
1772: from dual
1773: where exists
1774: (
1775: select 'Transaction with USSGL Code'
1776: from gl_bc_packets bp
1777: where bp.packet_id = g_packet_id
1778: and bp.ussgl_transaction_code is not null
1779: );
1780:
1783: from dual
1784: where exists
1785: (
1786: select 'Associated Generated Row from existing GL Batch'
1787: from gl_bc_packets bp
1788: where bp.packet_id = g_packet_id
1789: and bp.je_batch_id is not null
1790: and bp.je_batch_id >= 0
1791: and bp.ussgl_transaction_code is not null
1846:
1847: close append_je;
1848:
1849:
1850: UPDATE GL_BC_PACKETS BP
1851: SET BP.ussgl_parent_id = GL_USSGL_PARENT_S.NEXTVAL
1852: WHERE
1853: BP.packet_id = g_packet_id
1854: AND BP.ussgl_transaction_code IS NOT NULL;
1853: BP.packet_id = g_packet_id
1854: AND BP.ussgl_transaction_code IS NOT NULL;
1855:
1856: -- =========================== FND LOG ===========================
1857: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
1858: -- ========================= FND LOG =============================
1859:
1860: if g_append_je_flag then
1861:
1863: -- ## Bug: 1387967/2178715 Federal AR |
1864: -- ## Drill down of USSGL generated transactions to AR. |
1865: -- ## ----------------------------------------------------------+
1866:
1867: UPDATE GL_BC_PACKETS BP
1868: SET
1869: ( BP.reference1,
1870: BP.reference2,
1871: BP.reference3,
1896:
1897: end if;
1898:
1899: -- =========================== FND LOG ===========================
1900: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
1901: psa_utils.debug_other_string(g_state_level,l_full_path, 'Calling glxfkf' );
1902: -- ========================= FND LOG =============================
1903:
1904: -- Retrieve Flex Info for the Flex Structure
1909: return(FALSE);
1910: end if;
1911:
1912:
1913: -- Insertion of USSGL transactions into gl_bc_packets
1914:
1915: -- When the USSGL Option is set, this process is executed prior to setting
1916: -- up of the denormalized columns and this applies to Funds Check and
1917: -- Funds Reservation. For all packet transactions with a USSGL transaction
1977: -- the transaction is created and the CCID is initialized to the negative
1978: -- value of the originating transaction's CCID. These transactions are
1979: -- then inserted into the Code Combinations table with new CCIDs
1980:
1981: sql_ussgl := 'insert into gl_bc_packets (packet_id, ' ||
1982: 'ledger_id, ' ||
1983: 'je_source_name, ' ||
1984: 'je_category_name, ' ||
1985: 'code_combination_id, ' ||
2159: 'gl_ussgl_transaction_codes uc, ' ||
2160: 'gl_ussgl_account_pairs guap, ' ||
2161: 'gl_code_combinations ccg, ' ||
2162: 'gl_code_combinations cco, ' ||
2163: 'gl_bc_packets bp, ' ||
2164: 'psa_event_types pet ' ||
2165: 'where lu.lookup_type = ''DR_CR'' ' ||
2166: 'and pet.je_source (+) = bp.je_source_name ' ||
2167: -- modified for bug 4167009
2195: 'gl_ussgl_transaction_codes uc, ' ||
2196: 'gl_ussgl_account_pairs guap, ' ||
2197: 'gl_code_combinations ccg, ' ||
2198: 'gl_code_combinations cco, ' ||
2199: 'gl_bc_packets bp ' ||
2200: 'where lu.lookup_type = ''DR_CR'' ' ||
2201: 'and uc.chart_of_accounts_id = ' ||
2202: 'guap.chart_of_accounts_id ' ||
2203: 'and uc.ussgl_transaction_code = ' ||
2277:
2278: sql_ussgl := sql_ussgl ||
2279: 'bp.code_combination_id ' ||
2280: 'from gl_code_combinations cc, ' ||
2281: 'gl_bc_packets bp ' ||
2282: 'where cc.code_combination_id = -1 * bp.code_combination_id ' ||
2283: 'and bp.packet_id = ' || g_packet_id || ' ' ||
2284: 'and bp.code_combination_id < 0 ' ||
2285: 'and bp.account_segment_value is not null';
2323:
2324:
2325: -- Update the CCID of the USSGL transaction in the Packet
2326:
2327: update gl_bc_packets bp
2328: set bp.code_combination_id = l_ccid_out
2329: where bp.code_combination_id = l_ccid
2330: and bp.account_segment_value=seg_val(g_acct_seg_index);
2331:
2329: where bp.code_combination_id = l_ccid
2330: and bp.account_segment_value=seg_val(g_acct_seg_index);
2331:
2332: -- =========================== FND LOG ===========================
2333: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets -> ' || SQL%ROWCOUNT);
2334: -- ========================= FND LOG =============================
2335:
2336: else
2337: exit;
2541:
2542: FUNCTION glxfiu RETURN BOOLEAN IS
2543:
2544: cursor pkt_id is
2545: select gl_bc_packets_s.nextval
2546: from dual;
2547:
2548: l_dummy VARCHAR2(80);
2549:
2565: -- =========================== FND LOG ===========================
2566: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_packet_id -> ' || g_packet_id );
2567: -- ========================= FND LOG =============================
2568:
2569: -- Insert Unreservation Packet into gl_bc_packets
2570: insert into gl_bc_packets
2571: (packet_id,
2572: ledger_id,
2573: je_source_name,
2566: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_packet_id -> ' || g_packet_id );
2567: -- ========================= FND LOG =============================
2568:
2569: -- Insert Unreservation Packet into gl_bc_packets
2570: insert into gl_bc_packets
2571: (packet_id,
2572: ledger_id,
2573: je_source_name,
2574: je_category_name,
2673: g_serial_id,
2674: g_resp_appl_id
2675: from psa_option_details_gt od,
2676: gl_budget_assignments ba,
2677: gl_bc_packets bp,
2678: gl_budorg_bc_options bo
2679: where (od.je_source_name || ';' || od.je_category_name =
2680: bp.je_source_name || ';' ||bp.je_category_name )
2681: and od.packet_id = bp.packet_id
2715: and bp.template_id is null
2716: and bp.status_code = 'A';
2717:
2718: -- =========================== FND LOG ===========================
2719: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert gl_bc_packets -> ' || SQL%ROWCOUNT );
2720: -- ========================= FND LOG =============================
2721:
2722: BEGIN
2723:
2731: (
2732: SELECT
2733: 'Record with non-null USSGL transaction code'
2734: FROM
2735: GL_BC_PACKETS BP
2736: WHERE
2737: BP.packet_id = g_packet_id_ursvd
2738: AND BP.ussgl_transaction_code IS NOT NULL
2739: );
2858:
2859:
2860: begin
2861:
2862: update gl_bc_packets bp
2863: set bp.funding_budget_version_id =
2864: (select decode(pk.actual_flag, 'B', pk.budget_version_id,
2865: bo.funding_budget_version_id)
2866: from gl_budget_assignments ba,
2866: from gl_budget_assignments ba,
2867: gl_budgets b,
2868: gl_budget_versions bv,
2869: gl_period_statuses ps,
2870: gl_bc_packets pk,
2871: gl_budorg_bc_options bo
2872: where
2873: ba.ledger_id(+) = g_ledger_id
2874: and ba.currency_code(+) = decode(PK.currency_code,
2906: WHEN OVERLAPPING_BUDGET THEN
2907:
2908: -- =========================== FND LOG ===========================
2909: psa_utils.debug_other_string(g_state_level,l_full_path, ' There are multiple overlapping budgets assigned to account');
2910: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated failed');
2911: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the status code = F/R based on g_fcmode');
2912: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the result code= F77/F80 based on overlapping budget');
2913: -- ========================= FND LOG =============================
2914:
2911: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the status code = F/R based on g_fcmode');
2912: psa_utils.debug_other_string(g_state_level,l_full_path, ' Updating the result code= F77/F80 based on overlapping budget');
2913: -- ========================= FND LOG =============================
2914:
2915: update gl_bc_packets bp
2916: set STATUS_CODE = DECODE(g_fcmode,'C','F','R'),
2917: RESULT_CODE=
2918: ( select DECODE(count(bo.FUNDING_BUDGET_VERSION_ID),1,'F77','F80')
2919: from
2916: set STATUS_CODE = DECODE(g_fcmode,'C','F','R'),
2917: RESULT_CODE=
2918: ( select DECODE(count(bo.FUNDING_BUDGET_VERSION_ID),1,'F77','F80')
2919: from
2920: gl_bc_packets pk,
2921: gl_budget_assignments ba,
2922: gl_budorg_bc_options bo
2923: where
2924: pk.rowid=bp.rowid
2938: end;
2939:
2940:
2941: -- =========================== FND LOG ===========================
2942: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
2943: -- ========================= FND LOG =============================
2944:
2945: update gl_bc_packets bp
2946: set (bp.automatic_encumbrance_flag,
2941: -- =========================== FND LOG ===========================
2942: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
2943: -- ========================= FND LOG =============================
2944:
2945: update gl_bc_packets bp
2946: set (bp.automatic_encumbrance_flag,
2947: bp.funds_check_level_code,
2948: bp.tolerance_percentage,
2949: bp.tolerance_amount,
3014: gl_budget_period_ranges br,
3015: gl_period_statuses ps,
3016: gl_code_combinations cc,
3017: psa_option_details_gt od,
3018: gl_bc_packets pk
3019: where uc.chart_of_accounts_id (+) = g_coa_id
3020: and uc.ussgl_transaction_code (+) =
3021: nvl(pk.ussgl_transaction_code, -1)
3022: and bv.budget_version_id (+) = nvl(pk.budget_version_id, -1)
3036: where bp.packet_id = g_packet_id
3037: and bp.template_id is null;
3038:
3039: -- =========================== FND LOG ===========================
3040: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
3041: -- ========================= FND LOG =============================
3042:
3043: update gl_bc_packets bp
3044: set (funds_check_level_code,
3039: -- =========================== FND LOG ===========================
3040: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
3041: -- ========================= FND LOG =============================
3042:
3043: update gl_bc_packets bp
3044: set (funds_check_level_code,
3045: amount_type,
3046: boundary_code) = (select
3047: nvl(min(decode(bo.funds_check_level_code, 'D',
3048: nvl(od.funds_check_level_code, 'D'),
3049: nvl(bo.funds_check_level_code, 'N'))), 'N'),
3050: min(bo.amount_type),
3051: min(bo.boundary_code)
3052: from gl_bc_packets pk,
3053: psa_option_details_gt od,
3054: gl_budget_assignments ba,
3055: gl_budorg_bc_options bo
3056: where pk.rowid = bp.rowid
3068: and bp.funding_budget_version_id is not null;
3069:
3070:
3071: -- =========================== FND LOG ===========================
3072: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
3073: -- ========================= FND LOG =============================
3074:
3075:
3076: /*----------------------------------------------------------------------+
3080: | no budget assignments... |
3081: +----------------------------------------------------------------------*/
3082:
3083: if(nvl(g_enable_efc_flag,'N')='Y') THEN
3084: update gl_bc_packets bp
3085: set bp.funds_check_level_code = 'B'
3086: where bp.packet_id = g_packet_id
3087: and bp.template_id is null
3088: and bp.funds_check_level_code = 'N'
3139: -- ensures that if a template is Proprietary, Summarization ignore any
3140: -- Budgetary Detail Accounts that may fall into the same Rollup Structure,
3141: -- and vice-versa
3142:
3143: insert into gl_bc_packets (packet_id,
3144: ledger_id,
3145: je_source_name,
3146: je_category_name,
3147: code_combination_id,
3227: from psa_option_details_gt od,
3228: gl_period_statuses ps,
3229: gl_summary_templates st,
3230: gl_account_hierarchies ah,
3231: gl_bc_packets bp,
3232: gl_summary_bc_options sb,
3233: gl_budgets b,
3234: gl_budget_versions bv,
3235: gl_period_statuses ps2
3291:
3292: if SQL%FOUND then
3293: -- =========================== FND LOG ===========================
3294: psa_utils.debug_other_string(g_state_level,l_full_path,
3295: ' Insert gl_bc_packets - summ trans ' || SQL%ROWCOUNT );
3296:
3297: psa_utils.debug_other_string(g_state_level,l_full_path,
3298: ' g_summarized_flag -> TRUE' );
3299: -- ========================= FND LOG =============================
3422:
3423: -- Bug 3574935
3424:
3425: --Bug 6823089 ..
3426: --l_max_packet_id gl_bc_packets.packet_id%type;
3427:
3428: -- Bug 5644702
3429: l_effective_period_num gl_period_statuses.effective_period_num%TYPE;
3430: l_period_name gl_period_statuses.period_name%TYPE;
3467: -- Bug 3574935 .. Start
3468: -- Obtain the maximum packet_id less than the current arrival sequence from
3469: -- table gl_bc_packet_arrival_order. This will then be used in the following
3470: -- UPDATE statement to help improve performance of the query. Making use of
3471: -- packet_id condition in the subquery makes index gl_bc_packets_n2 more
3472: -- selective and reduces number of rows processed during access to the table
3473: -- gl_bc_packet_arrival_order
3474:
3475: -- Bug 4651919 .. Start
3490: -- psa_utils.debug_other_string(g_state_level,l_full_path, ' l_max_packet_id -> '|| l_max_packet_id );
3491: -- ========================= FND LOG =============================
3492:
3493: -- Lock dummy table gl_bc_dual in Row Share Mode to ensure Read Consistency
3494: -- between gl_bc_packets and gl_balances in the next two balance update
3495: -- SQLs. This scheme requires posting to lock gl_bc_dual in exclusive mode
3496: -- before it commits, and to wait in a sleep cycle of 15 seconds until all
3497: -- the Funds Check processes release the locks. This prevents the Funds
3498: -- Checker from counting the Balances twice, in case Posting Commits in
3561: END;
3562: END LOOP;
3563: END IF;
3564:
3565: -- Update Approved and Pending Balances in gl_bc_packets
3566: --
3567: -- Transactions in the gl_bc_packets that would affect the transactions in
3568: -- this packet are :
3569: --
3563: END IF;
3564:
3565: -- Update Approved and Pending Balances in gl_bc_packets
3566: --
3567: -- Transactions in the gl_bc_packets that would affect the transactions in
3568: -- this packet are :
3569: --
3570: -- all approved packets, for the same Set of Books, that arrived earlier
3571: --
3628: -- EOB = Last Period in Latest Open Year of Budget
3629:
3630:
3631: update
3632: gl_bc_packets bp
3633: set (bp.budget_approved_balance,
3634: bp.actual_approved_balance,
3635: bp.encumbrance_approved_balance,
3636: bp.budget_pending_balance,
3667: )
3668: from gl_period_statuses ps,
3669: gl_budgets bd,
3670: gl_budget_versions bv,
3671: gl_bc_packets pk,
3672: gl_bc_packet_arrival_order ao
3673: where ps.application_id = 101
3674: and ps.ledger_id = g_ledger_id
3675: and ps.period_name = bd.last_valid_period_name
3756:
3757:
3758: -- =========================== FND LOG ===========================
3759: psa_utils.debug_other_string(g_state_level,l_full_path,
3760: ' Update approved and pending balance in gl_bc_packets ' || SQL%ROWCOUNT );
3761: -- ========================= FND LOG =============================
3762:
3763:
3764: -- Update Posted Balances in gl_bc_packets
3760: ' Update approved and pending balance in gl_bc_packets ' || SQL%ROWCOUNT );
3761: -- ========================= FND LOG =============================
3762:
3763:
3764: -- Update Posted Balances in gl_bc_packets
3765: --
3766: -- For Actuals, we subtract the begin balances of the first period of the
3767: -- transaction year, i.e YTD Funds Available includes actual activities
3768: -- accumulated during the current transaction year
3785: -- ------------------------------------ ----------- -------------------
3786: -- SORT AGGREGATE
3787: -- NESTED LOOPS
3788: -- NESTED LOOPS
3789: -- TABLE ACCESS BY ROWID GL_BC_PACKETS
3790: -- INDEX RANGE SCAN GL_BC_PACKETS_N1
3791: -- INDEX RANGE SCAN GL_BC_PERIOD_MAP_U2
3792: -- TABLE ACCESS BY ROWID GL_BALANCES
3793: -- INDEX RANGE SCAN GL_BALANCES_N1
3786: -- SORT AGGREGATE
3787: -- NESTED LOOPS
3788: -- NESTED LOOPS
3789: -- TABLE ACCESS BY ROWID GL_BC_PACKETS
3790: -- INDEX RANGE SCAN GL_BC_PACKETS_N1
3791: -- INDEX RANGE SCAN GL_BC_PERIOD_MAP_U2
3792: -- TABLE ACCESS BY ROWID GL_BALANCES
3793: -- INDEX RANGE SCAN GL_BALANCES_N1
3794: --
3826:
3827: -- Bugfix 2231059
3828:
3829: update
3830: gl_bc_packets bp
3831: set (bp.budget_posted_balance,
3832: bp.actual_posted_balance,
3833: bp.encumbrance_posted_balance) =
3834: (
3958:
3959:
3960: -- =========================== FND LOG ===========================
3961: psa_utils.debug_other_string(g_state_level,l_full_path,
3962: ' Update posted balance in gl_bc_packets ' || SQL%ROWCOUNT );
3963: -- ========================= FND LOG =============================
3964:
3965: -- Commit to release Lock on gl_bc_dual
3966: -- commit; Commented for Bug 7476309
4117: 'P38', 1,
4118: 'P39', 1))), 0, 'S', 1, 'A'),
4119: count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
4120: 'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
4121: from gl_bc_packets bp
4122: where bp.packet_id = g_packet_id
4123: and bp.template_id is null;
4124:
4125: l_ret_code gl_bc_packets.result_code%type;
4121: from gl_bc_packets bp
4122: where bp.packet_id = g_packet_id
4123: and bp.template_id is null;
4124:
4125: l_ret_code gl_bc_packets.result_code%type;
4126:
4127: -- Bug 5571064 .. Start
4128:
4129: CURSOR c_get_failed_distributions(p_packet_id IN NUMBER) IS
4127: -- Bug 5571064 .. Start
4128:
4129: CURSOR c_get_failed_distributions(p_packet_id IN NUMBER) IS
4130: SELECT distinct bc.source_distribution_id_num_1
4131: FROM gl_bc_packets bc
4132: WHERE bc.packet_id = p_packet_id
4133: AND bc.result_code like 'F%';
4134:
4135: TYPE source_dist_id_num_1_tbl_type IS TABLE OF gl_bc_packets.source_distribution_id_num_1%type INDEX BY binary_integer;
4131: FROM gl_bc_packets bc
4132: WHERE bc.packet_id = p_packet_id
4133: AND bc.result_code like 'F%';
4134:
4135: TYPE source_dist_id_num_1_tbl_type IS TABLE OF gl_bc_packets.source_distribution_id_num_1%type INDEX BY binary_integer;
4136:
4137: l_source_dist_id_num_1_tbl source_dist_id_num_1_tbl_type;
4138:
4139: -- Bug 5571064 .. End
4150: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFRC - START' );
4151: -- ========================= FND LOG =============================
4152:
4153: -- Update Result Code for all transactions in Packet
4154: update gl_bc_packets bp
4155: set bp.result_code =
4156: decode(bp.actual_flag || decode(bp.currency_code, g_func_curr_code,
4157: null, '1'), 'B1', 'P03',
4158: decode(bp.actual_flag || decode(bp.budget_version_id,
4260:
4261:
4262:
4263: -- =========================== FND LOG ===========================
4264: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update Result Code gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
4265: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_fcmode -> ' || g_fcmode);
4266: -- ========================= FND LOG =============================
4267:
4268: /* -------------------------------------------------------------------+
4272: +-----------------------------------------------------------------*/
4273:
4274: if(nvl(g_enable_efc_flag,'N')='Y') THEN
4275:
4276: UPDATE gl_bc_packets bp
4277: set result_code='F78'
4278: WHERE bp.packet_id = g_packet_id
4279: AND bp.result_code like 'F%'
4280: AND bp.funding_budget_version_id IS NULL
4300:
4301: -- Update Result Code for Detail Transactions when Summary Transactions
4302: -- fail Funds Check and Checking is Absolute
4303:
4304: update gl_bc_packets bp
4305: set bp.result_code =
4306: decode(bp.account_category_code || substr(bp.result_code, 1, 1),
4307: 'PP', 'F01', 'PF', 'F04', 'BP', 'F11', 'BF', 'F14')
4308: where bp.packet_id = g_packet_id
4313: (
4314: select
4315:
4316: 'Summary Row exists and fails Funds Check; Absolute'
4317: from gl_bc_packets pk,
4318: gl_account_hierarchies ah
4319: where ah.ledger_id = bp.ledger_id
4320: and ah.summary_code_combination_id = pk.code_combination_id
4321: and ah.detail_code_combination_id = bp.code_combination_id
4331: and pk.result_code in ('F02', 'F12')
4332: );
4333:
4334: -- =========================== FND LOG ===========================
4335: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
4336: -- ========================= FND LOG =============================
4337:
4338: -- Update Result Code for Detail Transactions when Summary Transactions
4339: -- fail Funds Check and Checking is Advisory
4337:
4338: -- Update Result Code for Detail Transactions when Summary Transactions
4339: -- fail Funds Check and Checking is Advisory
4340:
4341: update gl_bc_packets bp
4342: set bp.result_code =
4343: decode(bp.account_category_code, 'P', 'P22', 'B', 'P27')
4344: where bp.packet_id = g_packet_id
4345: and bp.template_id is null
4349: select
4350:
4351: 'Summary Row exists and fails Funds Check; Advisory'
4352: from gl_account_hierarchies ah,
4353: gl_bc_packets pk
4354: where ah.ledger_id = bp.ledger_id
4355: and ah.summary_code_combination_id = pk.code_combination_id
4356: and ah.detail_code_combination_id = bp.code_combination_id
4357: and pk.packet_id = bp.packet_id
4366: and pk.result_code in ('P20', 'P25')
4367: );
4368:
4369: -- =========================== FND LOG ===========================
4370: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
4371: -- ========================= FND LOG =============================
4372:
4373: end if;
4374:
4382: -- =========================== FND LOG ===========================
4383: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_ussgl_option_flag -> TRUE');
4384: -- ========================= FND LOG =============================
4385:
4386: update gl_bc_packets bp
4387: set bp.result_code =
4388: decode(bp.ussgl_transaction_code, null,
4389: decode(bp.account_category_code, 'P', 'F06', 'B', 'F15'),
4390: 'F05')
4396: and exists
4397: (
4398: select 'One or more Proprietary/Budgetary counterparts of ' ||
4399: 'this transaction exists and fails Funds Check'
4400: from gl_bc_packets pk
4401: where pk.packet_id = g_packet_id
4402: and pk.template_id is null
4403: and pk.result_code like 'F%'
4404: and (pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
4405: or pk.ussgl_link_to_parent_id in (bp.ussgl_link_to_parent_id, bp.ussgl_parent_id))
4406: );
4407:
4408: -- =========================== FND LOG ===========================
4409: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 4 updated -> ' || SQL%ROWCOUNT || ' rows');
4410: -- ========================= FND LOG =============================
4411:
4412: end if;
4413:
4439: -- ========================= FND LOG ===========================
4440: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside Ist IF ');
4441: -- ========================= FND LOG ===========================
4442:
4443: update gl_bc_packets bp
4444: set bp.result_code = 'P23'
4445: where bp.packet_id = g_packet_id
4446: and bp.result_code like 'F%'
4447: and bp.template_id is not null;
4446: and bp.result_code like 'F%'
4447: and bp.template_id is not null;
4448:
4449: -- ========================= FND LOG ===========================
4450: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 5 updated -> ' || SQL%ROWCOUNT || ' rows');
4451: -- ========================= FND LOG ===========================
4452:
4453: end if;
4454:
4451: -- ========================= FND LOG ===========================
4452:
4453: end if;
4454:
4455: update gl_bc_packets bp
4456: set bp.status_code = decode(bp.status_code || l_ret_code,
4457: 'PF', 'R',
4458: 'CF', 'F',
4459: decode(bp.status_code || substr(bp.result_code, 1, 1),
4464: bp.last_update_date = sysdate
4465: where bp.packet_id = g_packet_id;
4466:
4467: -- ========================= FND LOG ===========================
4468: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 6 updated -> ' || SQL%ROWCOUNT || ' rows');
4469: -- ========================= FND LOG ===========================
4470:
4471: END IF;
4472:
4477: OPEN c_get_failed_distributions (g_packet_id);
4478: FETCH c_get_failed_distributions bulk collect into l_source_dist_id_num_1_tbl;
4479:
4480: FORALL I IN 1..l_source_dist_id_num_1_tbl.count
4481: UPDATE gl_bc_packets pk
4482: SET result_code ='F77'
4483: WHERE pk.packet_id = g_packet_id
4484: AND pk.source_distribution_id_num_1 = l_source_dist_id_num_1_tbl(I)
4485: AND pk.result_code like 'P%';
4485: AND pk.result_code like 'P%';
4486:
4487: -- ========================= FND LOG ===========================
4488: psa_utils.debug_other_string(g_state_level,l_full_path,
4489: ' update gl_bc_packets 6.1, result_code to F77 for same packet and same distribution updated -> ' || SQL%ROWCOUNT || ' rows');
4490: -- ========================= FND LOG ===========================
4491:
4492: CLOSE c_get_failed_distributions;
4493:
4491:
4492: CLOSE c_get_failed_distributions;
4493:
4494: -- Bug 3553142
4495: -- If there is an advisory warning on any row in gl_bc_packets, all passed rows should indicate
4496: -- that one or more related lines have advisory warnings.
4497: -- Created 2 new LOOKUP_CODEs P12, P17
4498:
4499: UPDATE gl_bc_packets pk
4495: -- If there is an advisory warning on any row in gl_bc_packets, all passed rows should indicate
4496: -- that one or more related lines have advisory warnings.
4497: -- Created 2 new LOOKUP_CODEs P12, P17
4498:
4499: UPDATE gl_bc_packets pk
4500: SET result_code = 'P12'
4501: WHERE pk.packet_id = g_packet_id
4502: AND result_code = 'P10'
4503: AND exists (SELECT 'x'
4500: SET result_code = 'P12'
4501: WHERE pk.packet_id = g_packet_id
4502: AND result_code = 'P10'
4503: AND exists (SELECT 'x'
4504: FROM gl_bc_packets bc
4505: WHERE bc.packet_id = pk.packet_id
4506: AND bc.result_code = 'P20');
4507:
4508: -- ========================= FND LOG ===========================
4505: WHERE bc.packet_id = pk.packet_id
4506: AND bc.result_code = 'P20');
4507:
4508: -- ========================= FND LOG ===========================
4509: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 7 updated -> ' || SQL%ROWCOUNT || ' rows');
4510: -- ========================= FND LOG ===========================
4511:
4512:
4513: UPDATE gl_bc_packets pk
4509: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 7 updated -> ' || SQL%ROWCOUNT || ' rows');
4510: -- ========================= FND LOG ===========================
4511:
4512:
4513: UPDATE gl_bc_packets pk
4514: SET result_code = 'P17'
4515: WHERE pk.packet_id = g_packet_id
4516: AND result_code = 'P15'
4517: AND exists (SELECT 'x'
4514: SET result_code = 'P17'
4515: WHERE pk.packet_id = g_packet_id
4516: AND result_code = 'P15'
4517: AND exists (SELECT 'x'
4518: FROM gl_bc_packets bc
4519: WHERE bc.packet_id = pk.packet_id
4520: AND bc.result_code = 'P25');
4521:
4522: -- ========================= FND LOG ===========================
4519: WHERE bc.packet_id = pk.packet_id
4520: AND bc.result_code = 'P25');
4521:
4522: -- ========================= FND LOG ===========================
4523: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 8 updated -> ' || SQL%ROWCOUNT || ' rows');
4524: -- ========================= FND LOG ===========================
4525:
4526:
4527: -- =========================== FND LOG ===========================
4713: pa_stmt VARCHAR2(400);
4714: cur_pa INTEGER;
4715: ignore INTEGER;
4716:
4717: pa_retcode gl_bc_packets.result_code%TYPE;
4718: err_msg VARCHAR2(1024);
4719: err_stg VARCHAR2(1024);
4720:
4721: -- ========================= FND LOG ===========================
4811: -- ========================= FND LOG ===========================
4812:
4813: -- Update Result Code for Detail Transactions
4814:
4815: update gl_bc_packets bp
4816: set bp.result_code = 'P21'
4817: where bp.packet_id = g_packet_id
4818: and bp.result_code between 'F00' and 'F19'
4819: and bp.ussgl_link_to_parent_id is null
4824: (
4825: select 'If Partial Resv disallowed then all non-generated ' ||
4826: 'detail lines that failed with any validation errors ' ||
4827: 'or because of Funds Availability'
4828: from gl_bc_packets pk
4829: where pk.packet_id = g_packet_id
4830: and pk.template_id is null
4831: and pk.result_code like 'F%'
4832: and ((g_partial_resv_flag = 'N'
4838: and pk.result_code between 'F20' and 'F29'))
4839: );
4840:
4841: -- ========================= FND LOG ===========================
4842: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets1 -> ' || SQL%ROWCOUNT);
4843: -- ========================= FND LOG ===========================
4844:
4845: -- ========================= FND LOG ===========================
4846: psa_utils.debug_other_string(g_state_level,l_full_path, ' g_calling_prog_flag -> ' || g_calling_prog_flag);
4850: IF (g_calling_prog_flag = 'G') THEN
4851:
4852: -- Update Result Code for Generated Transactions
4853:
4854: update gl_bc_packets bp
4855: set bp.result_code = 'P26'
4856: where bp.packet_id = g_packet_id
4857: and bp.result_code between 'F00' and 'F19'
4858: and bp.ussgl_link_to_parent_id is not null
4858: and bp.ussgl_link_to_parent_id is not null
4859: and exists
4860: (
4861: select 'Corresp Original Transaction which was Overridden'
4862: from gl_bc_packets pk
4863: where pk.packet_id = g_packet_id
4864: and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
4865: and pk.result_code = 'P21'
4866: );
4867:
4868: END IF;
4869:
4870: -- ========================= FND LOG ===========================
4871: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets2 -> ' || SQL%ROWCOUNT);
4872: psa_utils.debug_other_string(g_state_level,l_full_path, ' RETURN -> TRUE ');
4873: -- ========================= FND LOG ===========================
4874:
4875: return(TRUE);
4957: 'P38', 1,
4958: 'P39', 1))), 0, 'S', 1, 'A'),
4959: count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
4960: 'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
4961: from gl_bc_packets bp
4962: where bp.packet_id = g_packet_id
4963: and bp.template_id is null;
4964:
4965: -- ========================= FND LOG ===========================
5021: -- Checking C (Checking) Pxx S (Passed Check)
5022: -- Checking C (Checking) Fxx F (Failed Check)
5023:
5024: if g_calling_prog_flag = 'G' then
5025: update gl_bc_packets bp
5026: set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
5027: 'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
5028: 'CF', 'F',
5029: decode(bp.status_code ||
5031: 'PF', 'R', 'CP', 'S', 'CF', 'F', 'T')),
5032: bp.last_update_date = sysdate
5033: where bp.packet_id = g_packet_id;
5034: else
5035: update gl_bc_packets bp
5036: set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
5037: 'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
5038: 'CF', 'F',
5039: decode(bp.status_code ||
5044: where bp.packet_id = g_packet_id;
5045: end if;
5046:
5047: -- ========================= FND LOG ===========================
5048: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
5049: -- ========================= FND LOG ===========================
5050:
5051: -- Update the Result Code of all Summary Transactions that fail Funds
5052: -- Reservation to 'P23', when each of their corresponding details got
5078: -- ========================= FND LOG ===========================
5079: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside Ist IF ');
5080: -- ========================= FND LOG ===========================
5081:
5082: update gl_bc_packets bp
5083: set bp.result_code = 'P23'
5084: where bp.packet_id = g_packet_id
5085: and bp.result_code like 'F%'
5086: and bp.template_id is not null;
5085: and bp.result_code like 'F%'
5086: and bp.template_id is not null;
5087:
5088: -- ========================= FND LOG ===========================
5089: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
5090: -- ========================= FND LOG ===========================
5091:
5092: end if;
5093:
5128: -- ========================= FND LOG ===========================
5129: psa_utils.debug_other_string(g_state_level,l_full_path, ' inside IInd IF ');
5130: -- ========================= FND LOG ===========================
5131:
5132: update gl_bc_packets bp
5133: set (bp.entered_dr,
5134: bp.entered_cr,
5135: bp.accounted_dr,
5136: bp.accounted_cr,
5144: nvl(max(pk.status_code), 'R'),
5145: decode(max(pk.status_code), null, bp.result_code,
5146: decode(bp.status_code, 'A', bp.result_code, 'P23'))
5147: from gl_account_hierarchies ah,
5148: gl_bc_packets pk
5149: where ah.ledger_id = g_ledger_id
5150: and ah.template_id = bp.template_id
5151: and ah.summary_code_combination_id = bp.code_combination_id
5152: and ah.detail_code_combination_id = pk.code_combination_id
5165: where bp.packet_id = g_packet_id
5166: and bp.template_id is not null;
5167:
5168: -- ========================= FND LOG ===========================
5169: psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
5170: -- ========================= FND LOG ===========================
5171:
5172: end;
5173: end if;
5228: -- If Standard Budgetary Control failed, CBC Journals shoud not be committed.
5229: -- This is achieved by calling function IGC_CBC_GL_FC_PKG.reconcile_glzcbc
5230: --
5231:
5232: FUNCTION glrchk(post_control IN gl_bc_packets.result_code%TYPE)
5233: RETURN BOOLEAN IS
5234:
5235: -- ========================= FND LOG ===========================
5236: l_full_path VARCHAR2(100);
5318:
5319: DECLARE
5320:
5321: gms_stmt VARCHAR2(400);
5322: gl_retcode gl_bc_packets.result_code%TYPE;
5323: gms_control gl_bc_packets.result_code%TYPE;
5324:
5325: BEGIN
5326: -- ========================= FND LOG ===========================
5319: DECLARE
5320:
5321: gms_stmt VARCHAR2(400);
5322: gl_retcode gl_bc_packets.result_code%TYPE;
5323: gms_control gl_bc_packets.result_code%TYPE;
5324:
5325: BEGIN
5326: -- ========================= FND LOG ===========================
5327: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLRCHK(GMS Reconcile) --> START ');
5484: -- Assumption :
5485: --
5486: -- This Module assumes that the packet being processed only includes
5487: -- Transactions from 1 single GL Journal Batch for performance reasons.
5488: -- This is how we populate gl_bc_packets currently in all GL Implementations
5489:
5490: FUNCTION glxfje RETURN BOOLEAN IS
5491:
5492: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5488: -- This is how we populate gl_bc_packets currently in all GL Implementations
5489:
5490: FUNCTION glxfje RETURN BOOLEAN IS
5491:
5492: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5493: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5494: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5495: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5496:
5489:
5490: FUNCTION glxfje RETURN BOOLEAN IS
5491:
5492: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5493: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5494: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5495: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5496:
5497:
5490: FUNCTION glxfje RETURN BOOLEAN IS
5491:
5492: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5493: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5494: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5495: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5496:
5497:
5498: cursor orig_bat is
5491:
5492: l_je_batch_id gl_bc_packets.je_batch_id%TYPE;
5493: l_gen_batch_id gl_bc_packets.je_batch_id%TYPE;
5494: l_actual_flag gl_bc_packets.actual_flag%TYPE;
5495: l_max_je_line_num gl_bc_packets.je_line_num%TYPE; -- bug 5139224
5496:
5497:
5498: cursor orig_bat is
5499: select max(bp.je_batch_id),
5498: cursor orig_bat is
5499: select max(bp.je_batch_id),
5500: max(bp.actual_flag),
5501: max(bp.je_line_num) -- bug 5139224
5502: from gl_bc_packets bp
5503: where bp.packet_id = g_packet_id
5504: and bp.template_id is null
5505: and bp.ussgl_link_to_parent_id is null;
5506:
5505: and bp.ussgl_link_to_parent_id is null;
5506:
5507: cursor generated_bat is
5508: select distinct bp.je_batch_id
5509: from gl_bc_packets bp
5510: where bp.packet_id = g_packet_id
5511: and bp.ussgl_link_to_parent_id is not null;
5512:
5513: cursor batch_id is
5657: BP.ussgl_link_to_parent_id,
5658: 'glxfje() generated: ' || g_packet_id
5659: from gl_period_statuses ps,
5660: gl_je_headers jh,
5661: gl_bc_packets bp
5662: where ps.application_id = 101
5663: and ps.ledger_id = g_ledger_id
5664: and ps.period_name = bp.period_name
5665: and jh.je_header_id = bp.je_header_id
5758: FROM
5759: GL_PERIOD_STATUSES PS,
5760: GL_JE_LINES JL,
5761: GL_JE_LINES JL1,
5762: GL_BC_PACKETS BP
5763: WHERE
5764: PS.application_id = 101
5765: AND PS.ledger_id = g_ledger_id
5766: AND PS.period_name = BP.period_name
5787: delete from gl_je_lines jl
5788: where jl.je_header_id in
5789: (
5790: select distinct bp.je_header_id
5791: from gl_bc_packets bp
5792: where bp.packet_id = g_packet_id
5793: and bp.ussgl_link_to_parent_id IS NOT NULL
5794: )
5795: and jl.reference_10 = 'glxfje() generated: ' || g_packet_id_ursvd;
5824: decode(g_fcmode, 'U', -1, 1),
5825: nvl(jh.running_total_accounted_cr, 0) +
5826: sum(nvl(bp.accounted_cr, 0)) *
5827: decode(g_fcmode, 'U', -1, 1)
5828: from gl_bc_packets bp
5829: where bp.packet_id = g_packet_id
5830: and bp.je_batch_id = jh.je_batch_id
5831: and bp.je_header_id = jh.je_header_id
5832: and bp.ussgl_link_to_parent_id is not null
5833: )
5834: where jh.je_header_id in
5835: (
5836: select distinct je_header_id
5837: from gl_bc_packets bp1
5838: where bp1.packet_id = g_packet_id
5839: and bp1.ussgl_link_to_parent_id is not null
5840: );
5841:
5869: nvl(jb.running_total_accounted_cr, 0) +
5870: sum(nvl(bp.accounted_cr, 0)) * decode(g_fcmode, 'U', -1, 1),
5871: decode(g_fcmode, 'U', 'R', 'P'),
5872: decode(g_fcmode, 'U', null, jb.packet_id)
5873: from gl_bc_packets bp
5874: where bp.packet_id = g_packet_id
5875: and bp.je_batch_id = jb.je_batch_id
5876: and bp.ussgl_link_to_parent_id is not null
5877: )
6001: min(jb.chart_of_accounts_id),
6002: min(jb.period_set_name),
6003: min(jb.accounted_period_type)
6004: from gl_period_statuses ps,
6005: gl_bc_packets bp,
6006: gl_je_batches jb
6007: where ps.application_id = 101
6008: and ps.ledger_id = g_ledger_id
6009: and ps.period_name = bp.period_name
6117: and jh.je_header_id = x.je_header_id
6118: and exists
6119: (
6120: select 'JE headers with associated generated transactions'
6121: from gl_bc_packets bp
6122: where bp.packet_id = g_packet_id
6123: and bp.je_batch_id = l_je_batch_id
6124: and bp.je_header_id = jh.je_header_id
6125: and bp.ussgl_link_to_parent_id is not null
6144: select sum(nvl(bp.entered_dr, 0)),
6145: sum(nvl(bp.entered_cr, 0)),
6146: sum(nvl(bp.accounted_dr, 0)),
6147: sum(nvl(bp.accounted_cr, 0))
6148: from gl_bc_packets bp
6149: where bp.packet_id = g_packet_id
6150: and bp.je_batch_id = l_je_batch_id
6151: and bp.je_header_id = to_number(jh.attribute1)
6152: and bp.ussgl_link_to_parent_id is not null
6203: ' ',
6204: 'Y',
6205: 'glxfje() generated: ' || g_packet_id
6206: from gl_je_headers jh,
6207: gl_bc_packets bp
6208: where jh.je_batch_id = l_gen_batch_id
6209: and jh.attribute1 = to_char(bp.je_header_id)
6210: and bp.packet_id = g_packet_id
6211: and bp.ussgl_link_to_parent_id is not null;
6277: decode(min(JL.context),min(JL.context3),null,min(JL.attribute9)),
6278: decode(min(JL.context),min(JL.context3),null,min(JL.attribute10))
6279: FROM
6280: GL_JE_HEADERS JH,
6281: GL_BC_PACKETS BP1,
6282: GL_BC_PACKETS BP,
6283: GL_JE_LINES JL
6284: WHERE
6285: JH.je_batch_id = l_gen_batch_id
6278: decode(min(JL.context),min(JL.context3),null,min(JL.attribute10))
6279: FROM
6280: GL_JE_HEADERS JH,
6281: GL_BC_PACKETS BP1,
6282: GL_BC_PACKETS BP,
6283: GL_JE_LINES JL
6284: WHERE
6285: JH.je_batch_id = l_gen_batch_id
6286: AND JH.attribute1 = to_char(BP.je_header_id)
6306: -- Update je_batch_id of all associated generated transactions from the
6307: -- ID of the Originating Batch to that of the newly created batch so that
6308: -- posting deletes only these packet rows
6309:
6310: update gl_bc_packets bp
6311: set bp.je_batch_id = l_gen_batch_id
6312: where bp.packet_id = g_packet_id
6313: and bp.ussgl_link_to_parent_id is not null;
6314:
6313: and bp.ussgl_link_to_parent_id is not null;
6314:
6315: -- ========================= FND LOG ===========================
6316: psa_utils.debug_other_string(g_state_level,l_full_path,
6317: ' update gl_bc_packets - je_bacth_id - ' || SQL%ROWCOUNT);
6318: -- ========================= FND LOG ===========================
6319:
6320: -- Clean up attribute1 in gl_je_headers (contains header id of the
6321: -- originating line)
6350: where jl.je_header_id in
6351: (
6352: select distinct jh.je_header_id
6353: from gl_je_headers jh,
6354: gl_bc_packets bp
6355: where jh.je_batch_id = bp.je_batch_id
6356: and bp.packet_id = g_packet_id
6357: and bp.ussgl_link_to_parent_id is not null
6358: )
6368: delete from gl_je_headers jh
6369: where jh.je_batch_id in
6370: (
6371: select distinct bp.je_batch_id
6372: from gl_bc_packets bp
6373: where bp.packet_id = g_packet_id
6374: and bp.ussgl_link_to_parent_id is not null
6375: );
6376:
6450: -- to rollback Funds Reserved after the Funds Checker call. This must be
6451: -- called before any commit that would otherwise confirm the final Funds
6452: -- Check Status of the packet
6453:
6454: -- This Module deletes all transaction lines of a packet in gl_bc_packets and
6455: -- the associated Arrival Order record in gl_bc_packet_arrival_order
6456:
6457: -- This Module also deletes the corresponding records for a packet being
6458: -- Unreserved
6480: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFPP1 --> START ');
6481: -- ========================= FND LOG ===========================
6482:
6483: -- Delete Packet Transactions
6484: delete from gl_bc_packets bp
6485: where bp.packet_id in (p_packetid, p_packetid_ursvd);
6486:
6487: -- ========================= FND LOG ===========================
6488: psa_utils.debug_other_string(g_state_level,l_full_path,
6485: where bp.packet_id in (p_packetid, p_packetid_ursvd);
6486:
6487: -- ========================= FND LOG ===========================
6488: psa_utils.debug_other_string(g_state_level,l_full_path,
6489: ' delete from gl_bc_packets ' || SQL%ROWCOUNT);
6490: -- ========================= FND LOG ===========================
6491:
6492: -- Delete Packet Arrival Order Record
6493: delete from gl_bc_packet_arrival_order ao
6526: psa_utils.debug_other_string(g_state_level,l_full_path, ' GLXFPP2 --> START ');
6527: -- ========================= FND LOG ===========================
6528:
6529: -- Delete Packet Transactions
6530: delete from gl_bc_packets bp
6531: where bp.event_id = p_eventid;
6532:
6533: -- ========================= FND LOG ===========================
6534: psa_utils.debug_other_string(g_state_level,l_full_path,
6531: where bp.event_id = p_eventid;
6532:
6533: -- ========================= FND LOG ===========================
6534: psa_utils.debug_other_string(g_state_level,l_full_path,
6535: ' delete from gl_bc_packets ' || SQL%ROWCOUNT);
6536: -- ========================= FND LOG ===========================
6537:
6538: -- Delete Packet Arrival Order Record
6539: delete from gl_bc_packet_arrival_order ao
6537:
6538: -- Delete Packet Arrival Order Record
6539: delete from gl_bc_packet_arrival_order ao
6540: where ao.packet_id in (select packet_id
6541: from gl_bc_packets
6542: where event_id = p_eventid);
6543:
6544: -- ========================= FND LOG ===========================
6545: psa_utils.debug_other_string(g_state_level,l_full_path,
6546: ' delete from gl_bc_packet_arrival_order ' || SQL%ROWCOUNT);
6547: -- ========================= FND LOG ===========================
6548:
6549: -- Delete History Record
6550: delete from gl_bc_packets_hists bp
6551: where bp.event_id = p_eventid;
6552:
6553: -- ========================= FND LOG ===========================
6554: psa_utils.debug_other_string(g_state_level,l_full_path,
6551: where bp.event_id = p_eventid;
6552:
6553: -- ========================= FND LOG ===========================
6554: psa_utils.debug_other_string(g_state_level,l_full_path,
6555: ' delete from gl_bc_packets_hists ' || SQL%ROWCOUNT);
6556: -- ========================= FND LOG ===========================
6557:
6558: EXCEPTION
6559:
6598: psa_utils.debug_other_string(g_state_level,l_full_path, ' glxfuf -> START ');
6599: -- ========================= FND LOG ===========================
6600:
6601: -- Update Status Code for the Packet Transactions
6602: update gl_bc_packets bp
6603: set bp.status_code = 'T'
6604: where bp.packet_id = g_packet_id;
6605:
6606: -- ========================= FND LOG ===========================
6606: -- ========================= FND LOG ===========================
6607: psa_utils.debug_other_string(g_state_level,l_full_path,
6608: ' g_packet_id -> ' || g_packet_id );
6609: psa_utils.debug_other_string(g_state_level,l_full_path,
6610: ' update gl_bc_packets with T -> ' || SQL%ROWCOUNT );
6611: -- ========================= FND LOG ===========================
6612:
6613: -- Update Affect Funds Flag
6614: update gl_bc_packet_arrival_order ao
6734: AND status = 'VALID';
6735:
6736: CURSOR c_packet_count(c_packet_id IN NUMBER) is
6737: SELECT count(*) pkt_cnt
6738: FROM gl_bc_packets
6739: WHERE packet_id = c_packet_id;
6740:
6741: cursor c_batch_id (c_packet_id IN NUMBER) is
6742: SELECT 'Y' batch_id
6739: WHERE packet_id = c_packet_id;
6740:
6741: cursor c_batch_id (c_packet_id IN NUMBER) is
6742: SELECT 'Y' batch_id
6743: FROM GL_BC_PACKETS
6744: WHERE packet_id = c_packet_id
6745: AND je_batch_id IS NOT NULL
6746: AND rownum = 1;
6747:
6749: l_packet_count c_packet_count%rowtype;
6750: l_batch_id c_batch_id%rowtype;
6751:
6752: fv_prepay_stmt VARCHAR2(2000);
6753: p_packet_id gl_bc_packets.packet_id%type;
6754: p_status NUMBER(15);
6755: l_full_path VARCHAR2(100);
6756:
6757: BEGIN
7157: from dual
7158: where exists
7159: (
7160: select 'Associated Generated Row from existing GL Batch'
7161: from gl_bc_packets bp
7162: where bp.packet_id = g_packet_id
7163: and bp.je_batch_id is not null
7164: and bp.je_batch_id >= 0
7165: and bp.ussgl_transaction_code is not null
7335:
7336: /*=======================================================================+
7337: | Function : OPTIMIZE_PACKETS |
7338: | Description : Invoked by bc_optimizer rountine. This acts as a pvt |
7339: | function. Function deletes rows from gl_bc_packets and |
7340: | inserts them in gl_bc_packets_hists. Only rows with |
7341: | status_code R, S, F, T, P, C are deleted. |
7342: +=======================================================================*/
7343:
7336: /*=======================================================================+
7337: | Function : OPTIMIZE_PACKETS |
7338: | Description : Invoked by bc_optimizer rountine. This acts as a pvt |
7339: | function. Function deletes rows from gl_bc_packets and |
7340: | inserts them in gl_bc_packets_hists. Only rows with |
7341: | status_code R, S, F, T, P, C are deleted. |
7342: +=======================================================================*/
7343:
7344: PROCEDURE optimize_packets (p_ledger_id IN NUMBER, p_purge_days IN NUMBER) IS
7346: BEGIN
7347: l_full_path := g_path||'Optimize_Packets';
7348:
7349: /*-----------------------------------------------------------------------+
7350: | New criteria for deleting rows from gl_bc_packets is as below: |
7351: | |
7352: | Status_Code: |
7353: | =========== |
7354: | R, S, F, T - All rows for the p_ledger_id |
7351: | |
7352: | Status_Code: |
7353: | =========== |
7354: | R, S, F, T - All rows for the p_ledger_id |
7355: | - These rows should get inserted in gl_bc_packets_hists |
7356: | |
7357: | P, C - All rows for the p_ledger_id for which session has |
7358: | expired or which are older than 5 days (120 hours) |
7359: | - These rows should not be stored in gl_bc_packets_hists |
7355: | - These rows should get inserted in gl_bc_packets_hists |
7356: | |
7357: | P, C - All rows for the p_ledger_id for which session has |
7358: | expired or which are older than 5 days (120 hours) |
7359: | - These rows should not be stored in gl_bc_packets_hists |
7360: | |
7361: +----------------------------------------------------------------------*/
7362:
7363:
7360: | |
7361: +----------------------------------------------------------------------*/
7362:
7363:
7364: DELETE from gl_bc_packets Q
7365: where
7366: Q.status_code IN ('P', 'C')
7367: and ((((sysdate - Q.last_update_date)*24) > 48) OR
7368: (NOT EXISTS (SELECT 'x'
7370: WHERE audsid = Q.session_id
7371: and Serial# = Q.serial_id)));
7372:
7373: -- =========================== FND LOG ===========================
7374: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 1 deleted ' || SQL%ROWCOUNT || ' rows');
7375: -- ========================= FND LOG =============================
7376:
7377: LOOP -- Bug 10171221
7378:
7375: -- ========================= FND LOG =============================
7376:
7377: LOOP -- Bug 10171221
7378:
7379: DELETE from gl_bc_packets Q
7380: where
7381: Q.ledger_id = p_ledger_id
7382: and Q.status_code in ('R','S','F', 'T') -- Bug 10171221
7383: and rownum < 501 returning
7472: GROUP_ID
7473: bulk collect into g_bc_pkts_hist;
7474:
7475: -- =========================== FND LOG ===========================
7476: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 2 deleted ' || SQL%ROWCOUNT || ' rows');
7477: -- ========================= FND LOG =============================
7478:
7479: EXIT WHEN g_bc_pkts_hist.count = 0;
7480:
7478:
7479: EXIT WHEN g_bc_pkts_hist.count = 0;
7480:
7481: FORALL i IN 1..g_bc_pkts_hist.count
7482: insert into gl_bc_packets_hists
7483: values g_bc_pkts_hist(i);
7484:
7485: END LOOP; -- Bug 10171221
7486:
7544:
7545: /*=======================================================================+
7546: | Function : BC_OPTIMIZER |
7547: | Description : Invoked by SRS "Budgetary Control Optimizer" |
7548: | Optimize GL_BC_PACKETS DATA |
7549: | Delete unprocessed payables BC events |
7550: | Delete processed orphan payables BC events |
7551: +=======================================================================*/
7552:
7572: psa_utils.debug_other_string(g_state_level,l_path_name,'p_purge_days = '||p_purge_days);
7573: psa_utils.debug_other_string(g_state_level,l_path_name,'p_delete_mode = '||p_delete_mode);
7574: IF (NVL(p_delete_mode, 'B') IN ('B', 'P')) THEN
7575: optimize_packets(p_ledger_id, p_purge_days);
7576: psa_utils.debug_other_string(g_state_level,l_path_name,'Successfully optimized the gl_bc_packets data');
7577: END IF;
7578:
7579: IF (NVL(p_delete_mode, 'B') IN ('B', 'E')) THEN
7580: -- R12 upgrade date fetch to delete all unprocessed events from R12 installation date to sysdate
7660:
7661: /*=======================================================================+
7662: | Function : BC_PURGE_HIST |
7663: | Description : Invoked by SRS "Budgetary Control History Purge" |
7664: | Deletes rows from gl_bc_packets_hists depending upon |
7665: | the criteria selected by user while running SRS |
7666: +=======================================================================*/
7667:
7668: PROCEDURE bc_purge_hist (err_buf OUT NOCOPY VARCHAR2,
7687: 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'));
7688: -- ========================= FND LOG =============================
7689:
7690:
7691: l_stmt := 'delete from gl_bc_packets_hists '||
7692: 'where (last_update_date < :purge_date) '||
7693: ' and ledger_id = :p_ledger_id ';
7694:
7695: l_status_code := CASE p_purge_mode||p_purge_statuses
7721: END bc_purge_hist;
7722:
7723: /*=======================================================================+
7724: | Function : GET_PACKET_ID |
7725: | Description : Returns the next packet_id using gl_bc_packets_s seq |
7726: +=======================================================================*/
7727:
7728: FUNCTION get_packet_id RETURN NUMBER IS
7729: l_pkt_id gl_bc_packets.packet_id%type;
7725: | Description : Returns the next packet_id using gl_bc_packets_s seq |
7726: +=======================================================================*/
7727:
7728: FUNCTION get_packet_id RETURN NUMBER IS
7729: l_pkt_id gl_bc_packets.packet_id%type;
7730: l_full_path VARCHAR2(100);
7731: BEGIN
7732:
7733: l_full_path := g_path||'Get_Packet_Id';
7731: BEGIN
7732:
7733: l_full_path := g_path||'Get_Packet_Id';
7734:
7735: select gl_bc_packets_s.nextval into l_pkt_id
7736: from dual;
7737:
7738: -- =========================== FND LOG ===========================
7739: psa_utils.debug_other_string(g_state_level,l_full_path, ' l_pkt_id -> ' || l_pkt_id);
7743: END get_packet_id;
7744:
7745: /*=======================================================================+
7746: | Function : POPULATE_BC_PKTS |
7747: | Description : Inserts data in gl_bc_packets using the plsql table |
7748: | passed as parameter. Commits in autonomous mode. |
7749: +=======================================================================*/
7750:
7751: FUNCTION populate_bc_pkts (p_bc_pkts IN BC_PKTS_REC) RETURN BOOLEAN IS
7758: BEGIN
7759:
7760: l_full_path := g_path||'Populate_Bc_pkts';
7761:
7762: -- Now that plsql table is populated, insert data in gl_bc_packets.
7763:
7764: FORALL i IN 1..p_bc_pkts.count
7765: INSERT INTO gl_bc_packets
7766: VALUES p_bc_pkts(i);
7761:
7762: -- Now that plsql table is populated, insert data in gl_bc_packets.
7763:
7764: FORALL i IN 1..p_bc_pkts.count
7765: INSERT INTO gl_bc_packets
7766: VALUES p_bc_pkts(i);
7767:
7768: commit;
7769:
7917: l_source_name gl_je_headers.je_source%TYPE;
7918: l_period_name gl_je_batches.default_period_name%TYPE;
7919: l_je_batch_id gl_je_batches.je_batch_id%TYPE;
7920: l_batch_name gl_je_batches.name%TYPE;
7921: l_packet_id gl_bc_packets.packet_id%TYPE;
7922: l_main_stmt varchar2(4000);
7923: l_tmp_stmt varchar2(4000);
7924: l_action_stmt varchar2(4000);
7925: l_je_stmt varchar2(4000);
7934: l_date varchar2(50);
7935: l_header boolean;
7936: l_je_first boolean;
7937: l_ledger_id gl_automatic_posting_options.ledger_id%TYPE;
7938: l_session_id gl_bc_packets.session_id%type;
7939: l_serial_id gl_bc_packets.serial_id%type;
7940: l_seg_ccid varchar2(200);
7941: l_je_header_name gl_je_headers.name%TYPE;
7942: l_je_header_id gl_je_headers.je_header_id%TYPE;
7935: l_header boolean;
7936: l_je_first boolean;
7937: l_ledger_id gl_automatic_posting_options.ledger_id%TYPE;
7938: l_session_id gl_bc_packets.session_id%type;
7939: l_serial_id gl_bc_packets.serial_id%type;
7940: l_seg_ccid varchar2(200);
7941: l_je_header_name gl_je_headers.name%TYPE;
7942: l_je_header_id gl_je_headers.je_header_id%TYPE;
7943: l_je_line_num gl_je_lines.je_line_num%TYPE;
7943: l_je_line_num gl_je_lines.je_line_num%TYPE;
7944: l_entered_dr gl_je_lines.entered_dr%TYPE;
7945: l_entered_cr gl_je_lines.entered_cr%TYPE;
7946: l_line_description gl_lookups.description%TYPE;
7947: l_line_result_code gl_bc_packets.result_code%TYPE;
7948: l_ccid gl_je_lines.code_combination_id%TYPE;
7949: l_rowid varchar2(100);
7950: l_priority gl_lookups.meaning%TYPE;
7951: l_je_seg_stmt varchar2(4000);
7951: l_je_seg_stmt varchar2(4000);
7952: l_je_sum_flex varchar2(4000);
7953: l_je_bud_stmt varchar2(4000);
7954: l_je_bud_flex varchar2(4000);
7955: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7956: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7957: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7958: l_je_bud_desc gl_lookups.description%TYPE;
7959: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7952: l_je_sum_flex varchar2(4000);
7953: l_je_bud_stmt varchar2(4000);
7954: l_je_bud_flex varchar2(4000);
7955: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7956: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7957: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7958: l_je_bud_desc gl_lookups.description%TYPE;
7959: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7960: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7953: l_je_bud_stmt varchar2(4000);
7954: l_je_bud_flex varchar2(4000);
7955: l_je_bud_dr gl_bc_packets.entered_dr%TYPE;
7956: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7957: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7958: l_je_bud_desc gl_lookups.description%TYPE;
7959: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7960: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7961: l_je_bud_seg_stmt varchar2(4000);
7956: l_je_bud_cr gl_bc_packets.entered_cr%TYPE;
7957: l_je_bud_result_code gl_bc_packets.result_code%TYPE;
7958: l_je_bud_desc gl_lookups.description%TYPE;
7959: l_je_bud_ccid gl_code_combinations.code_combination_id%TYPE;
7960: l_ussgl_parent_id gl_bc_packets.ussgl_parent_id%TYPE;
7961: l_je_bud_seg_stmt varchar2(4000);
7962: l_je_bud_sum_flex varchar2(4000);
7963:
7964: -- XML variables
8198: -- =========================== XML OUT =============================
8199:
8200:
8201: -- Get the session_id and serial# for the current session
8202: -- These columns will then be inserted in gl_bc_packets.
8203:
8204: -- ====== FND LOG ======
8205: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking get_session_details() ');
8206: -- ====== FND LOG ======
8524: l_je_stmt := l_je_stmt||', SUBSTRB(h.name,1,20), h.je_header_id, l.je_line_num, l.entered_dr, '||
8525: 'l.entered_cr, lk.description, p.result_code, l.code_combination_id, '||
8526: 'p.rowid '||
8527: 'FROM gl_je_lines l, gl_je_headers h, gl_code_combinations c, '||
8528: 'gl_lookups lk, gl_bc_packets p '||
8529: 'WHERE p.je_batch_id = '||l_failed_bc_pkts(x).je_batch_id||
8530: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8531: ' and p.ledger_id = '||l_ledger_id||
8532: ' and p.je_header_id = h.je_header_id'||
8628:
8629: END LOOP; --End of segments loop
8630:
8631: l_je_seg_stmt := l_je_seg_stmt||' FROM gl_code_combinations c, gl_account_hierarchies h, '||
8632: ' gl_bc_packets p where h.detail_code_combination_id = '||
8633: l_ccid||' and p.code_combination_id = h.summary_code_combination_id '||
8634: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8635: ' and c.code_combination_id = p.code_combination_id';
8636:
8690: nvl(ussgl_parent_id, 0)
8691: INTO
8692: l_ussgl_parent_id
8693: FROM
8694: gl_bc_packets
8695: WHERE
8696: rowid = l_rowid;
8697: EXCEPTION
8698: WHEN OTHERS THEN
8696: rowid = l_rowid;
8697: EXCEPTION
8698: WHEN OTHERS THEN
8699: -- =========================== FND LOG ===========================
8700: psa_utils.debug_other_string(g_excep_level,l_full_path, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8701: fnd_file.put_line(fnd_file.log, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8702: -- ========================= FND LOG =============================
8703: END;
8704:
8697: EXCEPTION
8698: WHEN OTHERS THEN
8699: -- =========================== FND LOG ===========================
8700: psa_utils.debug_other_string(g_excep_level,l_full_path, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8701: fnd_file.put_line(fnd_file.log, 'Funds C/R: Failed to fetch ussgl_parent_id from gl_bc_packets');
8702: -- ========================= FND LOG =============================
8703: END;
8704:
8705: l_je_bud_stmt := l_je_bud_stmt||' , p.entered_dr, p.entered_cr, p.result_code, l.description, '||
8702: -- ========================= FND LOG =============================
8703: END;
8704:
8705: l_je_bud_stmt := l_je_bud_stmt||' , p.entered_dr, p.entered_cr, p.result_code, l.description, '||
8706: ' c.code_combination_id FROM gl_code_combinations c, gl_bc_packets p, '||
8707: ' gl_lookups l WHERE p.ussgl_link_to_parent_id = '||l_ussgl_parent_id||
8708: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8709: ' and c.code_combination_id = p.code_combination_id'||
8710: ' and p.result_code between ''F00'' AND ''F30'' and '||
8765:
8766: END LOOP; --End of segments loop
8767:
8768: l_je_bud_seg_stmt := l_je_bud_seg_stmt||' FROM gl_code_combinations c, gl_account_hierarchies h, '||
8769: ' gl_bc_packets p where h.detail_code_combination_id = '||
8770: l_je_bud_ccid||' and p.code_combination_id = h.summary_code_combination_id '||
8771: ' and p.packet_id = '||l_failed_bc_pkts(x).packet_id||
8772: ' and c.code_combination_id = p.code_combination_id';
8773:
8883: l_full_path VARCHAR2(100);
8884: BEGIN
8885: l_full_path := g_path||'Glsibc';
8886:
8887: INSERT INTO GL_BC_PACKETS
8888: (packet_id,
8889: ledger_id,
8890: je_source_name,
8891: je_category_name,
8965: min(BP.serial_id),
8966: min(BP.application_id)
8967: FROM
8968: GL_ACCOUNT_HIERARCHIES AH,
8969: GL_BC_PACKETS BP,
8970: GL_BC_PACKET_ARRIVAL_ORDER AO,
8971: GL_SUMMARY_TEMPLATES ST,
8972: GL_SUMMARY_BC_OPTIONS SB,
8973: GL_BUDGETS B,
9022: HAVING
9023: sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
9024:
9025: -- =========================== FND LOG ===========================
9026: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9027: -- ========================= FND LOG =============================
9028:
9029: END glsibc;
9030:
9043:
9044: -- =========================== FND LOG ===========================
9045: fnd_file.put_line(fnd_file.log, 'p_curr_temp_id = '||p_curr_temp_id);
9046: fnd_file.put_line(fnd_file.log, 'p_ledger_id = '||p_ledger_id);
9047: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Inserting into GL_BC_PACKETS ...');
9048: -- =========================== FND LOG =============================
9049:
9050: INSERT INTO GL_BC_PACKETS
9051: (packet_id,
9046: fnd_file.put_line(fnd_file.log, 'p_ledger_id = '||p_ledger_id);
9047: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Inserting into GL_BC_PACKETS ...');
9048: -- =========================== FND LOG =============================
9049:
9050: INSERT INTO GL_BC_PACKETS
9051: (packet_id,
9052: ledger_id,
9053: je_source_name,
9054: je_category_name,
9114: min(BP.serial_id),
9115: min(BP.application_id)
9116: FROM
9117: GL_ACCOUNT_HIERARCHIES AH,
9118: GL_BC_PACKETS BP,
9119: GL_BC_PACKET_ARRIVAL_ORDER AO,
9120: GL_SUMMARY_TEMPLATES ST,
9121: GL_SUMMARY_BC_OPTIONS SB,
9122: GL_BUDGETS B,
9137: AND AO.affect_funds_flag = 'Y'
9138: AND ST.template_id = p_curr_temp_id
9139: AND NOT EXISTS
9140: ( Select 'Y'
9141: From GL_BC_PACKETS BP2
9142: Where BP2.ledger_id = p_ledger_id
9143: And BP2.template_id = p_curr_temp_id
9144: And BP2.code_combination_id = AH.summary_code_combination_id
9145: And BP2.packet_id = BP.packet_id
9187: HAVING
9188: sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
9189:
9190: -- =========================== FND LOG ===========================
9191: psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9192: -- ========================= FND LOG =============================
9193:
9194:
9195: -- =========================== FND LOG ===========================
9192: -- ========================= FND LOG =============================
9193:
9194:
9195: -- =========================== FND LOG ===========================
9196: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9197: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9198: -- =========================== FND LOG =============================
9199:
9200: UPDATE gl_bc_packets bp2
9193:
9194:
9195: -- =========================== FND LOG ===========================
9196: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9197: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9198: -- =========================== FND LOG =============================
9199:
9200: UPDATE gl_bc_packets bp2
9201: SET (entered_dr, entered_cr, accounted_dr, accounted_cr,
9196: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9197: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Updating GL_BC_PACKETS ...');
9198: -- =========================== FND LOG =============================
9199:
9200: UPDATE gl_bc_packets bp2
9201: SET (entered_dr, entered_cr, accounted_dr, accounted_cr,
9202: effect_on_funds_code) =
9203: (SELECT SUM (NVL (bp.entered_dr, 0)), SUM (NVL (bp.entered_cr,0)),
9204: SUM (NVL (bp.accounted_dr, 0)),
9228: -1, 'D',
9229: 'I'
9230: )
9231: )
9232: FROM gl_bc_packets bp,
9233: gl_account_hierarchies ah,
9234: gl_bc_packet_arrival_order ao,
9235: gl_summary_templates st,
9236: gl_summary_bc_options sb,
9286: FROM gl_code_combinations
9287: WHERE template_id = p_curr_temp_id);
9288:
9289: -- =========================== FND LOG ===========================
9290: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9291: -- ========================= FND LOG =============================
9292:
9293:
9294:
9296: | The Delete statement will be executed here ALWAYS |
9297: +---------------------------------------------------*/
9298:
9299: -- =========================== FND LOG ===========================
9300: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Deleting from GL_BC_PACKETS ...');
9301: -- =========================== FND LOG =============================
9302:
9303:
9304: DELETE FROM gl_bc_packets bp
9300: fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Deleting from GL_BC_PACKETS ...');
9301: -- =========================== FND LOG =============================
9302:
9303:
9304: DELETE FROM gl_bc_packets bp
9305: WHERE bp.ledger_id = p_ledger_id
9306: AND bp.template_id = p_curr_temp_id
9307: AND bp.packet_id IN (
9308: SELECT ao.packet_id
9316: AND ah.template_id = p_curr_temp_id
9317: AND ah.summary_code_combination_id = bp.code_combination_id);
9318:
9319: -- =========================== FND LOG ===========================
9320: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9321: -- ========================= FND LOG =============================
9322:
9323: -- =========================== FND LOG ===========================
9324: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9320: psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9321: -- ========================= FND LOG =============================
9322:
9323: -- =========================== FND LOG ===========================
9324: fnd_file.put_line(fnd_file.log, 'GL_BC_PACKETS');
9325: -- =========================== FND LOG =============================
9326:
9327: END glsfbc;
9328:
9371: l_full_path VARCHAR2(100);
9372: BEGIN
9373: l_full_path := g_path||'Populate_Group_Id';
9374:
9375: UPDATE gl_bc_packets
9376: SET group_id = p_grp_id,
9377: je_batch_name = p_je_batch_name
9378: WHERE ae_header_id IN (SELECT ae_header_id
9379: FROM xla_ae_headers
9380: WHERE group_id = p_grp_id
9381: and application_id = p_application_id);
9382:
9383: -- =========================== FND LOG ===========================
9384: psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
9385: -- ========================= FND LOG =============================
9386:
9387: END populate_group_id;
9388:
9720: p_event_id => events_rec.event_id
9721: );
9722: p_return_code := 'Y';
9723: END LOOP;
9724: ELSIF (p_error_type = 'GL_BC_PACKETS_EMPTY') THEN
9725: psa_utils.debug_other_string(g_state_level,l_full_path, 'Checking for Events Not Processed');
9726: FOR events_rec IN (SELECT *
9727: FROM xla_events_gt e
9728: WHERE NOT EXISTS (SELECT 1
9728: WHERE NOT EXISTS (SELECT 1
9729: FROM xla_psa_bc_lines_v l
9730: WHERE l.event_id = e.event_id)) LOOP
9731: l_message := 'Event '||events_rec.event_id||' is not processed.';
9732: Fnd_message.set_name('PSA','PSA_GL_BC_PACKETS_EMPTY');
9733: Fnd_Message.Set_Token('PARAM_NAME',l_message);
9734: psa_bc_xla_pvt.psa_xla_error
9735: (
9736: p_message_code => 'PSA_GL_BC_PACKETS_EMPTY',
9732: Fnd_message.set_name('PSA','PSA_GL_BC_PACKETS_EMPTY');
9733: Fnd_Message.Set_Token('PARAM_NAME',l_message);
9734: psa_bc_xla_pvt.psa_xla_error
9735: (
9736: p_message_code => 'PSA_GL_BC_PACKETS_EMPTY',
9737: p_event_id => events_rec.event_id
9738: );
9739: p_return_code := 'Y';
9740: END LOOP;
9750:
9751: FUNCTION budgetary_control (p_ledgerid IN NUMBER,
9752: p_return_code OUT NOCOPY VARCHAR2) return BOOLEAN IS
9753:
9754: l_session_id gl_bc_packets.session_id%type;
9755: l_serial_id gl_bc_packets.serial_id%type;
9756:
9757: l_packet_id gl_bc_packets.packet_id%type;
9758: l_bc_pkts bc_pkts_rec;
9751: FUNCTION budgetary_control (p_ledgerid IN NUMBER,
9752: p_return_code OUT NOCOPY VARCHAR2) return BOOLEAN IS
9753:
9754: l_session_id gl_bc_packets.session_id%type;
9755: l_serial_id gl_bc_packets.serial_id%type;
9756:
9757: l_packet_id gl_bc_packets.packet_id%type;
9758: l_bc_pkts bc_pkts_rec;
9759: l_packets num_rec;
9753:
9754: l_session_id gl_bc_packets.session_id%type;
9755: l_serial_id gl_bc_packets.serial_id%type;
9756:
9757: l_packet_id gl_bc_packets.packet_id%type;
9758: l_bc_pkts bc_pkts_rec;
9759: l_packets num_rec;
9760: l_ret_code VARCHAR2(1);
9761: l_bc_ret_code VARCHAR2(1);
9765: l_p_status_cnt NUMBER(5);
9766: l_t_status_cnt NUMBER(5);
9767: l_je_source_name xla_subledgers.je_source_name%type;
9768: invalid_je_source_name EXCEPTION;
9769: gl_bc_packets_empty EXCEPTION;
9770: l_xla_return_code VARCHAR2(1);
9771:
9772:
9773: -- ========================= FND LOG ===========================
9785: sum(decode(status_code, 'S', 1, 0)) success_cnt,
9786: sum(decode(status_code, 'A', 1, 0)) approved_cnt,
9787: sum(decode(status_code, 'F', 1, 0)) failed_cnt,
9788: sum(decode(status_code, 'R', 1, 0)) rejected_cnt
9789: FROM gl_bc_packets
9790: WHERE packet_id = p_packet_id
9791: GROUP BY ae_header_id;
9792:
9793: CURSOR get_ledger_category (p_ledgerid IN NUMBER) IS
10031: IS
10032: SELECT 'Funds Failure for hierarchy'
10033: FROM DUAL
10034: WHERE EXISTS
10035: (SELECT 'X' FROM GL_BC_PACKETS
10036: WHERE (ae_header_id, ae_line_num, event_id)
10037: IN (select ae_header_id, ae_line_num, event_id
10038: from psa_bc_alloc_gt
10039: where hierarchy_id = p_hierarchy_id
10074: 'Y', 'P',
10075: 'F'
10076: )
10077: )
10078: FROM gl_bc_packets bp
10079: WHERE bp.packet_id = p_packet_id AND bp.template_id IS NULL;
10080:
10081: -- Bug 5397349 .. Start
10082: CURSOR c_get_result_codes (p_packet_id IN NUMBER) IS
10080:
10081: -- Bug 5397349 .. Start
10082: CURSOR c_get_result_codes (p_packet_id IN NUMBER) IS
10083: SELECT result_code, ae_header_id, ae_line_num
10084: FROM gl_bc_packets
10085: WHERE packet_id = p_packet_id;
10086:
10087: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
10088: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
10083: SELECT result_code, ae_header_id, ae_line_num
10084: FROM gl_bc_packets
10085: WHERE packet_id = p_packet_id;
10086:
10087: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
10088: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
10089: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
10090:
10091: l_result_code_tbl result_code_tbl_type;
10084: FROM gl_bc_packets
10085: WHERE packet_id = p_packet_id;
10086:
10087: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
10088: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
10089: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
10090:
10091: l_result_code_tbl result_code_tbl_type;
10092: l_xla_hdr_tbl xla_hdr_tbl_type;
10085: WHERE packet_id = p_packet_id;
10086:
10087: TYPE result_code_tbl_type IS TABLE OF gl_bc_packets.result_code%type INDEX BY binary_integer;
10088: TYPE xla_hdr_tbl_type IS TABLE OF gl_bc_packets.ae_header_id%type INDEX BY binary_integer;
10089: TYPE xla_line_tbl_type IS TABLE OF gl_bc_packets.ae_line_num%type INDEX BY binary_integer;
10090:
10091: l_result_code_tbl result_code_tbl_type;
10092: l_xla_hdr_tbl xla_hdr_tbl_type;
10093: l_xla_line_tbl xla_line_tbl_type;
10230:
10231: l_packets := num_rec();
10232:
10233: -- Get the session_id and serial# for the current session
10234: -- These columns will then be inserted in gl_bc_packets.
10235:
10236: -- ====== FND LOG ======
10237: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking get_session_details() ');
10238: -- ====== FND LOG ======
10423: --=================== Allocation Attributes first level of validation Logic End ===========================
10424:
10425:
10426: -- Now select the event_id and other information to be inserted
10427: -- in gl_bc_packets in plsql table. We will select all event_id
10428: -- to be inserted at one go for performance reasons. I have selected all columns
10429: -- from gl_bc_packets and put NULL for columns which should not be populated. This is to
10430: -- overcome a limitation with FORALL clause later in the code.
10431:
10425:
10426: -- Now select the event_id and other information to be inserted
10427: -- in gl_bc_packets in plsql table. We will select all event_id
10428: -- to be inserted at one go for performance reasons. I have selected all columns
10429: -- from gl_bc_packets and put NULL for columns which should not be populated. This is to
10430: -- overcome a limitation with FORALL clause later in the code.
10431:
10432:
10433: -- ====== FND LOG ======
10545: ORDER BY xv.entity_id, (nvl(entered_dr, 0)-nvl(entered_cr, 0)), source_distribution_id_num_1;
10546:
10547: IF SQL%NOTFOUND THEN
10548:
10549: check_for_xla_errors ('GL_BC_PACKETS_EMPTY', l_xla_return_code);
10550:
10551: OPEN get_ledger_category(p_ledgerid);
10552: FETCH get_ledger_category INTO l_ledger_category;
10553: CLOSE get_ledger_category;
10558: -- ====== FND LOG ======
10559:
10560: IF (l_ledger_category = 'PRIMARY') OR (psa_bc_xla_pvt.g_application_id = 602) THEN
10561: -- ==================== FND LOG ===========================
10562: 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');
10563: -- ==================== FND LOG ===========================
10564:
10565: raise gl_bc_packets_empty;
10566: ELSE
10561: -- ==================== FND LOG ===========================
10562: 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');
10563: -- ==================== FND LOG ===========================
10564:
10565: raise gl_bc_packets_empty;
10566: ELSE
10567:
10568: -- ====== FND LOG ======
10569: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Do not process for Secondary Ledger ');
10609: -- ====== FND LOG ======
10610:
10611: END LOOP;
10612:
10613: -- Insert autonomous procedure populate_bc_pkts to insert data in gl_bc_packets
10614:
10615: -- ====== FND LOG ======
10616: psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Invoking populate_bc_pkts() ');
10617: -- ====== FND LOG ======
10684: -- ====== FND LOG ======
10685:
10686: IF (y.success_cnt = y.total_cnt) OR (y.approved_cnt = y.total_cnt) THEN
10687: SELECT nvl(min('A'), 'S') into l_xla_hdr_status
10688: FROM gl_bc_packets
10689: WHERE packet_id = l_packets(i) and
10690: ae_header_id = y.ae_header_id and
10691: result_code IN ('P20', 'P22', 'P25', 'P27', 'P31', 'P35', 'P36', 'P37',
10692: 'P38', 'P39');
10716: ELSE
10717: UPDATE xla_ae_headers_gt
10718: SET funds_status_code = l_ret_code
10719: WHERE ae_header_id IN (SELECT ae_header_id
10720: FROM gl_bc_packets
10721: WHERE packet_id = l_packets(i)) and
10722: ledger_id = p_ledgerid;
10723:
10724: -- ====== FND LOG ======
10859: --================== Allocation Attributes second level of validation Logic start =================
10860: /* Now we need to check that the transaction rows which are put for funds operation,
10861: have been successfully processed by funds checker. If atleast one transaction row
10862: for a hierarchy fails funds operation, we will fail all the related transaction rows in
10863: GL_BC_PACKETS with the status F77. */
10864:
10865: IF (l_alloc_used = 'Y') THEN
10866: FOR h in c_get_hierarchy_id
10867: LOOP
10870: l_serial_id);
10871: FETCH c_chk_funds_hier INTO dummy;
10872: IF (c_chk_funds_hier%FOUND) THEN
10873: CLOSE c_chk_funds_hier;
10874: -- update the statuses of gl_bc_packets
10875: -- related rows to 'F' Failed or 'R' Rejected.
10876: UPDATE gl_bc_packets
10877: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10878: 'M', 'F',
10872: IF (c_chk_funds_hier%FOUND) THEN
10873: CLOSE c_chk_funds_hier;
10874: -- update the statuses of gl_bc_packets
10875: -- related rows to 'F' Failed or 'R' Rejected.
10876: UPDATE gl_bc_packets
10877: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
10878: 'M', 'F',
10879: 'R', 'R',
10880: 'P', 'R')
10888: AND session_id = l_session_id
10889: AND serial_id = l_serial_id;
10890: -- ====== FND LOG ======
10891: psa_utils.debug_other_string(g_state_level, l_full_path, 'BCTRL -> Updated '
10892: ||sql%rowcount||' rows of GL_BC_PACKETS with F77 status.');
10893: -- ====== FND LOG ======
10894:
10895: ELSE
10896: CLOSE c_chk_funds_hier;
10902: 'M', 'F',
10903: 'R', 'F',
10904: 'P', 'F')
10905: WHERE ae_header_id IN (SELECT ae_header_id
10906: FROM gl_bc_packets
10907: WHERE result_code = 'F77'
10908: AND session_id = l_session_id
10909: AND serial_id = l_serial_id) and
10910: ledger_id = p_ledgerid;
10915:
10916: UPDATE xla_validation_lines_gt vl
10917: SET vl.funds_status_code = 'F77'
10918: WHERE vl.ae_header_id IN (SELECT ae_header_id
10919: FROM gl_bc_packets
10920: WHERE result_code = 'F77'
10921: AND session_id = l_session_id
10922: AND serial_id = l_serial_id) and
10923: vl.ledger_id = p_ledgerid;
10983:
10984: return TRUE;
10985:
10986: EXCEPTION
10987: WHEN GL_BC_PACKETS_EMPTY THEN
10988: IF (NOT g_xla_debug) THEN
10989: -- ====== FND LOG ======
10990: psa_utils.debug_other_string(g_error_level,l_full_path, ' BCTRL -> XLA_AE_HEADERS_GT DUMP');
10991: psa_utils.debug_other_string(g_error_level,l_full_path, ' -------------------- ');
11081: select count(*) into l_var_1
11082: from xla_psa_bc_lines_v;
11083: -- ====== FND LOG ======
11084: psa_utils.debug_other_string(g_error_level,l_full_path, ' XLA_PSA_BC_LINES_V returns '||l_var_1||' rows. ');
11085: psa_utils.debug_other_string(g_error_level,l_full_path, ' Error: Populated 0 rows in gl_bc_packets.');
11086: psa_utils.debug_other_string(g_error_level,l_full_path, ' RETURN -> FALSE');
11087: -- ====== FND LOG ======
11088: END IF;
11089: p_return_code := 'T';
11112:
11113:
11114: CURSOR c_success_evt_exists IS
11115: SELECT 'Successful event exists in the current packet'
11116: FROM gl_bc_packets
11117: WHERE event_id IN (SELECT event_id
11118: FROM psa_bc_xla_events_gt
11119: )
11120: AND application_id = PSA_BC_XLA_PVT.g_application_id
11140: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> p_failed_evnt_array.COUNT: '|| p_failed_evnt_array.COUNT);
11141: -- ====== FND LOG ======
11142:
11143: FORALL i IN 1..p_failed_ldgr_array.COUNT
11144: UPDATE gl_bc_packets
11145: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
11146: 'M', 'F',
11147: 'R', 'R',
11148: 'P', 'R')
11154:
11155: l_f81_cnt := SQL%ROWCOUNT;
11156:
11157: -- ====== FND LOG ======
11158: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f81_cnt||
11159: ' rows to F81 status.');
11160: -- ====== FND LOG ======
11161:
11162:
11160: -- ====== FND LOG ======
11161:
11162:
11163: FORALL j IN 1..p_failed_evnt_array.COUNT
11164: UPDATE gl_bc_packets
11165: SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
11166: 'M', 'F',
11167: 'R', 'R',
11168: 'P', 'R')
11173:
11174: l_f82_cnt := SQL%ROWCOUNT;
11175:
11176: -- ====== FND LOG ======
11177: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f82_cnt||
11178: ' rows to F82 status.');
11179: -- ====== FND LOG ======
11180:
11181: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
11177: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f82_cnt||
11178: ' rows to F82 status.');
11179: -- ====== FND LOG ======
11180:
11181: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
11182: -- row/s to failure then there was a genuine failure in GL_BC_PACKETS
11183: -- and we should not overwrite that failure with XLA_ERROR for the event.
11184: -- We need to retain the original failure status reported and inform the same
11185: -- back to the calling transaction.
11178: ' rows to F82 status.');
11179: -- ====== FND LOG ======
11180:
11181: -- This check is to ensure that if we have not updated any GL_BC_PACKETS
11182: -- row/s to failure then there was a genuine failure in GL_BC_PACKETS
11183: -- and we should not overwrite that failure with XLA_ERROR for the event.
11184: -- We need to retain the original failure status reported and inform the same
11185: -- back to the calling transaction.
11186:
11213: UPDATE gl_bc_packet_arrival_order
11214: SET affect_funds_flag = 'N'
11215: WHERE affect_funds_flag = 'Y'
11216: AND packet_id IN ( SELECT packet_id
11217: FROM gl_bc_packets bc
11218: WHERE event_id IN ( SELECT event_id
11219: FROM psa_bc_xla_events_gt
11220: )
11221: AND application_id = PSA_BC_XLA_PVT.g_application_id