[Home] [Help]
2579: End of comment for bug fix 4625734 *****/
2580:
2581: -- ##########################################################################
2582: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2583: -- with gl_project_flag = 'G' to gl_interface
2584:
2585: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2586: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends
2587: -- ENC_CONTROL_ID and END_DATE for the relevant TIME_PERIOD_ID
2582: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2583: -- with gl_project_flag = 'G' to gl_interface
2584:
2585: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2586: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends
2587: -- ENC_CONTROL_ID and END_DATE for the relevant TIME_PERIOD_ID
2588: -- and GROUP_ID into the tie back procedure
2589: -- ##########################################################################
2590:
2736: FROM psp_enc_summary_lines
2737: WHERE group_id = l_group_id;
2738: -- End of changes for bug fix 4507892
2739: BEGIN
2740: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');
2741:
2742: l_created_by := fnd_global.user_id;
2743:
2744: -- gl_tie_tab.delete; Commented for bug fix 4625734
2766: END IF;
2767:
2768: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_type_id: ' || l_enc_type_id);
2769:
2770: SELECT gl_interface_control_s.nextval
2771: INTO l_group_id
2772: FROM dual;
2773:
2774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2792:
2793: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines');
2794:
2795:
2796: INSERT INTO gl_interface
2797: (status, set_of_books_id, accounting_date,
2798: currency_code, date_created, created_by,
2799: actual_flag, user_je_category_name, user_je_source_name,
2800: encumbrance_type_id, code_combination_id, entered_dr,
2847: AND pec.business_group_id = g_bg_id
2848: AND pec.set_of_books_id = g_sob_id
2849: AND pec.gl_phase = 'Summarize');*/
2850:
2851: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Number of records inserted into GL_INTERFACE: ' || SQL%ROWCOUNT);
2852:
2853: /***** Commented the following as part of bug fix 4625734
2854: OPEN enc_control_cur;
2855: LOOP
2930: ELSIF int_rec.dr_cr_flag = 'D' THEN
2931: l_ent_cr := NULL;
2932: l_ent_dr := int_rec.summary_amount;
2933: END IF;
2934: --insert into psp_stout values(3, 'before inserting into gl interface');
2935: -- insert into psp_stout values(4, 'sline id is '||to_char(int_rec.enc_summary_line_id));
2936:
2937:
2938: insert_into_gl_int(
2983: INT_REC.ATTRIBUTE30,
2984: L_RETURN_STATUS);
2985:
2986: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2987: --dbms_output.put_line('Insert into gl_interface failed');
2988: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2989: END IF;
2990:
2991: END LOOP; --int_cur loop
3024: FROM dual;
3025:
3026: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_int_run_id: ' || l_int_run_id);
3027:
3028: insert into gl_interface_control(
3029: je_source_name,
3030: status,
3031: interface_run_id,
3032: group_id,
3038: l_group_id,
3039: g_sob_id
3040: );
3041:
3042: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
3043: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
3044:
3045: req_id := fnd_request.submit_request(
3046: 'SQLGL',
3161: END LOOP;
3162: -- End of changes for bug fix 4625734
3163:
3164: /***** Commented the following for bug fix 4625734 as the distinct group is fetched as prt of bug fix 4625734
3165: --- added this wrapper LOOP on delete gl_interface for 2444657
3166: --- this is to ensure that all interface recs are purged, in case
3167: -- the previous Liquidation did not.
3168: for i in 1..gl_tie_tab.count
3169: loop
3167: -- the previous Liquidation did not.
3168: for i in 1..gl_tie_tab.count
3169: loop
3170: if gl_tie_tab(i).r_group_id is not null then
3171: delete gl_interface
3172: where group_id = gl_tie_tab(i).r_group_id
3173: and user_je_source_name = l_user_je_source
3174: and set_of_books_id = l_sob_id;
3175:
3187: End of comment for bug fix 4625734 *****/
3188:
3189: -- Introduced the following for bug fix 4625734
3190: FORALL recno IN 1..r_groups.group_id.COUNT
3191: DELETE gl_interface
3192: WHERE group_id = r_groups.group_id(recno)
3193: AND user_je_source_name = l_user_je_source
3194: AND set_of_books_id = g_sob_id;
3195:
3192: WHERE group_id = r_groups.group_id(recno)
3193: AND user_je_source_name = l_user_je_source
3194: AND set_of_books_id = g_sob_id;
3195:
3196: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted groups from gl_interface for which gl_enc_tie_back is complete');
3197: -- End of changes for bug fix 4625734
3198:
3199: COMMIT; -- moved commit below del stmnt for 2479579
3200:
3332: p_return_status OUT NOCOPY VARCHAR2
3333: ) IS
3334: CURSOR int_count_cur IS
3335: SELECT COUNT(1)
3336: FROM gl_interface
3337: WHERE user_je_source_name = 'OLD'
3338: AND set_of_books_id = p_set_of_books_id
3339: AND group_id = p_group_id;
3340:
3349:
3350: CURSOR gl_tie_back_reject_cur IS
3351: SELECT status,
3352: to_number(trim(substr(reference6,3))) --- 4072324
3353: FROM gl_interface
3354: WHERE user_je_source_name = 'OLD'
3355: AND set_of_books_id = p_set_of_books_id
3356: AND group_id = p_group_id;
3357: /***** Commented for bug fix 4625734
3480: -- l_award_id NUMBER(15);
3481: -- l_task_id NUMBER(15);
3482: -- l_status VARCHAR2(50);
3483: -- l_reference6 VARCHAR2(100);
3484: l_cnt_gl_interface NUMBER;
3485: -- l_enc_summary_line_id NUMBER(10);
3486: -- l_gl_project_flag VARCHAR2(1);
3487: -- l_suspense_ac_failed VARCHAR2(1) := 'N'; Commented for Enh. 2768298 Removal of suspense posting in Liquidation
3488: -- l_reversal_ac_failed VARCHAR2(1) := 'N';
3529: l_cnt NUMBER;
3530: -- Introduced the following for bug fix 4625734
3531: CURSOR int_count_cur IS
3532: SELECT COUNT(1)
3533: FROM gl_interface
3534: WHERE user_je_source_name = 'OLD'
3535: AND set_of_books_id = p_set_of_books_id
3536: AND group_id = p_group_id
3537: AND status = 'NEW';
3558: begin
3559: /***** Changed the following SELECT into CURSOR for bug fix 4625734
3560: select count(*)
3561: into l_cnt
3562: from gl_interface
3563: where user_je_source_name = 'OLD'
3564: and set_of_books_id = p_set_of_books_id
3565: and group_id = p_group_id
3566: and status = 'NEW';
3584: -- for some reason. Return FALSE in this case. So cleanup the tables and try to transfer
3585: -- again after summarization in the second pass.
3586: -- -------------------------------------------------------------------------------------------
3587:
3588: delete from gl_interface
3589: where user_je_source_name = 'OLD'
3590: and set_of_books_id = p_set_of_books_id
3591: and group_id = p_group_id;
3592:
3589: where user_je_source_name = 'OLD'
3590: and set_of_books_id = p_set_of_books_id
3591: and group_id = p_group_id;
3592:
3593: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3594:
3595: -- Introduced the following for bug fix 4625734
3596: OPEN superceded_line_id_cur;
3597: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
3647: IF (PROCESS_COMPLETE) THEN
3648:
3649: /***** Commented the following for bug fix 4625734
3650: SELECT count(*)
3651: INTO l_cnt_gl_interface
3652: FROM gl_interface
3653: WHERE user_je_source_name = 'OLD'
3654: AND set_of_books_id = p_set_of_books_id
3655: AND group_id = p_group_id;
3648:
3649: /***** Commented the following for bug fix 4625734
3650: SELECT count(*)
3651: INTO l_cnt_gl_interface
3652: FROM gl_interface
3653: WHERE user_je_source_name = 'OLD'
3654: AND set_of_books_id = p_set_of_books_id
3655: AND group_id = p_group_id;
3656: End of comment for bug fix 4625734 *****/
3655: AND group_id = p_group_id;
3656: End of comment for bug fix 4625734 *****/
3657:
3658: OPEN int_count_cur;
3659: FETCH int_count_cur INTO l_cnt_gl_interface;
3660: CLOSE int_count_cur;
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3663:
3658: OPEN int_count_cur;
3659: FETCH int_count_cur INTO l_cnt_gl_interface;
3660: CLOSE int_count_cur;
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3663:
3664: IF (l_cnt_gl_interface > 0) THEN
3665: OPEN gl_tie_back_reject_cur;
3666: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3660: CLOSE int_count_cur;
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3663:
3664: IF (l_cnt_gl_interface > 0) THEN
3665: OPEN gl_tie_back_reject_cur;
3666: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3667: CLOSE gl_tie_back_reject_cur;
3668:
3714: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3715: AND pesl.status_code = 'N');
3716:
3717: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3718: ELSIF (l_cnt_gl_interface = 0) THEN
3719: g_accepted_group_id := p_group_id;
3720:
3721: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_accepted_group_id: ' || g_accepted_group_id);
3722:
3751: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase, summ_gl_dr_amount, summ_gl_cr_amount in psp_enc_controls SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3752: END IF;
3753:
3754: /***** Commented the following for bug fix 4625734
3755: IF l_cnt_gl_interface > 0 THEN
3756:
3757: OPEN gl_tie_back_reject_cur;
3758: LOOP
3759: FETCH gl_tie_back_reject_cur INTO l_status,l_enc_ref;
4066: end if;
4067: END IF;
4068: * /
4069:
4070: ELSIF l_cnt_gl_interface = 0 THEN
4071: g_accepted_group_id := p_group_id; --- for 3477373
4072: --
4073: OPEN gl_tie_back_success_cur;
4074: LOOP
4178: END gl_enc_tie_back;
4179:
4180: /***** Commented the following procedure as part of bug fix 4625734
4181: --* ##########################################################################
4182: -- This procedure inserts data into gl_interface
4183: -- ##########################################################################
4184:
4185: PROCEDURE insert_into_gl_int (
4186: P_SET_OF_BOOKS_ID IN NUMBER,
4230: P_ATTRIBUTE30 IN VARCHAR2,
4231: P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
4232: BEGIN
4233:
4234: INSERT INTO GL_INTERFACE(
4235: STATUS,
4236: SET_OF_BOOKS_ID,
4237: ACCOUNTING_DATE,
4238: CURRENCY_CODE,
4330: P_ATTRIBUTE27,
4331: P_ATTRIBUTE28,
4332: P_ATTRIBUTE29,
4333: P_ATTRIBUTE30);
4334: --dbms_output.put_line('Insert into gl_interface successful.................');
4335:
4336: p_return_status := fnd_api.g_ret_sts_success;
4337:
4338: EXCEPTION
4336: p_return_status := fnd_api.g_ret_sts_success;
4337:
4338: EXCEPTION
4339: WHEN OTHERS THEN
4340: --dbms_output.put_line('Error while inserting into gl_interface..........');
4341: g_error_api_path := 'insert_into_gl_int:'||g_error_api_path;
4342: fnd_msg_pub.add_exc_msg('psp_enc_liq_tran','insert_into_gl_int');
4343: p_return_status := fnd_api.g_ret_sts_unexp_error;
4344: